optimizing MySQL :: need help ::

Portal Home > Knowledgebase > Articles Database > optimizing MySQL :: need help ::

Posted by X-x-X, 03-28-2011, 11:35 AM
Hi I need help to optimizing MySQL in my server please. I have load from MySql sometime can you tell me the best settings for a file "my.cnf" My Server specification: The number of sites : 166 ( Most of them have a VB Community ) Thank you
Posted by HostingFields, 03-28-2011, 11:48 AM
You will have to provide more informations then that. Please provide output of mysqltuner and tunning-primer. Good luck!
Posted by X-x-X, 03-28-2011, 12:10 PM
How can I print output of mysqltuner ?
Posted by X-x-X, 03-28-2011, 12:11 PM
tunning-primer Info :
Posted by HostingFields, 03-28-2011, 12:14 PM
Just copy it and paste it here. If that doesn't work for you, for some reason you can always do a screenshot and post it here. Good luck!
Posted by X-x-X, 03-28-2011, 12:24 PM
The mysqltuner info :
Posted by Akken, 03-28-2011, 12:49 PM
what y want to run on it?
Posted by X-x-X, 03-28-2011, 05:20 PM
The number of sites : 166 ( Most of them have a VB Community %70 )
Posted by X-x-X, 03-29-2011, 03:37 AM
help please >>
Posted by X-x-X, 03-29-2011, 09:55 AM
I need help
Posted by net, 03-29-2011, 10:00 AM
Moved > Hosting Security and Technology.
Posted by HostingFields, 03-29-2011, 11:39 AM
Please do free -m
Posted by X-x-X, 03-29-2011, 04:27 PM

Posted by HostingFields, 03-30-2011, 01:16 PM
You can try this configuration i use on similar server as yours. I am no MySQL expert, but since nobody else is helping you, lets try with this if you want. Please backup your configuration first before trying this one. When you insert this configuration, please restart MySQL. service mysql restart After that, you can try running both scripts (it usually recommend mysql to be up and running for at least 24 hours so) This could be a good start. Good luck!
Posted by X-x-X, 03-30-2011, 03:22 PM
Thank you for the assistance This shows your initiative in helping others After using the settings I could not able to restart the database "Failed"
Posted by HostingFields, 03-30-2011, 03:24 PM
Please post the full output. Thanks
Posted by IDediServer Kevin, 03-30-2011, 05:24 PM
You cannot use other peoples configuration files, every database is different. Please enable your slow query log and set the long_query_time to 1. Allow time for the slow log query to generate and use mysqlsla to analyze the log file. You will not get an optimized MySQL server without doing your homework or asking for configuration settings, most problems are with queries thus tuning my.cnf in most cases doesn't prove to be a performance boost of any magnitude. If you use mysqlreport and your MyISAM or INNODB buffer pools are 100% utilized then adjust those.
Posted by X-x-X, 03-31-2011, 12:14 PM
I see ....
Posted by webdesign jr, 04-04-2011, 09:54 AM
neither I am mysql expert but - definitively enable the query cache, increase the memory for the cache until not fully used, say until 80 percent usage - similarly increase gradually the memory for buffers, especiall key buffer and InnoDB Buffer Pool , but be extremely carefull when changing buffers allocated per thread eg. join buffer size etc. - if you set it 2M and got 500 threads running each with 2 joins, this is 2G memory for this buffer only! - use table chache effectively - increase the open file limit for mysql user to eg. 5000 than set the number of cached tables to 1000, later to 1500 - in short do small changes in week intervals, use automatically generated mysql reports, evaluate effects and do additional changes hop it helps