Performance_schema success stories : replication SQL thread tuning

A lot of customers have lagging slaves. It could be one of the top issues at support, due to the infamous row based replication without primary key issue :

Bug #53375 RBR + no PK => High load on slave (table scan/cpu) => slave failure

If you use binlog_format = statement or mixed ,  there are several ways of monitoring the SQL thread. The most ancient is the log-slow-slave-statements  variable. From 5.6.11, it is a dynamic variable, before that you had to restart the slave mysqld to enable it.

Once on, you can trace what’s going on in the SQL thread and analyze the slow query log. Of course, as the SQL thread could be running long write queries or a lot of fast queries, so it is crucial to set long_query_time = 0 for say 60 seconds to catch the fast writes. Fast does not mean that they can not be optimized :-).

From MySQL 5.6.14, there is even better. After this bug fix : Bug 16750433 – THE STATEMENT DIGEST DOES NOT SHOW THE SLAVE SQL THREAD STATEMENTS, the Performance_schema statement digests are showing the SQL thread statements as well !

So that ps_helper / mysql_sys can be used to see what’s going on in the SQL thread.

Now an real example :

If you see this kind of things : show global status like ‘Handler%’ or in the show engine innodb status :

500k index records scan per second is a bit too much for a (single) thread. No wonder that

Seconds_Behind_Master: 76162

is large and increasing.

The slave was up for 12 min and nearly 10 min was spent on the updates ! 18000 updates in 10 minutes, that’s fast 🙂 So do we need to buy better hardware ?

No. Average rows examined is between 100 – 200. Maybe we can optimize it ?

Indeed, adding an index on (c1,c2) fixed the issue. And cut by 100 the rows examined average. After one hour, the slave that was lagging 1 day had caught up.

rows_examined

 

This is the MEM graph for the rows_examined.

Thank you to Hauns Froehlingsdorf who worked with me on this issue.

Things to note here :

  • the performance_schema rocks for statement replication. I truly hope that row based replication will get the instrumentation it deserves. Maybe row events could appear as statements ? That’s how they are processed by the parser when you do point-in-time recovery for example …
  • index merge used for updates is much more expensive that it seems : it is not certainly not 3 rows_examined, not 100-200 much more here.  Index entries are locked for 2 index ranges, so more locks in memory in REPEATABLE READS isolation at least. Here are the 2 bugs for completeness :
  • Bug 14226987 – ROWS_EXAMINED IS NOT CORRECT FOR INDEX MERGE QUERIES 
  • Bug 14226171 – EXCESSIVE ROW LOCKING WITH UPDATE IN 5.5.25 
Posted in Uncategorized | Leave a comment

Performance_schema success stories : host summary tables

This question was asked at support by a customer to solve a difficult issue.

How to identify a sporadic burst of queries coming from one of the hosts accessing the database ?

If there are hundreds of hosts, it can be challenging, especially if the queries are fast. No chance for them to get logged in the famous slow query log !

Here is the solution using the performance_schema in MySQL 5.6 :

Here is the result :

Note that if you use the thread pool plugin, you need to upgrade to 5.6.15 or later because of this bug : Bug 17049691 : PROCESSLIST_USER AND PROCESSLIST_HOST ARE ALWAYS NULL WITH THREAD_POOL.

If you want to determine when the burst occurs, you can create an event or a cron job to load the results in a table like this :

I created a pull request for mysql-sys, so that host based P_S tables can be accessed using sys views and possibly from MySQL Workbench 6.1.

Posted in performance_schema, success stories, tip, Uncategorized | Leave a comment

50 tips to boost MySQL Performance Webinar follow up

Thank you for attending the webinar !  Here are the ppt slides.

If you missed it, you can still join the archived event by clicking the URL below.
http://w.on24.com/r.htm?e=748845&s=1&k=171F8C0CECD105B0F4ED721CA6F2C704

There were a lot of attendees and a lot of questions. I could not answer everything during the limited time. But here are finally the answers !

Question Answer
Can MEM be used on community version? Yes, of course.
any known problems for MySQL running on an overprovisioned VMWare environment….for instance, the VMWare admins over allocate CPU for all the Vms? Yes, a busy MySQL server needs cores, memory and fast storage and network. If these ressources are shared by busy servers then performance drops dramatically.
what makes XFS better over EXT4 ? XFS works better with O_DIRECT. XFS had historically better support for SSD (TRIM). See http://dimitrik.free.fr/blog/archives/2012/01/mysql-performance-linux-io.html
Does sync_binlog=1 effect on write performance or read performance Write performance only (fsyncs)
What are the disadvantage of setting wait_timeout and interactive_timeout value to 1 ? Any idle query sleeping longer than 1s will be interrupted.
You focus on innoDB, is there no case where MyISAM would be preferable? Not really. MyISAM uses the OS to cache the data and requires a lot of system calls.
MySQL 5.6 introduced read only transactions and full text indexes for InnoDB. It can only think of spatial indexes.
what monitor instruments you recommend in mac os x system. On Mac OS X, you can install MEM to monitor the database. The performance_schema and ps_helper will also work on any platform. DTrace can be helpful too.
How do i check Qcache_free_blocks? show global status like ‘Qcache_free_blocks’;
How Table Partitioning effect the performance on read and write ? Partitioning can improve read and write performance. But it requires more tuning than a normal table. The table and query designs are critical. I would recommend to use as little partitions as possible, use pruning for all queries. Partitioning is the best way to delete useless rows (drop or truncate partitions). If a query does not use pruning, then it is more expensive in terms of locking and table access, especially range index scans. Partitioning is good for big data when the insertion rate is limited by the table size.
Are there any recommendations for running MySQL on ESX? see http://www.vmware.com/files/pdf/Virtualization-for-MySQL-on-VMware.pdf
what is the scope of mysql dba in market as per current trends? I can not answer this question. I know that there is a high demand for MySQL DBAs worldwide
Sir I have E-5 family server getting slow while fetching 1 row from table  
any tips to make it more faster, Using MYISAM It is hard to tell. Again I recommend InnoDB. If you can, please open a support request or submit your question to http://forums.mysql.com/
Posted in conference, tip, Uncategorized | 2 Comments

How to calculate a specific InnoDB index size ?

MySQL provides commands to see the overall index size versus the data size.

One of them is “show table status” :

So here, we have these “estimations”, run ANALYZE TABLE before to get more accurate estimates :

Data_length: 143310848,    136Mb  clustered index size.

Index_length: 146030592,  139Mb secondary index size.

In this example, I have 3 indexes : 1 auto-generated clustered index and 2 secondary indexes.

In this case, having the index_length, it is easy to guess the index size of each secondary index. In the general case, it is possible to drop secondary indexes one by one, optimize and see the change in index_length …

From 5.6, there is a better way : this is the hidden 5.6 gem I want to share with you :

Using default options, MySQL 5.6 now computes table and index statistics “on the fly” and persist them in these 2 tables : mysql.innodb_table_stats and mysql.innodb_index_stats.

If you need exact values, run ANALYZE TABLE before running the query ! The stats could be out of date due to a (dynamic) configuration change or if the table is just being heavily updated.

Regarding index stats, each index has a stat called ‘Number of pages in the index‘. The index size can be obtained by multiplying this value by the InnoDB page size.

More details on index stats can be found in this MySQL optimizer blog by my colleague Oystein : http://oysteing.blogspot.co.uk/2011/05/innodb-persistent-statistics-save-day.html.  The MySQL manual should also soon be updated with this useful information.

Note that the tip also works with partitioned table :

 

 

Posted in tip | Leave a comment

A small optimizer change worth noticing

MySQL uses internal temporary tables to execute some queries. Usually the tables are stored in memory and on disk if some conditions are met :

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

  • Presence of a BLOB or TEXT column in the table

  • Presence of any string column in a GROUP BY or DISTINCT clause larger than 512 bytes

  • Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or UNION ALL is used

http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

This was true until MySQL 5.6.15. A voluntary side effect of this bug fix :

Bug 17566396 – MATERIALIZATION IS NOT CHOSEN FOR LONG UTF8 VARCHAR JOINS

was to loosen the condition on the second item. You can now use up to varchar(512) whatever the character set (utf8, utf8mb4) in group by / distinct and of course in joins. I often see varchar(255), usually in latin1 and more and more in utf8.

Here is an example :

Result in 5.6.14 :

Result in 5.6.15 :

This is the internal bug for the documentation change request :

Bug 17935006 – INTERNAL TEMPORARY TABLE LIMITATIONS CHANGED IN 5.6.15

A big thank to the Optimizer team for fixing this very ancient restriction !

Posted in tip | Leave a comment

Poor man’s Online Optimize in 5.6

Table space fragmentation has generally 2 origins :

  • File System fragmentation : the data file is spread physically on many non contiguous locations on the disk.
  • Internal Fragmentation : the data and index pages have “holes” : this happens when  rows are deleted or updated, especially at random.

As a result, performance is affected by table space fragmentation. Data typically takes more space on disk and in memory. The disk is more busy than it should.

File System fragmentation can be detected using the filefrag command on Linux (and similar on different OS). When using MyISAM, MYI files are usually very fragmented on the FS, much more than the MYD files.

To measure internal InnoDB fragmentation, there is no general formula. It is possible to compute the theoretical size and compare it to the actual size on disk. It only works on fixed length data types. I have this trick : it is mentioned in 50 Tips for Boosting MySQL Performance [CON2655]. The idea is to compare the fragmented table and a much smaller table having the same table definition and sample data.
It works well when the average row length can be estimated from the sample data even for variable length rows.

Here 20000 rows 15-20 Mb on disk gives a good idea. Inserting the sample data the same empty table actually defragments the sample, since it contains non empty rows.

The table is likely internally fragmented

if Avg_row_length(t) > Avg_row_length(t_defrag)

where Avg_row_length comes from show table status.

Example : some random data is created, uniformly distributed random row length : 1M rows, 640Mb. Internal fragmentation is created by deleting the even rows (one every two rows).

As you can see, the table t1 is fragmented since its average row length is 2 times larger than t1_defrag.

Fortunately, there is a command that fixes both FS and internal fragmentation on most storage engines :

For InnoDB, optimize does rebuild the table and analyze it. So this command also works :

After the table rebuild, the average row length is back to normal and the table size is minimal on disk, here 2 times smaller, 324Mb.

Note that the optimize and alter table engine=InnoDB commands take an exclusive lock on the table.

Update : from MySQL 5.6.17, optimize and alter table engine=InnoDB are online operations. See this blog and the MySQL manual for more information.

http://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/

It means that if you use these commands before MySQL 5.6.17, your application : reads, writes, other DDL will be blocked during the table rebuild. So most of the time these commands were run in maintenance windows. Or using special tricks such as pt-online-schema-change or Online Schema Change at Facebook.

In MySQL 5.6, this is no longer required in most cases thanks to InnoDB online DDL. Online DDL is a major 5.6 feature. Even though optimize table is not an online operation, in practice, you can use this online DDL :

where <row_format> is given by show table status. Usually :

Make sure :

Check also : innodb_sort_buffer_size that can speed up online DDL.

The disk usage is even slightly smaller, 320Mb, than the classical rebuild and it is also slightly faster.

With innodb_sort_buffer_size= 32M, the alter is faster :

 

Posted in online DDL, tip | 2 Comments

MySQL Connect slides

Thank you for attending MySQL Connect 2013.

The Saturday session 50 Tips for Boosting MySQL Performance [CON2655] was sold out. It shows the interest in practical recipes to solve performance problems. Maybe the topic of a book as suggested by the audience ?

On Monday, the tutorial Enhancing Productivity with MySQL 5.6 New Features [TUT8131] was less crowded due to MySQL 5.6 Replication Tips and Tricks [TUT8133]  happening at the same time, again tips and tricks are more popular :-).

Here are the slides :

The ppt files are here (additional notes) :

Feel free to comment, report problems or ask questions if unclear !

 

Posted in conference | Leave a comment

I am speaking at MySQL Connect 2013

I open this blog to announce that I will be speaking at MySQL Connect in 2 weeks.183037-mysql-tk-imspeaking-250x250-1951648

 I will present a conference session :

and a tutorial session :

I am very happy to be part of this great event and to be able to meet the MySQL Community, our customers and my colleagues there. Looking forward to seeing you !

It is not too late to register !!

183037-mysql-tk-bn-336x280-1951592

 

 

Posted in conference | Leave a comment