Hello,
This is my first post here, after I have tried to search existing posts for an answer to my problem, and could not find it.
I have recently moved my database from a single 2GB RAM machine (that ran the entire LAMP), to a cluster configuration, in which the database is on its own machine with 4GB RAM, and also being replicated.
Since then, I started receiving a lot of “Out of Memory” errors in mysql error log. They come in bulks, and at random times. Here is a sample:
[B]Quote:[/B] |
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1042860 bytes)
080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:02 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 5:20:04 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 5:20:04 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:18:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:18:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 9:20:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:20:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 9:27:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 15:52:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 15:52:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:19 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080113 16:01:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080113 16:01:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
|
One time they also caused the server to crash and failover to the slave. Note, that it seems the process only needed 1M of allocation before it terminated…
At first I thought it had to do with the OS being a 32-bit (rhel4), and the mysqld process larger than 2GB - I had innodb_buffer_pool_size of 2000M - so I reduced it to 1000M, and I see that mysqld is under 2GB (at the time of writing: VIRT - 1855m, RES - 1.4g), but I am still getting these errors.
I also tried to isolate a specific query that does it, so I turned on query logging with error messages, and I see that several queries cause this.
Here is my.cnf:
[B]Quote:[/B] |
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
max_connections = 530
default-character-set = utf8
max_allowed_packet = 16M
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
key_buffer_size = 256M
max_seeks_for_key = 500
Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4
thread_cache_size = 16
table_cache = 2048
tmp_table_size = 256M
max_heap_table_size = 257M
ft_min_word_len = 3
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 2M
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
log-slow-queries=slow-log
long_query_time=1
log-bin=db-bin-log
server-id = 1
slave-skip-errors=126,1062
|
Any help would be greatly appreciated.
Or