9

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.

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

SERVER STATS:

  • Intel(R) Xeon(R) CPU E5530 @ 2.40GHz
  • cpu cores: 4
  • Memory: 12293480 (12 gigs)

OS:

  • 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

MY.CNF:

[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

migrated from serverfault.com Apr 18 '12 at 20:59

This question came from our site for system and network administrators.

  • Configure the error log dev.mysql.com/doc/refman/5.5/en/error-log.html and check if something is registered when the problem happens – CesarC Apr 18 '12 at 15:17
  • I used this site omh.cc/mycnf and determined that the problem is most likely with the configuration itself. I am going to adjust a lot of the myisam related connection pools and see if that helps with memory consumption. – Peter Apr 18 '12 at 16:43
  • 2
    CentOS 5.5 is not current. 5.8 is (if you care about OS security) – Nils Apr 20 '12 at 20:10
  • 1
    The solution would be interesting. Can you post it as answer to your own question? – Nils Apr 20 '12 at 20:13
  • duplicate post in reddit thread where it was solved. it was also posted on MySQL's forums – Mark McKinstry May 1 '12 at 1:57
2
  1. You should check the MySQL error log

  2. Check that this value is the same as ulimit -a's open files:

    int my.cnf 
    [mysqld_safe]
    open-files-limit = 8192
    
0

Your configuration is buggy.

Here use this tool. It tells you how much memory RAM do you need for your custom configuration?

Your current RAM as you mentioned is 12GB but you need 31.6GB for 500 active MySQL connections.

Session variables
max_allowed_packet 16.0 MB
sort_buffer_size 8.0 MB
net_buffer_length 16.0 KB
thread_stack 192.0 KB
read_rnd_buffer_size 16.0 MB
read_buffer_size 2.0 MBSession variables
max_allowed_packet 16.0 MB
sort_buffer_size 8.0 MB
net_buffer_length 16.0 KB
thread_stack 192.0 KB
read_rnd_buffer_size 16.0 MB
read_buffer_size 2.0 MB
join_buffer_size 8.0 MB
Total (per session)50.2 MB
Global variables
innodb_log_buffer_size 1.0 MB
query_cache_size 64.0 MB
innodb_buffer_pool_size 7.0 GB
innodb_additional_mem_pool_size 16.0 MB
key_buffer_size 32.0 MB
Total 7.1 GB
Total memory needed (for 500 connections): 31.6 GB

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy