MySQL Database Optimization technology configuration Chapter, index chapter (must see must see Turn)

Turn from:MySQL Database Optimization technology configuration Chapter, index chapter (must see must SEE) (i) Reduce database access

As static as possible for pages that can be statically

Static local to a dynamic page, using static

Some data can be generated in XML or saved as a text file

Using data caching techniques, for example: MemCached



(ii) Optimized detection methods

1. User Experience Detection

2.Mysql Status Detection

Use the show status command inside the MySQL command line to get the current MySQL status.

The main focus is on the following attributes:

Key_read_requests (number of requests for Index reads) (Key_buffer_size setting effect)

Key_reads (Number of indexed read responses)

Key_blocks_used

Qcache_*

Open_tables (Affected by Table_cache settings)

Opened_tables

Table_locks

3. Third-party tool detection

Mysqlreporthttp://hackmysql.com/mysqlreport

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

The system and MySQL log

System commands: Top, SAR

MySQL's Log:slow_query.log



(iii) Optimization of hardware

Hardware, the most likely MySQL bottleneck is the disk, followed by CPU and memory

Disk aspects

Using a faster disk will help MySQL very well.

Using more hard drives, through raid, can increase the speed of a single disk problem

For RAID mode, RAID 0+1 or RAID 1+0 is recommended

Cpu

There is no doubt that higher clock CPUs and more CPU numbers can give MySQL more

High performance

Memory

Higher memory, often allowing more data in MySQL to be cached in memory,

However, an important factor is the need to have the correct MySQL configuration

Card

Use gigabit NICs and gigabit networks

(iv) Optimization of operating system

1. Do not use swap areas. If memory is low, add more memory or configure your system to use less memory

2. Do not use NFS disks

3. Increase the number of open files for the system and MySQL server

Using Ulimit–n 65535

4. Increase the number of processes and threads in the system.
5. Close unnecessary applications, optimize drive parameters, use Hdparm test

(v) Application-level optimization

1. Use multi-server load balancing (multiple read and write, data synchronization with replication technology)

2. Partitioning of the table (custom partition, mysql5.1 start to support the self-partitioning feature)

3. Using Data caching technology memcached



Six Optimization of MySQL Configuration

1.key_buffer (=512): Amount of memory used by index buffers

This is very important for the MyISAM table, set the 25%-30% of the available memory to be better, by checking the status values key_read_requests and Key_reads,

You can tell if the Key_buffer settings are reasonable. Proportional key_reads/key_read_requests should be as low as possible, or at least 1:100,1:1000 better, otherwise the Key_buffer setting is a little bit too small

2.innodb_buffer_pool_size (= 512): Amount of memory used by index buffers



3.table_cache (=1024): Size of data table buffers

Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents.

You can determine whether you need to adjust the value of the 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 equals Table_cache and you find that the Opened_tables status value is growing, you need to increase the value of the Table_cache parameter.

It is also not possible to blindly set the Table_cache parameter to a very large value, if set too high, may result in insufficient file descriptors, resulting in performance instability or connection failure.

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

The allocation memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the actual allocated total sort buffer size is 100x6 = 600MB.

Therefore, the recommended setting for a server that has around 4GB is 6-8m

5.join_buffer_size: The length of the buffer associated with the query

4G memory above, recommended greater than 32M, this parameter corresponding to the allocation of memory is exclusive to each connection!

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

The number of clients that are allowed to connect to the MySQL server at the same time can be observed and estimated by the maximum number of concurrent connections that the system is setting

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

Typically set to the number of CPUs x2

8.innodb_buffer_pool_size (=): InnoDB table Cache Pool Size

This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default key_buffer_size settings,

However, InnoDB is a snail in the default innodb_buffer_pool_size settings.

Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of usable memory.

Some of the rules that apply to Key_buffer are--if you have a small amount of data and do not burst, you do not need to set the innodb_buffer_pool_size too large.

9.innodb_flush_logs_at_trx_commit (=1): Log refresh mode after transaction commit

is InnoDB 1000 times times slower than MyISAM? Maybe you forgot to modify this parameter. The default value is 1, which means that each committed update transaction (or statements outside of each transaction) is flushed to disk.

This is quite resource intensive, especially when there is no battery backup cache. Many applications, especially those from MyISAM, set its value to 2, which is not to flush the log to disk.

Instead, it is flushed to the operating system's cache only. Logs are still flushed to disk every second, so there is usually no loss of 1-2 updates per second. If it's set to 0, it's a lot faster, but it's relatively unsafe,

When the MySQL server crashes, some transactions are lost. Set to 2 command to lose the part of the transaction that was flushed to the operating system cache.

(vii) Optimization of tables

1. Select the right data engine

MyISAM: Table for a large number of read operations

InnoDB: Suitable for a large number of read-write tables

2. Select the appropriate column type

Use the SELECT * from Tb_test PROCEDURE analyse () to analyze each field of the table, giving recommendations for optimizing column types

3. Use NOT NULL for columns that do not hold null values, which is especially important for the columns you want to index

4. Create an appropriate index

5. Use fixed-length fields, faster than longer

(eight) establishing indexing principles

1. Proper use of indexes

A table can use only one index in a query, using the Explain statement to verify the operation of the optimizer

Use analyze to help the optimizer make more accurate predictions about the use of indexes

2. The index should be created on the data columns involved in searching, sorting, grouping, etc.

3. Try to build the index in data columns with few data duplication, so it's best

For example: Birthday column, which can be indexed but not indexed by gender column

4. Try to index relatively short values

Reduces disk IO operations, which can hold more key values in the index buffer, increasing the hit rate

If you are indexing a long string, you can specify a prefix length

5. Rational use of multi-column indexes

If multiple criteria often need to be combined to query, use a multicolumn index (because one table can use only one index at a time, and only one for multiple single-column indexes)

6. Make full use of the leftmost prefix

That is, the order of the columns in the multi-column index should be properly arranged, the most commonly used in the front

7. Do not create too many indexes

Only fields that are frequently applied to where,order by,group by need to be indexed.

8. Use slow query log to find out slow query (Log-slow-queries, Long_query_time)

(ix) Full utilization of the index

1. Try to compare data columns with the same data type

2. Make the index columns independent of the comparison expression whenever possible, where MyCol < 4/2 uses the index, and where MyCol * 2 < 4 does not use

3. If possible, do not add a function to the query field

As where year (Date_col) < 1990 transformed into where Date_col < ' 1990-01-01 '

where To_days (Date_col)-To_days (Curdate ()) < cutoff transformed into where Date_col < Date_add (Curdate (), INTERVAL Cutoff day)

4. Do not use wildcard characters at the beginning of the like Mode 5. Use straight join to force the optimizer to join in the order of the FROM clause, either select Straight join, force all junctions, or select * from a straight Join B enforces the order of two tables. 6. Use force index to enforce use of the specified index. such as SELECT * FROM Song_lib Forces Index (song_name) Order by Song_name is 7 more efficient than without force index. Try to avoid using MySQL Automatic type conversion, otherwise you will not be able to use the index. such as the num_col of the int type with where num_col= ' 5 '

(10) Optimization of SQL statements

1. Create an appropriate statistical intermediate result table to reduce the chance of querying data from large tables

2. Try to avoid using subqueries and use connections instead. For example:

Select a.ID, (select MAX (created) from posts WHERE author_id = a.id) as Latest_post

From authors a

Can be changed to:

SELECT a.id, MAX (p.created) as Latest_post

From authors as a

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 '

The limit 2000 is changed to:

Select song_id from Song_lib a

INNER JOIN Singer_lib B on a.singer_id=b.singer_id and first_char= ' a ' limit 2000

3. When inserting a repeating key, use the on DUPLICATE key UPDATE:

Insert into Db_action.action_today (User_id,song_id,action_count) VALUES (1,1,1) on DUPLICATE KEY UPDATE action_count= action_count+1;

4. Avoid using cursors

Cursors are extremely inefficient, and can be accomplished by adding temporary tables, using multi-table queries, multi-table updates, and not using cursors.

(11) using explain to parse SQL statements using indexes

When you put a keyword in front of a SELECT statement Explain,mysql explains how it will handle select, providing information about how tables are joined and in what order, with the help of explain, You can know when you have to index the table to get a faster select that uses the index to find records, and you know whether the optimizer joins the table in the best order. To force the optimizer to use a specific junction order for a SELECT statement, add a straight_join clause:

The general syntax for the EXPLAIN command is: EXPLAIN <sql command > such as: EXPLAIN SELECT * from a INNER join B on a.id=b.id

Explain analysis of the results of the parameters detailed:

1.table: This is the name of the table.

2.type: The type of connection operation.

System: There is only one record in the table (a table with very few data actually applied)

Const: The table has a maximum of one matching row, which is used to compare all parts of primary key or unique index with a constant value.

Example: SELECT * from Song_lib where song_id=2 (song_id as table primary key)

Eq_ref: For each row combination from the preceding table, a row is read from the table with the index of unique or primary key,

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, a row is read from the table with a non-unique or primary key index

such as: 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 ' (type value of B is ref, because B.singer_name is a normal index)

Ref_or_null: The join type is like ref, but mysql can be specifically searched for rows that contain null values.

such as: SELECT * from Singer_lib where singer_name= ' CCC ' or singer_name is null

Index_merge: The join type indicates the use of the index merge optimization method

Key: It shows the name of the index that MySQL actually uses. If it is empty (or null), then MySQL does not use the index.

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

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

4.rows:mysql the number of records that it must scan before it can find the correct results. Obviously, the ideal number here is 1.

5.Extra: Many different options may appear here, most of which will adversely affect the query. There are generally:

Using where: Indicates that the Where condition is used

Using Filesort: Indicates that a file sort is used, that is, an ORDER BY clause is employed, and the index of the field in the order by is not used, thus requiring

Additional sorting overhead, so if a using Filesort is present, sorting is inefficient and needs to be optimized, such as by forcing the index

The method (Force index)

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

MySQL optimizer (show variables, show status). After the installation of MySQL, the configuration file should be in the/usr/local/mysql/share/mysql directory, the configuration of several files, there are my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf, Different traffic sites and different configuration of the server environment, of course, need to have a different configuration files.

In general, MY-MEDIUM.CNF this formulation will meet most of our needs; generally we will copy the configuration file to/etc/my.cnf only need to modify this configuration file, use Mysqladmin variables Extended-status–u Root–p can see the current parameters, there are 3 configuration parameters is the most important, that is, Key_buffer_size,query_cache_size,table_cache.

Key_buffer_size only works on the MyISAM table,

KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. Generally we set to 16M, actually slightly larger site this number is far from enough, by checking the status values key_read_requests and Key_reads, you can know key_buffer_size set is reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Or if you installed the phpMyAdmin can be seen through the server running state, I recommend the use of phpMyAdmin to manage MySQL, the following status values are I obtained through the phpMyAdmin instance analysis:

This server has been running for 20 days

key_buffer_size–128m
key_read_requests–650759289
key_reads-79112

Proportion close to 1:8,000 health is very good

Another way to estimate key_buffer_size is to add up the size of the index of each table in your site database. Take this server as an example: a large number of table indexes add up to about 125M this figure will grow larger with the table.

Starting with 4.0.1, MySQL provides a query buffering mechanism. Using query buffering, MySQL stores the SELECT statement and query results in a buffer and will read the result directly from the buffer for the same SELECT statement (case-sensitive) in the future. Depending on the MySQL user manual, the use of query buffering can be up to 238% efficient.

By adjusting the following parameters you can know whether the query_cache_size is set properly

Qcache Inserts
Qcache Hits
Qcache Lowmem prunes
Qcache Free Blocks
Qcache Total Blocks

The value of Qcache_lowmem_prunes is very large, it indicates that buffering is not enough, and the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, when the buffer size needs to be increased qcache_hits the value is not big, Indicates that your query repetition rate is very low, in which case the use of query buffering will affect efficiency, then you can consider not querying the buffer. In addition, adding sql_no_cache in the SELECT statement can make it clear that query buffering is not used.

Qcache_free_blocks, if the value is very large, it indicates a lot of fragmentation in the buffer query_cache_type specifies whether to use query buffering

I set:

Query_cache_size = 32M
Query_cache_type= 1

The following status values are obtained:

Qcache queries in cache 12737 indicates the number of currently cached bars
Qcache Inserts 20649006
Qcache hits 79060095 It looks like the repeat query rate is pretty high.
Qcache Lowmem prunes 617913 There are so many instances of cache too low
Qcache Not Cached 189896
Qcache free memory 18573912 currently remaining cache space
Qcache Free Blocks 5328 This number seems a little bit too much debris.
Qcache Total Blocks 30953

If the memory allows 32M, you should add a little bit.

Table_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' open% Tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.

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

I set Table_cache = 256

Get the following status:

Open Tables 256
Opened Tables 9046

Although Open_tables is already equal to Table_cache, it has been running for 20 days relative to the server run time, and the value of Opened_tables is very low. Therefore, it should be of little use to increase the value of Table_cache. If the above values occur after 6 hours of operation, consider increasing the table_cache.

If you do not need to record 2 binary log to turn this function off, note that after you turn off the data before the problem can not be restored, you need to manually back up, the binary log contains all the updated data statements, the purpose is to restore the database as far as possible to restore the data to the final state. In addition, if you do synchronous replication (Replication), you also need to use binary log shipping to modify the situation.

LOG_BIN Specifies the log file, and if the file name is not provided, MySQL will generate its own default file name. MySQL automatically adds a digital citation after the file name, and rebuilds a new binary every time the service is started. In addition, you can specify the index file using Log-bin-index, use BINLOG-DO-DB to specify the database for the record, and use BINLOG-IGNORE-DB to specify the database that is not logged. Note: binlog-do-db and binlog-ignore-db specify only one database at a time, and multiple statements are required to specify multiple databases. Also, MySQL will change all database names to lowercase, and must use lowercase names when specifying the database, otherwise it will not work.

You just need to put the # number in front of him to turn off this function

#log-bin

Turning on the slow query log (slow query log) Slow query log is useful for tracking problematic queries. It records all queries that have checked the long_query_time, and if necessary, records records that do not use the index. Here is an example of a slow query log:

To open the slow query log, you need to set parameters log_slow_queries, Long_query_times, log-queries-not-using-indexes.

log_slow_queries Specifies the log file, and if the file name is not provided, MySQL will generate its own default file name. LONG_QUERY_TIMES Specifies the threshold for a slow query, which is 10 seconds by default. Log-queries-not-using-indexes is a parameter introduced after 4.1.0, which indicates that a query that does not use an index is logged. Author Set long_query_time=10

Author set:

Sort_buffer_size = 1M
max_connections=120
Wait_timeout =120
back_log=100
Read_buffer_size = 1M
Thread_cache=32
interactive_timeout=120
Thread_concurrency = 4

Parameter description:

Back_log

The number of connections required for MySQL to be available. When the primary MySQL thread gets very many connection requests in a very short time, this works, and then the main thread takes some time (albeit very short) to check the connection and start a new thread. The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on this queue size. Unix Listen (2) system calls to the manual pages should have more details. Check your OS documentation to find out the maximum value for this variable. Attempting to set a limit of back_log above your operating system will be invalid.

Max_connections

The maximum number of concurrent connections, 120 more than this value will automatically recover, a problem can be automatically resolved

Thread_cache

No specific instructions were found, but the setting was 32 20 days after the creation of more than 400 threads and the previous day created thousands of threads so it's still useful

Thread_concurrency

#设置为你的cpu数目x2, for example, there is only one CPU, then thread_concurrency=2
#有2个cpu, then thread_concurrency=4.
Skip-innodb
#去掉innodb支持

Code:
[Client]
#password = Your_password
Port = 3306
Socket =/tmp/mysql.sock
#socket =/var/lib/mysql/mysql.sock
# here follows entries for some specific programs


# The MySQL server
[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock
#socket =/var/lib/mysql/mysql.sock
Skip-locking
Key_buffer = 128M
Max_allowed_packet = 1M
Table_cache = 256
Sort_buffer_size = 1M
Net_buffer_length = 16K
Myisam_sort_buffer_size = 1M
max_connections=120
#addnew Config
Wait_timeout =120
back_log=100
Read_buffer_size = 1M
Thread_cache=32
Skip-innodb
Skip-bdb
Skip-name-resolve
join_buffer_size=512k
Query_cache_size = 32M
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 = 16M


[MySQL]
No-auto-rehash
# Remove The next comment character if you is not a familiar with SQL
#safe-updates


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


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


[Mysqlhotcopy]
Interactive-timeout

Add

Optimization Table_cachetable_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' open% Tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures. For machines with 1G of memory, the recommended value is 128-256.

Case 1: The case comes from a server that is not particularly busy table_cache–512open_tables–103 opened_tables–1273 uptime–4021421 (measured in seconds) the Case tab The le_cache seems to be set too high. At peak times, the number of open tables is much less than Table_cache.

Case 2: The case comes from a development server. table_cache–64open_tables–64opened-tables–431uptime–1662790 (measured in seconds) although open_tables is already equal to Table_cache, the phase The value of the opened_tables is also very low for the server run time. Therefore, it should be of little use to increase the value of Table_cache.
Case 3: The case comes from a upderperforming server table_cache–64 open_tables–64 opened_tables–22423uptime–19538 The case Table_cache set too low. Although the running time is less than 6 hours, the open_tables reaches the maximum value, and the Opened_tables value is also very high. This will require increasing the value of the Table_cache. Optimization key_buffer_sizekey_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 if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Key_buffer_size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used. You can use the Check status value created_tmp_disk_tables to learn more. For 1G memory machines, 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 status key_buffer_size–16777 216 (16M) key_read_requests–597579931key_reads-53832731 Case 1 in the proportion of less than 1:10,000, is a healthy situation, case 2 of the ratio of 1:11, the alarm has been sounded. Two important parameters Table_cache and Key_buffer in ================================mysql tuning

Based on my own experience, this paper discusses two very important parameters in MySQL server optimization, namely table_cache,key_buffer_size. Table_cache indicates the size of the table cache. When MySQL accesses a table, if there is space in the MySQL table buffer, then the table is opened and placed in the table buffer, and the benefit is that the contents of the table can be accessed more quickly. In general, you can determine whether you need to increase the value of Table_cache by looking at the status values Open_tables and Opened_tables of the peak time of the database, that is, if Open_tables is close to Table_cache, And opened_tables This value is gradually increasing, it is necessary to consider increasing the size of this value. In the case of MySQL default installation, the value of the Table_cache value in the machine below 2G memory defaults to 256 to 512, if the machine has 4G memory, the default value is 2048, but this means that the larger the machine memory, this value should be greater, because Table_cache increased , making MySQL respond faster to SQL, inevitably resulting in more deadlocks (dead Lock), which slows down the entire set of database operations and severely impacts performance. So in peacetime maintenance or in accordance with the actual situation of the library to make judgments, find the most suitable for your maintenance of the Table_cache value of the library, some said: "Performance optimization is an art", which is true. Most of the works of art, mostly through the thoroughly tempered, finely carved and made. Here also to explain a problem, that is, table_cache enlarged after encountering the file descriptor is not enough to use the problem, in the MySQL configuration file there is such a hint Reference "The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. Therefore you has 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] " That is to pay attention to this problem, the thought of here, some brothers may use ulimit-n to make adjustments, but this adjustment is not true, after changing the terminal, this value will return to the original value, so it is best to use sysctl or modify/etc/ sysctl.conf file, but also in the configuration file open_files_limit This parameter to increase, for 4G memory server, believe that the server is now purchased almost 4G, then this open_files_limit at least to increase to 4096, If there is no special case, set it to 8192. Let's say key_buffer_size this parameter, Key_buffer_sizeo represents the size of the index buffer, strictly speaking, it determines the speed of database indexing processing, especially the speed of index reading. According to some experts in the network wrote the article indicates that you can check the status values key_read_requests and Key_reads, you will know key_buffer_size set is reasonable. The proportion key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better, although I have not found the rationale for the theory, but I have maintained a few of the actual well-run libraries to do the test after the show that This ratio is close to 1:20,000, which proves the correctness of what they say, and we might as well use it. Postscript: As I said earlier, performance optimization is a deliberately, affecting the performance of MySQL a lot of factors, this article just select the two parameters which I think are more important, look forward to discussing with the Netizen more MySQL performance optimization technology.