The v2 patch 之 SqlChanges

杀死idle线程

KILL IF_IDLE can be used to kill a connection but only if it is idle.

MAX_QUERIES_PER_MINUTE can be used in place of MAX_QUERIES_PER_HOUR. This version of MySQL enforces query limits per minute rather than per hour and the value stored in the MySQL privilege table is the rate per minute.

CREATE MAPPED USER 'foo' ROLE 'bar' and
DROP MAPPED USER 'foo' support mapped users. See MysqlRoles for more details.

SHOW PROCESSLIST WITH ROLES and
SHOW USER_STATISTICS WITH ROLES use the role name rather than the user name in results..

有用的用户表状态监控 UserTableMonitoring

• SHOW USER_STATISTICS
• SHOW TABLE_STATISTICS
• SHOW INDEX_STATISTICS
• SHOW CLIENT_STATISTICS
• FLUSH TABLE_STATISTICS
• FLUSH INDEX_STATISTICS
• FLUSH CLIENT_STATISTICS
  
  MysqlRateLimiting
  
• MAKE USER 'foo' DELAYED 1000
• MAKE CLIENT '10.0.0.1' DELAYED 2000
• SHOW DELAYED USER
• SHOW DELAYED CLIENT

SHOW INNODB LOCKS provides more details on InnoDB lock holders and waiters.

FLUSH SLOW QUERY LOGS rotates the slow query log.

MAKE MASTER REVOKE SESSION disconnects all sessions but the current one and prevents future connections from all users unless they have SUPER, REPL_CLIENT or REPL_SLAVE privileges. MAKE MASTER GRANT SESSION undoes this.

mysql> SHOW TABLE_STATISTICS;
+--------------+-----------+--------------+-------------------------+--------+
| Table | Rows_read | Rows_changed | Rows_changed_x_#indexes | Engine |
+--------------+-----------+--------------+-------------------------+--------+
| ETL.Customer | 0 | 2 | 2 | InnoDB |
| mysql.db | 2 | 0 | 0 | MyISAM |
| ETL.Mer | 8 | 1 | 1 | MyISAM |
| mysql.user | 6 | 0 | 0 | MyISAM |
+--------------+-----------+--------------+-------------------------+--------+
4 rows in set (0.00 sec)

mysql> SHOW USER_STATISTICS\G
*************************** 1. row ***************************
User: root
Total_connections: 1
Concurrent_connections: 1
Connected_time: 923
Busy_time: 0
Cpu_time: 0
Bytes_received: 0
Bytes_sent: 352
Binlog_bytes_written: 0
Rows_fetched: 1
Rows_updated: 0
Table_rows_read: 0
Select_commands: 1
Update_commands: 0
Other_commands: 1
Commit_transactions: 0
Rollback_transactions: 0
Denied_connections: 0
Lost_connections: 0
Access_denied: 0
Empty_queries: 0