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.
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.
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.