MySQL Monitoring and optimization

One, MySQL connection number

1. Configure MySQL Connection number:

VIM/ETC/MY.CNF [mysqld] modified below

max_connections=1000 does not write default to 100.

Wait_timeout=60 setting the time-out period

2. View the current number of connections:

Show status like '%threads_connected% ';

　　

Show Processlist;

　　

Second, MySQL cache

1. Turn on the cache:

VIM/ETC/MY.CNF mysqld Add or modify the following

Query_cache_type=on #开启缓存

query_cache_size=10m #缓存总大小

Query_cache_limit=1m #查询结果超过设置值, it will not cache

The MySQL service needs to be restarted to take effect.

2. Check the cache status:

SHOW VARIABLES like '%query_cache% ';

　　

3. Open Profile:

SET @ @profiling = 1; Set Profile Open

SELECT @ @profiling; See if profile is turned on

Show Profiles; View all Profiles

　　

Show profile for query 9; To view the time consumed by a specified SQL statement

　　

　　

Can be seen: the same SQL statement, the 9th is the first query consumption time, long time, the 10th is from the cache query consumption time, significantly shorter.

Note: 1, the SELECT statement must be identical in order to go from the cache, for example: The case is not the same, although the query results, but do not walk the cache.

2. Any query that contains an indeterminate function (such as now (), current_date ()) will not be cached.

4, Querycache use status:

SHOW STATUS like ' qcache% ';

　　

Query Cache Hit rate = qcache_hits/(qcache_hits + qcache_inserts);
Qcache_free_blocks How many remaining blocks are currently in the Query cache. If the value is large, it means that there are more memory fragments in query Cache, and you may need to look for a suitable opportunity to defragment it. If this value is very large, you can use the flush query cache to clean up the query buffer fragmentation to improve memory usage performance. The statement does not remove any queries from the cache.

5. Query Statement life cycle:

1.Mysql Server Monitoring 3306 port

2. Verifying access to users

3. Create a MySQL thread

4. Check memory (Qcache)

5. Parsing SQL

6. Generate a query plan

7. Open the table

8. Check memory (Buffer Pool)

9. Read data to disk

10. Write Memory

11. Return data to the client

12. Close the table

13. Close the thread

14. Close the connection

Third, InnoDB storage engine

1. Open Innodb_buffer_pool

VIM/ETC/MY.CNF mysqld Add or modify the following

innodb_buffer_pool_size=20m #设置bufferpool大小

Innodb_buffer_pool_dump_now=on #默认为关闭OFF. If this parameter is turned on, InnoDB saves hot data from the InnoDB buffer pool to the local hard disk when the MySQL service is stopped.

Innodb_buffer_pool_load_at_startup = on #默认为关闭OFF. If this parameter is turned on, MySQL loads the local hot data into the InnoDB buffer pool when the MySQL service is started.

2. View Innodb_buffer_pool Status

SHOW VARIABLES like '%innodb_buffer_pool% ';

　　

3, the query Innodb_buffer_pool current use situation:

SHOW STATUS like '%innodb_buffer_pool% ';

　　

4. Automatic Submission

SET @ @autocommit = 0; #关闭自动提交, valid only for the current user

Modify the configuration file my.cnf Add autocommit = 0 under the [mysqld] module; #重启后永久生效

SELECT @ @autocommit; #查看自动提交是否开启

Commit #提交执行语句

5. Lock:

Show Processlist; #查看当前锁定sql

　　

Note: If the modified data condition is an indexed column, it is a row-level lock, otherwise it is a table-level lock.

Four, slow query

1, query slow query log is open

SHOW VARIABLES like '%query% ';

　　

2. Slow query log settings (Execute command)

Set global slow_query_log=on; #开启慢查询日志

Set global long_query_time=1; #设置记录查询超过多少秒的Sql存入慢查询

Set global slow_query_log_file= '/opt/data/slow_query.log '; #设置慢查询日志路径, this path requires write access

3. Parsing slow query log

-S: Is the way of ordering, C, T, L, R are sorted according to the number of records, time, query time, return records to sort, AC, at, AL, AR, the corresponding flashbacks;-T: is the meaning of top N, that is, to return the data of the number of previous bars; G: You can write a regular matching pattern in the back, case insensitive, such as: Query the top 10 SQL query time by SQL

Mysqldumpslow-s t-t 10-g ' select '/opt/lampp/var/mysql/xiaoxitest-slow.log

　　

4. Use Explain/desc to view SQL efficiency

EXPLAIN Update user set age=11 where id=1;

DESC Update user set age=11 where id=1;

　　

Type column: From Worst to best

All<index<range<ref<eq_ref<const,system<null

All is a full-table scan, which usually means that MySQL has to scan the entire table, from beginning to end, to find the desired line.　　The InnoDB table is the primary key order. The index join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.　　(That is, although all and index are read-only, index is read from the index and all is read from the hard disk). A range range scan is a restricted index scan that starts at a point within the index and returns rows that match the entire domain. This is better than a full index scan because it does not need to traverse all indexes.　　It is obvious that between or in the where sentence with a ">" or "<" query or in.　　A Ref non-unique index scan that returns all rows that match a single value. Eq_ref Use this index lookup, MySQL knows that only one record that matches the criteria is returned. This access can be seen when MySQL uses a primary key or a unique index.　　MySQL is very good for these types of access type optimizations.　　Const,system when MySQL can optimize a part of a query and convert it to a constant, it will use these types of access. NULL This access means that MySQL can decompose query statements during the optimization phase, and even no longer need to access tables or indexes during the execution phase. five, MySQL optimization1, read and write separation master: only responsible for writing data from: Only read data 2, distributed data MySQL usually does not cause great pressure on the bandwidth. Therefore, the data can be distributed in different geographical locations, and the data distribution across the room is realized. 3. Load balancing with MySQL replication, read operations can be distributed across multiple servers for optimized read-intensive applications. 4, reduce the number of IO the majority of database operations in 90% of the time is the use of IO operations, reducing the number of IO is the first priority of SQL optimization. is also the most obvious optimization means of effectiveness. 5, the CPU calculation to reduce the SQL optimization needs to consider is the optimization of CPU operation. Order BY, group By,distinct ... Are all CPU-intensive (these operations are basically CPU-processed in-memory data comparison operations). When our IO optimization is done at a certain stage, reducing CPU computing becomes an important goal of our SQL optimization. 6. After changing the SQL execution plan to define the optimization goals, we need to determine how to achieve our goals. For SQL statements, there is only one way to achieve the above 2 goals, that is to change the SQL execution plan, let him try to "less detours", as far as possible through a variety of "shortcuts" to find the data we need to achieve "reduce the number of IO" and "reduce CPU computing" goal. 7. Create an index for frequently used query conditions

MySQL Monitoring and optimization