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?
Thanks
[mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock user=mysql log-error=/var/log/mysqld.log log-slow-queries = /var/log/mysql-slow-queries query_cache_size=16M
old_passwords=1 set-variable = sort_buffer=512K set-variable = key_buffer=64M set-variable = table_cache=512
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
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 :-)
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 :-)
-Eric
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.
-Eric
Hey Eric
Yeah we've been using that :) 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.
-Eric
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.