HAYASHIER.COM - Private Page
MySQL, PostgreSQL トラブルシューティング コマンド実行例 まとめ

トラブルシューティング

MySQL

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW GLOBAL VARIABLES
SHOW (SESSION) VARIABLES
SHOW ENGINE INNODB STATUS\G;
SHOW FULL PROCESSLIST \G;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id DESC LIMIT 10\G;
SHOW SLAVE HOSTS;
SHOW MASTER STATUS \G;
SHOW SLAVE STATUS \G;
SHOW INDEX FROM mytable;
SHOW TABLE STATUS LIKE 'mytable'\G
SHOW PROFILE;
SELECT user,host FROM mysql.user ORDER BY user,host;
SELECT SLEEP(@@long_query_time+1); 
SHOW GRANTS FOR `[user]`@`[host]`;
SELECT
 table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
 floor(data_length+index_length) as Allsize,
 floor(data_length) as Dsize,
 floor(index_length) as Isize
 FROM information_schema.tables
 WHERE table_schema=database()
 ORDER BY (data_length+index_length) DESC;
SELECT CONCAT(table_schema, '.', table_name),
        CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
        CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
        CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
        CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
        ROUND(index_length / data_length, 2)                                           idxfrac
 FROM   information_schema.TABLES
 ORDER  BY data_length + index_length DESC
 LIMIT  10;

ベンチマーク

SELECT BENCHMARK(1000000, SELECT * FROM users);
SHOW CREATE TABLE mytable

MySQL 8.0未満

SELECT now();
SELECT * FROM information_schema.PROCESSLIST ORDER BY TIME DESC;
SELECT * FROM information_schema.innodb_lock_waits \G
SELECT * FROM information_schema.innodb_trx \G
SELECT * FROM information_schema.innodb_locks\G
SELECT * FROM performance_schema.threads \G
SHOW ENGINE INNODB STATUS \G
$ while true; do \
  LANG=C TZ=UTC date ; \
  mysql --host xxxxxxxxxxxxx.us-east-1.rds.amazonaws.com \
        --port 3306 --user username -ppassword \
        --execute 'SHOW FULL PROCESSLIST \G'; sleep 60; done \
| tee show-full-processlist.`TZ=UTC date +%Y%m%d-%H%M%S`.txt

historyにパスワード等を残したくない場合 .my.cnf.XXXXXを以下の内容で作成してchmod 600を設定。

[client]
host = yourdb.ci52xxxxxxx.us-east-1.rds.amazonaws.com
user = youruser
password = XXXXXXXX
$ while true; do \
  LANG=C TZ=UTC date ; \
  mysql --defaults-file=/home/ec2-user/.my.cnf.caseXXXXX \
        --execute 'SHOW FULL PROCESSLIST \G'; sleep 60; done \
| tee show-full-processlist.`TZ=UTC date +%Y%m%d-%H%M%S`.txt

インポート/エクスポート

$ mysqldump -u root -p -h xxxxxxxxxx.us-east-1.rds.amazonaws.com devdb  > dump.sql
$ cat dump.sql | mysql -u root -p -h xxxxxxxxxx.us-east-1.rds.amazonaws.com proddb

各種メモリサイズ調査

SELECT
    @@key_buffer_size AS KEY_BUFFER_SIZE_Bytes,
    @@query_cache_size AS QUERY_CACHE_SIZE_Bytes,
    @@innodb_buffer_pool_size AS INNODB_BUFFER_POOL_SIZE_Bytes,
    @@innodb_additional_mem_pool_size AS INNODB_ADDITIONAL_MEM_POOL_SIZE_Bytes,
    @@innodb_log_buffer_size AS INNODB_LOG_BUFFER_SIZE_Bytes,
    @@max_heap_table_size AS MAX_HEAP_TABLE_SIZE_Bytes,
    @@read_buffer_size AS READ_BUFFER_SIZE_Bytes,
    @@read_rnd_buffer_size AS READ_RND_BUFFER_SIZE_Bytes,
    @@sort_buffer_size AS SORT_BUFFER_SIZE_Bytes,
    @@join_buffer_size AS JOIN_BUFFER_SIZE_Bytes,
    @@binlog_cache_size AS BINLOG_CACHE_SIZE_Bytes,
    @@thread_stack AS THREAD_STACK_Bytes,
    @@tmp_table_size AS TMP_TABLE_SIZE_Bytes,
    @@max_allowed_packet AS MAX_ALLOWED_PACKET_Bytes,
    @@max_connections AS MAX_CONNECTIONS,
    (
      @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size
    + @@max_allowed_packet
    ) / (1024 * 1024 * 1024) AS PER_THREAD_MEMORY_GB,
    (@@max_connections * (
      @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size
    + @@max_allowed_packet
  )) / (1024 * 1024 * 1024) AS MAX_TOTAL_THREAD_MEMORY_GB,
    (
    @@key_buffer_size
  + @@query_cache_size
  + @@innodb_buffer_pool_size
  + @@innodb_additional_mem_pool_size
  + @@innodb_log_buffer_size
  + @@max_heap_table_size) / (1024 * 1024 * 1024) AS MAX_GLOBAL_MEMORY_GB,
    (
    @@key_buffer_size
  + @@query_cache_size
  + @@innodb_buffer_pool_size
  + @@innodb_additional_mem_pool_size
  + @@innodb_log_buffer_size
  + @@max_heap_table_size

  + @@max_connections * (
      @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size
    + @@max_allowed_packet
  )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB \G;

Others

MySQL

SHOW CREATE TABLE <テーブル名>;

PostgreSQL

$ pg_dump -st <テーブル名> -U <ユーザー名> -h <エンドポイント> <テータベース名>

参考

参考

個人ブログ

サンプルデータ

Others

グローバルやスレッド変数を元にしたメモリ使用量推定用クエリー

https://dev.mysql.com/doc/refman/5.6/ja/memory-use.html

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_memory` $$
CREATE PROCEDURE `my_memory` ()
BEGIN

DECLARE var VARCHAR(100);
DECLARE val VARCHAR(100);
DECLARE done INT;

DECLARE GLOBAL_SUM DOUBLE;
DECLARE PER_THREAD_SUM DOUBLE;
DECLARE MAX_CONN DOUBLE;
DECLARE HEAP_TABLE DOUBLE;
DECLARE TEMP_TABLE DOUBLE;

DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

SET GLOBAL_SUM=0;
SET PER_THREAD_SUM=0;
SET MAX_CONN=0;
SET HEAP_TABLE=0;
SET TEMP_TABLE=0;

OPEN CUR_GBLVAR;

mylp:LOOP
FETCH CUR_GBLVAR INTO var,val;
IF done=1 THEN
LEAVE mylp;
END IF;
IF var in ('key_buffer_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','query_cache_size') THEN

SET GLOBAL_SUM=GLOBAL_SUM+val;
ELSEIF var in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','thread_stack','max_allowed_packet','net_buffer_length') THEN

SET PER_THREAD_SUM=PER_THREAD_SUM+val;
ELSEIF var in ('max_connections') THEN

SET MAX_CONN=val;
ELSEIF var in ('max_heap_table_size') THEN

SET HEAP_TABLE=val;

ELSEIF var in ('tmp_table_size','max_heap_table_size') THEN
SET TEMP_TABLE=if((TEMP_TABLE>val),TEMP_TABLE,val);
END IF;

END LOOP;
CLOSE CUR_GBLVAR;

select Global Buffers as Parameter,CONCAT(GLOBAL_SUM/(1024*1024),' M') as Value union
select Per Thread,CONCAT(PER_THREAD_SUM/(1024*1024),' M') union
select Maximum Connections,MAX_CONN union
select Total Memory Usage,CONCAT((GLOBAL_SUM + (MAX_CONN * PER_THREAD_SUM))/(1024*1024),' M') union
select + Per Heap Table,CONCAT(HEAP_TABLE / (1024*1024),' M') union
select + Per Temp Table,CONCAT(TEMP_TABLE / (1024*1024),' M') ;

END $$
DELIMITER ;

CALL my_memory();