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.
Miscellaneous Tweaks
If you have tuned your key buffer and optimized your query cache and yet still find your site struggling, you can make a few additional small changes 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.
Query Optimization
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:
> Select as little data as possible. Rather than SELECT *, select only the fields you need.
> If you only need a few rows, use LIMIT to select the number you need.
> Declare fields as NOT NULL when creating tables to save space and increase speed.
> Provide default values for fields, and use them where you can.
> Be careful with table joins because they are the easiest way to write inefficient queries.
> If you must use joins, be sure you join on fields that are indexed. They should preferably be integer fields because these are faster than strings for comparisons.
> Find and fix slow queries. Add log-long-format and log-slow-queries = /var/log/slow-queries.log to your /etc/my.cnf file, under [mysqld], and MySQL can tell you the queries that took a long time to complete.
> Use OPTIMIZE TABLE to defragment tables and refresh the indexes.
Reference
> http://www.coker.com.au/bonnie++/ — The home page of bonnie, a disk benchmarking tool. It also contains a link to RAID benchmarking utilities and Postal, a benchmarking utility for SMTP servers.
> http://httpd.apache.Org/docs-2.0/misc/perf-tuning.html — The official Apache guide to tuning your web server.
> http://dev.mysql.com/doc/refman/5.0/en/optimization.html — Learn how to optimize your MySQL server directly from the source, the MySQL manual.
One particular MySQL optimization book will really help you get more from your system if you run a large site:
CHAPTER 32
Command-Line Master Class
In the earlier years of Linux, people made a big fuss of the graphical environments that were available — they rushed to tell new users that you really did not need to keep going back to the command line to type things. Now that Linux is more mature, people accept that the command line is still a reality, and a welcome one. Although the GUI does make life easier for day-to-day tasks, your options are limited by what the developers want you to have — you cannot bring commands together in new ways, and neither can you use any of the GUI features if, for example, your GUI is broken. It does happen!
In his book
One evening, Master Foo and Nubi attended a gathering of programmers who had met to learn from each other. One of the programmers asked Nubi to what school he and his master belonged. Upon being told they were followers of the Great Way of UNIX, the programmer grew scornful.
'The command-line tools of UNIX are crude and back ward,' he scoffed. 'Modern, properly designed operating systems do everything through a graphical user inter face.'
Master Foo said nothing, but pointed at the moon. A nearby dog began to bark at the master's hand.
'I don't understand you!' said the programmer.
Master Foo remained silent, and pointed at an image of the Buddha. Then he pointed at a window.
'What are you trying to tell me?' asked the programmer.
Master Foo pointed at the programmer's head. Then he pointed at a rock.
'Why can't you make yourself clear?' demanded the programmer.
Master Foo frowned thoughtfully, tapped the programmer twice on the nose, and dropped him in a nearby trash can.
As the programmer was attempting to extricate himself from the garbage, the dog wandered over and piddled on him.
At that moment, the programmer achieved enlightenment.
Whimsical as the story is, it illustrates that there are some things that the GUI just does not do well. Enter
