out of memory problem with mysql

I have a site with one wordpress blog getting about 8.000 unique visitors a day, I have activated Wordpress Super Cache plugin but anyway, mysql stills hangs my system. With this message when I enter with ajax console:

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

post your my.cnf

you likely just have your memory or child settings way to high given your load.

same problem right now :cry:

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/

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.

@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

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct