MySQL database server optimization details _ MySQL

MySQL database server optimization details bitsCN.com

MySQL database server optimization details

I recently checked some information about MySql optimization on the Internet and completed some optimization settings in detail against the MySql manual. However, due to the time constraints, before you can experiment or test these settings, you can apply these methods to the actual situation and draw a specific conclusion. The optimization method is roughly as follows:

To use the background management program of the MySql server, the program must run because the client accesses the database by connecting to the server. Next let's optimize our MySql database service based on the system variables and status variables of the server.

Before that, we need to master the following methods:

How to View MySql status and variables:

Mysql> show status -- Display status information (extended show status like 'XXX ')

Mysql> show variables -- Display System variables (extended show variables like 'XXX ')

Mysql> show innodb status -- display the InnoDB storage engine status

Shell> mysqladmin variables-u username-p password -- Display System variables

Shell> mysqladmin extended-status-u username-p password -- Display status information

View status variables and help:

Shell> mysqld -- verbose -- help [| more # display row by row]

First, let's look at the variables related to the request connection:

To adapt to more database application users, MySql provides connection (client) variables to provide different solutions for different user groups, the back_log statement is as follows:

Max_connections refers to the maximum number of connections of MySql. if the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections. of course, this is based on the support of the machine, because the higher the number of connections, MySql will provide a connection buffer for each connection, the higher the memory overhead will be. Therefore, you must adjust this value appropriately and do not blindly increase the setting value. You can use the 'conn' % 'wildcard to view the number of connections in the current status to determine the value size.

Back_log indicates the number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread. The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. If you want to have many connections in a short period of time, you need to add it. That is to say, if the connection data of MySql reaches max_connections, the new request will be stored in the stack to wait for a connection to release the resource. the number of stacks is back_log, if the number of connections waiting exceeds back_log, connection resources are not granted. In addition, this value (back_log) is limited to the size of the listening queue of your operating system for incoming TCP/IP connections. Your operating system has its own limits on the queue size (check your OS documentation to find the maximum value of this variable ), trying to set back_log to be higher than your operating system limit will be invalid.

After optimizing the MySql connection attributes, we need to look at the buffer variables:

When using the MySql database to store a large amount of data (or using complex queries), we should consider the MySql memory configuration. If the MySQL server is configured to use too little memory, it will lead to poor performance. if too much memory is configured, it will lead to a crash, the query cannot be executed, or the switching operation will seriously slow down. In the current 32-bit platform, it is still possible to use up all the address space, so you need to review it.

The formula for calculating memory usage can solve this problem. However, the formula is complicated now. More importantly, the calculated value is just a "theoretical possibility" and not a actually consumed value. In fact, regular servers with 8 GB of memory often run to the maximum theoretical value (GB or higher ). In addition, you will not easily use "excess factors" (it actually depends on applications and configurations ). Some applications may need 10% of the theoretical memory, while some require only 1%.

So what can we do?

Let's take a look at the global buffering that needs to be allocated and always exists at startup!

Global buffer:

Key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size, query_cache_size

Note: If you use a large number of MyISAM tables, you can also increase the operating system cache space so that MySQL can also be used. These are also added to the memory values required by the operating system and applications. you may need to add 32 MB or more memory to the MySQL server code and various small static buffers. These are the memory you need to consider when starting the MySQL server. The remaining memory is used for connection.

Key_buffer_size determines the index processing speed, especially the index reading speed. Generally, we set it to 16 M. by checking the status values Key_read_requests and Key_reads, we can check whether the key_buffer_size setting is reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better (the preceding status value can be obtained using 'key _ read % 'to display status data ). Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value 'created _ tmp_disk_tables 'to learn the details.

Innodb_buffer_pool_size is equivalent to key_buffer_size for The InnoDB table. InnoDB uses this parameter to specify the memory size to buffer data and indexes. For a separate MySQL database server, you can set this value to 80% of the physical memory.

Innodb_additional_mem_pool_size specifies the memory pool size that InnoDB uses to store data dictionaries and other internal data structures. The default value is 1 MB. Generally, it doesn't need to be too large. it only needs to be enough. it should be related to the complexity of the table structure. If not, MySQL will write a warning message in the error log.

Innodb_log_buffer_size specifies the size of the cache used by InnoDB to store log data. if your table operations contain a large number of concurrent transactions (or large-scale transactions), and the log files must be recorded before the transaction is committed, increase this value whenever possible to improve log efficiency.

Query_cache_size is the query buffer size of MySql. (MySQL provides a query buffer mechanism starting from 4.0.1). MySQL stores SELECT statements and query results in the buffer. in the future, for the same SELECT statement (case sensitive ), the results will be read directly from the buffer zone. According to the MySQL User Manual, query Buffering can achieve a maximum efficiency of 238%. Check the status value 'qcache _ % 'to check whether the query_cache_size setting is reasonable. if the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is insufficient frequently, if the Qcache_hits value is also very large, it indicates that the query buffer is used very frequently and the buffer size needs to be increased. if the Qcache_hits value is not large, it indicates that your query repetition rate is very low, in this case, the query buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no query buffer is used.

In addition to global buffering, MySql also issues connection buffering for each connection.

Connection buffer:

Each thread connected to the MySQL server must have its own buffer. About 256 KB need to be allocated immediately, and even when the thread is idle, they use the default thread stack and network cache. After the transaction starts, more space is required. Running a small query may only add a small amount of memory consumption to the specified thread. However, if you perform complex operations on the data table, such as scanning, sorting, or temporary tables, you need to allocate about read_buffer_size, sort_buffer_size, read_rnd_buffer_size, and tmp_table_size memory space. However, they are allocated only when needed and released after those operations are completed. Some are allocated to separate blocks immediately. Tmp_table_size may be as high as the maximum memory space that MySQL can allocate to this operation. Note: there is more than one thing to consider here-multiple caches of the same type may be allocated, for example, used to process subqueries. Memory usage for some special queries may be larger-if bulk_insert_buffer_size memory needs to be allocated for batch inserts on the MyISAM TABLE; execute alter table, optimize table, the memory size of myisam_sort_buffer_size needs to be allocated during the repair table command.

Read_buffer_size indicates the buffer size read by MySql. Requests that perform sequential scans on the table will be allocated with a read buffer, and MySql will allocate it with a memory buffer. The read_buffer_size variable controls the size of the buffer. If you want to scan the table in a very frequent order and think that frequent scanning is too slow, you can increase the performance by increasing the variable value and memory buffer size.

Sort_buffer_size is the buffer size used by MySql to perform sorting. To increase the order by speed, first check whether MySQL can use indexes instead of additional sorting stages. If not, increase the variable sort_buffer_size.

Read_rnd_buffer_size is the random read buffer size of MySql. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. if you need to sort a large amount of data, you can increase the value accordingly. However, MySql will issue this buffer space for each client connection. therefore, set this value as much as possible to avoid excessive memory overhead.

Tmp_table_size is the buffer size of the heap (stacked) table of MySql. All joins are completed in a DML command, and most Joins can be completed even without a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a large record length (the sum of the lengths of all columns) or tables containing BLOB columns are stored on the hard disk. If the size of an internal heap (stacked) table exceeds tmp_table_size, MySQL can automatically change the heap table in memory to a hard disk-based MyISAM table as needed. You can also increase the size of the temporary table by setting the tmp_table_size option. That is to say, if you increase this value, MySql will also increase the size of the heap table, which can improve the join query speed.

After setting the buffer size, let's take a look:

The number of tables opened by all threads in table_cache. increasing this value can increase the number of file descriptors required by mysqld. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values 'open _ tables 'and 'opened _ tables' of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding status value can be obtained using 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.

After the above optimization settings, MySql should basically meet your needs (of course, when the optimization settings are appropriate). We should also understand and note:

Only the memory consumption of a simple query OLTP (online transaction processing) application is usually less than 1 MB for each thread buffered by default. Unless complex queries are required, the buffer size of each thread does not need to be increased. Using a 1 MB buffer to sort 10 rows of records is basically as fast as using a 16 MB buffer (in fact, 16 MB may be slower, but this is something else ).

Find out the peak memory consumption of the MySQL server. This makes it easy to calculate the memory, file cache, and other applications required by the operating system. In a 32-bit environment, you also need to consider the 32-bit limit. The value of the limit "mysqld" is about 2.5 GB (in fact, many other factors need to be taken into account ). Run the "ps aux" command to view the value of "VSZ" (virtual memory allocated by the MySQL process ). Monitor the memory change value to know whether to increase or decrease the current memory value.

Finally, let's take a look at the optimization settings:

After installing MySql, the preparation file should be in the./share/mysql ("./" That is, the MySql installation directory) directory, the preparation file has a few, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf. The. ini file exists in the MySql installation directory in the Windows environment. Websites with different traffic and server environments with different configurations must have different configuration files.

Under normal circumstances, the preparation of the my-medium.cnf file can meet most of our needs; General we will copy the configuration file to/etc/my. cnf. in win environment, copy it to my. ini. you only need to modify this configuration file.

BitsCN.com