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