Portal Home > Knowledgebase > Articles Database > Apache + MYSQL Optimisation for high end server
Apache + MYSQL Optimisation for high end server
| Posted by Roido, 06-24-2008, 10:13 AM |
| Hi,
I have a fairly high end server in which I have installed SIM. SIM is restarting Apache up to 10 times a day, presumably due to high load causing un-availability.
On restart, Apache / MYSQL is stable until the load / mem usage begins to climb then it is restarted again. Here are my 'load' stats for today:
Load for today
High (2:18am): 4.63
Low (3:30am): 1.20
Mean: 1.84
Latest: 1.61
Mem usage for today
High (1:36am): 9,192.9 MB
Low (1:48am): 7,995.7 MB
Mean: 8,683.1 MB
Latest: 8,781.7 MB
I have seen it using 20GB RAM before.
I have tried to follow various optimisation guides but these seem tailored to less powerful servers.
The web application I run on this server is almost entirely MYSQL based, with thousands of DB calls a day. Across the entire system I probably get 200,000 bot hits per day or even more. At peak times search engine bots are literally hammering the server.
My server specs are:
# Processor: Quad-Core Xeon Processor * 8
# CPU Speed: 3Ghz
# RAM: 32 GB DDR2
# HDD: 36 GB 15k Raptor X2 + 1TB Sata
# OS: 32Bit CentOS
Changes / optimisation of the actual application is not possible.
My configuration files are:
My.cnf
Httpd.conf
Now I realise that server optimisation is not a one off job and it requires tweaking to adjust to your current set-up and conditions. But I would appreciate it if someone could suggest some baseline changes which I can apply.
And from there I can do the tweaks.
Any help would be appreciated.
Last edited by Roido; 06-24-2008 at 10:19 AM.
|
| Posted by zuborg, 06-24-2008, 11:32 AM |
| MaxRequestsPerChild 100
ServerLimit 2048
MaxClients 2048
KeepAlive On
KeepAliveTimeout 10
key_buffer=512M
join_buffer_size=16M
query_cache_size=256M
query_cache_limit=8M
sort_buffer_size=32M
myisam_sort_buffer_size=32M
Is PAE enabled in your kernel ? I mean - is OS actually using 32G, not 4G ?
|
| Posted by Roido, 06-24-2008, 11:50 AM |
| Thanks for your reply.
Yes, I should have mentioned that PAE is enabled.
I'll try your suggestions and report back.
Thanks again.
|
| Posted by zuborg, 06-24-2008, 11:54 AM |
| what is average size of httpd process ? mysqld one ?
|
| Posted by Roido, 06-24-2008, 11:59 AM |
| Can you tell this from top?
|
| Posted by zuborg, 06-24-2008, 12:11 PM |
| Of course
14M per httpd, 250M of mysqld
httpd memory consumption looks somewhat high, maybe there are some not used modules compiled into apache or php, like xml support for php.
Was mysql restarted with new settings ?
Overall load is not high, low number of httpd processes, very low mysqld cpu usage (if mysql was not restated ten minutes ago)...
|
| Posted by anatolijd, 06-24-2008, 12:37 PM |
| well,
at least you can increase SIM load limits to some upper value than 4.
you didn`t provided detailed explanation what exactly the server load is, what it is generated by, commands top/iostat output.
The only way i see for you is to tune mysql configuration. I see it is optimized for innodb, are there chances that is uses MyISAM tables also?
If so, having key_buffer=16M may be not good idea.
Try this or something similar to quicly get the important state variables and suggestions:
http://www.day32.com/MySQL/tuning-primer.sh
|
| Posted by Roido, 06-25-2008, 09:40 AM |
| Since I made your suggested changes the server has been rock solid! Not one Apache restart which is a record. So thanks for that!
I have lots of modules within httpd.conf that I do not use. Do you think I should comment out all of the ones that I do not require?
Yes, MYSQL was just restarted when I took this screen shot.
I have attached another screenshot of my top output.
I see that a MYSQL process is using 2.2GB. Do you think this is normal?
Hi,
should have said that I only monitor Apache with SIM.
All the tables for this application use innodb.
Thanks for the link to that tool I definitely check it out.
|
| Posted by zuborg, 06-25-2008, 10:09 AM |
| Welcome
Yes, it will decrease response delay.
The problem is that your OS is 32-bit. It limits address space for any process to 4G (~1G is reserved for kernel, so 4G mean <3G is upper limit of accessible RAM per process).
So, you may experience problems in future when mysql server will notice that there is not enough memory to work. I don't know exactly will you get this problem or no. If yes - decrease some buffers by half to keep running memory usage <2.5G.
High memory usage mean that mysql uses it efficiently instead of temporary files, which is good. Just keep it away from overusage.
Then key_buffer=512M is useless, decrease it back to 16M.
|
| Posted by Webdude, 06-25-2008, 10:11 AM |
| http://203.154.169.146/manual/mod/co...questsperchild
http://203.154.169.146/manual/mod/core.html#maxclients
I note this because you cant set maxclients higher than 256 unless you set it in httpd.h and recompile your apache.
There's a lot of dispute on best settings for other things like MaxRequestsPerChild. On the system mentioned here, I would start it at 20, see how that goes. Change each day 20, 40, 60, 80, 100. Find the one that works best for you. If you set to zero then it's basically putting each process to unlimited, which you dont want on a machine with load issues. Some people take it to 1000 and even 10,000 or more.... but personal opinion is it should never go above 100.
If you need to, turn keepalive off. Keepalive is essentially the same as persistent connections which is good in many cases, but not always. With that many processes hanging around, especially if not used, turning off keepalive should help stop them. It's personal preference though. If you dont feel it needs persistent connections, turn it off and see how things go. If it were my server I would turn it off, set maxchld to 20, and monitor from there, then tweak as needed.
|
| Posted by zuborg, 06-25-2008, 10:23 AM |
| It's not neccesary to edit httpd.h, you may add -DHARD_SERVER_LIMIT=2048 to `make` flags or simply `export HARD_SERVER_LIMIT=2048` in bash before compiling
Disabling keep-alive highly reduce web site performance and could never be used.
|
| Posted by Webdude, 06-25-2008, 11:09 AM |
| Either way really. Like most things linux, theres more than one way to skin a cat.
Untrue in many cases... true in others, which is why I said to test it. Really depends on what the server is mostly used for, as stated below.
|
Add to Favourites 
Print this Article
Also Read
Spam Tjek (Views: 764)
Westnic (Views: 787)