O'Reilly logo

Linux Server Hacks by Rob Flickenger

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #84. MySQL Server Tuning

Try these practical steps to help make your MySQL server run as efficiently as it can

Many Linux administrators find themselves suddenly the "DBA in residence" when there is nobody else willing (or able) to take on the job. Many people specialize in tuning and maintaining databases for a living and don't touch sysadmin responsibilities at all, and yet more than one Linux administrator I've known has been required to take on DBA responsibilities with little training (and certainly no increase in pay). While this hack won't turn you into a DBA expert, it will hopefully show you some practical steps that have helped improve performance in real-world installations.

Here are five steps you can take to optimize your MySQL installation, roughly in increasing order of difficulty (and effectiveness).

  1. Run mysqlcheck -o database . This will optimize your tables, reclaiming lost space by "defragging" your database. This is especially useful if you have recently changed the structure of your database, or have deleted a large amount of data from it.

  2. Renice mysqld . If you are running a dedicated MySQL server, you can tell the scheduler to run mysql at a much higher priority than other tasks. The mysql manual recommends adding a line like this to your safe_mysqld script:

    renice -20 $$

    However, I have also found it necessary to find the following hunk of code:

    NOHUP_NICENESS="nohup" if test -w / then NOHUP_NICENESS=`nohup nice 2>&1` if test $? -eq 0 && test x"$NOHUP_NICENESS" ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required