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.