MySQL View database status Show status

Show [Global | session] status [like ' xxx '];

Global option to view all MySQL connection status

Session options, view current connection status, default session option

Flush status; You can reset many status values to zero.

Get the total number of MySQL user processes

Ps-ef |awk ' {print $} ' |grep mysql |grep-v ' grep ' |wc-l

View MySQL current number of connections

Netstat-an |grep 3306 |grep "established" |wc-l

View Host performance Status

Uptime

11:03:30 up, 9:48, 3 users, load average:0.70, 0.91, 2.63

View server startup duration, number of users, load

Top Vmstat,top View CPU Usage

Vmstat iostat View IO status

Free-h Viewing memory usage

Database Performance Status query:

1) QPS Query volume per second

Show global status like ' question% ';

+---------------+----------+

| variable_name | Value |

+---------------+----------+

| Questions | 39661402 |

+---------------+----------+

2) TPs per second transaction volume

Show global status like ' Com_commit ';

+---------------+---------+

| variable_name | Value |

+---------------+---------+

| Com_commit | 8495153 |

+---------------+---------+

Show global status like ' Com_rollback ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Com_rollback | 0 |

+---------------+-------+

(3) key Buffer hit ratio
Mysql>show Global status like ' key% ';

Key_buffer_read_hits = (1-key_reads/key_read_requests) * 100%
Key_buffer_write_hits = (1-key_writes/key_write_requests) * 100%

(4) InnoDB Buffer Hit Rate
Mysql> Show status like ' innodb_buffer_pool_read% ';

Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%

(5) Query Cache Hit Rate
Mysql> Show status like ' qcache% ';

Query_cache_hits = (Qcahce_hits/(qcache_hits + qcache_inserts)) * 100%;

(6) Table Cache State Amount
Mysql> show global status like ' open% ';
Compare open_tables and Opend_tables values

(7) Thread Cache Hit Rate
Mysql> show global status like ' thread% ';
Mysql> show global status like ' Connections ';

Thread_cache_hits = (1-threads_created/connections) * 100%

(8) Lock status
Mysql> show global status like '%lock% ';

table_locks_waited/table_locks_immediate=0.3% if the ratio is larger, it indicates that the blocking caused by the table lock is more serious.
Innodb_row_lock_waits Innodb line Lock, too large may be caused by a gap lock

(9) Replication delay Amount
MySQL > Show slave status
View delay Time

() TMP Table condition (temporary table condition)
MySQL > Show status like ' create_tmp% ';

Created_tmp_disk_tables/created_tmp_tables ratio is best not more than 10%, if the Created_tmp_tables value is larger,
It may be that there are too many sentences or a connection sentence is not optimized

(one) Binlog Cache usage Status
MySQL > Show status like ' binlog_cache% ';

If the Binlog_cache_disk_use value is not 0, you may need to increase the binlog_cache_size size

(innodb_log_waits) Quantity
MySQL > Show status like ' Innodb_log_waits ';

Innodb_log_waits value is not equal to 0, indicating that Innodb log buffer is waiting due to lack of space

Show variables like '%timeout% '; View Connection Timeout configuration

MySQL View database status Show status