Sixth. Optimizing Server Settings-high performance MySQL Schwartz

The default configuration for MySQL is not available for use in a large number of resources because of its high versatility.

Do not expect to change the configuration file will bring huge performance gains. The size of the lift depends on the workload, and you can usually get two to three times times the performance boost by choosing the appropriate configuration parameters. At this point, the performance boost is incremental. For greater improvement, it is common to check the architecture of the server architecture, queries, and applications.

6.1 Configuration Basics

1 First you need to know where MySQL gets the configuration information. (You can use the startup script to--defaults-file= the configuration file location.)

2 The configuration file is divided into sections, the first line of each section is [program name]

The MySQL program reads the parts with the same name as the program names, and many clients read the client section, which is where common settings are placed.

The server will typically read the mysqld section.

6.1.1 syntax, scope and dynamics

The configuration settings are lowercase, using underscores or dashes to split the words.

There are several scopes for configuration settings. Some settings are valid throughout the server ( global domain ), others are for each connection ( session domain ), and some are only valid for objects. Many session domain variables are the same as global variables, and can be thought of as global variables providing a default value. If you modify the value of a session domain variable, it is only valid within the current connection, and the value disappears after the connection is closed.

Notable examples are:

* The query_cache_size variable is global

* The sort_buffer_size variable has a global default value, but can be set in the session

* Join_buffer_size has a global default value and can be set in the session.

In addition to setting variables in the configuration file, you can set some values (not all values) when the server is running. MySQL called them dynamic variables.

eg

mysql> SET sort_buffer_size = <value>;

mysql> SET GLOBAL sort_buffer_size = <value>;