There are already many guides on the Internet on how to optimize MySQL server, MySQL is a very popular opensource database engine.
but most of such guides are too technical or too general or too specific which will not fit for any configuration.
What I have found and seems very promising, is a perl script which analyses your MySQL server and makes some recommendations regarding how to change your configuration to optimize the performance.
I tried it on a production hosting server and results were satisfactory, I am sure such scripts still can not beat database engineers, but if you dont have enough money to hire a a database engineer. they can be handful 😉
Here is the explanation from its website :
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
You can find the script and guide on this link : http://mysqltuner.com/