out of memory problem with mysql
root@localhost:/mnt # Out of Memory: Kill process 9624 (mysqld) score 34539 and children.
Out of memory: Killed process 9624 (mysqld).
To give an idea of resources usage during a peak:
For example, right now:
root@localhost:/tmp: # ps aux | grep apache | wc -l
73
root@localhost:/home/fmartin # free
total used free shared buffers cached
Mem: 645324 410904 234420 0 4260 36436
-/+ buffers/cache: 370208 275116
Swap: 263160 65028 198132
I don't know how to identify which procces is causing mysql to exceed memory usage (wordpress is running along some other little modules). Is there any tool to track mysql problematic queries or any hint for mysql configuration file?
Thanks for attention
5 Replies
you likely just have your memory or child settings way to high given your load.
Out of Memory: Kill process 6488 (mysqld) score 35146 and children.
Out of memory: Killed process 6488 (mysqld).
this is my my.cnf with comment lines stripped out
> [client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
maxallowedpacket = 16M
thread_stack = 128K
threadcachesize = 8
querycachelimit = 1M
querycachesize = 16M
log_bin = /var/log/mysql/mysql-bin.log
expirelogsdays = 10
maxbinlogsize = 100M
skip-bdb
[mysqldump]
quick
quote-names
maxallowedpacket = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
you can comment out
log_bin = /var/log/mysql/mysql-bin.log
expirelogsdays = 10
maxbinlogsize = 100M
Unless your planning on doing replication
I would turn on logslowqueries and log-queries-not-using-indexes
Well and you could uncomment skip-innodb if your not using any of that engine should free up a bit of memory.
But really I see nothing wrong unless I'm missing something.
@MrRx7:
odd, pretty much the same as my sql server. I'm getting pretty good load on and havn't hit the OOM for sql yet at least.
you can comment out
log_bin = /var/log/mysql/mysql-bin.log
expirelogsdays = 10
maxbinlogsize = 100M
Unless your planning on doing replication
I would turn on logslowqueries and log-queries-not-using-indexes
Well and you could uncomment skip-innodb if your not using any of that engine should free up a bit of memory.
But really I see nothing wrong unless I'm missing something.
Thanks for your help, yesterday i upgraded to latest wordpress version but the server is not responding right now (but this time.. without oom killer). I tried what you said and lets see what's happen next
root@localhost:/etc/apache2 # vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 80 150992 27556 102892 0 0 12 49 158 48 4 0 94 2
0 0 80 130840 27588 103084 0 0 35 217 752 216 6 1 94 0
0 0 80 122296 27616 103136 0 0 10 176 466 150 3 0 97 0
0 0 80 109704 27640 103296 0 0 15 225 585 193 6 0 94 0
0 0 80 120620 27656 103400 0 0 19 190 551 176 5 0 94 1
0 0 80 130356 27668 103440 0 0 8 305 359 106 2 0 97 1
0 0 80 138072 27688 103572 0 0 27 174 521 167 4 0 95 0
0 0 80 148332 27696 103576 0 0 0 198 610 219 8 0 92 0
2 0 80 168120 27700 103832 0 0 52 158 426 127 2 0 98 0
0 0 80 167340 27712 103912 0 0 21 170 629 186 4 0 96 0
and now during a ****traffifc peak****
vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 58 532392 7784 1556 18148 0 2 7 20 113 33 3 0 95 2
0 61 532128 6952 1720 20544 843 407 1374 488 682 204 0 1 18 82
0 60 532428 6760 1720 20524 883 455 969 520 520 174 0 0 44 56
0 60 532468 6128 1700 20632 1058 486 1154 519 554 217 0 0 49 51
0 57 532472 5836 1668 21040 597 533 714 580 587 179 0 1 49 50
0 50 531752 6724 1552 20092 1210 334 1298 400 643 238 1 1 56 42
6 50 528968 5940 1492 16764 1890 0 2011 240 643 393 1 0 38 61
0 50 527384 6852 1372 15492 1163 28 1210 139 444 187 1 1 52 46
2 45 525924 5880 1212 14684 2445 314 2473 328 797 313 1 2 41 56
0 52 529564 6292 1228 14468 283 835 301 882 418 101 0 1 24 75
````
i think i should get more memory, any of the modifications i have made to php.ini, apache.conf, my.cnf and the use of wordpress super cache have been enough