実行計画の確認。
EXPLAIN SELECT * FROM Country WHERE ...;
innodb でも DELETE でデータが歯抜け状態になるので、削除が多いやつは定期的にやるとよろしいらしい。
# SHOW TABLE STATUS で状態確認してからやってね
> OPTIMIZE TABLE users;
あと統計情報は innodb の場合殆ど気にsなくていいらしいが、大きくデータのカーディナリティが変化したりすると狂うので以下で更新してやるとよい。
> ANALYZE TABLE users;
SQL_NO_CACHE を使用したクエリの実行が最初の実行で遅い
クエリキャッシュが唯一のキャッシュではありません。...InnoDBバッファープールには、頻繁にアクセスされるインデックスとデータが含まれます。最初のクエリの後、一部のインデックスとデータはキャッシュされるため、次回はそれらがメモリから読み取られます。おそらくいくつかのデータ/インデックスページはクエリごとに1回しかアクセスされません。
最初に実行するときにクエリを高速化する方法はありません。ただし、バッファプールが十分に大きい場合、クエリは常に高速になります。MySQLのパフォーマンスには、大きなバッファプールが非常に重要であることに注意してください。一般的な推奨事項は、総メモリの75〜80%を維持することです。
https://dev.mysql.com/doc/refman/5.6/ja/query-cache-configuration.html
https://qiita.com/mamy1326/items/d1548d8cf4528277172a
https://qiita.com/tukiyo3/items/797f9916e6494ec33991
クエリキャッシュ機能は 5.7 で非推奨 ( デフォルト OFF ) 、8 から削除されたらしいよ。スケールしづらいという理由。
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES | --キャッシュクエリを使用可能か
| query_cache_limit | 1048576 | --クエリキャッシュ最大サイズ(1MB)
| query_cache_size | 33554432 | --クエリキャッシュ領域。(32MB)
| query_cache_type | ON | --クエリをキャッシュする(SELECT SQL_NO_CACHE以外)
| query_cache_wlock_invalidate | OFF | --書込みロック獲得時に、ロックしたテーブルに関するクエリキャッシュを無効にするか
+------------------------------+----------+
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 2108 | --空きメモリーブロックの数。起動時は1。1だと良い。それ以上だと断片化しているためFLUSH QUERY CACHEにてキャッシュのデフラグも検討。
| Qcache_free_memory | 6830336 | --クエリーキャッシュ用の空きメモリーの量
| Qcache_hits | 3810442057 | --クエリーキャッシュヒットの数。
| Qcache_inserts | 397612602 | --クエリーキャッシュに追加されるクエリーの数
| Qcache_lowmem_prunes | 307230686 | --メモリーが少ないためクエリーキャッシュから削除されたクエリーの数
| Qcache_not_cached | 151054542 | --非キャッシュクエリーの数 (キャッシュできないか、query_cache_type 設定のためキャッシュされない)
| Qcache_queries_in_cache | 8044 | --クエリーキャッシュ内に登録されたクエリーの数
| Qcache_total_blocks | 26009 | --クエリーキャッシュ内のブロックの合計数
# InnoDB の場合 ...
mysql> SHOW VARIABLES LIKE "innodb_%_size";
+---------------------------------+---------+
| Variable_name | Value |
+---------------------------------+---------+
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
+---------------------------------+---------+
# 設定ファイル変更
$ vi /etc/my.cnf
> innodb_buffer_pool_size = 256M # 8 MB からぶちあげ
> innodb_log_file_size = 64M # 上記の 25% 程度が適当らしい
# 再起動
$ service mysqld restart
デフォルトでは 3306 です。
ソケット sock
や一時ディレクトリ/ログ系も /var/lib/mysql
から移動させるかどうかは割とケースバイケースぽい ( 参考 ) 。ルートボリュームでイケるならそのままでもイイケド、別ストレージが SSD で高速 & ルートボリュームは HDD で低速なときはまるっとお引越ししたほうがいいね。
$ sodo su
$ service mysqld stop # 一度 mysqld を立ち上げてから落とすこと
$ vim /etc/my.cnf
> datadir=/export/mysql
$ cp -pRf /var/lib/mysql/* /export/mysql
$ service mysqld start
# Set mysql data storage.
sudo service mysqld stop
sudo mkdir -p /export
sudo chmod -R 755 /export
sudo mkdir -p /export/mysql
sudo chmod -R 755 /export/mysql
sudo chown -R mysql:mysql /export/mysql
sudo cp -pRf /var/lib/mysql/* /export/mysql
sudo sed -i -e "s|datadir=/var/lib/mysql|datadir=/export/mysql|" /etc/my.cnf
sudo service mysqld start
less /var/log/mysqld.log
アプリ側でデフォルト値ないカラムの指定しないままSQL分投げてない?MySQL5.5以前と5.6以降で sql_mode
が異なることが原因らしい。とりあえず処置として sql_mode
の STRICT_TRANS_TABLES
をはずせば警告文にとどまりエラーにならんので /etc/my.cnf
の sql_mode
を "" とかにして再起動。
[ utf8mb4_general_ci ] あたりが無難
設定がUTCのときはそのまま利用し、出力時に変換(+9:00)するのがよい? ref: http://tmtms.hatenablog.com/entry/2015/08/22/mysql-timezone
セーフモードで入れ。https://goo.gl/qoGcNv
http://qiita.com/tayasu/items/c5ddfc481d6b7cd8866d
予約語とハイフンつなぎ単語のエスケープで利用。通常のSQLでは不要(DBスキーマ扱いになってしまう)。
大体 /etc/my.cnf
にいるけどインストールバッチ(mysql_install_db.shとか)使うとMySQL本体ディレクトリにDB毎のが自動生成されてそっちが優先されちゃう面倒。
http://www.slideshare.net/takumamiura3/ss-44633934
$ mysql -u${DB_USER} -p${DB_PW} -D${DB_NAME} -N -e 'show tables' | xargs -IARG mysql -u${DB_USER} -p${DB_PW} -e 'truncate table ARG' -D${DB_NAME}
未検証。
SET FOREIGN_KEY_CHECKS = 0;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
FROM information_schema.tables
WHERE table_schema = 'database_name'; -- specify DB name here.
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;