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

Editor to share with you how to effectively configure MySQL parameters, I believe 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!

1. Get the current configuration parameters

To optimize the configuration parameters, you must first understand the current configuration parameters and operation. Use the following command to obtain the configuration parameters currently used by the server:

Mysqld-verbose-help

Mysqladmin variables extended-status-u root-p

In the MySQL console, run the following command to get the value of the status variable:

Mysql > SHOW STATUS

If you only check a few state variables, you can use the following command:

Mysql > SHOW STATUS LIKE'[matching pattern]'; (you can use%,? Etc.)

2. Optimize the parameters

Parameter optimization is based on the premise that we usually use InnoDB tables instead of MyISAM tables in our database. When optimizing MySQL, two configuration parameters are the most important, namely table_cache and key_buffer_size.

Table_cache

Table_cache specifies the size of the table cache. 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. By checking the state values of peak time Open_tables and Opened_tables, you can decide whether you need to increase the value of table_cache. 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 state values can be obtained using SHOW STATUS LIKE '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.

For machines with 1 gigabyte of memory, the recommended value is 128, 256.

Case 1: this case comes from a server that is not particularly busy

Table_cache-512

Open_tables-103

Opened_tables-1273

Uptime-4021421 (measured in seconds)

In this case, the table_cache seems to be set too high. At peak times, the number of open tables is much less than that of table_cache.

Case 2: this case is from a development server.

Table_cache-64

Open_tables-64

Opened-tables-431

Uptime-1662790 (measured in seconds)

Although open_tables is already equal to table_cache, the value of opened_tables is also very low relative to server elapsed time. Therefore, increasing the value of table_cache should not be of much use.

Case 3: this case is from a upderperforming server

Table_cache-64

Open_tables-64

Opened_tables-22423

Uptime-19538

In this case, the table_cache is set too low. Although the run time is less than 6 hours, open_tables has reached its maximum, and the value of opened_tables is also very high. This requires an increase in the value of table_cache.

Key_buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Key_read_requests and Key_reads, you 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 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state value).

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.

For machines with 1G memory, if the MyISAM table is not used, the recommended value is 16m (8-64m).

Case 1: health status

Key_buffer_size-402649088 (384m)

Key_read_requests-597579931

Key_reads-56188

Case 2: alert statu

Key_buffer_size-16777216 (16m)

Key_read_requests-597579931

Key_reads-53832731

In case 1, the proportion is less than 1PR 10000, which is a healthy condition; in case 2, the proportion reaches 1:11 and the alarm has been sounded.

Optimize query_cache_size

Starting with 4.0.1, MySQL provides a query buffering mechanism. Using query buffering, MySQL stores SELECT statements and query results in a buffer, and in the future, for the same SELECT statement (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.

You can see whether the query_cache_size setting is reasonable by checking the status value Qcache_*, (the above status values can be obtained using SHOW STATUS LIKE 'Qcache%'). If the value of Qcache_lowmem_prunes is very large, it means that there is often insufficient buffering; if the value of Qcache_hits is also very large, it means that query buffering is used very frequently, and you need to increase the buffer size at this time; if the value of Qcache_hits is not large, it means that your query repetition rate is very low, in this case, using query buffering will affect efficiency, then 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.

Other parameters related to query buffering are query_cache_type, query_cache_limit, and query_cache_min_res_unit. Query_cache_type specifies whether to use query buffering, which can be set to 0, 1, 2, which is a SESSION-level variable. Query_cache_limit specifies the size of the buffer that can be used by a single query, which defaults to 1m. Query_cache_min_res_unit, introduced after version 4.1, specifies the minimum unit of buffer space allocated, which defaults to 4K. Check the status value Qcache_free_blocks, which, if very large, indicates that there is a lot of fragmentation in the buffer, which indicates that the query results are relatively small, and you need to reduce query_cache_min_res_unit at this point.

Open binary log (Binary Log)

The binary log contains all the statements that update the data, and its purpose is to use it to restore the data to its final state as much as possible when restoring the database. In addition, if you are doing synchronous replication (Replication), you also need to use binary logs to send changes.

To enable binary log, you need to set the parameter log-bin. Log_bin specifies the log file, and if you don't provide a file name, MySQL will generate the default file name itself. MySQL automatically adds a numeric index to the file name, and each time you start the service, a new binary is regenerated.

In addition, you can use log-bin-index to specify index files, binlog-do-db to specify databases for records, and binlog-ignore-db to specify databases that are not recorded. Note that binlog-do-db and binlog-ignore-db specify only one database at a time, and multiple statements are required to specify multiple databases. Moreover, MySQL will change all database names to lowercase, and all lowercase names must be used when specifying the database, otherwise it will not work.

Use the SHOW MASTER STATUS command in MySQL to view the current binary log status.

Enable slow log (slow query log)

Slow query logs are useful for tracking problematic queries. It records all queries that have checked long_query_time and, if necessary, records that do not use indexes. Here is an example of a slow query log:

To enable slow log, you need to set parameters log_slow_queries, long_query_times, and log-queries-not-using-indexes. Log_slow_queries specifies the log file, and if you don't provide a file name, MySQL will generate the default file name itself. Long_query_times specifies the threshold for slow queries, which defaults to 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0 that instructs records for queries that do not use indexes.

Configure InnoDB

Compared to the MyISAM table, the correct configuration of parameters is more critical to the InnoDB table. Among them, the most important parameter is innodb_data_file_path. It specifies the space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file allows automatic expansion. In this way, when the space is used up, the auto-extension data file will automatically grow (in 8MB) to accommodate the additional data. For example:

Innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend

The two data files are placed on different disks. The data is first placed in ibdata1, and when it reaches 900m, the data is placed in ibdata2. Once 50 MB of 8MB is reached, ibdata 2 will automatically grow in units.

If the disk is full, you need to add a data file to the other disk. To do this, you need to look at the size of the last file and calculate the nearest integer (MB). Then manually change the size of the file and add a new data file. For example, if ibdata2 already has 109MB data, you can modify it as follows:

Innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend

Flush_time

If the system has a problem and often locks or reboots, this variable should be set to a non-zero value, which will cause the server to refresh the cache of the table by flush_time seconds. Writing out changes to the table in this way degrades performance, but reduces the chances of table corruption or data loss.

Default values are generally used.

Binlog_cache_size

The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled (--log-bin option) If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.

3. Storage engine

In MYSQL 3.23.0, the MyISAM storage engine was introduced. It is a non-transactional storage engine and has become the default storage engine for MYSQL. However, if you use the setup wizard to set parameters, it will use InnoDB as the default storage engine. InnoDB is a transactional storage engine.

When you create a table, you can specify a storage engine for the table, with the following syntax:

CREATE TABLE t (I INT) ENGINE = MyISAM

CREATE TABLE t (I INT) TYPE = MyISAM

If not specified, the default storage engine is used. You can also use ALTER TABLE to replace the table engine, with the following syntax:

ALTER TABLE t ENGINE = MyISAM

The same database can contain tables from different storage engines.

Transactional tables have the following characteristics:

Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic are all the contents of the article "how to configure MySQL parameters effectively". 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.