Technology sharing | some MySQL DBA practical SQL statements

Author: Du Kaisheng

Contents:

  • 1, Connection related
  • 2, Long affairs
  • 3, Metadata lock
  • 4, Lock wait
  • 5, Global read lock
  • 6, Memory usage monitoring
  • 7, Partition table
  • 8, Database information overview
  • 9, Tables not updated for a long time
  • 10, Primary key, index
  • 11, Storage engine
  • 12, Real time load

Reading tips: 1) This article involves a large number of SQL statements, which is better on * * PC. 2) SQL is based on Oracle MySQL version 5.7. Other versions are not fully applicable due to different data sources. 3) SQL usage scenarios include session connection, metadata lock, global lock, lock wait, long transaction, memory monitoring, partition table, low-frequency update table, primary key, index, storage engine, real-time load * * which are tool articles. It is recommended to collect and save them for subsequent viewing.

1, Connection related

View the session level parameter settings and status variables of a user's connection, which are used to observe other session connection behaviors and help locate connection problems

  • For example: to view the character set setting with user connection ID 19, or to view all user connections without specifying the processlist? ID condition
SELECT T1.VARIABLE_NAME,
       T1.VARIABLE_VALUE,
       T2.PROCESSLIST_ID,
       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST),
       T2.PROCESSLIST_DB,
       T2.PROCESSLIST_COMMAND
FROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1,
     PERFORMANCE_SCHEMA.THREADS T2
WHERE T1.THREAD_ID = T2.THREAD_ID
  AND T1.VARIABLE_NAME LIKE 'character%'
  AND PROCESSLIST_ID ='19';
+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+
| VARIABLE_NAME            | VARIABLE_VALUE | PROCESSLIST_ID | concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+
| character_set_client     | gbk            |             19 | root@localhost                                      | db             | Query               |
| character_set_connection | gbk            |             19 | root@localhost                                      | db             | Query               |
| character_set_database   | utf8mb4        |             19 | root@localhost                                      | db             | Query               |
| character_set_filesystem | binary         |             19 | root@localhost                                      | db             | Query               |
| character_set_results    | gbk            |             19 | root@localhost                                      | db             | Query               |
| character_set_server     | utf8mb4        |             19 | root@localhost                                      | db             | Query               |
+--------------------------+----------------+----------------+-----------------------------------------------------+----------------+---------------------+
6 rows in set (0.01 sec)
  • For example, we found that the connection with user ID 254 closed the SQL log bin setting
SELECT T1.VARIABLE_NAME,
       T1.VARIABLE_VALUE,
       T2.PROCESSLIST_ID,
       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',
       T2.PROCESSLIST_DB,
       T2.PROCESSLIST_COMMAND
FROM PERFORMANCE_SCHEMA.VARIABLES_BY_THREAD T1,
     PERFORMANCE_SCHEMA.THREADS T2
WHERE T1.THREAD_ID = T2.THREAD_ID
  AND T1.VARIABLE_NAME LIKE 'sql_log_bin';
+---------------+----------------+----------------+------------------+----------------+---------------------+
| VARIABLE_NAME | VARIABLE_VALUE | PROCESSLIST_ID | User@Host        | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+---------------+----------------+----------------+------------------+----------------+---------------------+
| sql_log_bin   | OFF            |            254 | root@localhost   | NULL           | Sleep               |
| sql_log_bin   | ON             |            256 | root@localhost   | NULL           | Sleep               |
| sql_log_bin   | ON             |            257 | root@10.211.55.2 | NULL           | Sleep               |
| sql_log_bin   | ON             |            258 | root@10.211.55.2 | NULL           | Sleep               |
| sql_log_bin   | ON             |            259 | root@localhost   | NULL           | Query               |
| sql_log_bin   | ON             |            261 | root@localhost   | NULL           | Sleep               |
+---------------+----------------+----------------+------------------+----------------+---------------------+
4 rows in set (0.00 sec)
  • Example: view the network traffic change with user connection ID 24
SELECT T1.VARIABLE_NAME,
       T1.VARIABLE_VALUE,
       T2.PROCESSLIST_ID,
       concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',
       T2.PROCESSLIST_DB,
       T2.PROCESSLIST_COMMAND
FROM PERFORMANCE_SCHEMA.STATUS_BY_THREAD T1,
     PERFORMANCE_SCHEMA.THREADS T2
WHERE T1.THREAD_ID = T2.THREAD_ID
  AND T2.PROCESSLIST_USER = 'root'
  AND PROCESSLIST_ID= 24
  AND VARIABLE_NAME LIKE 'Byte%';
+----------------+----------------+----------------+----------------+----------------+---------------------+
| VARIABLE_NAME  | VARIABLE_VALUE | PROCESSLIST_ID | User@Host      | PROCESSLIST_DB | PROCESSLIST_COMMAND |
+----------------+----------------+----------------+----------------+----------------+---------------------+
| Bytes_received | 224            |             24 | root@127.0.0.1 | NULL           | Sleep               |
| Bytes_sent     | 182            |             24 | root@127.0.0.1 | NULL           | Sleep               |
+----------------+----------------+----------------+----------------+----------------+---------------------+
2 rows in set (0.00 sec)

2, Long affairs

Uncommitted user connection for more than 5s after transaction is opened

SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 5 ;
+----------------+---------------------+---------------------+---------------+------+-----------------+------+-----------+
| PROCESSLIST_ID | NOW()               | TRX_STARTED         | TRX_LAST_TIME | User | Host            | DB   | TRX_QUERY |
+----------------+---------------------+---------------------+---------------+------+-----------------+------+-----------+
|             24 | 2019-12-16 02:49:52 | 2019-12-16 02:41:15 |           517 | root | 127.0.0.1:58682 | db   | NULL      |
+----------------+---------------------+---------------------+---------------+------+-----------------+------+-----------+
1 row in set (0.01 sec)

3, Metadata lock

MySQL 5.7 enables metadata lock tracking to track and locate metadata lock related blocking problems

// Temporary open, dynamic effective
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';
// Added in the configuration file, effective after restart
performance-schema-instrument = wait/lock/metadata/sql/mdl=ON

Scenario 1: kill the session holding the MDL lock to make the DDL statement execute smoothly.

  • DDL statements are usually blocked because there are long transactions that are not committed in time after getting resources. Therefore, find and kill the session whose transaction run time is longer than DDL run time to issue the DDL statement smoothly. The SQL statement is as follows:
// Find threads with transaction run time > = DDL wait time
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND TO_SECONDS(now())-TO_SECONDS(trx_started) >=
    (SELECT MAX(Time)
     FROM INFORMATION_SCHEMA.processlist
     WHERE STATE='Waiting for table metadata lock'
       AND INFO LIKE 'alter%table%' OR INFO LIKE 'truncate%table%') ;
+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+
| PROCESSLIST_ID | NOW()               | TRX_STARTED         | TRX_LAST_TIME | User | Host      | DB   | TRX_QUERY |
+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+
|            253 | 2019-12-24 01:42:11 | 2019-12-24 01:41:24 |            47 | root | localhost | NULL | NULL      |
+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+
1 row in set (0.00 sec)
// kill long transactions and release the MDL resources held
kill 253;

Note: due to the limited metadata information record of MySQL, it is possible to kill innocent long transactions by mistake here, which cannot be completely avoided.

  • When the blocking source is killed, there may be a situation where the DDL statement and the blocked SQL statement are locked at the same time. At this time, the transaction start time is equal to the DDL start time connection will occur. Such transactions also need to be killed.
//Find threads with transaction start time = DDL statement transaction start time
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
       NOW(),
       TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
       USER,
       HOST,
       DB,
       TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
  AND trx_started =
    (SELECT MIN(trx_started)
     FROM INFORMATION_SCHEMA.INNODB_TRX
     GROUP BY trx_started HAVING count(trx_started)>=2)
  AND TRX_QUERY NOT LIKE 'alter%table%'
  OR TRX_QUERY IS NULL;
+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+
| PROCESSLIST_ID | NOW()               | TRX_STARTED         | TRX_LAST_TIME | User | Host      | DB   | TRX_QUERY |
+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+
|            255 | 2019-12-24 01:42:44 | 2019-12-24 01:42:33 |            11 | root | localhost | NULL | NULL      |
+----------------+---------------------+---------------------+---------------+------+-----------+------+-----------+
1 row in set (0.00 sec)
//Kill the blocking source
kill 255;

Scenario 2: kill drops the user connection of the DDL statement and cancels the distribution of the DDL statement to ensure that the service is not blocked.

// Find the user connection where the DDL statement is located
SELECT *
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE INFO LIKE 'ALTER%TABLE%';
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| ID  | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                             |
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 254 | root | localhost | NULL | Query   |  730 | Waiting for table metadata lock | alter table db.t1 add index (id) |
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
1 row in set (0.00 sec)
// Kill the user connection of DDL statement
kill 254;

4, Lock wait

View the blocked thread, blocked thread information and related users, IP and PORT related to lock waiting

SELECT locked_table,
       locked_index,
       locked_type,
       blocking_pid,
       concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)",
       blocking_lock_mode,
       blocking_trx_rows_modified,
       waiting_pid,
       concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)",
       waiting_lock_mode,
       waiting_trx_rows_modified,
       wait_age_secs,
       waiting_query
FROM sys.x$innodb_lock_waits T1
LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID
LEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
| locked_table | locked_index | locked_type | blocking_pid | blocking(user@ip:port) | blocking_lock_mode | blocking_trx_rows_modified | waiting_pid | waiting(user@ip:port) | waiting_lock_mode | waiting_trx_rows_modified | wait_age_secs | waiting_query                   |
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
| `db`.`t1`    | PRIMARY      | RECORD      |          228 | dks@127.0.0.1:56724    | X                  |                          1 |         231 | root@127.0.0.1:50852  | S                 |                         0 |             1 | insert into db.t1(id) values(2) |
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
1 row in set, 3 warnings (0.00 sec)
  • If you don't care about blocking related users, IP and ports, you can directly view the InnoDB ﹣ lock ﹣ waits table information.
select * from sys.x$innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2019-12-23 02:14:22
                    wait_age: 00:00:32
               wait_age_secs: 32
                locked_table: `db`.`t1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 7204404
         waiting_trx_started: 2019-12-23 02:14:18
             waiting_trx_age: 00:00:36
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 213
               waiting_query: delete from db.t1 where id=200
             waiting_lock_id: 7204404:1994:3:4
           waiting_lock_mode: X
             blocking_trx_id: 7204394
                blocking_pid: 207
              blocking_query: select * from   sys.x$innodb_lock_waits
            blocking_lock_id: 7204394:1994:3:4
          blocking_lock_mode: X
        blocking_trx_started: 2019-12-23 02:10:06
            blocking_trx_age: 00:04:48
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 207
sql_kill_blocking_connection: KILL 207
1 row in set, 3 warnings (0.00 sec)

Parameters affecting lock wait timeout

5, Global read lock

The lock duration column of performance table schema.metadata indicates that the FTWRL statement has been added, and the COMMIT status of object type indicates that the lock has been successfully added

  • Scenario 1: kill the session adding FTWRL and resume business operation
SELECT processlist_id,
       mdl.OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_DURATION,
       LOCK_STATUS
FROM performance_schema.metadata_locks mdl
INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id
AND processlist_id <> connection_id()
AND LOCK_DURATION='EXPLICIT';
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
|            231 | GLOBAL      | NULL          | NULL        | SHARED    | EXPLICIT      | GRANTED     |
|            231 | COMMIT      | NULL          | NULL        | SHARED    | EXPLICIT      | GRANTED     |
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
2 rows in set (0.00 sec)
// Kill the user connection adding FTWRL
kill 231;
  • Scenario 2: kill the thread whose statement execution time is greater than FTWRL execution time to ensure that FTWRL is issued successfully
SELECT T2.THREAD_ID,
       T1.ID AS PROCESSLIST_ID,
       T1.User,
       T1.Host,
       T1.db,
       T1.Time,
       T1.State,
       T1.Info,
       T3.TRX_STARTED,
       TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME
FROM INFORMATION_SCHEMA.processlist T1
LEFT JOIN PERFORMANCE_SCHEMA.THREADS T2 ON T1.ID=T2.PROCESSLIST_ID
LEFT JOIN INFORMATION_SCHEMA.INNODB_TRX T3 ON T1.id=T3.trx_mysql_thread_id
WHERE T1.TIME >=
    (SELECT MAX(Time)
     FROM INFORMATION_SCHEMA.processlist
     WHERE INFO LIKE 'flush%table%with%read%lock')
  AND Info IS NOT NULL;
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
| THREAD_ID | PROCESSLIST_ID | User | Host              | db   | Time | State                   | Info                                        | TRX_STARTED         | TRX_LAST_TIME |
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
|       284 |            246 | root | localhost         | NULL |  364 | User sleep              | select * from db.t1 where sleep(1000000000) | 2019-12-23 14:57:23 |           364 |
|       286 |            248 | root | 10.211.55.2:55435 | NULL |  232 | Waiting for table flush | flush table with read lock                  | NULL                |          NULL |
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
2 rows in set (0.00 sec)

6, Memory usage monitoring

By default, only the performance ﹣ schema library is used for memory statistics. For global memory statistics, you need to enable it manually

//Dynamic start, start statistics after start
update performance_schema.setup_instruments set
enabled = 'yes' where name like 'memory%';
//Added in the configuration file, effective after restart
performance-schema-instrument='memory/%=COUNTED'
  • View the instance memory consumption distribution. There are multiple memory related views under sys library to help users locate and analyze memory overflow problems
SELECT event_name,
       current_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory%innodb%';
+-------------------------------------------+---------------+
| event_name                                | current_alloc |
+-------------------------------------------+---------------+
| memory/innodb/buf_buf_pool                | 134.31 MiB    |
| memory/innodb/log0log                     | 32.01 MiB     |
| memory/innodb/mem0mem                     | 15.71 MiB     |
| memory/innodb/lock0lock                   | 12.21 MiB     |
| memory/innodb/os0event                    | 8.37 MiB      |
| memory/innodb/hash0hash                   | 4.74 MiB      |
...
+-------------------------------------------+---------------+
42 rows in set (0.01 sec)

7, Partition table

  • View partition table information in the instance
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       count(PARTITION_NAME) AS PARTITION_COUNT,
       sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS,
       CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA,
         TABLE_NAME
ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME       | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| db           | t1               |             365 |                0 | 5.70M       | 17.11M       | 22.81M     |
| db           | t2               |             391 |                0 | 6.11M       | 0.00M        | 6.11M      |
| db           | t3               |               4 |            32556 | 2.28M       | 0.69M        | 2.97M      |
| db           | t4               |              26 |                0 | 0.41M       | 2.44M        | 2.84M      |
| db           | t5               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |
| db           | t6               |               4 |                0 | 0.06M       | 0.00M        | 0.06M      |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
6 rows in set (1.04 sec)
  • View the specific information of a partition table. Take the partition table with database name db and table name e as an example
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       PARTITION_NAME,
       PARTITION_EXPRESSION,
       PARTITION_METHOD,
       PARTITION_DESCRIPTION,
       TABLE_ROWS,
       CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH,
       CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH,
       CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
                         'mysql',
                         'INFORMATION_SCHEMA',
                         'performance_schema')
  AND PARTITION_NAME IS NOT NULL
  AND TABLE_SCHEMA='db'
  AND TABLE_NAME='e';
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| db           | e          | p0             | id                   | RANGE            | 50                    |       4096 | 0.20M       | 0.09M        | 0.30M      |
| db           | e          | p1             | id                   | RANGE            | 100                   |       6144 | 0.28M       | 0.13M        | 0.41M      |
| db           | e          | p2             | id                   | RANGE            | 150                   |       6144 | 0.28M       | 0.13M        | 0.41M      |
| db           | e          | p3             | id                   | RANGE            | MAXVALUE              |      16172 | 1.52M       | 0.34M        | 1.86M      |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
4 rows in set (0.00 sec)

8, Database information overview

  • Count the size of each database in the instance
SELECT TABLE_SCHEMA,
       round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB,
       round(SUM(data_length)/1024/1024,2) AS DATA_MB,
       round(SUM(index_length)/1024/1024,2) AS INDEX_MB,
       COUNT(*) AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys',
                           'mysql',
                           'INFORMATION_SCHEMA',
                           'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;
+--------------+----------+---------+----------+--------+
| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | TABLES |
+--------------+----------+---------+----------+--------+
| cloud        |   229.84 |  223.02 |     6.83 |     41 |
| db           |    66.42 |   30.56 |    35.86 |     31 |
| dks          |    14.41 |    9.70 |     4.70 |    621 |
| test         |     0.06 |    0.06 |     0.00 |      4 |
| db2          |     0.03 |    0.03 |     0.00 |      2 |
+--------------+----------+---------+----------+--------+
5 rows in set, 1 warning (0.91 sec)
  • Count the sizes of tables under a database
SELECT TABLE_SCHEMA,
       TABLE_NAME TABLE_NAME,
                  CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,
                  CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,
                  CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,
                  engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'performance_schema',
                           'sys',
                           'mysql')
  AND TABLE_SCHEMA='db'
ORDER BY (data_length + index_length) DESC LIMIT 10;
+--------------+-----------------------+-------------+--------------+------------+--------+
| TABLE_SCHEMA | table_name            | data_length | index_length | total_size | engine |
+--------------+-----------------------+-------------+--------------+------------+--------+
| db           | t1                    | 5.70M       | 22.81M       | 28.52M     | InnoDB |
| db           | t2                    | 15.19M      | 9.59M        | 24.78M     | InnoDB |
| db           | t3                    | 6.11M       | 0.00M        | 6.11M      | InnoDB |
| db           | t4                    | 2.28M       | 0.69M        | 2.97M      | InnoDB |
| db           | t5                    | 0.41M       | 2.44M        | 2.84M      | InnoDB |
| db           | t6                    | 0.17M       | 0.00M        | 0.17M      | InnoDB |
| db           | t7                    | 0.17M       | 0.00M        | 0.17M      | InnoDB |
| db           | t8                    | 0.02M       | 0.11M        | 0.13M      | InnoDB |
| db           | t9                    | 0.08M       | 0.00M        | 0.08M      | InnoDB |
| db           | t10                   | 0.05M       | 0.02M        | 0.06M      | InnoDB |
+--------------+-----------------------+-------------+--------------+------------+--------+
10 rows in set, 1 warning (0.01 sec)
  • View the basic information of the table below a library
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       table_collation,
       engine,
       table_rows
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'sys',
                           'mysql',
                           'performance_schema')
  AND TABLE_TYPE='BASE TABLE'
  AND TABLE_SCHEMA='db'
ORDER BY table_rows DESC ;
+--------------+-----------------------+--------------------+--------+------------+
| TABLE_SCHEMA | table_name            | table_collation    | engine | table_rows |
+--------------+-----------------------+--------------------+--------+------------+
| db           | t1                    | utf8_general_ci    | InnoDB |     159432 |
| db           | t2                    | utf8mb4_general_ci | InnoDB |      32556 |
| db           | t3                    | utf8mb4_general_ci | InnoDB |       2032 |
...
| db           | t100                  | utf8mb4_general_ci | InnoDB |          0 |
| db           | t101                  | utf8mb4_general_ci | InnoDB |          0 |
+--------------+-----------------------+--------------------+--------+------------+
25 rows in set, 1 warning (0.01 sec)

9, Tables not updated for a long time

If the update time is NULL, the instance has not been updated since it was started

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
ORDER BY UPDATE_TIME ;
+--------------+-----------------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME            | UPDATE_TIME         |
+--------------+-----------------------+---------------------+
| db           | t1                    | NULL                |
| db           | t2                    | NULL                |
| db           | t3                    | NULL                |
| db           | t4                    | 2019-12-16 07:45:29 |
| db           | t5                    | 2019-12-16 16:52:01 |
+--------------+-----------------------+---------------------+
22 rows in set, 1 warning (0.01 sec)

10, Primary key, index

No primary key, unique key and secondary index base table

  • MySQL Innodb storage engine organizes tables for indexes, so setting appropriate primary key fields is critical to performance
SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND T2.TABLE_TYPE='BASE TABLE'
  AND T1.TABLE_SCHEMA='db'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING MAX(COLUMN_KEY)='';

No primary key, unique key, only secondary index table

  • This type of table has no efficient index, so it is easy to cause replication delay when playing back from the library
SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS  T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND T2.TABLE_TYPE='BASE TABLE'
  AND T1.COLUMN_KEY != ''
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';

Only primary key and unique key table

  • Because there is no secondary index in this type of table structure, it may cause performance jitter in frequent full table scanning after the application of SQL statement goes online
SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
  AND T2.TABLE_TYPE='BASE TABLE'
  AND T1.COLUMN_KEY != ''
  AND T1.TABLE_SCHEMA='db'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'MUL';

No primary key, unique key table

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
                           'MYSQL',
                           'INFORMATION_SCHEMA',
                           'PERFORMANCE_SCHEMA')
AND   T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';

11, Storage engine

  • Storage engine distribution
SELECT TABLE_SCHEMA,
       ENGINE,
       COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'PERFORMANCE_SCHEMA',
                           'SYS',
                           'MYSQL')
  AND TABLE_TYPE='BASE TABLE'
GROUP BY TABLE_SCHEMA,
         ENGINE;
  • Non INNODB storage engine table
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_COLLATION,
       ENGINE,
       TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'SYS',
                           'MYSQL',
                           'PERFORMANCE_SCHEMA')
  AND TABLE_TYPE='BASE TABLE'
  AND ENGINE NOT IN ('INNODB')
ORDER BY TABLE_ROWS DESC ;

12, Real time load

while true
do 
mysqladmin -uroot -pxxxxxxx extended-status -r -i 1 -c 30 --socket=/mysqldata/mysqld.sock 2>/dev/null|awk -F"|" "BEGIN{ count=0 ;}"'{ if($2 ~ /Variable_name/ && ++count == 1){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}' 
done

Tags: Database MySQL SQL Session PCL

Posted on Wed, 15 Jan 2020 04:10:52 -0500 by johnsonzhang