GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

query plan gets worse

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • query plan gets worse

    Mysql, 5.5.21, high load, but very few slow queries. How, after about a week, we start to see like 30 slow queries a minute, and it gets worse and worse. The only thing I can do is restart the server. I did "explain" on one of the slow queries, and the result is unexpected, completely using the wrong index. After a server restart, I run the explain again and everything is fine (this query is no longer "slow" when it uses the correct index).

    How do I go about finding out why this happens? So, one query is fine for about a week. Then suddenly it's not, because mysql chooses the wrong query plan. Restart the server -- query plan is fine, query run fast.

    Thanks!

  • #2
    What happens if you run ANALYZE on the table in question?

    Is the index you are referring to close to ideal? I often consider that when the optimizer choose wrong it has to choose between two bad ones hence the ambivalent behavior.

    Or check out the innodb_stats_sample_pages variable. Since if your table is large then you can increase the number of pages in the statistics sample without too much overhead and give the optimizer better statistics to work with.

    Comment


    • #3
      Inconsistency at choosing the query plan in MySQL drives me crazy too
      I guess the only thing you can do is to drop the wrong indexes (if they are not needed for other queries), or just use the query hints (http://dev.mysql.com/doc/refman/5.5/en/index-hints.html).

      Even if you get consistent results after e.g. optimize table, you never know when MySQL will start to use the wrong index again.

      Comment


      • #4
        The one index that usually gets picked is pretty close to ideal. The other index isn't even close, hence the query takes a long time. But why does this happen? I have done analyze table, but the other thing that makes it "go back to normal" is restarting the server. Can't figure out what happens with mysql that makes suddenly choose bad execution plans. By the way, it's not just this one query, it's like one day all of the sudden everything starts running bad until the server gets huge load from all of these queries running slowly. I save off the innodb status during the issue but I don't' see anything unusual there......

        Comment


        • #5
          What's happening is unstable statistics, and there are a variety of fixes for this that have been available for a while in Percona Server and in standard MySQL (although some are not released until MySQL 5.6 comes out). Check the manual for storing statistics in a table and for disabling automatic statistics update.

          Comment


          • #6
            Interesting, is there any way to measure the quality of the statistics over time? And why if you restart the database, everything works like it did before? From what I'm reading analyze table might not fix this... what's strange is that one morning it seemed like ALL queries were slow and we had to restart the server... I'd like to collect some metrics (this time I did show engine innodb status) but I'm not sure what metrics to collect to figure out how this suddenly happened.

            Comment


            • #7
              It's entirely possible that you have several problems happening, not just one. We created pt-stalk to help diagnose problems like this. You can watch my webinars, read slides, read blog posts, etc if the documentation isn't enough to figure out how to use it.

              Comment


              • #8
                Have you tried specifying an index to use?

                http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

                Comment

                Working...
                X