Optimizing MySQL Database Performance : Editing my.cnf Settings

Optimizing MySQL Database Performance Can be Done by Editing my.cnf File. By default, MySQL Server is configured for very low RAM server. Previously we wrote about how to Fix Sudden Error Establishing a Database Connection in WordPress. Actually, that was not truly only for WordPress, it will work for any CMS. That Fix works nice and is intended as a quick fix. After you are running the Server nicely, you must understand why MySQL Gone Away or Error Establishing a Database Connection are appearing. That is what we will do with Optimizing MySQL Database Performance by tuning its settings file named my.cnf. Also, you must optimize the PHP part in the way we have described in APC Configuration and Optimization for Rackspace Cloud Server (PVHVM).

 

Optimizing MySQL Database Performance : The Server Setup and Other Parts We Have Considered

 

 

We are taking that you are using PVHVM Virtualization Mode. We are assuming that you are running WordPress on a Single Performance Server on PVHVM mode in the way we described in the Full step by step guide to Install WordPress on Ubuntu 13.10 on Rackspace.

Location of my.cnf vary. You should run this command :

It will give you an informative output like :

You should consider – which is Global and Loads first in order. If ~/.my.cnf is Global and order is like above, you must have the same settings (copy one from another, you can use FTP if you feel uncomfortable). As MySQL is very important part of the setup, you must take a full image backup of the server plus backup of WordPress or the software you are running. In case you want to start Editing from /etc/my.cnf ; you must copy it to your computer for urgent and quick rescue.

query_cache_size is the size of cache as MySQL can cache the result set, avoiding the overhead of running through the data over and over.
key_buffer is the size of the buffer used with indexes. With a larger buffer, SQL command will finish faster. We keep one forth in numerical value of the physical RAM of the server for SQL server.

We provided two examples of explanation, you must read the docs to learn about the others.

This is a standard settings for a 2GB server :

You should change one by one (or add if does not exist), restart mysql server to check if fronted is going fine (else comment out or change the last thing…simple) and finally restart the whole server ( reboot is the command ) after stopping it :

Obviously, if you replace the word stop with restart or start, the desired easy to understand function will take place.

DO NOT try to optimize MySQL in Rackspace Deployment. They are fully optimized. There is a Perl script for MySQL Tuning, do not use it for PVHVM unless you are a database Guru.