We start with system-level factors, because some of these decisions must be made very early to achieve large performance gains. In other cases, a quick look at this section may suffice. However, it is always nice to have a sense of how much can be gained by changing factors that apply at this level.
The operating system to use is very important. To get the best use of multiple-CPU machines, you should use Solaris (because its threads implementation works well) or Linux (because the 2.4 and later kernels have good SMP support). Note that older Linux kernels have a 2GB filesize limit by default. If you have such a kernel and a need for files larger than 2GB, get the Large File Support (LFS) patch for the ext2 file system. Other file systems such as ReiserFS and XFS do not have this 2GB limitation.
Before using MySQL in production, test it on your intended platform.
Other tips:
If you have enough RAM, you could remove all swap devices. Some operating systems use a swap device in some contexts even if you have free memory.
Avoid external locking. Since MySQL 4.0, the default has been for external locking to be disabled on all systems. The
--external-lockingand--skip-external-lockingoptions explicitly enable and disable external locking.Note that disabling external locking does not affect MySQL's functionality as long as you run only one server. Just remember to take down the server (or lock and flush the relevant tables) before you run myisamchk. On some systems it is mandatory to disable external locking because it does not work, anyway.
The only case in which you cannot disable external locking is when you run multiple MySQL servers (not clients) on the same data, or if you run myisamchk to check (not repair) a table without telling the server to flush and lock the tables first. Note that using multiple MySQL servers to access the same data concurrently is generally not recommended, except when using MySQL Cluster.
NoteMySQL Cluster is currently not supported in MySQL 5.5. Users wishing to upgrade a MySQL Cluster from MySQL 5.0 or 5.1 should instead migrate to MySQL Cluster NDB 7.0 or 7.1; these are based on MySQL 5.1 but contain the latest improvements and fixes for
NDBCLUSTER.The
LOCK TABLESandUNLOCK TABLESstatements use internal locking, so you can use them even if external locking is disabled.