0

Usually, mysql's connecton time is in the 0.001 second range. However, when I run a large reporting query (that spans multiple tables with aggregates), I get tons of warnings saying that new mysql connections are taking between .5 and 2 seconds just to connect! Why would a single large query affect connection times so much?

Doing research online, I can't seem to find which mysql settings would need to be tweaked to mitigate the issue.

I'm on MySQL 5.6, using all InnoDB tables.

  • @light94 Unfortunately that is unrelated to my situation. I already have that option enabled, and it only happens during large queries. – timetofly Dec 15 '14 at 1:17
  • 1
    What's the cpu load at whilst the long query is running? What are your drives doing at this time? All all your databases on the same drive? Are IO's high? Do you use text columns in the report? – user55350 Dec 19 '14 at 13:23
  • @Merlin All low. It seems the contention was somehow memory related, as I noticed that it started happening when mysql's available memory was used up. To clarify, when I turn on mysql, the memory usage slowly increases until it reaches a maximum cap (and doesn't increase anymore). Once it's reached, this starts happening. Any ideas? – timetofly Dec 19 '14 at 16:03
0

It's look like your reporting query lock for a while the tables needed by general queries.

There is no general recipe for such cases and you have to refactor your reporting to avoid locking. Sometimes you can achieve this with splitting one big query into the series of smaller ones. Sometimes the good approach is to copy some tables for exclusive use by report generator. Sometimes it is something else. All depends on the your DB scheme.

  • Unfortunately that's not my problem. 1) Slow query log shows the queries having a total lock time of 0.000x seconds. 2) Locked tables should not affect connection time. By connection time, I mean literally the time it takes just to connect to the server without running any queries. Thanks for your time though. – timetofly Dec 15 '14 at 16:32
  • Connecting invokes queries too. At least for authorization. I've used HeidiSQL for a years - that is not an ultimate solution but it have handy tools like process list and easy to use profiler. If you have used PHPMyAdmin, you'll be impressed with it. – Kondybas Dec 15 '14 at 18:24

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.