Optimizing MySQL and Apache - Help - Knowledgebase

Portal Home > Knowledgebase > Articles Database > Optimizing MySQL and Apache - Help

Optimizing MySQL and Apache - Help

Posted by goldyweb, 04-25-2010, 04:23 PM
Hello, I have been having some load issues with my server due to the response time in the MySQL server (I suspect that it is due to that). The load hits to 15/20 at peak hours (like just about 2 hours) and is below 1 most of the time. I have been tracking down the process on what is causing it and it seems it is the ajax chat on about 3 websites on the server. I would like to optimize the server in such a way that the ajax chat does not cause any issue to the server and slow down the other websites on the server. Here are my 'my.cnf' configs: I have also optimized my Apache to a particular extent although it hasn't made any significant difference. Server Details: Core2Duo - (2 processors) 2GB DDRII RAM WHM/Cpanel + Easy Apache + MySQL 5.1 PHP module - suPHP, PHP 5.2.9 Here are some recommendation's according to the MySQL tuner: I don't really get what it asks me to change and what value as the things seems pretty much the same. Can anyone direct me through? Can anyone help me please to optimize it further? Last edited by goldyweb; 04-25-2010 at 04:30 PM.
Posted by Richard-BCS, 04-25-2010, 05:16 PM
Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits 1. Optimize your tables in phpmyadmin and clean them (http://dev.mysql.com/doc/refman/5.1/...ize-table.html) 2. Set tmp_table_size/max_heap_table_size equal and you might want to increase it 3.Increase your table_cache by a lot. Make sure that you update the variables mentioned above and wait 48 hours until you run the MySQL tuner script again. Then check the output again if you get the same result try increasing those variables again.
Posted by goldyweb, 04-25-2010, 05:25 PM
Optimizing each and every database is quite time consuming while you do it via phpmyadmin. Is there a cron I can set? Or is there a SSH command which would do it? Or is there a my.cnf setting which can also help? tmp_table_size/max_heap_table_size is suggested as 32M while it is already 64M. Would Increasing help? Increase your table_cache by a lot - Well, 'a alot' doesn't give me a idea of how much. Can you please give me a figure for it?
Posted by goldyweb, 04-26-2010, 07:20 PM
I would just like to bump it up further so others can look into it. Been 24 hours.
Posted by VMhostsTech, 04-29-2010, 06:43 PM
Hi, Some times the mysql may increase load on the server when php scripts tries to fetch data from mysql. You need to check whether any php scripts are using resources of the server at the same time when load gets increased. If there appears that php is also running try installing eAccelerator on the server.
Posted by Steven, 04-29-2010, 09:30 PM
You can tweak mysql until the day you die but, if a script is coded poorly not much will come from it depending on how the query operates - example: lack of indexes. Faster disks will help often. You should upgrade your ram as well. Have you been able to track down any of the queries? If so can you run an 'explain' on them and post them back here?
Posted by goldyweb, 05-02-2010, 10:07 PM
I have tried some of its recommendations and here is the output after 2 days: New results from the tuners: MySQLtuner.pl Tuning-Primer.sh Things still don't seem to be top notch. Any help over this please ? MY.CNF @VMhostsTech I don't suppose the script is heavy enough to boost the load high. Ajax chat's normally work easily with about 50 simultaneous users without any problem on a 512MB server (I have seen it), well 1 website but yes it has worked out pretty easily. @Steven I haven't reached 1/2 way on the RAM limits as per the WHM/cPanel and also after monitoring the 'top' command for an hour, it doesn't seem to me that the RAM is a problem. 250GB SATA HDD is what I have. Can you give me an example of a 'explain' query? I don't have much knowledge on mySQL. Sorry..