Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.