Learning MySQL

Server Variables

Variables configure server resources and can be used to optimize the server settings to suit the hardware of the host computer, and to allocate resources for improved performance. For example, the variable max_connections specifies the maximum number of clients that can be connected to the server at any one time.

When choosing a value for a server variable, you need to think carefully about the nature of your application and your clients. For example, when setting the max_connections variable, you need to remember that clients can include application web pages that interact with the database. This variable affects the number of people who can concurrently load the database-enabled web pages; each request to load such a page counts as a separate connection. Of course, these connections are short, typically lasting only a few seconds while the page is generated and served to the web browser.

Some of the more important variables control how memory and files are managed. MySQL databases are stored in files in the data directory, and the server needs to open and close these files. However, opening and closing files is a relatively slow operation, so the fewer times we need to do this, the better. The MySQL server variable table_cache specifies the maximum number of tables that can be open at once. The larger this number, the fewer times we need to close open files and open closed ones.

You also need to consider how the max_connections value influences the value you choose ...