Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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
share|improve this question

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

This question came from our site for professional 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 –  mmckinst May 1 '12 at 1:57
add comment

1 Answer 1

  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
    
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.