Ok, so I’ve got a MySQL database… it is on a dedicated server – a cheap one. Only has 2gig ram and a low end i3 processor. It is running on a Unix based system and the performance is not very good.

Problem is the dedicated server is paid up for another 1/2 year, so I want run out the contract and move to a bigger box after the contract runs out.

I started doing some Googling (is that a word?) to locate information on tweaking MySQL.

Came across an excellent bit of kit called mysqltuner. Easy to install with apt-get or yum as it is available as a package (mysqltuner). Then just execute mysqltuner and provide the admin login/password for the local server.

I knew the server was not setup correctly, but no idea what needs to be adjusted. The sql server needs to run for at least 24 hours to get a baseline of what needs to be adjusted.

I made a few small adjustments (reducing max connections, which sucks up way too much ram) and have restarted the server. Will run the command again after 24 hours and make a few more of the suggest changes. Nice piece of software, if you are looking to tweak your MySQL – do check it out.

You can see in the image that the server has been running for 180 days, 88% of the usage is writing and the maximum number of connections at one time to the server during that time has been 619 simultaneous connections.

However because the performance of the slow performance of the machine we moved most of the clients off the box so we have reduced the maximum connections down to 100 which saves a lot of ram.

The funny part I see is the databases consume 45.7gigs currently and the tools suggests a buffer_pool_size of more than 45gigs. That means the SQL server could run 100% out of RAM, sorry mySQL that isn’t going to happen on this server 🙁