Getting the most out of MySQL

Wednesday 6 January 2010

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_hosting

Return to blog page

Put Your Servers In Safe Hands With Our Supreme Cloud Hosting

View our services to see which of our hosting options best suits your needs.

ISO 27001 Certified

Compliant to ISO 9001:2015 & ISO 27001:2013, CWCS is independently audited for your assurance that you will receive the highest level of quality, and will ensure your information secure, intact, and only available to those authorised to access it.

G-Cloud Supplier

The UK Government G-Cloud is an initiative targeted at easing procurement by public-sector bodies in departments of the UK Government of commodity information technology services using cloud computing.

Cyber Essentials

Cyber Essentials certifiication demonstrates our commitment to cyber security. Government and industry have worked together to produce a set of standards which helps organisations safeguard against the most common cyber threats.

Call us on 0800 1 777 000 or email us at sales@cwcs.co.uk