What is the best configuration my.cnf for my server, 5660 ?

Portal Home > Knowledgebase > Articles Database > What is the best configuration my.cnf for my server, 5660 ?

Posted by ALMGHARI, 05-24-2010, 09:22 AM
Hello, Please, This my server, what is the best configuration my.cnf please ? 2 x Xeon 5660 2.8 GHz 6 GB RAM DDR3 146GB , SAS . 15K 8TB BW CentOS Enterprise Linux - x86_64 + WHM/Cpanel Thanks. Last edited by ALMGHARI; 05-24-2010 at 09:27 AM.
Posted by IDediServer Kevin, 05-24-2010, 11:09 AM
Almghari, No matter what anyone says it is not a simple answer to give you. The tweaking of my.cnf depends on MANY factors, but I can help you make a good basic one. (imo mysqltuner is garbage) Do you know what type of tables you will be utilizing, all MyISAM, InnoDB, mix of both or something else? If you cannot answer this the likely answer is MyISAM. Thanks
Posted by ALMGHARI, 05-24-2010, 11:14 AM
Hello, xcintrik Yes xcintrik , All my tables MyISAM
Posted by IDediServer Kevin, 05-24-2010, 11:20 AM
Almghari, Ok that makes things a little more simple. Since you are also using Cpanel/Apache etc on this server things will fight for memory so you need to adjust apache so that your server doesn't consume all of its memory and SWAP out of memory. Can you post me the output of ps auwx --sort rss | tail -n 5 Also do you have populated databases yet? If so also run du --max-depth=0 -h /var/lib/mysql
Posted by ALMGHARI, 05-24-2010, 11:58 AM

Posted by IDediServer Kevin, 05-24-2010, 12:12 PM
Almghari, Can you post ' ps auwx --sort rss | egrep 'http|apache ' ? I would set in my.cnf under [mysqld] key_buffer_size=1G Then restart mysqld. If you can post the output of the above as well so we can adjust apache max clients. Being that this is MyISAM not much else needs done in my.cnf (maybe max clients but I wouldn't adjust that unless its absolutely needed) Regarding MySQL performance you will only gain performance via proper queries and indexing. The 1G size for indexes should allow for adequate growth as MySQL likely will not use that much until the database and index sizes grow. Thanks
Posted by ALMGHARI, 05-24-2010, 12:40 PM
my.cnf Now , That's enough ?
Posted by IDediServer Kevin, 05-24-2010, 12:48 PM
Almghari, If you want to see slow queries (you will need to figure out how to optimize them. Add log-slow-queries long_query_time=2 Any query taking longer than 2 seconds will be added to the slow query log file (default location: /var/lib/mysql/mysql-slow.log) You could also play with query cache settings. Start with something such as query_cache_size = 300M I would see how mysql performs with these settings while looking at the slow query log for optimizations at the indexing level. I also doubt you can support 500 connections while running cpanel/apache etc, I would suggest 275 at the most. A properly tuned database server such as this should never hit 275 connections anyways, if it does that means the database needs optimized.
Posted by ALMGHARI, 05-24-2010, 12:51 PM

Posted by NoSupportLinuxHostin, 05-24-2010, 01:38 PM
This is good advice in general, but it is not quite that black and white. If the OP is running a single really busy site on the server, then the advice is good. However, if the OP is hosting a thousand small sites for a lot of different webmasters of various levels of skill, then it is possible to use up 500 connections. So depending on whether this server is for a single busy site or a bunch of shared hosting sites, the config could vary.
Posted by IDediServer Kevin, 05-24-2010, 01:41 PM
The server could then use 500 connections yes but it shouldn't be doing that, this is ASSUMING persistent connections are used which would be a waste of memory imo in this case. Also understand that if that is the case MySQL is taking a large portion of memory on idle connections.
Posted by ALMGHARI, 05-24-2010, 02:35 PM
Thank you xcintrik Other opinions ...
Posted by ALMGHARI, 05-24-2010, 07:02 PM
Other opinions ...
Posted by Karl Austin, 05-25-2010, 05:51 AM
A 300M query cache? Even the busiest sites we host rarely fill 64MB of query cache with a 4MB limit on size. I'd check very carefully how much of that query cache you are using as with only 6GB of RAM that 300MB could be better used elsewhere IMHO.

Add to Favourites Print this Article