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