Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

ƒ -i or -information Provides detailed information about the table. Information that is

provided includes record length, the space used, and the lost space. Some nice

detailed information to see if you need to run the repair part of this utility.

ƒ -q or -quick Causes the utility to make a faster repair. The original file is not

touched. To use the original file, add another -q (myisamchk -qq).

ƒ -r or -recover This makes the utility repair any damaged files. It will also clean up

files and reclaim lost space.

ƒ -o or -safe-recovery This is the slower version of -r, and can sometimes repair

things that -r cannot.

ƒ -S or -sort-index This option causes myisamchk to sort the index from high to

low. This will result in faster queries when an index is used.

ƒ -u or -unpack Will uncompress a file that was compressed with myisampack.

ƒ -v This will give a more detailed explanation (verbose).

ƒ -w Causes the utility to wait until the table is unlocked before it performs any repair or

sorting operations.

You can also set the memory that can be allocated for these tasks. The more memory, the quicker the

task can be accomplished.

Maintaining your database tables is a must for good performance. A routine should be established so

that your tables are in good order. A shell script in a cron job will do for Unix based systems or a

batch job or Perl script that is scheduled for a Windows platform. Most problems can be caught before

they grow to the point where they would affect production.

To free up space and check your table for errors enter the following command:

>bin/myisamchk –r /usr/local/mysql/data/*/*.myi

This will clear up the dead space and check and fix your table. If you want to reorder your indexes on a

table, perform the following command:

>bin.myisamchk –S /usr/local/mysql/data/*/*.myi

All your indexes will be reindexed. This can take some time, so you may want to raise the memory that

would be used for this operation. You can do this by closing any other applications that are running and

using the -o switch on the myisamchk utility.

The myisamchk utility can be used to help speed up your database. By clearing dead space, it allows

for smaller records, which is the main focus of performance. Smaller is better.

Compiling and Compression

If everything you have done so far does not work, it is time to take more drastic measures. The first measure
you may want to consider is recompiling the source for your platform using a different compiler. The RPM
versions of MySQL are compiled using the fastest compiler with all the optimizations turned on. You may
want to move to a different platform, such as a Sun system, to gain some more performance power and the
use of more processors. You can also increase the speed of your executable by using a compiler that
creates faster executables for that system. Some people have claimed that this alone increases their
database's performance by 20-30%.
If you do not want to compile MySQL, the next option is to buy the email support so you can get the
myisampack utility. When used on tables that are read-only, a significant improvement of performance
is realized. This may make the difference.

Summary

Optimization is a black art. It takes a lot of practice and experience to know what works. And even then, it
can be a trial-and-error ordeal. The points that were made today, as well as the guidelines that were
provided, should give you a starting point to begin your quest for peak performance.

The following is a brief overview of the steps that can be taken to optimize yourdatabase:

1. Look at the hardware that is going to run your database server. Is it good enough to
   handle the volume of traffic that is going to be produced?


2. Look at the operating system. Some are better than others. Sun's OS, as well as
   Linux, is great for multiple processors. On the other hand, NT is easier to use.
   Weigh the pros and cons, and decide which is the best platform.