For users with dedicated servers or VDS servers which use MySQL quite heavily, there can be ways to improve the performance without having to increase the specification and cost of the service.
Setting the memory limits correctly in the my.cnf file can help optimise a slow running server by limiting or expanding the amount of memory MySQL has to play with. This is usually stored for linux users in /etc/my.cnf or /etc/mysql/my.cnf. For most windows users it can be in the MySQL directory or etc directory within the mysql install directory.
Pay special attention to the [mysqld] command and those directly below it. Here are some useful limits which may already be set on your server:
set-variable = max_connections = 300
This variable will help limit the effects of any attacks or badly written code, but if you set this too low then users will start getting MySQL errors as soon as the limit is hit. We recommend somewhere between 100 and 500, depending on your server spec and the complexity of your MySQL code.
set-variable = max_allowed_packet=32M
This is the size of any individual packet sent from or to the SQL server, it can be a statement or one returned row. Try to keep this at 32M or below unless you know you are storing huge amounts of data (like a BLOB) in your databases, although for better speed try to keep the databases as small as possible and do not store large data chunks which could easily be stored as files and just referenced in the DB.
If you change these values, ensure to restart MySQL afterwards.
Special attention should be paid to the “query cache”. This is especially useful for sites which have a lot of SELECT statements running often. The query cache allows you to cache in memory the most common select queries, this reduces the load on the hard disk and can speed up MySQL processing quite considerably.
Log onto MySQL command line or a PHPMyAdmin (as the admin or root user) and type:
SHOW VARIABLES LIKE ‘have_query_cache’;
You should get a response similar to this:
| Variable_name | Value |
| have_query_cache | NO |
You might get a YES response, however it is important to set the cache size. This is the size in Bytes of the query cache (minimum 40000). We recommend you calculate this as a proportion of your overall available RAM on your server, avoid any more than 25% for a normal web server. It can sometimes be worth adjusting this to see how results change.
For exciting news, tips and developments, please follow us on Twitter – twitter.com/cwcs_hostingReturn to blog page