I have a web application calling a mysql database through vpn.
Sometimes (when a lot of customers are using the system) the cpu from the remote mysql host reaches 100% and everything goes so slow (I even get error 500).
I turned on slow queries log and nothing goes in there.
I saw the processlist and there are some queries (around 30 which is normal since I use a lot of ajax) that they are sorting data.
The query that does that is (with numbers filled in):
SELECT cs.id_client as id_client, TIMESTAMPDIFF(SECOND,cs.call_start,cs.call_end) AS realduration, cs.*, cs.call_start + INTERVAL 0 HOUR as call_start_corrected, cs.call_end + INTERVAL 0 HOUR as call_end_corrected,`c`.`cost` AS `cscost` ,`c`.`call_rate` AS `ccall_rate`
FROM `callscallshop` AS `cs` LEFT JOIN
`calls` AS `c`
ON `c`.`call_start` = `cs`.`call_start`
WHERE `c`.`caller_id` = `cs`.`caller_id` AND
`cs`.`id_client` IN (9301) AND
year(cs.call_start) = year(now()) AND
month(cs.call_start) = month(now()) AND
week(cs.call_start) = week(now()) AND
`cs`.`invoice_id` = '-1'
ORDER BY cs.call_start DESC
I run the above on a local copy of the database and I get 0.0027 as time to execute.
I remove the time difference and the ordering and the difference time execution is so small..
On google chrome I see that the ajax response goes over 30s sometimes.
The task manager of the db server shows that the main load is mysql.exe
Does any of you have a clue to help me solve this?
Do you think is the query or something with the servers?
UPDATE
Ajax calls wait up to 30s every day in the afteroon!! It seems to be cpu usage that reaches 100%.
They do not allow to apply indexing on the server.
I will be grateful for any idea.
Your LEFT JOIN is forced to be an INNER JOIN by the presence of this clause:
WHERE c.caller_id = cs.caller_id
Write your ON clause like this instead and you'll fix that problem.
ON c.call_start = cs.call_start AND c.caller_id = cs.caller_id
The query you showed us can be optimized significantly, especially in the case that your calllscallshop table has a great many rows. Why? you are doing a date range search in an unsargeable way. Let's fix that.
You seem to be in a production emergency -- 500 messages and all -- so let's take things in order, easiest first.
First, create a compound covering index on the calllscallshop table. Let's see. Your query looks for equality on caller_id and invoice_id. It looks for set containment on id_client. It needs to do a range scan on call_start.
So, let's add this index to calllscallshop: (caller_id, invoice_id, call_start, id_client). Do that soon, and see if your problem improves. You can add the index without changing a line of software.
Also, add this index to calls: (caller_id, call_start)
Second, we need to do the this-week search much more efficiently. As a bonus, the change we make will fix a problem you have at the very beginning of each calendar year. You're doing it like this.
year(cs.call_start) = year(now()) AND /* slow */
month(cs.call_start) = month(now()) AND /* slow */
week(cs.call_start) = week(now()) /* slow, wrong at year-end */
If you want this search to be sargeable, you need this instead:
cs.call_start >= midnight on the first day of this week
AND cs.call_start < midnight on the first day of next week
This way of writing the query makes MySQL able to range-scan search an index on call_start. It can be stunningly faster.
So the question is, how do we come up with midnight on the first day of this week?
If your weeks start on Sunday, this little formula does the trick.
CURDATE()-INTERVAL DAYOFWEEK(CURDATE())-1 DAY
This works because DAYOFWEEK() returns Sunday=1, Monday=2, etc. So to get the preceding Sunday for any day, we back up DAYOFWEEK()-1 days.
So let's write your date range search like this:
cs.call_start >= CURDATE()-INTERVAL DAYOFWEEK(CURDATE())-1 DAY
AND cs.call_start < CURDATE()-INTERVAL DAYOFWEEK(CURDATE())-1 DAY + INTERVAL 7 DAY
That change should help your query a lot.
As bonus, you can do this
GROUP BY DATE(call_start)-INTERVAL DAYOFWEEK(DATE(call_start))-1 DAY
to get weekly summaries of your data if you need them.
One more thing: When you're dealing with production data, doing SELECT * is considered harmful. Instead, you should specify the exact columns you need.
Ollies suggestions were very helpful but didn't solve the problem.
The problem was not in the sql directly, but in assosiation with the php code.
To be more clear:
What solved the problem was executing a query for multiple cabins and therefore having less connections and query executions per second.
The ajax call response dropped from 30 seconds to 850ms!
Thanks Ollie, your suggestions made my code a bit quicker and cleaner.