show status

どれだけのデータが変更されているか把握する事で、
データベースをどのように調整したら良いかある程度デザインする上での指標になるかと思います。

データ追加頻度

mysql> show status like '%insert%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert             | 1     |INSERTコマンド実行数:再接続すると0にリセットされます
| Com_insert_select      | 0     |
| Delayed_insert_threads | 0     |
| Innodb_rows_inserted   | 1     |起動からのInnoDB Row Insert累計件数
| Qcache_inserts         | 0     |
+------------------------+-------+
5 rows in set (0.00 sec)

mysql> insert into TABLE001(title,comment) values('confirmation','Innodb_rows_inserted time 2');
Query OK, 1 row affected (0.42 sec)

mysql> show status like '%insert%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert             | 2     |
| Com_insert_select      | 0     |
| Delayed_insert_threads | 0     |
| Innodb_rows_inserted   | 2     |
| Qcache_inserts         | 0     |
+------------------------+-------+
5 rows in set (0.00 sec)

mysql>

insert

更新頻度

mysql> show status like '%update%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_update          | 0     | UPDATEコマンド実行数:再接続すると0にリセットされます
| Com_update_multi    | 0     |
| Handler_update      | 0     | Update Row数:再接続すると0にリセットされます
| Innodb_rows_updated | 0     | 起動からのInnoDB Row Update累計件数
+---------------------+-------+
4 rows in set (0.00 sec)

mysql> select * from TABLE001 where id = 39;
+----+--------------+----------------------+
| id | title        | comment              |
+----+--------------+----------------------+
| 39 | confirmation | Com_update increment |
+----+--------------+----------------------+
1 row in set (0.00 sec)

mysql> update TABLE001 set comment = 'Innodb_rows_updated test1' where id = 39;
Query OK, 1 row affected (0.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> show status like '%update%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_update          | 1     |
| Com_update_multi    | 0     |
| Handler_update      | 1     |
| Innodb_rows_updated | 1     |
+---------------------+-------+
4 rows in set (0.00 sec)

mysql> 

update

削除頻度

mysql> show status like '%delete%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_delete          | 0     |
| Com_delete_multi    | 0     |
| Handler_delete      | 0     |
| Innodb_rows_deleted | 0     |
+---------------------+-------+
4 rows in set (0.00 sec)

mysql>  delete from TABLE001 where id > 40;
Query OK, 5 rows affected (0.44 sec)

mysql> show status like '%delete%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_delete          | 1     | DELETEコマンド実行数:再接続すると0にリセットされます
| Com_delete_multi    | 0     | 
| Handler_delete      | 5     | DELETE Row数:再接続すると0にリセットされます
| Innodb_rows_deleted | 5     | 起動からのInnoDB Row Delete累計件数
+---------------------+-------+
4 rows in set (0.00 sec)

mysql>

delete

並列状態

mysql> show status like '%threads%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 0     |
| Threads_connected      | 2     | 取得した瞬間の値
| Threads_created        | 5     | 起動から作成されたスレッド数累計
| Threads_running        | 2     | 取得した瞬間の値
+------------------------+-------+
6 rows in set (0.00 sec)

mysql>

mysql> show processlist;
+----+------------+--------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User       | Host         | db   | Command     | Time | State                                                                 | Info             |
+----+------------+--------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  3 | slave_user | home002:4367 | NULL | Binlog Dump | 5203 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  5 | root       | localhost    | test | Query       |    0 | NULL                                                                  | show processlist |
+----+------------+--------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

mysql>


Comments are closed.

Post Navigation