MySQL killing the server

michux · 12-15-2006, 05:37 AM

I have a pretty popular blog running on Wordpress. It uses PHP4 and MySQL 5.0 DB. Once in a while it happens that the MySQL server makes the server run really slow. The website does not work at that time (Wordpress cannot connect to the DB) and all the operations (even an SSH login) take 10 times longer than usually. Restarting MySQL does not help. I need to kill it (killall -9 mysqld) and start it again. Then it just starts working perfectly like before.

I'm sure it's some kind of configuration issue. The server is running Debian Etch and it's a PIII with 512MB RAM.

Do you have any ideas what the problem might be and how to solve it? If you need any logs, let me know and I'll use pastebin. I'm just not exactly sure where to look for.

graq · 12-16-2006, 08:39 AM

Maybe apache is waiting for the database connections and growing beyond your available RAM?

Try working out how much traffic you server at peak times (number of connections and size of threads) and configure your web server and database tightly to that.

That should help stop the server overloading.

michux · 12-17-2006, 07:53 AM

Quote:

Originally Posted by graq

Maybe apache is waiting for the database connections and growing beyond your available RAM?

Try working out how much traffic you server at peak times (number of connections and size of threads) and configure your web server and database tightly to that.

That should help stop the server overloading.

I'm not an Apache expert by no means... so.. could you give me some clues which options I should tinker with? I'm getting some 1000-1500 hits an hour.

graq · 12-17-2006, 01:04 PM

You haven't stated which version of Apache you are using. One of the directives to search on is MaxClients (http://httpd.apache.org/docs/2.0/misc/perf-tuning.html).

I can be a difficult thing to give specific advice on, as it depends on various things. The basic calculation is that if you have 128Mb of RAM and each Apache process uses, on average, 10Mb - then you should never have more than 12 at any one time (13 processes would be > 128Mb). Obviously these figures can vary greatly.

The only time I have experienced something similar to what you are describing is because Apache is creating more processes than the box can handle. The processes don't start off using up the full 10Mb (from the example above) and so end up in a deadlcok each process waiting for the other to release memory before it finishes it's job.

Reading up on MySQL configuration on their website could also be helpful (so that it fits well your Apache config).

Many other things can help with this problem (tune your SQL queries, tune your HTML, add RAM).

Maybe you are suffering from some other problem - but tuning your webserver config can never be bad

michux · 12-17-2006, 06:02 PM

Quote:

Originally Posted by graq

You haven't stated which version of Apache you are using. One of the directives to search on is MaxClients (http://httpd.apache.org/docs/2.0/misc/perf-tuning.html).

I can be a difficult thing to give specific advice on, as it depends on various things. The basic calculation is that if you have 128Mb of RAM and each Apache process uses, on average, 10Mb - then you should never have more than 12 at any one time (13 processes would be > 128Mb). Obviously these figures can vary greatly.

The only time I have experienced something similar to what you are describing is because Apache is creating more processes than the box can handle. The processes don't start off using up the full 10Mb (from the example above) and so end up in a deadlcok each process waiting for the other to release memory before it finishes it's job.

Reading up on MySQL configuration on their website could also be helpful (so that it fits well your Apache config).

Many other things can help with this problem (tune your SQL queries, tune your HTML, add RAM).

Maybe you are suffering from some other problem - but tuning your webserver config can never be bad

Thanks. I'll investigate the options.

sheryco · 12-21-2006, 04:08 AM

Hey Michux, if would help if you pasted your my.cnf file for better understand of your mysql performance.

michux · 12-24-2006, 08:00 AM

Quote:

Originally Posted by sheryco

Hey Michux, if would help if you pasted your my.cnf file for better understand of your mysql performance.

Here it is:

Code:

[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

old_passwords	= 1
bind-address		= 127.0.0.1
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
query_cache_limit	= 1048576
query_cache_size        = 16777216
query_cache_type        = 1

log_bin			= /var/log/mysql/mysql-bin.log

expire_logs_days	= 10
max_binlog_size         = 100M

skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

michux · 12-29-2006, 09:47 AM

So, any ideas?

michux · 01-01-2007, 08:14 AM

I moved the MySQL server to another machine and it seems that it was not MySQL killing the server but rather Apache killing MySQL. I just experienced a similar problem on the server with Apache only -- some cached pages still show up but all those that require a DB connection display a message about DB problems. But MySQL on the other server was up and running and I could connect to it w/o problems locally. Killing apache and restarting it was the only solution to bring life back into the server.

The machine is a PIII with 512MB of RAM.

Here are some apache settings that might be important to tweak:

Code:

ServerType standalone
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 30
MinSpareServers 5
MinSpareServers 5
MaxSpareServers 9
StartServers 3
MaxClients 100
MaxRequestsPerChild 0

If you have any ideas how to tweak those settings, I'd be very glad to hear them...

graq · 01-06-2007, 05:26 PM

As per my previous post, I'd recommend that you read up on Apache documentation (httpd.apache.org). Unless I missed it, you haven't stated which version you are using.

Your settings depend on what you are serving, to how many people and when.

Here is a setting for commercial web server that primarily delivers dynamic mod_perl content, where each process tends to be 35 to 40 Mb (10Mb shared) with 1Gb RAM:

Code:

<IfModule prefork.c>
StartServers       7
MinSpareServers    5
MaxSpareServers   25
ServerLimit       50
MaxClients        50
MaxRequestsPerChild  500
</IfModule>

To get a rudimentary idea of how big your processes are, log in to your sever when it is busy (or deliberately stress test it yourself) and type:

Code:

top | grep http

Read up on MaStartServers, MinSpareServers, MaxSpareServers, ServerLimit, MaxClients, MaxRequestsPerChild and understand how Apache controls it's threads. Monitor your usage and do some simple mathematics (size of process * number of processes = how much memory I have). That should give you some good starting values.

michux · 01-16-2007, 06:08 PM

Quote:

Originally Posted by graq

Read up on MaStartServers, MinSpareServers, MaxSpareServers, ServerLimit, MaxClients, MaxRequestsPerChild and understand how Apache controls it's threads. Monitor your usage and do some simple mathematics (size of process * number of processes = how much memory I have). That should give you some good starting values.

Thanks a lot for these hints. I'll let you all know if I had any success...

cormpadre · 06-27-2007, 10:07 AM

hi everyone,
I seem to be facing the same issue, it seems like instead of reusing the old unused apache2 processes. They just get bloated and apache spawns new processes to deal with new requests,it is a real killer on preformance. The only reason i can think of is that the processes are not either being recycled or killed
i apologise if im being abstract but im quite new to apache. Any progress being made as i am stumped.

michux · 07-06-2007, 07:02 PM

Quote:

Originally Posted by cormpadre

hi everyone,
I seem to be facing the same issue, it seems like instead of reusing the old unused apache2 processes. They just get bloated and apache spawns new processes to deal with new requests,it is a real killer on preformance. The only reason i can think of is that the processes are not either being recycled or killed
i apologise if im being abstract but im quite new to apache. Any progress being made as i am stumped.

I fixed the issue finally by using phpaccellerator software. Well, perhaps it could be treated more as a workaround than real solution but this indeed helped to get rid of the excesive memory usage, swapping and basically killing the server.

Thus it seems the problem was PHP and mysql or apache were affected only as a result of poor PHP performance (or poor PHP code, this needs to be investigated since we use a lot of Wordpress plugins which may be causing problems).

Anyway, hope this helps some of you