About 4 months ago we migrated from MS SQL Server to MySQL 5.5. Since then we have been experiencing an issue once approximately ever 25 days since where CentOS runs out of memory and as a result it kills MySQL. MySQL safe restarts mysql so the database is only completely down for a minute or two, but we can suffer performance and connectivity losses for hours before CentOS kills the mysqld thread.
We usually see issues from 1 AM to 5 AM, but never during the day when traffic is highest which is what's really baffling about this situation. Despite normally seeing connectivity and performance issues from 1am to 5am, mysql server typically gets killed around 4 AM or 5 AM, around the same time mysqldump runs.
mysqldump may have been the culprit. However it starts at 4 AM daily, but we see problems as early as 1 AM on some nights. Also
mysqldump is running with the
--opt switch, so it shouldn't be buffering a lot of data during the dump process.
We also considered the backup app that we're using that gets the dump files and backs them up to tape. We changed the time that it runs to 6 AM and the problem was unchanged.
We have several jobs that run periodically throughout the night, but none are very resource intensive and don't take very long to run at all.
Here's some stats for what we're working with and the current entries in the
my.cnf file. Any help or suggestions for things that we can try would be very much appreciated.
- Intel(R) Xeon(R) CPU E5530 @ 2.40GHz
- cpu cores: 4
- Memory: 12293480 (12 gigs)
- CentOS 5.5
- Linux 2.6.18-274.12.1.el5 #1 SMP Tue Nov 29 13:37:46 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-name-resolve ssl-ca=<file location> ssl-cert=<file location> ssl-key=<file location> back_log = 50 max_connections = 500 table_open_cache = 2048 table_definition_cache = 9000 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M read_buffer_size = 2M read_rnd_buffer_size = 16M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 130 thread_concurrency = 16 query_cache_size = 64M query_cache_limit = 1M ft_min_word_len = 4 default-storage-engine=INNODB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=/log/mysql/mysql-bin expire_logs_days=7 binlog_format=mixed key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 7G innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 70 innodb_lock_wait_timeout = 120 [mysql] no-auto-rehash [mysqld_safe] open-files-limit = 8192