One method of tuning involves adjusting the settings in your BIOS. Because the BIOS is not Linux and every BIOS seems different, always read your motherboard manual for better possible settings and make certain that all the drives are detected correctly by the BIOS. Change only one setting at a time.

Linux does provide a limited means to interact with BIOS settings during the boot process (mostly overriding them). In this section, you will learn about those commands.

Other options are in the following list, and are more fully outlined in the BOOTPROMPT HOWTO and the kernel documentation. These commands can be used to force the IDE controllers and drives to be optimally configured. Of course, YMMV (Your Mileage May Vary) because these do not work for everyone.

These options can be entered into /etc/lilo.conf or /boot/grub/grub.conf in the same way as other options are appended.

The hdparm utility can be used by root to set and tune the settings for IDE hard drives. You would do this to tune the drives for optimal performance.

Once a kernel patch and associated support programs, the hdparm program is now included with Fedora Core. You should experiment only with the drives mounted read-only because some settings can damage some file systems when used improperly. The hdparm command also works with CD-ROM drives and some SCSI drives.

The general format of the command is this:

# hdparm command device

This command runs a hard disk test:

hdparm -tT /dev/hda

You will need to replace /dev/hda with the location of your hard disk. hdparm will then run two tests: cached reads and buffered disk reads. A good IDE hard disk should be getting 400–500MB/sec for the first test, and 20–30MB/sec for the second. Note your scores, and then try this command:

hdparm -m16 -d1 -u1 -c1 /dev/hda

That enables various performance-enhancing settings. Now try executing the original command again—if you see an increase, you should run this command:

hdparm -m16 -d1 -u1 -c1 -k1 /dev/hda

The extra parameter tells hdparm to write the settings to disk so that they will be used each time you boot up—ensuring optimal disk performance in the future.

The man entry for hdparm is extensive and contains useful detailed information, but because the kernel configuration selected by Fedora Core already attempts to optimize the drives, it might be that little can be gained through tweaking. Because not all hardware combinations can be anticipated by Fedora Core or by Linux and performance gains are always useful, you’re encouraged to try.

# hdparm -tT device

Systemwide tweaks to hdparm are formally handled through the /etc/sysconfig/harddisks files, but this file’s use is poorly documented and, therefore, of little use.

Never content to leave things alone, Linux provides several tools to adjust and customize the file system settings. The belief is that hardware manufacturers and distribution creators tend to select conservative settings that will work well all the time, leaving some of the potential of your system leashed—that’s why you have chosen Red Hat Fedora 5 Unleashed to help you.

The Linux file system designers have done an excellent job of selecting default values used for file system creation and the 2.6 version of the Linux kernel now contains new code for the IDE subsystem that significantly improves I/O (input/output) transfer speeds over older versions, obviating much of the need for special tweaking of the file system and drive parameters if you use IDE disks. Although these values work well for most users, some server applications of Linux benefit from file system tuning. As always, observe and benchmark your changes.

# sync ; sync

mke2fs -O sparse_super will create a file system with sparse superblocks, reducing the space allocated to root for file system repair. This is not a performance enhancement per se, but it frees up additional space on a drive. This command works only on pre-2.2 kernels and is included here because a number of existing references don’t make that distinction. With kernel version 2.2, the sparse superblock option has been moved to tune2fs. This feature is turned on by default, so you will always create any new file systems with sparse superblocks.

mke2fs -b blocksize sets the block size. The block size chosen can also have an effect on the performance of the file system. A larger block size works better with large files and vice versa. There doesn’t seem to be any hard and fast rule about this, and most advice is to accept the default block size of 1024KB unless you want to spend some time running benchmarks.

With tune2fs, you can adjust the tunable file system parameters on an ext2 or ext3 file system. A few performance-related items of note are as follows:

Note that most of these uses of tune2fs free up space on the drive at the expense of the capability of fsck to recover data. Unless you really need the space and can deal with the consequences, just accept the defaults; large drives are now relatively inexpensive.

This utility checks an ext2/ext3 file system. Some useful arguments taken from man e2fsck are as follows:

Although not a performance tuning program per se, the badblocks utility checks an (preferably) unmounted partition for bad blocks. It is not recommended that you run this command by itself, but rather allow it to be called by fsck. It should be used directly only if you specify the block size accurately—don’t guess or assume anything.

The options available for badblocks are detailed in the man page. They allow for very low-level manipulation of the file system that is useful for data recovery by file system experts or for file system hacking, but is beyond the scope of this chapter and the average user.

Whenever Linux reads a file, it changes the last access time—known as the atime. This is also true for your web server: If you are getting hit by 50 requests a second, your hard disk will be updating the atime 50 times a second. Do you really need to know the last time a file was accessed? If not, you can disable the atime setting for a directory by typing this:

chattr -R +A /path/to/directory

The chattr command changes file system attributes, of which “don’t update atime” is one. To set that attribute, use +A and specify -R so that it is recursively set. /path/to/directory gets changed, and so do all the files and subdirectories it contains.

When you add indexes to your data, it enables MySQL to find data faster. However, ideally you want to have these indexes stored in RAM for maximum speed, and the variable key_buffer_size defines how much RAM MySQL can allocate for index key caching. If MySQL cannot store its indexes in RAM, you will experience serious performance problems. Fortunately, most databases have relatively small key buffer requirements, but you should measure your usage to see what work needs to be done.

To do this, log in to MySQL and type SHOW STATUS LIKE '%key_read%';. That returns all the status fields that describe the hit rate of your key buffer—you should get two rows back: Key_reads and Key_read_requests, which are the number of keys being read from disk and the number of keys being read from the key buffer. From these two numbers you can calculate the percentage of requests being filled from RAM and from disk, using this simple equation:

100 – ((Key_reads / Key_read_requests) × 100)

That is, you divide Key_reads by Key_read_requests, multiply the result by 100 and then subtract the result from 100. For example, if you have Key_reads of 1000 and Key_read_requests of 100000, you divide 1000 by 100000 to get 0.01; then you multiply that by 100 to get 1.0, and subtract that from 100 to get 99. That number is the percentage of key reads being served from RAM, which means 99% of your keys are served from RAM.

Most people should be looking to get more than 95% served from RAM, although the primary exception is if you update or delete rows very often—MySQL can’t cache what keeps changing. If your site is largely read only, this should be around 98%. Lower figures mean you might need to bump up the size of your key buffer.

If you are seeing problems, the next step is to check how much of your current key buffer is being used. Use the SHOW VARIABLES command and look up the value of the key_buffer_size variable. It is probably something like 8388600, which is eight million bytes, or 8MB. Now, use the SHOW STATUS command and look up the value of Key_blocks_used.

You can now determine how much of your key buffer is being used by multiplying Key_blocks_used by 1024, dividing by key_buffer_size, and multiplying by 100. For example, if Key_blocks_used is 8000, you multiply that by 1024 to get 8192000; then you divide that by your key_buffer_size (8388600) to get 0.97656, and finally multiply that by 100 to get 97.656. Thus, almost 98% of your key buffer is being used.

Now, on to the important part: You have ascertained that you are reading lots of keys from disk, and you also now know that the reason for reading from disk is almost certainly because you do not have enough RAM allocated to the key buffer. A general rule of thumb is to allocate as much RAM to the key buffer as you can, up to a maximum of 25% of system RAM—128MB on a 512MB system is about the ideal for systems that read heavily from keys. Beyond that, you will actually see drastic performance decreases because the system has to use virtual memory for the key buffer.

Open /etc/my.cnf in your text editor, and look for the line that contains key_buffer_size. If you do not have one, you need to create a new one—it should be under the line [mysqld]. When you set the new value, do not just pick some arbitrarily high number. Try doubling what is there right now (or try 16MB if there’s no line already); then see how it goes. To set 16MB as the key buffer size, you would need a line like this:

[mysqld]
set-variable = key_buffer_size=16M
datadir=/var/lib/mysql

Restart your MySQL server with service mysqld restart, and then go back into MySQL and run SHOW VARIABLES again to see the key_buffer_size. It should be 16773120 if you have set it to 16M. Now, because MySQL just got reset, all its values for key hits and the like will also have been reset. You need to let it run for a while so that you can assess how much has changed. If you have a test system you can run, this is the time to run it.

After your database has been accessed with normal usage for a short while (if you get frequent accesses, this might be only a few minutes), recalculate how much of the key buffer is being used. If you get another high score, double the size again, restart, and retest. You should keep repeating this until you see your key buffer usage is below 50% or you find you don’t have enough RAM to increase the buffer further—remember that you should never allocate more than 25% of system RAM to the key buffer.

Newer versions of MySQL allow you to cache the results of queries so that, if new queries come in that use exactly the same SQL, the result can be served from RAM. In some ways the query cache is quite intelligent: If, for example, part of the result changes due to another query, the cached results are thrown away and recalculated next time. However, in other ways it is very simple. For example, it uses cached results only if the new query is exactly the same as a cached query, even down to the capitalization of the SQL.

The query cache works well in most scenarios. If your site has an equal mix of reading and writing, the query cache will do its best but will not be optimal. If your site is mostly reading with few writes, more queries will be cached (and for longer), thus improving overall performance.

First, you need to find out whether you have the query cache enabled. To do this, use SHOW VARIABLES and look up the value of have_query_cache. All being well, you should get YES back, meaning that the query cache is enabled. Next, look for the value of query_cache_size and query_cache_limit—the first is how much RAM in bytes is allocated to the query cache, and the second is the maximum result size that should be cached. A good starting set of values for these two is 8388608 (8MB) and 1048576 (1MB).

Next, type SHOW STATUS LIKE 'Qcache%'; to see all the status information about the query cache. You should get output like this:

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 169544 |
| Qcache_hits             | 698    |
| Qcache_inserts          | 38     |
| Qcache_lowmem_prunes    | 20     |
| Qcache_not_cached       | 0      |
| Qcache_queries_in_cache | 18     |
| Qcache_total_blocks     | 57     |
+-------------------------+--------+
8 rows in set (0.00 sec)

From that, we can see that only 18 queries are in the cache (Qcache_queries_in_cache), we have 169544 bytes of memory free in the cache (Qcache_free_memory), 698 queries have been read from the cache (Qcache_hits), 38 queries have been inserted into the cache (Qcache_inserts), but 20 of them were removed due to lack of memory (Qcache_lowmem_prunes), giving the 18 from before. Qcache_not_cached is 0, which means 0 queries were not cached—MySQL is caching them all.

From that, we can calculate how many total queries came in—it is the sum of Qcache_hits, Qcache_inserts, and Qcache_not_cached, which is 736. We can also calculate how well the query cache is being used by dividing Qcache_hits by that number and multiplying by 100. In this case, 94.84% of all queries are being served from the query cache, which is a great number.

In our example, we can see that many queries have been trimmed because there is not enough memory in the query cache. This can be changed by editing your /etc/my.cnf file and adding a line like this one, somewhere in the [mysqld] section:

set-variable = query_cache_size=32M

An 8MB query cache should be enough for most people, but larger sites might want 16MB or even 32MB if you are storing a particularly large amount of data. Very few sites have need to go beyond a 32MB query cache, but keep an eye on the Qcache_lowmem_prunes value to ensure that you have enough RAM allocated.

Using the query cache does not incur much of a performance hit. When MySQL calculates the result of a query normally, it simply throws the result away when the connection closes. With the query cache, it skips the throwing away, and so there is no extra work being done. If your site does have many updates and deletes, be sure to check whether you get any speed boost at all from the query cache.

If you have tuned your key buffer and optimized your query cache and yet still find your site struggling, there are a few additional small changes you can make that will add some more speed.

When reading from tables, MySQL has to open the file that stores the table data. How many files it keeps open at a time is defined by the table_cache setting, which is set to 64 by default. You can increase this setting if you have more than 64 tables, but you should be aware that Fedora does impose limits on MySQL about how many files it can have open at a time. Going beyond 256 is not recommended unless you have a particularly database-heavy site and know exactly what you are doing.

The other thing you can tweak is the size of the read buffer, which is controlled by read_buffer_size and read_buffer_rnd_size. Both of these are allocated per connection, which means you should be very careful to have large numbers. Whatever you choose, read_buffer_rnd_size should be three to four times the size of read_buffer_size, so if read_buffer_size is 1MB (suitable for very large databases), read_buffer_rnd_size should be 4MB.

The biggest speed-ups can be seen by reprogramming your SQL statements so they are more efficient. If you follow these tips, your server will thank you: