mysql_sql查性能语句
mysql> SHOW PROCESSLIST; +----+--------+----------------------+-------+-------------+--------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+----------------------+-------+-------------+--------+-----------------------------------------------------------------------+------------------+ | 3 | copy_u | 192.168.31.128:49106 | NULL | Binlog Dump | 292296 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 7 | copy_u | 192.168.31.42:60886 | NULL | Binlog Dump | 1688 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 8 | root | localhost | DATA1 | Query | 0 | init | SHOW PROCESSLIST | +----+--------+----------------------+-------+-------------+--------+-----------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec) 查看系统上运行的所有线程
mysql> SHOW GLOBAL STATUS; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 2 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 5 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 4 | | Bytes_received | 8341 | | Bytes_sent | 57632 | 。。。。。 显示全局变量的所有值 mysql> SHOW SESSION STATUS; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 2 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 5 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 4 | | Bytes_received | 601 | ....... 显示会话变量的统计信息
mysql> SHOW TABLE STATUS; +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ | TEST1 | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 0 | NULL | 2019-10-24 23:54:15 | NULL | NULL | latin1_swedish_ci | NULL | | | | TEST2 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2019-10-25 14:01:18 | NULL | NULL | latin1_swedish_ci | NULL | | | | emploee | InnoDB | 10 | Compact | 3 | 5461 | 16384 | 0 | 0 | 0 | NULL | 2019-10-28 10:41:29 | NULL | NULL | latin1_swedish_ci | NULL | | | | log | MyISAM | 10 | Dynamic | 6 | 48 | 292 | 281474976710655 | 2048 | 0 | 7 | 2019-10-28 10:56:35 | 2019-10-28 15:13:51 | NULL | latin1_swedish_ci | NULL | | | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+ 4 rows in set (0.00 sec) 查看给定数据库的表的详情，包括存储引起，排序规则collation 创建数据，索引数据 行统计信息
mysql> SHOW GLOBAL VARIABLES; +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | avoid_temporal_upgrade | OFF | | back_log | 80 | | basedir | /usr/ 。。。。。。 显示系统变量，确定当前配置是否已经被更改或者某些选项是否被设置，有些变量是只读，只能通过配置文件或命令行在启动的时候修改
mysql> SHOW SESSION STATUS LIKE '%log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_binlog | 0 | +---------------+-------+ 1 row in set (0.00 sec) 利用LIKE选择内容
mysql> SHOW ENGINES ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) 显示所有已知的存储引擎列表及其状态（是否启用），主要用于查询数据库使用何种引擎，以及复制时master 和slave是否相同引擎
与mysql复制相关的命令如下
SHOW BINLOG EVENTS [IN log_file] [FROM pos] [LIMIT offset row_count]
显示被记录到二进制日志中的事件。指定审核文件，没有则默认第一个当前使用的文件。限定输出为某个特定位置之后的所有事件，
主要用于诊断复制问题的命令
一般会使用LIMIT语句，要不要会太多记录输出
如果查看大量事件，应该考虑使用mysqlbinlog
SHOW RELAYLOG EVENTS [IN log_file] [FROM pos] [LIMIT offset row_count]
和上面的一样，不过是只能在slave上执行
mysql> SHOW BINARY LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 143 | | master-bin.000002 | 143 | | master-bin.000003 | 895 | | master-bin.000004 | 400 | | master-bin.000005 | 120 | | master-bin.000006 | 403 | | master-bin.000007 | 509 | | master-bin.000008 | 168 | | master-bin.000009 | 889 | | master-bin.000010 | 143 | | master-bin.000011 | 940 | | master-bin.000012 | 549 | | master-bin.000013 | 143 | | master-bin.000014 | 3641 | +-------------------+-----------+ 14 rows in set (0.00 sec) 显示服务器上的二进制日志列表
mysql> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000014 | 3641 | | gl_db | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 显示master当前的配置，二级制文件及其位置
mysql> SHOW SLAVE HOSTS; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 2 | | 3306 | 1 | 94dd0e21-f675-11e9-a9fa-005056241bbd | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec) 通过--report-host连接到master的slave列表 确定哪些slave 连接到master
mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: MYSQL_MASTER Master_User: copy_u Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000014 Read_Master_Log_Pos: 3641 Relay_Log_File: slave-relay-bin.000029 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3641 Relay_Log_Space: 4142 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: ed4f6920-f653-11e9-a91e-000c2930612e Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) 显示复制的slave的状态信息
例子说明
mysql> SHOW VARIABLES LIKE '%thread%'; +-----------------------------------------+---------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------+ | innodb_purge_threads | 1 | | innodb_read_io_threads | 4 | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_write_io_threads | 4 | | max_delayed_threads | 20 | | max_insert_delayed_threads | 20 | | myisam_repair_threads | 1 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 402 | | pseudo_thread_id | 8 | | thread_cache_size | 9 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | +-----------------------------------------+---------------------------+ 15 rows in set (0.00 sec) 显示线程状态变量
可以查看在线MySQL参考手册可以知道那些变量需要监控
下面是query cache的变量
查询缓存是MySQL的重要性能之一
允许服务器在内存中缓存频繁使用的查询语句和查询结果，从而提高性能
mysql> SHOW VARIABLES LIKE '%query_cache%' -> ; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.00 sec) 检测query_cache是否开启，这是一个全局变量，但是是只读的。 query_cache_size：决定查询缓存的临时关闭与开启，设置为0，立即关闭查询，并将所有缓存中的查询删除掉。与have_query_cache 无关 have_query_cache ：用于表示查询缓存可不可以用 设置 query_cache_type 为oFF ,不会释放查询缓存的缓冲区，必须同时query_cache_size为0才会完全关闭查询缓存
查询缓存的状态变量
mysql> SHOW STATUS LIKE '%Qcache%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031352 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 24 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.00 sec)
浙公网安备 33010602011771号