onsdag 5 juni 2013

MySQL 5.6 - a picture is worth a thousand words!

I feel obligated to use the classic quote from  Arthur Brisbane "One look is worth a thousand words". I recently received the picture below and the question; "guess when we upgraded to MySQL 5.6"?

The graph is from MySQL Enterprise Monitor (more information about MySQL Enterprise Monitor can be found here) and  is showing the amount of rows accessed via using indexes verses full table scan. So all blue stuff is where MySQL is not using indexes for statements, this is of course always a bad thing.

At the 4th of June you see that almost all "Rows Read via Full Scan" went away, this was the point in time when customer upgraded to MySQL 5.6, the only spike seen now is when they run mysqldump for backups. Main reason behind this improvement is from all our optimizer improvements in MySQL 5.6, we improved the optimizer in several areas in MySQL 5.6 but key for this customer was our optimization of subqueries.

If you want to read more about what our wonderful engineering team are doing our VP of engineering Tomas Ulin summarized it in a great blogpost here. If you want to keep up with all the great stuff our optimizer team is doing start following their blog.

4 kommentarer:

  1. Emmm. If picture is so worth could you post it with bigger resolution?)))

  2. Hi Viacheslav,

    Picture is from customer and this is the only resolution i have available, sorry for this.

    Blue is; "Rows read via full scans"
    Orange is; "Rows read via indexes"

    X-axis is: dates
    Y-axis is: avg rows/sec

  3. Wouldn't MariaDB do the same?

    1. Not sure how this is relevant, customer upgrade from MySQL 5.5 to MySQL 5.6. Customer is using MySQL, I don't know how different versions of PostgreSQL, Sybase nor MariaDB would handle this specific workload between versions, i guess all brands have their own strengths and weaknesses.