Before we get into the tuning of server settings and discussions of various tips and tricks regarding performance, we should cover briefly the importance of hardware settings-and by this we mean the hardware components of the server on which MySQL service is running. First of all, always try to aim for 64-bit processors along with 64-bit compatible operating systems. x64 is crucial in order to maximize memory efficiency.

As always, having high bandwidth memories can give a powerful boost. The formula of "the more, the better" applies to CPU cache sizes as well. Multiple CPU platforms, SMP, and even multi-core systems are preferred due to their increased scalability (serving multiple queries at the same time on a parallel processing basis).

RAID gives a ton of benefits, but be careful when deciding which kind of level you choose. For example, RAID1 (or RAID10) seems to be an okay choice, while RAID5 tends to become frustrating and too expensive to manage; in that case, the costs exceed the benefits by far. If you pick RAID then set medium-to-high RAID chunk size. Something along the lines of 512KB-1MB should be enough, but as always-test for yourself!

Lastly, when it comes to server performance, some people neglect the quality and throughput of network devices, such as network cards for example. Nowadays gigabit is the trend and they are really worth the effort. It's important that you check whether they are configured and working properly (full duplex?).

Moreover, keep in mind that the operating system is also responsible for the way your MySQL server performs. The server instance per se is a single process and, thus, the OS should be configured to permit really large process sizes. Additionally, enable the --large-pages option in MySQL if the OS you are running has this kind of capability. Also, since tables are quite large (usually) consider using a larger file system block size.

Finally, do various benchmarks on Disk I/O and memory performance because these two strongly affect the performance of your MySQL server. Also, enable and try different schemes of caching, re-benchmark the performance, and compare results. Fine-tune the read-ahead kernel technique. Check this documentation and read more.

Now let's talk a little bit about common mistakes that should be avoided. First of all, don't try to search for an "optimal" or very best MySQL configuration file because, who knows? You may find one that is claimed to be the best, and then you're going to set yourself up for nothing but frustration. However, check their content and see the values of the variables; try to search for the meaning of each in the official documentation.

As soon as you have "demystified" the content of the configuration file, then you can grab only the meaningful ones-here we refer to those that are useful for your own needs and server specs. Oh, and do not even attempt to use the default MySQL configuration. It won't be enough; the default config file is really weak and small.

There are a few commands that you should use to monitor the load and performance of the OS. For example, vmstat gives an overview of processes, virtual memory information, paging, block I/O, CPU usage, and all that; mpstat reports CPU-related statistics and also gives you global averages for each processor/core. Finally, you may want to check out iostat. This command gives you lots of input/output statistics...

Even though MySQL is cross-platform during the aforementioned commands, we've somewhat focused on Linux as the main operating system. If you are using Windows, then try to find third-party applications and/or utilities that report the same information to you. The bottom line is that you should be able to monitor the disk I/O and CPU performance.