One of my clients complained serious performance issues in a web-application written in Java. I was suspecting that there might be some unoptimized queries which are causing the problem. I've enabled mysql general logging to figure out what queries are running behind a problematic application function. There were around 11840 'Prepare' statements in the log whereas 'Execute' statements were only 9 in number - I couldn't understand what does this mean, can somebody assist? Thanks

  • For performance issues, enable the slowlog with long_query_time=1. Later use mysqldumpslow -s t or pt-query-digest to find the 'worst' queries. – Rick James Apr 15 '16 at 16:21
  • Perhaps his code prepares thousands of possible queries, then picks the one he needs to execute. (Inefficient way to do things.) – Rick James Apr 15 '16 at 16:22
  • 1
    Thank you for the help. In fact I already checked this parameter. But the problem is not of long running queries but is of a lot of queries with prepare prefix against a single application function. So I am not able to understand the reason of too many prepare statements without the execute statements. – user2436428 Apr 15 '16 at 16:57
  • How rapidly were the prepares executed -- that is perpares/minute or per second? If hundreds were done in a few seconds, that might support the code pattern I was guessing about. – Rick James Apr 15 '16 at 20:00
  • 11840 prepares in a minute or so, and only 9 were executed – user2436428 Apr 16 '16 at 4:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.