Optimization of mysql-system status view-location problem, analysis of sql, index

The initial stage of system development pays attention to function realization, and data is relatively small. With the increase of data volume, performance problems will gradually become prominent.

Query system status information

show  [sesison |global ] status;
mysqladmin extended-status

show  status like 'Com_%';
mysql> show  status like 'Com_%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_delete                  | 0     | Number of deletions performed, one cumulative execution
| Com_insert                  | 0     |  implement insert Number of times, one execution cumulative, batch execution cumulative only once
| Com_savepoint               | 0     |
| Com_select                  | 1     |  implement select Number of times to execute one more time
| Com_unlock_tables           | 0     |
| Com_update                  | 0     | implement update Number of times to perform a cumulative one
. . . . . . . . . . 
+-----------------------------+-------+
149 rows in set (2.43 sec)

Operational records of innnodb's tables

mysql> show  status like 'Innodb_row_%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
| Innodb_rows_deleted           | 0     | Number of rows performing deletion operations
| Innodb_rows_inserted          | 0     |implement insert Number of rows
| Innodb_rows_read              | 8     |  select Number of rows returned by the query
| Innodb_rows_updated           | 0     |implement updata Number of rows of operations
+-------------------------------+-------+
9 rows in set (0.00 sec)

Number of connections to mysql server

mysql> show  status like 'Connections%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 5     |
+---------------+-------+
1 row in set (0.00 sec)

linux command uptime server working time

[root@localhost ~]# uptime
22:54:13 up  3:54,  3 users,  load average: 0.08, 0.02, 0.01

Number of slow queries in mysql database

mysql> show  status like 'Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.01 sec)

Open slow query monitoring and locate sql of slow query

     41 #Open Slow Query
     42 slow_query_log = 1
     43 long_query_time=2
     44 slow_query_log_file = /var/lib/mysql/slow.log

Locate sql execution by show processlist

 

 

Execution plan analysis of slow sql by execution plan expalin

mysql> explain select  * from  complain where  complain_code='TSD20190515006';
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys       | key                 | key_len | ref   | rows  | filtered | Extra |
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | complain | NULL       | ref  | complain_code_index | complain_code_index | 195     | const | 54570 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
select_type type type description
    SIMPLE: Simple Table Query
    PRIMARY: Main Query, Outgoing Query
    UNION: The second or subsequent query statement in Union
    SUBQUERY: The first select in a subquery

table: table of output results
 type: The way mysql queries rows in tables, access types
    ALL: Full table scanning, traversing the whole table
    Index: Full scan of index, traversing the whole index
    ref: Prefix Scan Using Nonunique Scan or Unique Index
    eq_ref: Similar to ref, the difference is that the index uses a unique index. For each index key value, there is only one record match in the table.
            Use primary key or unique index as Association criteria in multiple tables
    const/system single table has at most one matching row, and the query speed is fast.
    NULL: You can get results directly without accessing tables or indexes

Analysis of sql by show profile

Verification profile Is it open?
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (2.23 sec)


profiling Close by default and pass set Command to open
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)




mysql> select  count(1) from  complain;
+----------+
| count(1) |
+----------+
|  1638400 |
+----------+
1 row in set (1.32 sec)

//Query sql statement execution
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 2.22956925 | show tables                     |
|        2 | 0.00213900 | show databases                  |
|        3 | 0.00025525 | SELECT DATABASE()               |
|        4 | 0.00040425 | show tables                     |
|        5 | 0.00005675 | selcet  count(1) from  complain |
|        6 | 1.31990750 | select  count(1) from  complain |
+----------+------------+---------------------------------+
6 rows in set, 1 warning (0.00 sec)

//View the execution of specific sql
mysql> show profile for query 6;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000265 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000538 |
| init                 | 0.000089 |
| System lock          | 0.000043 |
| optimizing           | 0.000021 |
| statistics           | 0.000018 |
| preparing            | 0.000043 |
| executing            | 0.000004 |
| Sending data         | 1.318605 |   mysql The thread starts accessing the data and returns the time to the client
| end                  | 0.000014 |
| query end            | 0.000014 |
| closing tables       | 0.000030 |
| freeing items        | 0.000201 |
| cleaning up          | 0.000017 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

sending data Express mysql The time when a thread starts accessing data rows and returns the results to the client, not just the time when the results are returned to the client.
sending data In the state of state, mysql Threads often need to do a lot of disk reading, which is often the most time-consuming operation.


profiling The relevant information exists. information_schema.profiling In the table
select  * from  information_schema.profiling
where  query_id=1




mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000072 | 0.000000 |   0.000000 |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |
| Opening tables       | 0.000013 | 0.000000 |   0.000000 |
| init                 | 0.000026 | 0.000000 |   0.000000 |
| System lock          | 0.000007 | 0.000000 |   0.000000 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |
| statistics           | 0.000011 | 0.000000 |   0.000000 |
| preparing            | 0.000008 | 0.000000 |   0.000000 |
| executing            | 0.000002 | 0.000000 |   0.000000 |
| Sending data         | 0.204742 | 0.203969 |   0.000000 |
| end                  | 0.000012 | 0.000000 |   0.000000 |
| query end            | 0.000009 | 0.000000 |   0.000000 |
| closing tables       | 0.000007 | 0.000000 |   0.000000 |
| freeing items        | 0.000086 | 0.001000 |   0.000000 |
| cleaning up          | 0.000016 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.01 sec)


mysql> show profile all for query 1;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file          | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting             | 0.000072 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000006 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_authorization.cc |         802 |
| Opening tables       | 0.000013 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc          |        5714 |
| init                 | 0.000026 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         121 |
| System lock          | 0.000007 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc              |         323 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         151 |
| statistics           | 0.000011 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         367 |
| preparing            | 0.000008 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize              | sql_optimizer.cc     |         475 |
| executing            | 0.000002 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         119 |
| Sending data         | 0.204742 | 0.203969 |   0.000000 |                 0 |                   2 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                  | sql_executor.cc      |         195 |
| end                  | 0.000012 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | handle_query          | sql_select.cc        |         199 |
| query end            | 0.000009 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4946 |
| closing tables       | 0.000007 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc         |        4998 |
| freeing items        | 0.000086 | 0.001000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc         |        5610 |
| cleaning up          | 0.000016 | 0.000000 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc         |        1924 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)




mysql> show profile page faults for query 1;
+----------------------+----------+-------------------+-------------------+
| Status               | Duration | Page_faults_major | Page_faults_minor |
+----------------------+----------+-------------------+-------------------+
| starting             | 0.000072 |                 0 |                 0 |
| checking permissions | 0.000006 |                 0 |                 0 |
| Opening tables       | 0.000013 |                 0 |                 0 |
| init                 | 0.000026 |                 0 |                 0 |
| System lock          | 0.000007 |                 0 |                 0 |
| optimizing           | 0.000003 |                 0 |                 0 |
| statistics           | 0.000011 |                 0 |                 0 |
| preparing            | 0.000008 |                 0 |                 0 |
| executing            | 0.000002 |                 0 |                 0 |
| Sending data         | 0.204742 |                 0 |                 0 |
| end                  | 0.000012 |                 0 |                 0 |
| query end            | 0.000009 |                 0 |                 0 |
| closing tables       | 0.000007 |                 0 |                 0 |
| freeing items        | 0.000086 |                 0 |                 0 |
| cleaning up          | 0.000016 |                 0 |                 0 |
+----------------------+----------+-------------------+-------------------+
15 rows in set, 1 warning (0.00 sec)


mysql> show profile block io for query 1;
+----------------------+----------+--------------+---------------+
| Status               | Duration | Block_ops_in | Block_ops_out |
+----------------------+----------+--------------+---------------+
| starting             | 0.000072 |            0 |             0 |
| checking permissions | 0.000006 |            0 |             0 |
| Opening tables       | 0.000013 |            0 |             0 |
| init                 | 0.000026 |            0 |             0 |
| System lock          | 0.000007 |            0 |             0 |
| optimizing           | 0.000003 |            0 |             0 |
| statistics           | 0.000011 |            0 |             0 |
| preparing            | 0.000008 |            0 |             0 |
| executing            | 0.000002 |            0 |             0 |
| Sending data         | 0.204742 |            0 |             0 |
| end                  | 0.000012 |            0 |             0 |
| query end            | 0.000009 |            0 |             0 |
| closing tables       | 0.000007 |            0 |             0 |
| freeing items        | 0.000086 |            0 |             0 |
| cleaning up          | 0.000016 |            0 |             0 |
+----------------------+----------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

 

 

Tags: MySQL SQL Database mysqladmin

Posted on Sun, 06 Oct 2019 07:41:37 -0700 by osusano