7.2 관리 및 진단

7.2.1 show explain for <thread=id>

• MariaDB 10.0부터 스레드의 쿼리 실행계획 확인 가능

  
show processlist 

MariaDB [employees]> show processlist;
+----+------+--------------------+-----------+---------+------+-------+------------------+----------+
| Id | User | Host               | db        | Command | Time | State | Info             | Progress |
+----+------+--------------------+-----------+---------+------+-------+------------------+----------+
| 13 | root | 192.168.56.1:49182 | employees | Sleep   |    0 |       | NULL             |    0.000 |
| 14 | root | localhost          | employees | Query   |    0 | init  | show processlist |    0.000 |
+----+------+--------------------+-----------+---------+------+-------+------------------+----------+
2 rows in set (0.00 sec)

• show explain for <해당 번호>; explain 명령 실행계획은 옵티마이저가 구조와 통계정보를 기반으로 예뮬레이션에 보여주는 내용이라면 show explain은 실제 수행되는 상태의 실행계획이다.

MariaDB [employees]> show processlist;
+----+------+--------------------+-----------+---------+------+--------------+----------------------------------+----------+
| Id | User | Host               | db        | Command | Time | State        | Info                             | Progress |
+----+------+--------------------+-----------+---------+------+--------------+----------------------------------+----------+
| 13 | root | 192.168.56.1:49182 | employees | Query   |    3 | Sending data | select * from employees,salaries |    0.000 |
| 14 | root | localhost          | employees | Query   |    0 | init         | show processlist                 |    0.000 |
+----+------+--------------------+-----------+---------+------+--------------+----------------------------------+----------+
2 rows in set (0.00 sec)

MariaDB [employees]> show explain for 13;
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL |  299113 |       |
|    1 | SIMPLE      | salaries  | ALL  | NULL          | NULL | NULL    | NULL | 2837904 |       |
+------+-------------+-----------+------+---------------+------+---------+------+---------+-------+
2 rows in set, 1 warning (0.03 sec)
 
MariaDB [employees]> show explain for 13;
ERROR 1933 (HY000): Target is not running an EXPLAINable command

7.2.2 슬로우 쿼리 로그에 실행계획 출력

• MariaDB 10.0.5 버전부터 슬로우쿼리 실행시간 + 실행계획 출력가능

• 참고사이트 : http://m.blog.naver.com/jevida/220357383281

7.2.3 구조화된 실행 계획 출력

MariaDB [employees]> explain format=JSON
    -> select count(*) from employees e,dept_emp de where de.emp_no=e.emp_no and e.last_name like '%M%';
----------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
----------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "e",
      "access_type": "ALL",
      "possible_keys": ["PRIMARY"],
      "rows": 299113,
      "filtered": 100,
      "attached_condition": "(e.last_name like '%M%')"
    },
    "table": {
      "table_name": "de",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "emp_no"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["emp_no"],
      "ref": ["employees.e.emp_no"],
      "rows": 1,
      "filtered": 100,
      "using_index": true
    }
  }
} |
----------------------------------------------------------------------+

7.2.4 스레드 단위의 메모리 사용량

• MariaDB 10.0 부터 show status 명령어에 Memory_used 란 항목이 추가됨
• show global status 명령어는 전체 메모리 사용량을 의미함

MariaDB [(none)]> show status like '%Memory%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Innodb_read_views_memory | 192      |
| Memory_used              | 67464    |
| Qcache_free_memory       | 33429488 |
+--------------------------+----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> show global status like '%Memory%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| Innodb_read_views_memory | 192       |
| Memory_used              | 207512784 |
| Qcache_free_memory       | 33429488  |
+--------------------------+-----------+
3 rows in set (0.01 sec)

• 모든 스레드의 메모리 사용량을 확인하고 싶다면 INFORMATION_SCHEMA 데이터베이스로 접속해서 processlist 테이블 조회하면 된다.

MariaDB [(none)]> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [information_schema]> select id,user,host,memory_used from processlist;
+----+------+--------------------+-------------+
| id | user | host               | memory_used |
+----+------+--------------------+-------------+
| 16 | root | localhost          |       84584 |
| 15 | root | 192.168.56.1:49183 |       67464 |
+----+------+--------------------+-------------+
2 rows in set (0.00 sec)

MariaDB [information_schema]> select sum(memory_used) from processlist;
+------------------+
| sum(memory_used) |
+------------------+
|           152048 |
+------------------+
1 row in set (0.00 sec)

7.2.5 SHUTDOWN 명령어

• MariaDB 10.0.4 부터는 원격지에서 shutdown 명령어 사용 가능

7.2.6 사용자나 쿼리 실행 강제 종료(KILL)

• 특정 커넥션 접속 종료 : KILL CONNECTION 스레드 아이디

MariaDB [information_schema]> show processlist;
+----+------+--------------------+--------------------+---------+------+-------+------------------+----------+
| Id | User | Host               | db                 | Command | Time | State | Info             | Progress |
+----+------+--------------------+--------------------+---------+------+-------+------------------+----------+
| 15 | root | 192.168.56.1:49183 | employees          | Sleep   | 2199 |       | NULL             |    0.000 |
| 16 | root | localhost          | information_schema | Query   |    0 | init  | show processlist |    0.000 |
+----+------+--------------------+--------------------+---------+------+-------+------------------+----------+
2 rows in set (0.00 sec)

MariaDB [information_schema]> kill connection 15;
Query OK, 0 rows affected (0.03 sec)

MariaDB [information_schema]> show processlist;
+----+------+-----------+--------------------+---------+------+-------+------------------+----------+
| Id | User | Host      | db                 | Command | Time | State | Info             | Progress |
+----+------+-----------+--------------------+---------+------+-------+------------------+----------+
| 16 | root | localhost | information_schema | Query   |    0 | init  | show processlist |    0.000 |
+----+------+-----------+--------------------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

7.2.7 GET DIAGNOSTICS

• MySQL 5.6 and MariaDB 10.0.x 에러 발생 시 에러번호 , SQLSTATE 그리고 에러 메시지 스토어드 프로그램에서 참조할 수 있는 기능이다.