0

We have a medium sized ecommerce website which is entirely custom built in using Java and MySQL database on AWS infrastructure. Over a period of past several years, our orders volume has grown substantially high and so the size of our database.

From past couple of months we are facing a problem of slow CRM (Admin application) especially reporting. These reports are primarily built around customers and our orders data. Few points to be noted about our system

  • We have two web apps - storefront & backend CRM application
  • Storefront is mostly cached so impact is not much visible there
  • CRM and storefront are connected to a single database server having a single schema
  • We have added several indexes in our customer, order and order_item tables to increase speed of queries used in reporting.
  • These three tables are mostly frequently used for reporting purposes. Also website keep adding new records in these tables when a new user registers or places order.
  • AWS RDS used as database server runs at average 35% CPU
  • If I run these queries directly on RDS using MySql workbench then also it is slow.

I want to understand, how could I possibly improve performance of our CRM application. What is the key area where do I need to work on which will have substantial impact.

  • Is it infrastructure? Better database server?
  • Optimization in queries needed?
  • Better indexing in tables needed?

Edit - Added sample query

For example, I am running following query on orders table to fetch orders count for a specific date. It takes around average 3 second of time to return data. I expect queries like this shouldn't take more than 1 second of time.

SELECT count(*) as Count FROM orders WHERE date(CONVERT_TZ(CreatedDate,'+00:00','+05:30')) = '2018-07-30' 
AND OrderStatusId IN (5,10,15) AND Deleted = 0

There are several such queries, which gets fired to build a report. Which eventually presents any report to user not less than 10-20 seconds

Edit - Added modified queries and their response time

As per the answers, I have tested this query without convert_tz function and one without convert_tz as well as date. But results are not quite good. There is a gain of just 300 ms if I remove both convert_tz and date functions.I tested it multiple times, and everytime this is the difference between these queries.

  1. Query 1 - time taken = 4.91 sec

SELECT count(*) as Count FROM orders WHERE date(CreatedDate) = '2018-07-30' AND OrderStatusId IN (5,10,15) AND Deleted = 0;

  1. Query 2 - time taken = 1.72 sec

SELECT count(*) as Count FROM orders WHERE CreatedDate >= '2018-07-30 00:00:00' AND CreatedDate <= '2018-07-30 23:59:59' AND OrderStatusId IN (5,10,15) AND Deleted = 0;

  1. Query 3 - time taken = 2.02 sec

SELECT count(*) as Count FROM orders WHERE date(CONVERT_TZ(CreatedDate,'+00:00','+05:30')) = '2018-07-30' AND OrderStatusId IN (5,10,15) AND Deleted = 0;

  • Your last point seems to be key If I run these queries directly on RDS using MySql workbench then also it is slow.. That said showing the table structure and queries might help us give you some pointers. – Dave Jul 30 '18 at 12:10
  • 2
    How slow is slow? How long does it take to get your query results, 1 sec, 10 sec, 1 minute, more? Are your queries returning lots of excess data? For example, all orders even if you only need those of last month or you need just the number of records? – Bart van Ingen Schenau Jul 30 '18 at 12:24
  • Edited my question and added sample query, please check – Abhishek Sharma Jul 30 '18 at 12:34
  • for your sample query, store the date in utc and lose the timezone conversion – Ewan Jul 30 '18 at 15:53
  • I have edited my question. Added tests of different queries without timezone conversion – Abhishek Sharma Jul 31 '18 at 4:19
2

Since you noted that your queries run slow when using the MySql Workbench, that's the first place to start.

In Sql Server, it's possible to view the query plan for a query and see exactly how the database will process it. Furthermore, you can check to see which index (if any) is used.

In the case of your sample query, your WHERE clause contains a calculation CONVERT_TZ(CreatedDate...). Sql Server would see this and resort to a table scan - it would read every record in the table, do this conversion, then filter accordingly. That's incredibly slow if you have a lot of records.

If possible, add a new field to that table CreatedDateUTC and do the calculation once. You'll be able to create an index on that field afterwards.

In other words, start with your last two bullets

Optimization in queries needed?
Better indexing in tables needed?

  • 1
    Also worth noting: In Postgres, you can actually create and index on the result of a function. Might be worth looking into for MySql. – Maybe_Factor Jul 31 '18 at 3:27
  • I have edited my question. I tested variations of this queries to understand impact of CONVERT_TZ but results does not seems to be very optimistic. All my timestamps are already stored in UTC, I need to fetch them according to local timezone – Abhishek Sharma Jul 31 '18 at 4:11
1

The standard answer to the problem of reports, which by their nature are slow to calculate is to run them against a data warehouse server rather than the transactional database.

This requires that you export your data out of your transactional DB. Normally this can be done as a continuous process.

A data warehouse database is structured differently to allow for fast report style queries and, as its a different server, queries against it wont affect the performance of your application.

However! your sample SQL seems pretty simple. It is probably slow due to the conversion of each rows date to a particular timezone. This is easily solved by the standard practice of storing dates as UTC.

It could be that some simple optimisation of your queries will enable you to run your reports against your transactional DB, or a simple replication of your Database to a failover/reporting server

  • 1
    Having a read replica optimized for reporting is a good idea, but I do feel like that we have reached to that scale. I have tested for timezone conversion function impact, edited my question please check – Abhishek Sharma Jul 31 '18 at 4:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.