MySQL Performance tuning

1 how MySQL works

1.1 MySQL Architecture:

Connection pool

SQL interface

Analyzer

Optimizer

Query cache

Storage Engine

File system

Management tools

1.2 Permanent definition:

Modifying a configuration file/etc/my.cnf

[MySQL]

Variable name = value

1.3 Temporary definition:

Mysql>set (global) variable =

2 Performance Tuning

2.1 When accessing data, the results are particularly slow. What are the possible causes of the analysis?

(1) Low hardware configuration: View application device usage CPU memory storage (hard disk)

Use Top,sar,uptime,free, etc.;

(2) network bandwidth: The speed of using network speed software;

(3) Provide low database service software version:

2.2 Concurrency and connection control

Mysql> Show variables like "max_connections"; #允许的最大并发连接数

Mysql> Show variables like "Connect_timeout"; #建立连接时, three times the time-out of the handshake

Mysql> Show variables like "Wait_timeout"; #建立连接后, waiting for the time-out to disconnect

To view the number of connections that are currently in use:

Mysql>show Global status like "Max_used_connections";

To view the default maximum number of connections:

Mysql> Show variables like "max_connections";

Ideal ratio: number of connections used/connection concurrency <=85%

2.3 Cache parameter Control

Key_buffer_size #用于myisam引擎的关键索引缓存大小

Sort_buffer_size #为每个要排序的线程分配此大小的缓存空间

Read_buffer_size #为顺序读取表记录保留的缓存大小

Read_rnd_buffer_size #为排序结果读取表记录保留的缓存大小

Number of threads allowed to reuse

Mysql> Show variables like "thread_cache_size";

The number of open tables that are cached for all threads.

table_open_cache=2000

Mysql> Show variables like "Table_open_cache";

Query cache related parameter settings?

Mysql> Show variables like "query_cache%";

Query cache write lock efficient storage engine (MYISAM)

Query_cache_wlock_invalidate | OFF

Query_cache_type 0|1|2

0 off do not store

1 Open Unconditional Storage

2 Open the specified storage before storing

Query cache statistics?

Mysql> Show global status Like "qcache%";

Qcache_hits 0 #查询缓存成功的次数 (that is, the results of the query are found in the cache)

Qcache_inserts 0 #总的

Qcache_lowmem_prunes 0 #每次缓存达到一定数值, re-emptying the number of times, the larger the number of empty means the cache is used unreasonable (always run out)

2.4 MySQL Log type

(1) Error log: Log information generated by the database service during startup and operation, enabled by default

The Log-error=/var/log/mysqld.log in/ETC/MY.CNF

(2) Binlog log: Default is not enabled.

(3) query log: Record all executed SQL commands, not enabled by default

Vim/etc/my.cnf

[Mysqld]

General-log #默认存储位置/var/lib/mysql

Default name: Host name. log

(4) Slow query log: Records SQL commands that display query results over a specified time. The default time-out is 10s.

Slow-query-log #启用慢查询日志

slow-query-log-file= file name #指定日志名称

Long-query-time= Digital #指定超时时间

Default storage location/var/lib/mysql

Default name: Host name-slow.log

[[email protected] ~]# mysqldumpslow/var/lib/mysql/host name-slow.log >sql.txt

#查看慢查询日志 and save the query with query timeout

Log-queries-not-using-indexes #记录未使用索引的查询

MySQL Performance tuning