After the initial installation of a MySQL database, IT managers may detect that the I/O performance of their servers has begun to degrade.
One of the most common reasons for this is hard drive fragmentation. If your particular installation uses a high number of random calls, this will often lead to fragmentation across all the installed memory.
Having a defrag regime in place is essential to avoid this performance issue.
One of the key areas to look for fragmentation is in the SQL tables. This can often happen with random deletion and insertions increasingly fragmenting a table until there is a drop in performance in the host server.
In addition, if you are seeing increases in disk space usage that can't be explained by other actions you have taken, it's likely that a fragmented table is to blame, as they tend to take up more of the available disk space.
First step
The most common tables to experience fragmentation will be InnoDB and MyISAM. For system administrators tasked with understanding why their database performance has eroded, focusing on the former type of table first will always be a good first step.
This is because as the InnoDB table marks any written data as deleted that block remains empty and is not overwritten with new data. Of course, over time this artificially inflates the table with a corresponding issue with performance.
Generally, running the 'Optimise Table' routine will rebuild the table and its index. Note that the table will be locked while this command is carried out.
Also, sysadmins should be aware that secondary tables could still have high levels of fragmentation even after the routine is complete.
However, many sysadmins are being more intelligent with their defragmentation regimes, as they realise that some tables will have more traffic than others. Performing a hard defragmentation via the Optimise Table routine can be inefficient.
One consequence of doing this regularly is that each instance generates a transaction log, which can take increasing amounts of time to back up. Also, checking the actual level of fragmentation on each index before you start can often be highly revealing.
Setting a minimum level of index fragmentation before defragging is performed will ensure you are not performing unnecessary defrags on your servers.
Low fill factor
Sysadmins should also think about how they have their database set up. In some cases this will include a low fill factor, which speeds up writes to the database but conversely slows reads.
They can also can see performance advantages if they can cache their databases.
If this isn't possible look at how the database is spread over the installed hard drives. If you have shared storage across a Dell PowerEdge installation for instance, think about how you could simplify this to reduce the amount of random accesses your database has to perform over a given number of hard drives.
In addition to focusing on the database tables themselves, IT managers should also think about their maintenance of the physical hard drives that their servers are using. An OS defragmentation at regular intervals should offer better life expectancy for the hardware and also allow installed applications to see performance boosts.
