MySQL, tips

This article will be devoted to tips that will either enhance or tune MySQL for better performance.

Optimize MySQL tables

MySQL tables can grow large and cumbersome, and similar to defragging a hard drive to get optimal space, you can run

mysql> mysqlcheck -o <database>

to achieve a similar effect on your database. This is especially important and useful when you've changed a large database (e.g., deleted a lot of stuff from it.)

Play nice with others

If you're running a server that does a lot of heavy MySQL lifting, it makes sense that MySQL should run as fast as possible. An easy way to give MySQL priority is to re-nice it to a lower value. Go through the safe_mysqld script in your MySQL directory and add a line like

renice -10 $$ 

to it. You may find it necessary to comment out the section that starts with NOHUP_NICENESS and any if/fi loops in it. Note that if you set the nice value too low, the rest of the system's performance can be affected. For this reason, it's usually best not to go below -10 or -15; between -5 and -10 should be a pretty safe spot.

See also: MySQL