11.6 Optimizing the Operating System for MySQL Use

MySQL has many configurable parameters that you can change to optimize server performance. Several of these are related to operating system resources and thus cannot be set higher than what the operating system allows. MySQL operates within the boundaries of the limits set by the OS. For example, you might request enough file descriptors to be able to open 2,000 files at once, but the effective limit is 1,000 if that's how many descriptors the operating system allows to each process. You can sometimes gain more latitude by increasing operating system limits; then the MySQL server can take advantage of the higher limits. Some of the relevant operating system limits include the following:

• The per-process limit on the number of open files. This limits the maximum size of the table cache that holds file descriptors for table files. You can tell MySQL to allocate more file descriptors with the --open-files-limit option, but that option cannot be increased beyond the per-process limit allowed by the operating system. If your operating system can be reconfigured, you might be able to increase this limit, which effectively allows a larger maximum table cache size.

• The maximum number of clients that can be connected simultaneously. This limit is controlled by the max_connections server variable. You can increase this variable, but not beyond the number of threads allowed to each process by the operating system. (Each connection is handled by a separate thread within the server.) To allow more connections than that, you must reconfigure the operating system to allow more threads.

• The number of queued network connections for clients that are waiting to connect. For a busy server with a high rate of client connections, increasing the backlog allowed by the operating system allows you to increase the value of the back_log server variable that governs the size of the server's queue.