Shulou(Shulou.com)05/31 Report--

Mysql monitoring and optimization process is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

1. Monitoring and optimizing 1.1 Monitoring indicators

1.1.1 QPSmysql > show global status like 'Com%';mysql > show global status like' Queries';+-+-+ | Variable_name | Value | +-+ | Queries | 1983766 | +-+-+ 1 row in set (0.00 sec)

QPS = (Queries 2-Queries 1) / interval

Mysql > show global status where variable_name in ('Queries','uptime') +-+-+ | Variable_name | Value | +-+-+ | Queries | 1983768 | | Uptime | 1364443 | +-+-+ 2 rows in set (sec) 1.1.2 TPSmysql > show global status where variable_name in ('com_insert' 'com_update','com_delete','uptime') +-+-+ | Variable_name | Value | +-+-+ | Com_delete | 23676 | Com_insert | 793072 | Com_update | 259586 | Uptime | 1364651 | +-+-+ 4 rows in set (0.00 sec)

Transaction_sum= Com_delete+ Com_insert+ Com_update

TPS = (Transaction_sum 2-Transaction_sum 1) / (time 2-time 1)

1.1.3 concurrency mysql > show global status like 'Threads_running' +-+-+ | Variable_name | Value | +-+-+ | Threads_running | 2 | +-+-+ 1 row in set (0.01 sec) 1.1.4 connections # maximum number of connections mysql > show global status like 'max_used_connections%' +-- +-+ | Variable_name | Value | +-+-+ | Max_used_connections | 22 | | Max_used_connections_time | 0-09-04 13:49:52 | +-- +-+ 2 rows in set (0.00 sec) # current number of connections mysql > show global status like 'threads_connected' +-+-+ | Variable_name | Value | +-+-+ | Threads_connected | 2 | +-+-+ 1 row in set (0.01 sec) 1.1.5 cache hit rate # # number of reads from cache mysql > show global status like 'innodb_buffer_pool_read_requests' +-- +-+ | Variable_name | Value | +-- +-+ | Innodb_buffer_pool_read_requests | 16217299 | | +-+-+ 1 row in set (0.00 sec) # # number of reads from physical disk mysql > show global status like 'innodb_buffer_pool_reads' | +-+-+ | Variable_name | Value | +-+-+ | Innodb_buffer_pool_reads | 2067 | +-+- -+ 1 row in set (0.00 sec)

Cache hit ratio = (innodb_buffer_pool_read_requests-innodb_buffer_pool_reads) /

Innodb_buffer_pool_reads * 100%

1.1.6 Service availability

Periodic join, execute query: select @ @ version; or select user ()

Mysqladmin-uroot-pxxx-hxxxx ping

1.1.7 blocking # #

< mysql 5.7SELECT b.trx_mysql_thread_id as '被阻塞的线程', b.trx_query as '被阻塞的SQL', c.trx_mysql_thread_id as '阻塞线程', c.trx_query as '阻塞SQL', UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间'FROM information_schema.INNODB_LOCK_WAITS a JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id=b.trx_idJOIN information_schema.INNODB_TRX c ON a.blocking_trx_id=c.trx_idWHERE (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>

30 blocked # > mysql 5.7SELECT waiting_pid AS 'blocked thread', waiting_query AS 'blocked SQL', blocking_pid AS' blocked thread', blocking_query AS 'blocked SQL', wait_age AS' blocking time' Sql_kill_blocking_query AS 'recommended Action' FROM sys.innodb_lock_waitsWHERE (UNIX_TIMESTAMP ()-UNIX_TIMESTAMP (wait_started)) > 301.1.8 deadlock # # pt tool pt-deadlock-logger upright adminery pendant 123456, hobbies 127.0.0.1\-- create-dest-table\-- dest upright adminery packs 123456, hobbies 127.0.0.1.DobaBJT lockout # global parameters Log monitoring mysql > set persist innodb_print_all_deadlocks=on 11.1.9 slow query

Monitor slow query log

Real-time monitoring through information_shcema.processlist table

1.1.10 Master-slave delay

Show slave status\ G

Pt-heartbeat

# # Master library pt-heartbeat-- user=xx-- password=xxx-h master-- create-table-- database xxx-- update-- daemonize-- interval=1## slave library pt-hearbeat-- user=xx-- password=xx-h slave-- database xxx--monitor-- daemonize-- log / tmp/slave_lag.log1.1.11 master-slave status

Show slave status\ G

IO/SQL two thread states (yes or no)

1.2 load issu

1.3 Optimization

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.