MySQL Performance Tuning – Slow SHOW PROCEDURE/FUNCTION STATUS

backupmysql-5.5performanceperformance-tuningstored-procedures

I am experiencing a very unusual behaviour during nightly backups. The schema-only backup portion of the backup is taking a very long time to run and will even causes the system to swap memory.
Specifically I'm running :

mysqldump --user=my_back_user --password=my_back_user_password --host=localhost --all-databases --routines --no-data | bzip2 -c > schema_daily.sql.bz2

Our MySQL 5.5 server has about 230 database made up of about 2300 tables, 8800 stored procedures and 6700 functions. /var/lib/mysql is about 82GB, compressed (bzip2) the whole thing is about 6.2GB.

Daily performance of the server is good. It's rare to even see anything in the slow query log (long_query_time = 1 and log_queries_not_using_indexes = 1 ). We have a master-slave setup, but the slave is only there as failover. We did recently switch the backups to be taken from the slave as they were interfering with performance on the master.

If the slave is freshly rebooted, or swap is manually cleaned and MySQL restarted, the entire backup takes between 35 – 45 minutes. The time the backups take climbs up to around 6 hours after a few (4 or 5) runs. This crazy spike in time appears to be due to the queries SHOW PROCEDURE/FUNCTION STATUS WHERE Db = '' invoked by mysqldump while backing up the schema without data. The data backup portion, which includes the create database and table queries, but not stored procedures and function, is consistently fast.

I understand I can probably switch away from mysqldump and make this issue go away, but what is it that is causing this? This seems to me like something is wrong and/or misconfigured, which I'd like to address before changing how the backups are done. Is this something I can compensate for in my.cnf? I have been searching online for anything like this for a while and have come up empty.

Update 1

For the record, this same backup has been running in our Dev environment for about a month, on the mostly the same schema, but with less data and there has never been an issue with it.

Also, after originally writing this, it occurred to me that I haven't extensively tested both parts of the backup independently. I am fortunate to be able to do tests on the production system with this issue. I did a restart of MySQL, cleared the swap and ran just the schema section of the backup eight times consecutively and it never had an issue at all. On the contrary, it actually got a bit faster on the consecutive runs.

This implies to me that it's the combination of the schema backup and the data backup creating the issue I'm seeing. All the tables we use are InnoDB and as such I have only performance tuned for InnoDB. MySQL uses MyISAM for pretty much everything internally and this issue only occurs after a couple runs.

I haven't looked at MyISAM performance tuning in a lot time, so I will revisit that and how MySQL manages memory for that.

Update 2

As it's seeming more apparent that my InnoDB configuration may be to blame, I will supply more information on that front.

From my.cnf, this is my minus anything pointing to a file, directory, port timezone or id.

max_connections               = 500

character-set-server          = utf8
collation-server              = utf8_general_ci
join_buffer_size              = 262144

# InnoDB
innodb_file_per_table         = 1
innodb_buffer_pool_size       = 12G
innodb_buffer_pool_instances  = 6
innodb_log_file_size          = 256M
innodb_stats_on_metadata      = 0

innodb_read_io_threads        = 24
innodb_write_io_threads       = 24
innodb_io_capacity            = 2000
innodb_concurrency_tickets    = 5000
innodb_purge_threads          = 1
innodb_purge_batch_size       = 300
innodb_old_blocks_time        = 1000
innodb_old_blocks_pct         = 5
innodb_open_files             = 900

table_definition_cache        = 50000
table_open_cache              = 100000

symbolic-links                = 0

# Slow Queries
slow_query_log                = 1
slow_query_log_file           = /var/lib/mysql/slow_query.log
long_query_time               = 1
log_queries_not_using_indexes = 1

The system is a bare metal system :

  • 2x Intel(R) Xeon(R) CPU E5-2609 v3 @ 1.90GHz (6 cores each)
  • 16GB of RAM
  • Lots of SSD disk space on a RAID 5
  • Running CentOS 7

I've ruled out disk IO as a bottleneck with a simple dd test

# dd if=/dev/zero of=/root/testfile bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 4.32635 s, 248 MB/s

I had also ensured when setting up this system that swappiness was minimized in the kernel.

# sysctl vm.swappiness
vm.swappiness = 0

Any insights would be greatly appreciated.

Best Answer

Upon reading over How MySQL Uses Memory under the MyISAM storage engine section, something jumped out at me.

The connection buffer and result buffer each begin with a size equal to net_buffer_length bytes, but are dynamically enlarged up to max_allowed_packet bytes as needed.

When creating the slave and importing data I have to set max_allowed_packet to 1G in order for the import to succeed. This is not a setting in place on the Dev system which has been happily running this same backup, but it was still in place on the master and both slaves in production. So, after InnoDB has been in use and fills the buffer pool (set to 12GB), making good use of the systems memory (16GB), a backup runs which allows MySQL to use more memory than what is available. Swapping happens and performance takes an expected nose dive.

Related Question