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

# mysqlcheck -u <username> -p -o <database>
(enter password)

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.)

Larger Databases

The default installation of MySQL will use the '/var' volume (or mount point) to store the database files on FreeBSD systems. This volume is typically used to store system log files and as such is typically allocated a small portion of the available disk space, particularly since logs are rotated and removed as part of the system maintenance processes. This poses a great limitation if a MySQL database is to grow beyond the available space on that volume. Fortunately it is possible to utilise another mount point with greater storage space with which MySQL can utilise as a database store. This is done using the following method:

# /usr/local/etc/rc.d/mysql-server stop
# cd /var/db
# mv mysql /svr
# ln -s /svr/mysql mysql

(thanks to The FreeBSD Diary for this tip)

This has the effect of moving the physical 'mysql' database store from the default '/var' volume to the larger '/svr' volume and then creates a symbolic link to the original location. This 'trick' allows MySQL to function without needing to re-configure all of its data store locations individually. This is greatly beneficial to systems with lots of databases.

For more information on the concepts of symbolic links used in this method, review this article.

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