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
Is /proc/sys/vm/overcommit_memory set to 0?
If not, I believe it’s a setting in /etc/sysctl.conf on RedHat (I’m not a Redhat user). Make sure it’s set to 0 if it’s not. You can also change the value until next reboot by running “echo 0 > /proc/sys/vm/overcommit_memory” and see if it solves the problem.
It is set to 0:
cat /proc/sys/vm/overcommit_memory0
I forgot to mention, this is mysql 5.0.45.
Also, now the server from time to time gets huge loads (over 100!), mysqld is burning cpu on close to 200%, and I get over 95% IO waits, and temporary tables start to pile up in /var/lib/mysql… I am not sure if this problem is connected, or an entirely new problem.
Or
The change in the machine was from OEM server with SATA drives to a HP with RAID10.
The problems could be connected as you say, however the memory problems have been going on for a month (since the machine switching), but the IO problems are brand new (started today).
More info that might help, is when this IO load occured, I restarted mysql, and upon restart I get this:
| [B]Quote:[/B] |
|
080115 14:26:34 mysqld started
080115 14:26:35 InnoDB: Started; log sequence number 8 1135678335
080115 14:26:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.0.45-community-log’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Edition (GPL)
080115 14:27:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:43 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:44 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:45 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:46 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:27:53 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
080115 14:28:02 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
080115 14:28:05 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
|
Memory errors as soon as it starts up? Weird no?
Or
That is indeed.
Can you paste in my.cnf and the output of “show status;”?
I’m not an expert, but another set of eyes never hurts.
| [B]orensol wrote on Tue, 15 January 2008 09:01[/B] |
| [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)
[snip]
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…
|
This looks like a new connection, but you have no memory left. As you are running an 32bit OS you AFAIK cant use all the 4GB. Anyway habe a look at your system also.
Something like top or vmstat look into /var/log/messages.
Type into your mysql-client “show global status like ‘Threads_connected’” next time it happens. “Show status like ‘Max_used_connections’” should give you a good clue.
etc.
| [B]Quote:[/B] |
|
USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
mysql 15 0 196 404:07.09 34.7 1811m 1.4g 4688 S mysqld
|
mysqld is burning the cpu (196%!!)… How can I reduce cpu usage? Maybe I should reduce number of threads?
Or