Optimizing MySQL

I have a cloud server with a 1.2GHZ processor and 752MB RAM. My site experiences lag and lowered response time. MySQL hogs huge resources but it doesn’t improve the performance of the website. The website is highly database driven. The following is the config of the my.cnf file. How can we improve performance with this setup?


datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-slow-queries = /var/log/mysql-slow-queries

set-variable = sort_buffer=512K
set-variable = key_buffer=64M
set-variable = table_cache=512


Well, it sounds like you’re hoping that MySQL is a few button pushes away from taking a slow site and making it into a fast one.

That’s not likely to be the case :slight_smile:

MySQL tends to come with reasonable defaults… not that it can’t be tweaked, but chances are that if your performance is that bad, you’d want to start by reviewing the application being used, and the SQL calls being used.

You’ll likely have a far better time improving your performance by streamlining your SQL than by changing any of the MySQL settings.

If you Google for “optimize mysql”, you’ll see a number of articles on optimizing your SQL calls, along with some ideas for MySQL tweaks. Just be careful changing any of MySQL’s settings – settings are set the way they are for a reason, and if you change the wrong setting without knowing exactly why you’re changing it, you may end up decreasing your performance and causing more trouble down the road :slight_smile:


Do you by any chance know how to purge the mysql_slow_query file so we can remove all older queries from it?

The slow logs should be rotated weekly or nightly, depending on your setup.

However, if you look at the queries in the log, they should all be stamped with a date and time that the query was run. You’ll see something like this:

# Time: 110408 10:42:32 # User@Host: root[root] @ localhost [] # Query_time: 20 Lock_time: 0 Rows_sent: 3503645 Rows_examined: 3503645

You can use that to determine when the query was executed – at 10:42:32 on April 11, 2011 in the above case.


Hey Eric

Yeah we’ve been using that :slight_smile: Also this is what mysqltuner.pl suggested

General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_cache (> 64)

Sound ok?

Also, we force rotated logs, now mysql-slow-queries is always blank

The slow-queries log only shows really slow and really inefficient queries.

However, somewhat slow and somewhat inefficient queries may not show up in there.

Not seeing anything in there may just mean no “really slow/inefficient” queries are running.

Digging up the cause of the slowness you’re seeing may require careful profiling of your application.


Thanks Erik, I think we’ve worked most of the kinks in the system out. On reading your earlier reply, we went ahead and returned to the mysql config defaults.