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.

Improve this question
4
  • stackoverflow.com/questions/1292856/…
    – rahulmishra
    Commented Dec 14, 2014 at 17:04
  • @light94 Unfortunately that is unrelated to my situation. I already have that option enabled, and it only happens during large queries.
    – timetofly
    Commented Dec 15, 2014 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
    Commented Dec 19, 2014 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
    Commented Dec 19, 2014 at 16:03

1 Answer 1

Reset to default
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.

Improve this answer
2
  • 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
    Commented Dec 15, 2014 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
    Commented Dec 15, 2014 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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