LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices



Reply
 
Search this Thread
Old 12-15-2006, 06:37 AM   #1
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Rep: Reputation: 15
MySQL killing the server


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.
 
Old 12-16-2006, 09:39 AM   #2
graq
LQ Newbie
 
Registered: Feb 2006
Posts: 19

Rep: Reputation: 0
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.
 
Old 12-17-2006, 08:53 AM   #3
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
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.
 
Old 12-17-2006, 02:04 PM   #4
graq
LQ Newbie
 
Registered: Feb 2006
Posts: 19

Rep: Reputation: 0
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

Last edited by graq; 12-17-2006 at 02:09 PM.
 
Old 12-17-2006, 07:02 PM   #5
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
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.
 
Old 12-21-2006, 05:08 AM   #6
sheryco
Member
 
Registered: Feb 2003
Location: FTWorth, TX
Distribution: Gentoo, Debian, CentOS, FreeBSD, NetBSD & OpenBSD
Posts: 55

Rep: Reputation: 15
Hey Michux, if would help if you pasted your my.cnf file for better understand of your mysql performance.
 
Old 12-24-2006, 09:00 AM   #7
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
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
 
Old 12-29-2006, 10:47 AM   #8
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
So, any ideas?
 
Old 01-01-2007, 09:14 AM   #9
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
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...
 
Old 01-06-2007, 06:26 PM   #10
graq
LQ Newbie
 
Registered: Feb 2006
Posts: 19

Rep: Reputation: 0
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.
 
Old 01-16-2007, 07:08 PM   #11
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
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...
 
Old 06-27-2007, 11:07 AM   #12
cormpadre
LQ Newbie
 
Registered: Jun 2007
Posts: 2

Rep: Reputation: 0
same issue

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.
 
Old 07-06-2007, 08:02 PM   #13
michux
Member
 
Registered: Nov 2004
Location: Warsaw, Poland
Distribution: Ubuntu
Posts: 76

Original Poster
Rep: Reputation: 15
Fixed with phpaccelerator

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
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. NoviceW Linux - Networking 17 09-17-2014 03:13 PM
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. suziecorbett Linux - Software 8 10-09-2008 02:52 AM
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) sunlinux Linux - Software 1 11-07-2006 01:08 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 09:44 AM
MySQL installation, killing me... Please help! WorldBuilder Linux - Networking 8 06-10-2003 08:24 AM


All times are GMT -5. The time now is 10:55 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration