amegonの雑なブログ

日常的なものから技術的なものまでメモの雑記

MySQL、MariaDB でよく使うコマンドメモ

技術メモ

パーティション系

パーティション化したテーブルの情報を見たい

  • コマンド
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME =  '<テーブル名>';
  • 実行例
MariaDB [zabbix]> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME =  'history';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| zabbix       | history    | NULL           |                       NULL |    2029405 |
+--------------+------------+----------------+----------------------------+------------+
1 row in set (0.00 sec)

MariaDB [zabbix]>

↑ パーティショニングしていないテーブルだった。。。
手元にパーティションしたテーブル情報がなかったので、どこかで更新します。

断片化(フラグメント)系

断片化されているサイズ確認

  • コマンド
select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='<テーブル名>';
  • 実行例
MariaDB [zabbix]> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='history';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| zabbix       | history    |   4194304 |    2036794 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

MariaDB [zabbix]>

上記のコマンド結果の data_free に表示されている数値が断片化しているサイズを示す。
断片化のサイズが大きいと、使用している実サイズの中に利用できていない無駄な領域がたくさんあることを示している。

断片化を解消したい

  • コマンド
alter table <テーブル名> engine innodb, LOCK=NONE;

LOCK=NONE はDDL中にロックせずに並列DMLを可能にすることを指定する。
このオプションをつけてコマンド実行し、LOCK しないことができない場合(LOCKしないといけない場合)にはエラーになる(らしい)。

  • 実行例
MariaDB [zabbix]> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='history';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| zabbix       | history    |   4194304 |    2028627 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

MariaDB [zabbix]>
MariaDB [zabbix]> alter table history engine innodb, LOCK=NONE;
Query OK, 0 rows affected (2 min 25.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [zabbix]>
MariaDB [zabbix]>
MariaDB [zabbix]> select table_schema, table_name, data_free, table_rows from information_schema.tables where table_name='history';
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| zabbix       | history    |         0 |    2029270 |
+--------------+------------+-----------+------------+
1 row in set (0.00 sec)

MariaDB [zabbix]>

用語

InnoDB

いくつかの情報から、以下のことがわかった。

  • InnoDB は、高い信頼性と高いパフォーマンスとのバランスをとる汎用のストレージエンジン
  • MySQL5.5からデフォルトのストレージエンジンとなっており、MyISAM と違いトランザクションが利用可能
  • MySQLとMariaDBのためのデータベースエンジン

MySQL、MariaDB を利用する上では標準のエンジンと理解。

DDL (Data Definition Language)

DDL はデータ定義言語と呼ばれ、データベース内の表、ビューやインデックスなどの各種オブジェクトの作成や変更をするためのSQL文のこと。