MySQL Manual
5 MySQL Optimisation
- 5.5 Optimising the MySQL Server
- 5.5.1 System/Compile Time and Startup Parameter Tuning
- 5.5.2 Tuning Server Parameters
- 5.5.3 How Compiling and Linking Affects the Speed of MySQL
- 5.5.4 How MySQL Uses Memory
- 5.5.5 How MySQL uses DNS
- 5.5.6
SETSyntax
5.5.1 System/Compile Time and Startup Parameter Tuning
We start with the system level things since some of these decisions have to be made very early. In other cases a fast look at this part may suffice because it not that important for the big gains. However, it is always nice to have a feeling about how much one could gain by changing things at this level.
The default OS to use is really important! To get the most use of multiple-CPU machines one should use Solaris (because the threads works really nice) or Linux (because the 2.2 kernel has really good SMP support). Also on 32-bit machines Linux has a 2G file-size limit by default. Hopefully this will be fixed soon when new filesystems are released (XFS/Reiserfs). If you have a desperate need for files bigger than 2G on Linux-intel 32 bit, you should get the LFS patch for the ext2 filesystem.
Because we have not run MySQL in production on that many platforms, we advice you to test your intended platform before choosing it, if possible.
- If you have enough RAM, you could remove all swap devices. Some operating systems will use a swap device in some contexts even if you have free memory.
-
Use the
--skip-external-lockingMySQL option to avoid external locking. Note that this will not impact MySQL's functionality as long as you only run one server. Just remember to take down the server (or lock relevant parts) before you runmyisamchk. On some system this switch is mandatory because the external locking does not work in any case. The--skip-external-lockingoption is on by default when compiling with MIT-pthreads, becauseflock()isn't fully supported by MIT-pthreads on all platforms. It's also on default for Linux as Linux file locking are not yet safe. The only case when you can't use--skip-external-lockingis if you run multiple MySQL servers (not clients) on the same data, or runmyisamchkon the table without first flushing and locking themysqldserver tables first. You can still useLOCK TABLES/UNLOCK TABLESeven if you are using--skip-external-locking
