When other webmasters ask me how to make their site faster, one of the first things I’ll mention is to optimize ther SQL settings and SQL database cache. One of the most important things is to increase the table cache rate, let’s find out how to do it

1. Open a shell – either SSH directly into your server if you are on a Unix OS or if you’re currently on Windows you should use Putty

2. Now cd into a directory on a partition you use for own scripts. If you don’t have one create on at /root/scripts enter mkdir /root/scripts

3. Alright, now we’re in the directory. Enter wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

4. After setting the correct permissions to run it via chmox a+x mysql* we can execute it

5. The first time we run it it won’t be particularly useful, because the scripts needs to fetch data for 24 hours to give accurate recommendations

6. Anyway, run it once ./mysqltuner.pl

Run Mysqltuner To Optimize Sql.png

Locate My.cnf SQL Config File And Enable MySQL cache

7. Now, depending on your system locate the file my.cnf – typically it will reside in /etc/

8. Enter pico /etc/my.cnf to see if it resides there – if it doesn’t enter locate my.cnf

9. Next we are going to enable the built-in MySQL query cache – this can dramatically speed up your site

Copy and paste this code:

# MySQL 4.x / 5.x has query caching available.
query_cache_type=1
query_cache_limit=4M
query_cache_size=300M

10. You may want to increase or decrease the size depending on your server RAM

Let’s Kill Idle Connections

One of the most annoying problem with SQL is that some clients (some of them bots) will keep connections open because they have a slow connection or they are hammering your server on purpose

To avoid idle clients holding connections we are now going to set a timeout

1. Copy and paste this code to prevent idle connections

wait_timeout=300
connect_timeout=300
max_connect_errors=10

Finally: Increasing The Table Cache Rate

On this site we have big SQL tables, but even a regular default Drupal or WordPress install is not what I consider lite-weight – so we need to increase the table cache rate

1. Copy and paste this code into your my.cnf file

max_heap_table_size = 300M
tmp_table_size = 300M
table_open_cache = 15000

2. The first two lines will make sure SQL does not create so called “temp database” on disk and use memory instead. Handy to speed up your site and overall SQL performance

3. The last line is the most important to increase the table cache rate – previously called table_cache it’s now called table_open_cache in MySQL 5.3+ (please look up the SQL manual for exact usage for your MySQL version – you may need to use table_cache on MySQL 4.x)

4. After 24 hours run mysqltuner.pl again and see if you are supposed to increase the table_open_cache value

5. Look up additional recommendations mysqltuner.pl gives you and adjust your settings – the script may warn you to increase the max_heap_table_size value but keep in mind that at some point it will be large enough and some queries simply cannot be stored in your memory and you will still receive a warning.

Keep optimizing the my.cnf file until you’re happy with your SQL performance

SQL Alternatives: MongoDB / NoSQL

Alternatively, look up MongoDB, MongoPress and the various WordPress and Drupal modules that are available

MongoDB Drupal Module