MySQL database optimization technology configuration and indexing (mandatory)

(1) Reduce Database Access

Static pages are possible

Static parts of a dynamic page

Some data can be generated as XML or saved as text files

Use data caching technology, such as memcached

(2)Optimization Detection Method

1. User Experience Detection

2. MySQL status detection

Use the show status command in the MySQL command line to obtain the current MySQL status.

Focus on the following attributes:

Key_read_requests (number of index read requests) (the impact of key_buffer_size setting)

Key_reads (number of index read responses)

Key_blocks_used

Qcache _*

Open_tables (influenced by table_cache settings)

Opened_tables

Table_locks

3. Third-party Tool Testing

Mysqlreporthttp: // hackmysql.com/mysqlreport

Mytophttp: // jeremy.zawodny.com/mysql/mytop/

System and MySQL Log

System Command: Top, SAR

MySQL Log: slow_query.log

(3) hardware optimization

In terms of hardware, the most vulnerable part of MySQL is disk, followed by CPU and memory.

Disk

Using a faster disk will be helpful to MySQL

Using more hard disks and raid can speed up a single disk

For raid, we recommend that you use RAID 0 + 1 or raid 1 + 0.

CPU

There is no doubt that the CPU with a higher clock speed and more CPU can give MySQL more

High Performance

Memory

A higher memory usually allows MySQL to cache more data in the memory,

However, an important factor is that the correct MySQL configuration is required.

Nic

Use a gigabit Nic and a gigabit network

(4) Operating System Optimization

1. Do not use a swap zone. If the memory is insufficient, add more memory or configure your system to use less memory.

2. Do not use NFS Disks

3. Increase the number of files opened by the system and MySQL Server

Use ulimit-N 65535

4. Increase the number of processes and threads in the system.

5. disable unnecessary applications, optimize hard disk parameters, and use hdparm for testing.

(5) application-level optimization

1. Use multi-server Load balancer (multiple reads and writes, and use replication technology for data synchronization)

2. Table partition (custom partition, MySQL supports built-in Partition Function)

3. Use Data Cache Technology memcached

(6) MySQL Configuration Optimization

1. key_buffer (= 512): number of memory used by the index Buffer

This is very important for the MyISAM table. It is better to set it to 25%-30% of the available memory. Check the status values key_read_requests and key_reads,

You can check whether the key_buffer settings are reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better, otherwise it indicates that key_buffer settings are a little small

2. innodb_buffer_pool_size (= 512): The amount of memory used by the index buffer.

3. table_cache (= 1024): size of the data table cache area

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.

You can determine whether to adjust the value of table_cache by checking the open_tables and opened_tables Status values of the running peak time.

If you find that the value of open_tables is equal to table_cache and the status value of opened_tables is increasing, you need to add the table_cache parameter value,

You cannot blindly set the table_cache parameter to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.

4. sort_buffer_size (= 256): specify the length of the buffer for sorting.

The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 mb.

Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

5. join_buffer_size: the length of the buffer for the associated query.

4 GB memory or above. We recommend that you increase the memory size to 32 MB. The allocated memory corresponding to this parameter is exclusive to each connection!

6. max_connections (= 1024): number of threads that can be reused

The number of customers allowed to connect to the MySQL server at the same time. You can observe and estimate the maximum number of concurrent connections at the peak of the system to set

7. thread_cache (= *): number of threads that can be reused

Generally set to the number of CPUs × 2

8. innodb_buffer_pool_size (= 512): InnoDB table cache pool size

This is very important for InnoDB tables. Compared with MyISAM tables, InnoDB is more sensitive to buffering. MyISAM can run under the default key_buffer_size setting,

However, the default innodb_buffer_pool_size setting of InnoDB is similar to snail bait.

Because InnoDB caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need InnoDB, you can set it to up to 70-80% of available memory.

Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, you do not need to set innodb_buffer_pool_size too large.

9. innodb_flush_logs_at_trx_commit (= 1): The log refresh mode after the transaction is committed

Is it because InnoDB is 1000 times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that every update transaction committed (or a statement other than each transaction) will be refreshed to the disk,

This is quite resource-consuming, especially when there is no battery backup cache. Many applicationsProgram, Especially those from MyISAM, set its value to 2, that is, do not refresh the log to the disk,

Instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure,

Some transactions are lost when the MySQL server crashes. Set to 2 command for the part of the transaction that is lost and refreshed to the operating system cache.

(7) Table Optimization

1. select an appropriate Data Engine

MyISAM: Suitable for reading a large number of tables.

InnoDB: Suitable for writing and reading a large number of tables

 

2. Select the appropriate column type

You can use select * From tb_test procedure analyse () to analyze each field in the table and provide suggestions for optimizing the column type.

 

3. Use not null for columns that do not store null values. This is especially important for the columns you want to index.

 

4. Create an appropriate index

 

5. Use a fixed-length field to accelerate the process.

(8) Indexing principles

1. Use indexes reasonably

One table can only use one index in one query. The explain statement is used to check the operation of the optimization program.

Use analyze to help the optimizer make more accurate predictions on the index usage.

2. Indexes should be created on the Data columns involved in operations such as search, sorting, and grouping.

3. Try to create an index in a column with few duplicate data, so it is best to make the index unique.

For example, you can create an index for the birthday column, but do not create an index for the Gender column.

4. Try to compare shorter values for Indexing

Reduces disk I/O operations. The index buffer can accommodate more key values and increase the hit rate.

If you create an index on a long string, you can specify a prefix length.

5. Rational Use of Multi-column Indexes

If multiple conditions often need to be combined for query, you need to use multiple column indexes (because only one index can be used for a single table query at a time, and only one index can be used for creating multiple single column indexes)

6. Make full use of the leftmost prefix

That is to say, we need to reasonably arrange the order of the columns in the Multi-column index to rank the most commonly used columns first.

7. Do not create too many indexes.

Only the fields that are often used in where, order by, and group by must be indexed.

8. Use the slow query log to find the slow query (log-Slow-queries, long_query_time)

(9) Make full use of Indexes

1. Try to compare columns with the same data type

2. Try to make the index column independent in the comparison expression. Where mycol <4/2 uses the index, while where mycol * 2 <4 does not use

3. Try not to add a function to the query field,

For example, if where year (date_col) <1990 is transformed into where date_col <'2017-01-01'

Where to_days (date_col)-to_days (curdate () <cutoff transformed to where date_col <date_add (curdate (), interval cutoff Day)

4. Do not use wildcards at the beginning of the like mode. 5. you can use straight join to force the optimizer to join in the order of the from clause. You can select straight join, force all joins, or select * from a straight join B to force the order of the two tables. 6. Force index is used to force the specified index. For example, select * From song_lib Force Index (song_name) order by song_name is more efficient than Force Index. 7. Avoid using MySQL for automatic type conversion whenever possible. Otherwise, indexes cannot be used. For example, use where num_col = '5' for int-type num_col'

(10) Optimization of SQL statements

1. Create a suitable statistical intermediate result table to reduce the chance of querying data from a large table

2. Try to avoid subqueries and use the connection method instead. For example:

Select a. ID, (select max (created) from posts where author_id = A. ID) as latest_post

From authors

You can change it:

Select a. ID, max (P. Created) as latest_post

From authors as

Inner join posts P on (A. ID = P. author_id)

Group by A. ID

Select song_id from song_lib where singer_id in

(Select singer_id from singer_lib

Where first_char = 'A'

) Change limit 2000:

Select song_id from song_lib

Inner join singer_lib B on A. singer_id = B. singer_id and first_char = 'A' limit 2000

3. When the duplicate key is inserted, use the on duplicate key update:

Insert into db_action.action_today (user_id, song_id, action_count) values (1, 1) on duplicate key update action_count = action_count + 1;

4. Avoid using cursors.

The operation efficiency of cursors is extremely low. You can add temporary tables, use multi-table queries, and update multiple tables to complete tasks. Do not use cursors.

(11) Use explain to analyze the index usage of SQL statements

When you put the keyword "Explain" in front of a SELECT statement, MySQL explains how it will process the SELECT statement, and provides information about how the table is joined and in what order, with the help of "Explain, you can know when to add an index to the table to get a faster SELECT statement that uses indexes to search for records. You can also know whether the optimizer joins the table in the optimal order. To force the optimizer to use a specific join order for a select statement, add a straight_join clause. .

The general syntax of the explain command is: Explain <SQL command> For example: Explain select * from a inner join B on A. ID = B. ID

Explain the parameters of the explain analysis result:

1. Table: the name of the table.

2. Type: Type of the connection operation.

System: there is only one record in the table (only one data table is used in actual applications)

Const: A table can have at most one matching row. It is used to compare all the parts of the primary key or unique index with a constant value,

For example, select * From song_lib where song_id = 2 (song_id is the table's primary key)

Eq_ref: for each row combination from the preceding table, read a row from the table using the index of unique or primary key,

For example, select * From song_lib a inner join singer_lib B on A. singer_id = B. singer_id (type value of B is eq_ref)

Ref: for each row combination from the preceding table, read a row from the table with a non-unique or primary key index.

For example, select * From song_lib a inner join singer_lib B on A. singer_name = B. singer_name and

Select * From singer_lib B where singer_name = 'ccc '(The type value of B is ref, because B. singer_name is a common index)

Ref_or_null: The join type is like ref, but MySQL can search for rows containing null values,

For example, select * From singer_lib where singer_name = 'ccc 'or singer_name is null

Index_merge: The join type indicates that the index merge optimization method is used.

Key: it displays the name of the index actually used by MySQL. If it is null, MySQL does not use an index.

Key_len: the length of the part used in the index, in bytes.

3. Ref: the ref column shows which column or constant is used with the key to select rows from the table.

4. Rows: number of records that MySQL deems necessary to scan before finding the correct result. Obviously, the ideal number here is 1.

5. Extra: there may be many different options, most of which will have a negative impact on the query. Generally:

Using where: indicates that the where condition is used.

Using filesort: indicates that File Sorting is used, that is, the order by clause is used, and the index of the Field in order by is not used.

Additional sorting overhead. If using filesort is used, the sorting efficiency is very low and needs to be optimized. For example, a forced index is used.

(Force Index)

========================================================== ========

MySQL optimization (show variables, show status ).

After installing MySQL, the preparation file should be in the/usr/local/MySQL/share/MySQL directory. There are several preparation files, including my-huge. CNF my-medium.cnf my-large.cnf my-small.cnf, different traffic of the site and different preparation of the server environment, of course, need to have different preparation 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 only needs to modify this configuration file. You can use mysqladmin variables extended-status-u root-P to see the current parameters. Three configuration parameters are the most important, that is, key_buffer_size, query_cache_size, and table_cache.

Key_buffer_size only applies to the MyISAM table,

Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed. Generally, we set the value to 16 m. In fact, the number of sites that are slightly larger is far from enough. By checking the status values key_read_requests and key_reads, we can check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above status values can be obtained using show status like 'key _ read % ). Or if you have installed phpMyAdmin, you can see it through the server running status. I recommend you use phpMyAdmin to manage mysql. The following status values are all my instance analysis obtained through phpMyAdmin:

This server has been running for 20 days

Key _buffer_size-128 m
Key_read_requests-650759289
Key_read-79112

Ratio close to 1: 8000 healthy

Another way to estimate the key_buffer_size is to add up the size of the index space of each table in your website database. Take this server as an example: the number of large table indexes is about 125 MB, which will increase as the table grows.

MySQL provides a query buffer mechanism starting from 4.0.1. Using the Query Buffer, MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%.

You can check whether query_cache_size is set properly by adjusting the following parameters.

Qcache Inserts
Qcache hits
Qcache lowmem prunes
Qcache free Blocks
Qcache total Blocks

The value of qcache_lowmem_prunes is very large, which indicates that the buffer is insufficient frequently. The value of qcache_hits is very large, which indicates that the query buffer is frequently used. In this case, the value of qcache_hits is not large, this indicates that your query repetition rate is very low. In this case, using 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.

Qcache_free_blocks. If this value is very large, it indicates that many fragments in the buffer zone query_cache_type specify whether to use the query buffer.

My settings:

Query_cache_size = 32 m
Query_cache_type = 1

Obtain the following status values:

Qcache queries in cache 12737 indicates the number of items currently cached
Qcache inserts 20649006
Qcache hits 79060095 seems that the repeat query rate is quite high
Qcache lowmem prunes 617913 has so many times that the cache is too low
Qcache not cached 189896
Qcache free memory 18573912
Qcache free blocks 5328 seems to be a little big.
Qcache total blocks 30953

If the memory allows 32 MB, you should add some more

Table_cache specifies the table cache size. 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 values can be obtained using show status like '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.

For machines with 1 GB memory, the recommended value is 128-256.

I set table_cache = 256

The following status is displayed:

Opening tables 256
Opened tables 9046

Although open_tables is already equal to table_cache, opened_tables has a very low value after 20 days of running compared to the server running time. Therefore, increasing the value of table_cache should be of little use. If the preceding value appears after six hours, you need to increase the value of table_cache.

If you do not need to record binary logs, you can disable this function. After you disable it, you cannot recover the data before the problem occurs. You need to manually back up the data, the binary log contains all the statements for updating data. It is used to restore the data to the final state as much as possible when restoring the database. In addition, if you perform replication, you also need to use binary logs to transmit modifications.

Log_bin specifies the log file. If no file name is provided, MySQL generates its own default file name. MySQL automatically adds a numeric reference after the file name. A new binary file is generated every time the service is started. In addition, you can use Log-bin-index to specify the index file, BINLOG-do-dB to specify the database for the record, and BINLOG-ignore-dB to specify a database without record. Note: BINLOG-do-DB and BINLOG-ignore-DB specify only one database at a time and multiple statements are required for multiple databases. In addition, MySQL will change all database names to lowercase letters, and all database names must be in lower case when specifying the database, otherwise it will not work.

To disable this function, you only need to add the # sign before it.

# Log-bin

Enabling the slow query log (slow query log) is very useful for querying problematic traces. It records all long_query_time queries. If needed, you can also record records that do not use indexes. The following is an example of slow log query:

To enable slow query logs, you must set the log_slow_queries, long_query_times, and log-queries-not-using-indexes parameters.

Log_slow_queries specifies the log file. If no file name is provided, MySQL generates its own default file name. Long_query_times specifies the threshold for slow queries. The default value is 10 seconds. Log-queries-not-using-indexes is a parameter introduced after 4.1.0. It indicates that the record does not use an index for queries. The author sets long_query_time = 10

Settings:

Sort_buffer_size = 1 m
Max_connections = 120
Wait_timeout = 120
Back_log = 100
Read_buffer_size = 1 m
Thread_cache = 32
Interactive_timeout = 120
Thread_concurrency = 4

Parameter description:

Back_log

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. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. The UNIX listen (2) System Call manual page should have more details. Check your OS document to find the maximum value of this variable. Trying to set back_log to be higher than your operating system limit will be invalid.

Max_connections

The maximum number of concurrent connections. If 120 exceeds this value, it will be automatically restored. If a problem occurs, it can be automatically solved.

Thread_cache

No specific instructions are found, but it is useful to create more than 400 threads 20 days after 32, and thousands of threads have been created one day before.

Thread_concurrency

# Set your CPU count X2. For example, if there is only one CPU, then thread_concurrency = 2
# With 2 CPUs, thread_concurrency = 4
Skip-InnoDB
# Remove InnoDB support

Code:
[Client]
# Password = your_password
Port = 3306
Socket =/tmp/MySQL. Sock
# Socket =/var/lib/MySQL. Sock
# Here follows entries for some specific programs

# The MySQL Server
[Mysqld]
Port = 3306
Socket =/tmp/MySQL. Sock
# Socket =/var/lib/MySQL. Sock
Skip-locking
Key_buffer = 128 m
Max_allowed_packet = 1 m
Table_cache = 256
Sort_buffer_size = 1 m
Net_buffer_length = 16 K
Myisam_sort_buffer_size = 1 m
Max_connections = 120
# Addnew config
Wait_timeout = 120
Back_log = 100
Read_buffer_size = 1 m
Thread_cache = 32
Skip-InnoDB
Skip-bdb
Skip-name-resolve
Join_buffer_size = 512 K
Query_cache_size = 32 m
Interactive_timeout = 120
Long_query_time = 10
Log_slow_queries =/usr/local/mysql4/logs/slow_query.log
Query_cache_type = 1
# Try Number of CPU's * 2 for thread_concurrency
Thread_concurrency = 4
[Mysqldump]
Quick
Max_allowed_packet = 16 m

[MySQL]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-Updates

[Isamchk]
Key_buffer = 20 m
Sort_buffer_size = 20 m
Read_buffer = 2 m
Write_buffer = 2 m

[Myisamchk]
Key_buffer = 20 m
Sort_buffer_size = 20 m
Read_buffer = 2 m
Write_buffer = 2 m

[Mysqlhotcopy]
Interactive-Timeout

Supplement

Optimize table_cachetable_cache to specify the table cache size. 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 values can be obtained using show status like '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. For machines with 1 GB memory, the recommended value is 128-256.

Case 1: This case comes from a non-particularly busy server table_cache-512open_tables-103 opened_tables-1273 uptime-4021421 (measured in seconds). In this case, table_cache seems to be too high. During the peak time, the number of opened tables is much less than that of table_cache.

case 2: This case is from a development server. Table_cache-64open_tables-64opened-tables-431 uptime-1662790 (measured in seconds) Although open_tables is already equal to table_cache, opened_tables has a very low value compared to the server running time. Therefore, increasing the value of table_cache should be of little use.
case 3: in this case, table_cache-64 open_tables-64 opened_tables-22423 uptime-19538 is set too low on a upderming Ming server. Although the running time is less than 6 hours, open_tables reaches the maximum value, and opened_tables has a very high value. In this way, you need to increase the value of table_cache. Optimize key_buffer_sizekey_buffer_size to specify the index buffer size, which determines the index processing speed, especially the index reading speed. Check the status values key_read_requests and key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above status values can be obtained using show status like 'key _ read % ). 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. For machines with 1 GB memory, if the MyISAM table is not used, the recommended value is 16 m (8-64 m ).

Case 1: Health Condition key_buffer_size-402649088 (384 m) key_read_requests-597579931 key_reads-56188 Case 2: alarm status key_buffer_size-16777216 (16 m) the ratio of key_read_requests-597579931key_reads-53832731 in Case 1 is less than, which is healthy. the ratio in Case 2 reaches, and the alarm has been triggered.======================================

Two important parameters in MySQL optimization: table_cache and key_buffer

Based on my own experience, this article discusses two important MySQL Server optimization parameters: table_cache and key_buffer_size. Table_cache indicates the table cache size. When MySQL accesses a table, if there is still space in the MySQL table buffer, the table will be opened and put into the table buffer, in this way, you can access the content in the table more quickly. In general, you can check the status values open_tables and opened_tables of the database peak time to determine whether to increase the value of table_cache. That is, if open_tables is close to table_cache, and the value of opened_tables is gradually increased, we need to consider increasing the size of this value. When MySQL is installed by default, the value of table_cache is 256 to 512 by default for machines with less than 2 GB of memory. If the machine has 4 GB of memory, the default value is 2048, however, this means that the larger the machine memory, the larger the value, because the increase in table_cache makes MySQL's SQL response faster, inevitably, more deadlocks are generated, which slows down the entire set of database operations and seriously affects performance. Therefore, during normal maintenance, you still need to make judgments based on the actual situation of the database and find the table_cache value of the database that best suits your maintenance. Some people say: "performance optimization is an art ", that's right. All works of art have been refined and refined. The problem is that the file descriptor is not enough when table_cache is increased. There is a prompt in the MySQL configuration file. Reference "The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. Therefore you have to make sure to set the amount of open files allowed to at least 4096 in the variable "open-files-limit" in "section [mysqld_safe]" We should pay attention to this problem. At the thought of this, some of our siblings may use ulimit-N to make adjustments, but this adjustment is actually not correct. After changing the terminal, this value will return to the original value, so it is best to use sysctl or modify/etc/sysctl. in the conf file, we also need to increase the open_files_limit parameter in the configuration file. For 4G memory servers, I believe that the purchased servers are almost 4 GB, the open_files_limit must be increased to at least 4096. If there are no special cases, set it to 8192. Next we will talk about the key_buffer_size parameter. key_buffer_sizeo indicates the size of the index buffer. Strictly speaking, it determines the speed of database index processing, especially the speed of index reading. Some highly handwrittenArticleIndicates that you can check the status values key_read_requests and key_reads to check whether the key_buffer_size settings are reasonable. Proportional key_reads/key_read_requests should be as low as possible, at least, is better. Although I have not found a theoretical basis, after I have tested several libraries that are actually running well, I have shown that the ratio is close to, which proves that they are correct. We can use it. Postscript: As I mentioned earlier, performance optimization is a rare task and there are many factors that affect MySQL performance. This article only selects two parameters that I think are more important, we are looking forward to discussing more MySQL performance optimization technologies with netizens.