Shulou(Shulou.com)05/31 Report--

Editor to share with you how to optimize the configuration of mysql server, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Before that, we need to master the following methods:

How to view the status and variables of MySql:

Mysql > show status-- display status information (extended show status like XXX)

Mysql > show variables-displays the system variable (extended show variables like XXX)

Mysql > show innodb status-- displays the status of the InnoDB storage engine

Shell > admin variables-u username-p password-- displays system variables

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

View status variables and help:

Shell > mysqld-- verbose-- help [| more # displayed line by line]

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

In order to adapt to more database application users, MySql provides connection (client) variables to provide different solutions for different user groups. The author makes some details on max_connections,back_log, as follows:

Max_connections refers to the maximum number of connections in MySql. If the number of concurrent connection requests of the server is relatively large, it is recommended to increase the number of parallel connections. Of course, this is based on the case that the machine can support it, because if the number of connections is more, MySql will provide a connection buffer for each connection, which will cost more memory, so you should adjust this value appropriately, not blindly increase the setting value. You can view the number of connections in the current state through the conn% wildcard to determine the size of the value.

Back_log is the number of connections required for MySQL. This works when the main MySQL thread gets a lot of connection requests in a very short period of time, and then the main thread takes some (albeit short) time to check the connection and start a new thread. The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. If you expect to have many connections in a short period of time, you need to add it. In other words, if the connection data of MySql reaches max_connections, new requests will be stored in the stack waiting for a connection to release resources. The number of back_log in the stack will not be granted connection resources if the number of waiting connections exceeds back_log. In addition, this value (back_log) is limited to the size of your operating system's listening queue for incoming TCP/IP connections. Your operating system has its own limit on this queue size (you can check your OS document to find the maximum value of this variable), trying to set the limit that back_log is higher than your operating system will not be valid.

After optimizing the post-concatenated properties of MySql, we need to look at the buffer variables:

When using a MySql database to store large amounts of data (or using complex queries), we should consider the memory configuration of MySql. Configuring the MySQL server to use too little memory can result in suboptimal performance; configuring too much memory can lead to crashes, inability to execute queries, or severe slowdowns in swap operations. Under the current 32-bit platform, it is still possible to use up all the address space, so it needs to be examined.

The formula for calculating the use of memory can solve this part of the problem relatively. Nowadays, however, this formula is very complicated, and more importantly, the value calculated through it is only "theoretically possible" and not really consumed. In fact, regular servers with 8GB memory often run to the maximum theoretical value (100GB or higher). In addition, you will not easily use the "excess factor" (it actually depends on the application and configuration). Some applications may require 10% of the theoretical memory and some only 1%.

So, what can we do?

Take a look at the global buffers that need to be allocated at startup and always exist!

Global buffering:

Key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size,innodb_log_buffer_size, query_cache_size

Note: if you use a lot of MyISAM tables, you can also increase the operating system's cache space so that MySQL can also be used. To add these to the memory values required by the operating system and applications, you may need to add 32MB or more memory to the MySQL server code and various small static buffers. This is the memory you need to consider when the MySQL server starts. The rest of the memory is used for connections.

Key_buffer_size determines the speed of index processing, especially the speed of index reading. Generally, we set it to 16m. By checking the status values Key_read_requests and Key_reads, we can see whether the key_buffer_size setting is reasonable. The key_reads / key_read_requests ratio should be as low as possible, at least 1 key_read% is better. (the above state values can be obtained using key_read% to display state data). Key_buffer_size only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can use the check status value created_tmp_disk_tables to learn more.

Innodb_buffer_pool_size has the same effect on InnoDB tables as key_buffer_size does on MyISAM tables. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For a separate MySQL database server, you can set this value to up to 80% of 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 1m. It is usually not too large, as long as it is enough, which should have something to do with the complexity of the table structure. If not, MySQL writes a warning message to the error log.

Innodb_log_buffer_size specifies the cache size that InnoDB uses to store log data. If your table operation contains a large number of concurrent transactions (or large-scale transactions) and requires log files to be recorded before the transaction is committed, increase this value as much as possible to improve log efficiency.

Query_cache_size is the query buffer size of MySql. (since 4.0.1, MySQL provides query buffering.) using query buffering, MySQL stores SELECT statements and query results in a buffer, and in the future, for the same SELECT statements (case-sensitive), the results will be read directly from the buffer. According to the MySQL user manual, the use of query buffering can be up to 238% efficient. By checking the status value 'Qcache_%', you can see whether the query_cache_size setting is reasonable: if the value of Qcache_lowmem_prunes is very large, there is often insufficient buffering. If the value of Qcache_hits is also very large, query buffering is used very frequently, and the buffer size needs to be increased. If the value of Qcache_hits is small, it indicates that your query repetition rate is very low, in which case the use of query buffering will affect efficiency, so you can consider not using query buffering. In addition, adding SQL_NO_CACHE to the SELECT statement makes it clear that query buffering is not used.

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

Connection buffering:

Each thread that connects to the MySQL server needs its own buffer. You probably need to allocate 256K immediately, and even when threads are idle, they use the default thread stack, network cache, and so on. After the transaction starts, more space is needed. Running a smaller query may only add a small amount of memory to the specified thread, but if you do complex operations on a data table, such as scanning, sorting, or requiring a temporary table, you need to allocate about the size of read_buffer_size,sort_buffer_size,read_rnd_buffer_size,tmp_table_size. However, they are only allocated when needed, and are released after those operations are done. Some are immediately assigned into separate chunks. The tmp_table_size may be as high as the maximum memory space that MySQL can allocate to this operation. Note that there is more than one thing to consider here-- multiple caches of the same type may be allocated, for example, to process subqueries. Some special queries may use more memory-- if you need to allocate bulk_insert_buffer_size-size memory when doing batch inserts on MyISAM tables, and if you need to allocate myisam_sort_buffer_size-size memory when executing ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands.

Read_buffer_size is the MySql read-in buffer size. A request for a sequential scan of the table allocates a read buffer and MySql allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If sequential scan requests for tables are very frequent, and you think frequent scans are too slow, you can improve its performance by increasing the value of the variable and the memory buffer size.

Sort_buffer_size is the buffer size used by MySql to perform sorting. If you want to increase the speed of ORDER BY, first see if you can let MySQL use indexes instead of additional sorting phases. If not, try increasing the size of the sort_buffer_size variable.

Read_rnd_buffer_size is the random read buffer size of MySql. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySql scans the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.

Tmp_table_size is the heap (stacking) table buffer size of MySql. All joins are done within one DML instruction, and most unions can be done without even temporary tables. Most temporary tables are HEAP tables. Temporary tables with large record lengths (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 (stacking) table exceeds tmp_table_size,MySQL, you can automatically change the in-memory heap table 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, if you increase this value, MySql will also increase the size of the heap table, which can improve the speed of the join query.

When we have set the buffer size, let's take a look at:

The number of tables opened by all threads of table_cache, and increasing this value increases the number of file descriptors required by mysqld. Whenever MySQL accesses a table, if there is space in the table buffer, the table is opened and placed in it, allowing faster access to the table contents. You can determine whether you need to increase the value of table_cache by checking the state values' Open_tables' and 'Opened_tables', of the peak time. If you find that open_tables equals table_cache and opened_tables is growing, then you need to increase the value of table_cache (the above status values can be obtained using 'Open%tables'). Note that table_cache cannot be set to a large value blindly. If set too high, it may result in insufficient file descriptors, resulting in unstable performance or connection failure.

After making the above tuning settings, MySql should basically meet your needs

The above is all the content of this article "how to optimize mysql server configuration". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.