カテゴリー: SQL

MySQL SHOWによる解析とチューニング

2019年4月2日2019年4月12日 | by 優 | にコメントを残す

MySQLのチューニング調査依頼で時短で終わるようにバージョン CentOS7 MySQL5.6 DB, テーブルのDBエンジンの確認データベースの確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| exampledb       |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| exampledb |

+--------------------+

4 rows in set (0.00 sec)

デフォルトのエンジンの確認

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

…

の続きを読む

MySQL, RDB, チューニング

コンシューマ世界でのJOIN禁止、固定長ルールのまとめ私 JOIN禁止の世界は知らない(1テーブルに1億以上のレコードを扱ったことがない) 原則として正規化できるものは正規化してJOINを行い、正規化する必要のないものは正規化しないスタンス 1テーブルはどんなに大きくても20カラム限界がセオリー →正規化できるのではないか、テーブルをわける 1テーブルが大きく正規化されずに開発されたサービスの保 …

の続きを読む

MySQL, SQL, チューニング

SQL インデックスが効かない検索

2018年1月25日2019年1月25日 | by 優 | にコメントを残す

基本戦略テーブルを正規化する JOIN(INNER JOIN)を有効に活用する非正規化でテーブルを巨大にしないすべてのテーブルを正規化することは出来ない出来るテーブルのみ正規化する EXPLAINで計測する、INDEXが有効に動いているか確認するインデックスが効かない検索 LIKE INDEXが効く！完全一致 INDEXが効いて高 …

の続きを読む

SQL

Software Design SQL50本ノックやってみた。

2017年12月11日 | by 優 | にコメントを残す

環境構築編購入の価値ありです。 SQL1. SELECT

postgres=# SELECT * FROM payment;

 payment_id | customer_id | staff_id | rental_id | amount |        payment_date
------------+-------------+----------+-----------+--------+----------------------------
      16050 |         269 |        2 |         7 |   1.99 | 2007-01-24 21:40:19.996577
      16051 |         269 |        1 |        98 |   0.99 | 2007-01-25 15:16:50.996577
      16052 |         269 |        2 |       678 |   6.99 | 2007-01-28 21:44:14.996577
      16053 |         269 |        2 |       703 |   0.99 | 2007-01-29 00:58:02.996577
      16054 |         269 |        1 |       750 |   4.99 | 2007-01-29 08:10:06.996577
      16055 |         269 |        2 |      1099 |   2.99 | 2007-01-31 12:23:14.996577
      16056 |         270 |        1 |       193 |   1.99 | 2007-01-26 05:10:14.996577
      16057 |         270 |        1 |      1040 |   4.99 | 2007-01-31 04:03:42.996577
      16058 |         271 |        1 |      1096 |   8.99 | 2007-01-31 11:59:15.996577
      16059 |         272 |        1 |        33 |   0.99 | 2007-01-25 02:47:17.996577


(略)




postgres=# SELECT * FROM customer;

 customer_id | store_id | first_name  |  last_name   |                  email                   | address_id | activebool | create_date |     last_update     | active
-------------+----------+-------------+--------------+------------------------------------------+------------+------------+-------------+---------------------+--------
           1 |        1 | MARY        | SMITH        | MARY.SMITH@sakilacustomer.org            |          5 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           2 |        1 | PATRICIA    | JOHNSON      | PATRICIA.JOHNSON@sakilacustomer.org      |          6 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           3 |        1 | LINDA       | WILLIAMS     | LINDA.WILLIAMS@sakilacustomer.org        |          7 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           4 |        2 | BARBARA     | JONES        | BARBARA.JONES@sakilacustomer.org         |          8 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           5 |        1 | ELIZABETH   | BROWN        | ELIZABETH.BROWN@sakilacustomer.org       |          9 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           6 |        2 | JENNIFER    | DAVIS        | JENNIFER.DAVIS@sakilacustomer.org        |         10 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           7 |        1 | MARIA       | MILLER       | MARIA.MILLER@sakilacustomer.org          |         11 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           8 |        2 | SUSAN       | WILSON       | SUSAN.WILSON@sakilacustomer.org          |         12 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
           9 |        2 | MARGARET    | MOORE        | MARGARET.MOORE@sakilacustomer.org        |         13 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1
          10 |        1 | DOROTHY     | TAYLOR       | DOROTHY.TAYLOR@sakilacustomer.org        |         14 | t          | 2006-02-14  | 2006-02-15 09:57:20 |      1

(略)

postgres=# SELECT * FROM payment;

------------+-------------+----------+-----------+--------+----------------------------

16050 | 269 | 2 | 7 | 1.99 | 2007-01-24 21:40:19.996577

16051 | 269 | 1 | 98 | 0.99 | 2007-01-25 15:16:50.996577

16052 | 269 | 2 | 678 | 6.99 | 2007-01-28 21:44:14.996577

16053 | 269 | 2 | 703 | 0.99 | 2007-01-29 00:58:02.996577

16054 | 269 | 1 | 750 | 4.99 | 2007-01-29 08:10:06.996577

16055 | 269 | 2 | 1099 | 2.99 | 2007-01-31 12:23:14.996577

16056 | 270 | 1 | 193 | 1.99 | 2007-01-26 05:10:14.996577

16057 | 270 | 1 | 1040 | 4.99 | 2007-01-31 04:03:42.996577

16058 | 271 | 1 | 1096 | 8.99 | 2007-01-31 11:59:15.996577

16059 | 272 | 1 | 33 | 0.99 | 2007-01-25 02:47:17.996577

(略)

postgres=# SELECT * FROM customer;

-------------+----------+-------------+--------------+------------------------------------------+------------+------------+-------------+---------------------+--------

1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1

(略)

SQL2. 条件を指定する WHERE

SELECT
  payment_id,
  customer_id
FROM
  payment
WHERE
  customer_id = 1;


 payment_id | customer_id
------------+-------------
      16677 |           1
      16678 |           1
      18495 |           1
      18496 |           1
      18497 |           1
      18498 |           1
      18499 |           1
      18500 |           1
      18501 |           1
      22680 |           1


(略)

SELECT

payment_id,

customer_id

FROM

payment

WHERE

customer_id = 1;

payment_id | customer_id

------------+-------------

16677 | 1

16678 | 1

18495 | 1

18496 | 1

18497 | 1

18498 | 1

18499 | 1

18500 | 1

18501 | 1

22680 | 1

(略)

SQL3. cutomerテーブルの名前(first_n …

の続きを読む

MySQL, SQL, トラブルシューティング

MySQL デッドロック対処

2017年12月4日2019年1月21日 | by 優 | にコメントを残す

MySQLのデッドロック対処おまけでギャップロック寄稿しました。今回はトランザクションでよくありがちなデッドロックのご紹介。たすきがけのデッドロックよくデッドロックはなんぞや？といった時に提示されるパターンです。

CREATE TABLE a_table(
	a_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a_table(a_id, point, value) VALUES
	(1, 100, "a");
	



CREATE TABLE b_table(
	b_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO b_table(b_id, point, value) VALUES
	(1, 100, "a");

CREATE TABLE a_table(

a_id INT UNSIGNED PRIMARY KEY,

point INT UNSIGNED,

value VARCHAR(255)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a_table(a_id, point, value) VALUES

(1, 100, "a");

CREATE TABLE b_table(

b_id INT UNSIGNED PRIMARY KEY,

point INT UNSIGNED,

value VARCHAR(255)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO b_table(b_id, point, value) VALUES

(1, 100, "a");

…

の続きを読む

MySQL, Linux

Percona XtraBackup ロールフォワード

2017年12月2日2017年12月3日 | by 優 | にコメントを残す

Percona XtraBackup mysqldumpリストア時間比較おまけでロールフォワード寄稿しました。どっちを使う？ XtraBackup レプリケーションが行われているシステム。 WEBサービス。1秒も止められないようなもの。1秒で利益や損失が発生するサービス。 XtraBackup, mysqldumpの併用しておく。 mysqldump 業務アプリ。深夜や早朝、お …

の続きを読む

SQL, チューニング

SQLパフォーマンス要約なぜ、SQLは重たくなるのか？──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策

2017年11月29日2017年12月2日 | by 優 | にコメントを残す

自分用に要約メモ。詳しい記事は引用先をみてくださいな。 @see なぜ、SQLは重たくなるのか？ —『SQLパフォーマンス詳解』の翻訳者が教える原因と対策 ORMが自動生成するSQLを確認する通常はSELECT+JOIN句で処理できるSQL対して、N+1問題が発生していないか。 10記事取得するSQLで、記事取得で終わるSQL一発が、記事取得1+各記事 …

の続きを読む

MySQL, チューニング, Linux

MySQL 5.6 ファイルディスクリプタカーネルチューニング CentOS7

2017年10月26日2019年1月25日 | by 優 | にコメントを残す

MySQL5.6 ファイルディスクリプタチューニング CentOS7 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 10140) [Warning] Buffered warning: Changed limits: max_con …

の続きを読む

MySQL

MySQL5.6.22以下注意 SET GLOBAL sql_log_bin

2017年10月25日 | by 優 | にコメントを残す

MySQLのレプリケーション環境を運用していると、バイナリログへの記録を一時的に止めるためにsql_log_bin=0を実行したことがあるだろう。これを「set GLOBAL sql_log_bin=0」と、グローバル変数への変更として実行すると大変なことになる、という指摘。TwitterのMySQLエンジニア、Jeremy Cole氏の提言。 MySQL 5.5.41、5.6.22 …

の続きを読む

MySQL, SQL

SQL 置換 REPLACE

2017年10月21日 | by 優 | にコメントを残す

値を置換します。

UPDATE テーブル名 SET カラム名 = REPLACE(カラム名, '値old', '値new');

1	UPDATE テーブル名 SET カラム名 = REPLACE(カラム名, '値old', '値new');

万単位のレコード置換は本当に気持ちが良いもの。このクエリ大好き。使う機会を震えて待ってます（*’∀’人）

の続きを読む

MySQL

MySQL レプリケーション監視スクリプト

2017年9月1日2017年10月4日 | by 優 | にコメントを残す

レプリケーション監視スクリプト replication_check.sh

#!/bin/sh

#PASSWORD="パスワードを入力"
EMAIL=hoge@example.net
DOMAIN=crient.com
CRIENT="もげ株式会社"

#eval  "`mysql -u root -p${PASSWORD} -e 'show slave status \G' | sed -ne 's/: \(.*\)/="\1"/p' `"
eval  "`mysql --defaults-extra-file=/root/.my.cnf -e 'show slave status \G' | sed -ne 's/: \(.*\)/="\1"/p' `"
if [ "$?${Slave_IO_Running}${Slave_SQL_Running}" != "0YesYes" ]; then
echo "show slave status\G" | mysql --defaults-extra-file=/root/.my.cnf | mail -s "レプリケーションエラー:${DOMAIN}:${CRIENT}:"`hostname` ${EMAIL}
exit 1
fi

#!/bin/sh

#PASSWORD="パスワードを入力"

EMAIL=hoge@example.net

DOMAIN=crient.com

CRIENT="もげ株式会社"

#eval "`mysql -u root -p${PASSWORD} -e 'show slave status \G' | sed -ne 's/: $.*$/="\1"/p' `"

eval "`mysql --defaults-extra-file=/root/.my.cnf -e 'show slave status \G' | sed -ne 's/: $.*$/="\1"/p' `"

if [ "$?${Slave_IO_Running}${Slave_SQL_Running}" != "0YesYes" ]; then

echo "show slave status\G" | mysql --defaults-extra-file=/root/.my.cnf | mail -s "レプリケーションエラー:${DOMAIN}:${CRIENT}:"`hostname` ${EMAIL}

exit 1

# vi /etc/crontab

#レプリケーションチェック
*/1 * * * * root sh /root/replication_check.sh

# vi /etc/crontab

#レプリケーションチェック

*/1 * * * * root sh /root/replication_check.sh

の続きを読む

MySQL

【Munin】 MySQL InnoDB free tablespace UNKNOWNs: Bytes free is unknown.

2017年9月1日2017年10月10日 | by 優 | にコメントを残す

Munin-notification for net::example.net

net :: example.net :: MySQL InnoDB free tablespace
	UNKNOWNs: Bytes free is unknown.

Munin-notification for net::example.net

net :: example.net :: MySQL InnoDB free tablespace

UNKNOWNs: Bytes free is unknown.

# /etc/my.cnf

自動拡張のサイズ
innodb_autoextend_increment     = 64      ##InnoDBテーブルスペースファイル 自動拡大テーブルスペースがいっぱいになった時の 自動増分サイズ

# /etc/my.cnf

自動拡張のサイズ

innodb_autoextend_increment = 64 ##InnoDBテーブルスペースファイル自動拡大テーブルスペースがいっぱいになった時の自動増分サイズ

mysql> show global variables like '%innodb_autoextend_increment%';

+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64    |
+-----------------------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like '%innodb_autoextend_increment%';

+-----------------------------+-------+

| Variable_name | Value |

+-----------------------------+-------+

| innodb_autoextend_increment | 64 |

+-----------------------------+-------+

1 row in set (0.00 sec)

64になっている。また、1000は最大値 ※Valueの数字の後にMをつけてしまうと、1000になります； [cra …

の続きを読む

優技録

カテゴリー: SQL

MySQL SHOWによる解析とチューニング

コンシューマでJOIN禁止, 固定長DBまとめ

SQL インデックスが効かない検索

Software Design SQL50本ノックやってみた。

MySQL デッドロック対処

Percona XtraBackup ロールフォワード

SQLパフォーマンス要約なぜ、SQLは重たくなるのか？──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策

MySQL 5.6 ファイルディスクリプタカーネルチューニング CentOS7

MySQL5.6.22以下注意 SET GLOBAL sql_log_bin

SQL 置換 REPLACE

MySQL レプリケーション監視スクリプト

【Munin】 MySQL InnoDB free tablespace UNKNOWNs: Bytes free is unknown.