MySQL performance tuning
1 Mysql works
1.1 mysql architecture:
connection pool
SQL interface
analyzer
Optimizer
query cache
storage engine
file system
management tools
1.2 Permanent Definition:
Modify the configuration file/etc/my.cnf
[mysql]
Variable Name = Value
1.3 Provisional definition:
mysql>set (global) variable =
2 Performance tuning
2.1 When accessing data, results are particularly slow. What causes might the analysis be?
(1)Hardware Configuration Low: View usage of application CPU memory storage (hard disk)
Use top,sar, downtime,free, etc.;
(2)Network bandwidth: use network speed measurement software speed;
(3)Provide database services Software version low:
2.2 concurrency and connection control
mysql> show variables like "max_connections"; #Maximum number of concurrent connections allowed
mysql> show variables like "connect_timeout"; #timeout for three-way handshake when establishing a connection
mysql> show variables like "wait_timeout"; #Timeout for waiting to disconnect after establishing a connection
View the number of currently used connections:
mysql>show global status like "max_used_connections";
Check the default maximum number of connections:
mysql> show variables like "max_connections";
Ideal ratio: connections used/connection concurrency show variables like "thread_cache_size";
Number of tables open for all thread caches.
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 Valid Storage Engine (MYISAM)
query_cache_wlock_invalidate | OFF
query_cache_type 0|1|2
0 Close Do not store
1 Open Unconditional Storage
2 Open specified storage to store
Query cache statistics?
MySQL> show global status like "qcache%";
Qcache_hits 0 #Number of times the query cache was successful (i.e. the query result was found in the cache)
Qcache_inserts 0 #Total
Qcache_lowmem_prunes 0 #Number of times to empty the cache every time it reaches a certain value. The larger the number of times to empty, the unreasonable use of the cache (always used up)
2.4 Mysql Log Type
(1)Error log: records information generated by database service during startup and operation. Enabled by default.
log-error=/var/log/mysqld.log in/etc/my.cnf
(2)binlog log: Not enabled by default.
(3)Query log: records all executed SQL commands, not enabled by default
vim /etc/my.cnf
[mysqld]
general-log #Default storage location/var/lib/mysql
Default Name: Hostname.log
(4)Slow Query Log: Log SQL commands that display query results for a specified time. The default timeout is 10s.
slow-query-log #Enable slow query logging
slow-query-log-file= filename #Specify log name
long-query-time= number #Specify timeout
Default storage location/var/lib/mysql
Default Name: Hostname-slow.log
[root@ser54 ~]# mysqldumpslow /var/lib/mysql/hostname-slow.log >sql.txt
#View slow query logs and save query statements that timeout
log-queries-not-using-indexes #Log queries that do not use indexes
