Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need help in optimizing my my.cnf configuration file. I have two servers connected directly through LAN network (1 GBit/s). The first server is an Apache web server and the second one is a MySQL server.

The problem is taht the MySQL server uses a lot of CPU and my website become svery slow.

I have like 2500 simultaneous users.

http://www.textdump.com/v/?k=NTc1Ng==

Thank you very much for your help!

share|improve this question
add comment

4 Answers

I always have mysqltuner get info for me and suggest - it's a perl script you can get at github.

Direct link to the mysqltuner project page: http://blog.mysqltuner.com/

share|improve this answer
    
By the way, here's a direct quote from the mysqltuner faq page: "It’s key to remember that MySQLTuner is a script which can assist you with your server, but it is not the solution to a badly performing MySQL server." Seems like it could be a useful tool, but any performance gains from this are likely to be incremental, not anything huge. –  EEAA Feb 12 '10 at 5:37
add comment

With that high of concurrency, you really ought to look into something like memcached. When implemented correctly, it can greatly reduce the load of your database servers by caching (in RAM) frequently-accessed bits of content.

You may also consider spinning up a mysql slave or two. The idea there is that all of your database reads will be directed towards the slaves, and the writes to the master. Of course, your ability to do this somewhat depends on the application code and its support for clustered database systems.

share|improve this answer
add comment

I second mysqltuner!

You might want to start by using one of the example config files in /usr/share/mysql. There are several to chose from depending on the system resources available. Integrate any already customized options from your existing my.cnf into the example config.

Turn off storage engines that you aren't using as well:

skip-bdb
skip-ndbcluster
skip-federated
skip-innodb

At this point restart mysql and let it run for a few hours; so as to have representative performance values available. Then, as was recommended previously, download and run mysqltuner.

Beyond that you may want to configure graphing of mysql performance in Munin or another graphing solution. Hard to know if any anomalies or long term trends in mysql performance exist without something like this.

share|improve this answer
add comment

I would take a look at Jet Profiler for MySQL - It'll show you what queries are using the most cpu (among many other things) and suggest improvements.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.