MySQL basic tuning
Posted on Wed 20 April 2011 in MySQL
Most webapplications are using a database to store there information nowadays. Often MySQL is used to do this task if it's an cost sensitive organisation or person. MySQL is easy to install and manage in small environments but if you have many transactions and higher load on your database you should know how to speed up the database in a safe way.
Some time ago i did some research on this in the web and found some nice information, sites and tools.
Before we start we should think about the machine providing the database. Time is money and if you try to optimize a database on an 10 year old Pentium3 Server with 512MB Ram you should think about buying a better hardware and not spending hours of your time in such a task. Check your system logs to be sure everything with your hard disks is o.k if the performance was better in the past.
If that hardware is o.k make a backup.
First thing you should do if you want to optimize your database is to monitor how the database works now. We need some numbers to compare before and after optimization. Matthew Montgomer developed a nice script to analyze MySQL databases. You can find the script here.
Now review the output of the script and start optimizing you database.
After you are done restart your MySQL database and rerun the script to be sure your changes are o.k and the database is running.
Some more useful information are available here: