0

Our app has recently been receiving traffic up to 10-30 requests per sec. The life cycle of every request is pretty complex. MySQL's load average ranges from 5 (when the requests are 10 per sec) and goes to 20 (30 requests, peak traffic, lasts for a few minutes).

The load average is abysmally high due to extremely high CPU utilization of 200-350%. Now, for every request that we receive, we fire off 3 COUNT queries on a single table. They look pretty similar.Just to show you how one looks like:

SELECT COUNT(DISTINCT COL1,COL2)
FROM conversion_Details c 
WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')
AND (COL4 = 0 OR COL4 = 17)
AND COL5=5
AND COL2 = 'SOMETHING' 

As you can see, for 1 count query, we have 1 table and 5 columns involved. In total,we have 3 varchar columns, 2 int columns, and 1 timestamp column. I would like to add that all of these columns are already indexed, and the table contains about 1 million entries.

Now imagine 2 more queries like this one. These 3 queries are fired in parallel. If I comment these queries out,the load average comes down to less than 1.

Is there anything I can do to optimize my database further? MySQL is currently running on 8GB ram, 4 core, CentOS.

  • 1
    share execution plan result – Spike Sep 13 '16 at 6:04
  • What datatype is COL3? Any way to skip the date conversion? – Andrew Brennan Sep 13 '16 at 10:31
  • Also, if COL2 is always "SOMETHING", there's no reason to include it in your count distinct. – Andrew Brennan Sep 13 '16 at 10:34
  • 1
    So there's no need to include Col2 in your count distinct. "select count (distinct col1)" will produce identical output in all cases. Do you understand why? There is only ever one value of col2 for each execution of the query. – Andrew Brennan Sep 13 '16 at 14:17
  • 1
    I understand completely.Yeah I can save the date conversion. Thanks for your input ! – user65541 Sep 13 '16 at 14:36
3

You can save some cpu cycles by using the same datatype as the column in your date range. So instead of:

WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')

assuming col3 is a timestamp, use something like:

where col3 >= timestamp('2016-08-01 00:00:00') 
  and col3  < timestamp('2016-08-15 00:00:00')   

You might need to use the unix_timestamp instead of timestamp conversion so that 'between' will work, but you can always do a >= and a < instead of using between.

There's also no need to include Col2 in your select, since col2 will always have the same value in each execution.

So, the query would become:

SELECT COUNT(DISTINCT col1)
FROM conversion_Details c 
WHERE col3 >= timestamp('2016-08-01 00:00:00') 
  AND col3  < timestamp('2016-08-15 00:00:00') 
  AND col4 IN (0, 17) 
  AND col5 = 5 
  AND col2 = 'SOMETHING' ;

A covering index would help to improve efficiency further. Try this one (order matters):

ALTER TABLE conversion_Details
  ADD INDEX covering_index_52431
    (col5, col2, col4, col3, col1) ;
| improve this answer | |
  • 1
    It should be where col3 >= timestamp('2016-08-01') and clo3 < timestamp('2016-08-15'). They want the whole day of 14th August. Not just the 00:00:00 timestamp. – ypercubeᵀᴹ Sep 13 '16 at 17:08
  • you're right I made a mistake and left out the 00:00:00, edited. – Andrew Brennan Sep 14 '16 at 8:14
  • No, you had the 00:00:00 part alright, (I had removed it but it doesn't hurt to be there). But you had '2016-08-14 00:00:00' (14, not 15, which is fixed now ;) +1 anyway. I was to post an answer when I realized that most parts I was thinking to write had been covered by yours. – ypercubeᵀᴹ Sep 14 '16 at 8:22

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.