Dedicated / VPS Server, Manage your database

[runthis]

This is if you have a Dedicated solution.

Managing your database is obviously important, but you may want to take advantage of some of the great things you can now do

Some of these options need to be adjusted for specific sites as certain things could cause major issues, for example taking my first suggestion below and setting "tmp table size" to a high number would cause something like this to happen. Max user connections set at 200, tmp table size of 100mb would equal a 20gb database running if those connections were at max, be wary of these suggestions and research first.

The first step is taking a look at phpmyadmin > status > all status variables

Next, find your red sections, I am sure there is at least one. A common one is this

 

The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based. 

 

You can then go to Variables in phpmyadmin and navigate to the tmp table size row, depending on your phpmyadmin version (why dont you have the latest!) and click the variable of 16mb it is probably at and up this to 32mb. A great way to avoid these is to also rewrite your queries. too

 

Next thing you might see is

 

The number of tables that have been opened. If opened tables is big, your table cache value is probably too small. 

 

This is an easy fix and could be solved my navigating the same way as above to Variables and finding "table open cache" and adjusting this to a number that would cause this to not increase. Some testing may need to be done

 

Ok now the fabled innodb discussion. This is merely choice on your part and you should be looking this up, while InnoDB has increased the speed of my game it may slow your down :( Here is a way you change your tables to innodb though if ever interested in doing so. In a truly optimized world you should be using different engines on different tables for different reasons, for example if you have a table that stores data and is rarely accessed, like a log, you should use the Archive engine.

Since this solution was not found by me I will have to link the original article, this is a nice sweet code that converts the tables in your database to innodb for you, I am linking this as the person who wrote this offers warning and suggestions you should read first.

http://kvz.io/blog/2010/04/27/convert-all-tables-to-innodb-in-one-go/

 

That is all for now as I worked a long shift last night but there are tons more things you an do and hopefully this gets the pot stirring for some other nice methods from users here. Feel free to add your own findings.

 

One still relevant article on optimizing your mysql queries specifically is this - http://www.openlogic.com/wazi/bid/195905/Tips-and-Tricks-to-Optimize-MySQL

Edited April 7, 2013 by runthis