MySQLのチューニングやること

参考リンク

nippondanji.blogspot.jp

thinkit.co.jp

yakst.com

基本指針

具体的な方法

1. バッファサイズを適切に設定する

  • innodb_buffer_pool_size・・・InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。余談だが、実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要だ。

MySQLの推奨の70%で設定してみる。

  • key_buffer_size・・・MyISAMだけを利用する場合は、空きメモリの3割程度を割り当てるといい。残りはファイルシステムのキャッシュ用に残しておこう。
  • sort_buffer_size・・・ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。
  • read_buffer_size・・・全件スキャンをするときに利用するバッファ。OLTPでは128K〜512Kぐらいを割り当てると良い。
  • read_rnd_buffer_size・・・ソート処理でインデックスを利用する場合に利用するバッファ。OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。 http://nippondanji.blogspot.jp/2009/02/mysql10.html

https://yakst.com/ja/posts/61

パラメータの数値は以下を参考にしました。

http://sawara.me/mysql/1428/

/etc/my.cnf/

innodb_buffer_pool_size = 512M
innodb_log_file_size=64M

innodb_additional_mem_pool_size = 5M

key_buffer_size = 64M
join_buffer_size = 256K
read_buffer_size = 256K
max_allowed_packet = 2M
read_rnd_buffer_size = 1M
sort_buffer_size = 1M
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1
max_connections = 100 # default
max_heap_table_size = 4M
tmp_table_size = 4M

2. indexを適切に貼る

“適切に"というところも大事。全てにindexを貼ってしまうと、

3. テーブルの最適化

できるだけカラムサイズが小さくなるデータタイプを選択すること

ex. 数値を保存するときにVARCHARなどは使わないように!!INTやBIGINTの方がデータサイズはずっと小さくなる!

その他

メモリの消費量が大きいなと思ったとき

table_definition_cache を400くらいに設定しておくとかなりメモリの消費量を抑えることができます。

設定していたときのEC2サーバーで、ここまでのチューニングでMEM%が30%前後だったのが、20%前後で落ち着くようになりました。

blog.ybbo.net

MySQL :: MySQL 5.6 Reference Manual :: 5.1.5 Server System Variables

Macでステータスバーがフリーズしたときの解決方法

最近、うちのMacBookProでステータスバーが固まることがよくある。

いきなり固まることもあれば、Wi-Fiのon/offで固まることがあったり。

時計が進まなくなったり、スクリーンショットが使えなくなったり、半角/全角切り替えができなくなったりなどSystem周りで不便なので調べてみたら、いい方法が。

SystemUIServerのプロセスを再起動してあげれば直ります。

$ kill -9 `ps aux | grep 'SystemUIServer' | grep -v 'grep' | awk '{ print $2 }'`

以下の記事を参考にさせていただきました!

Mac OSX Yosemiteにしてから、メニューバーがよく固まる - かれ4

通知バーが動かない時

追記@20170529

ついでに、通知バーが動かないときは以下のコマンドで再起動できます。

$ killall NotificationCenter 

参考: How to Restart Notification Center in Mac OS X

aliasはっちゃおう

追記@20170731

# .zshrc
# Restart SystemUIServer
alias killsus="kill -9 `ps aux | grep 'SystemUIServer' | grep -v 'grep' | awk '{ print $2 }'`"

dotfiles/aliases.zsh at master · totzyuta/dotfiles · GitHub

その他参考

d.hatena.ne.jp

Wordpressの記事データをRailsのDBに移行する

目次

おおざっぱな流れ

大体の流れはこんな感じ。

  1. SQLでいじくっていい感じのCSVを作成
  2. mysqlでimportする

移行するデータ

以下のデータをWordpressからRailsのアプリケーションに移行します。

  • 記事データ
  • カテゴリのデータ
  • カテゴリと記事の関連付けデータ
  • タグのデータ
  • タグと記事の関連付けデータ
  • Primary Categoryの関連付けデータ
  • 画像データ
  • サムネイル画像と記事の関連付けデータ

1. CSVを抽出

ということで各データの抽出時に気をつけたことなど。

記事データ

wp_postsCSVで抽出。

この際、revisionやattachmentが混ざらないようにpost_typepostであるもののみを抽出してexport。

SQLは例えばこんな感じ。

SELECT ID AS id, post_title AS title, post_content AS body_source, post_date AS published_at, post_status AS status
FROM  `wp_posts` 
WHERE post_type =  "post" AND post_status="publish";

カテゴリ

wp_termsをexportします。wp_termsはカテゴリとタグどちらも扱うテーブルなので、そこに注意します。

wp_termsの中身がなんなのか、という関係性は wp_term_taxonomy というテーブルで管理されてます。

JOINしてwp_term_taxonomy.taxonomyがcategoryのものだけとってきます。

SELECT wp_terms.term_id AS id, wp_terms.name AS name, wp_terms.slug AS name_en
FROM wp_terms
JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'category';

wp_term_relationshipswp_termswp_postsの関連付けをしているのでそちらも。railsアプリの方のdb schemaに合わせてSQLをつくります。

SELECT object_id AS article_id, term_taxonomy_id AS category_id
FROM  `wp_term_relationships`;

タグ

タグは、カテゴリの移行とほぼSQLになります。

JOINしてwp_term_taxonomy.taxonomypost_tag のものだけとってきます。

SELECT wp_terms.term_id AS id, wp_terms.name AS name
FROM wp_terms
JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'post_tag';
SELECT object_id AS article_id, term_taxonomy_id AS tag_id
FROM  `wp_term_relationships`;

Primary Category

ついでに、yoastというプラグインでPrimary Categoryというものを設定していたので、そちらもimportします。

primary categoryの管理方法は以下の質問が参考になりました。

php - How to get primary category from DB tables in wordpress? - Stack Overflow

以下のようにすれば、記事のIDとprimary categoryのidだけを取り出すことができます。

SELECT post_id AS id, meta_value AS primary_category_id 
FROM `wp_postmeta` 
WHERE meta_key="_yoast_wpseo_primary_category";

画像データ

画像のデータは、これまでにuploadされたものに関してはWordpressでuploadしたURLが挿入されるので、そのURLの整合性を保てるように移動します。

具体的には、 /wp-content/uploads/ ディレクトリをそのままRailsアプリの public に移します。

そうすると、例えば以下のようなURLの画像が、そのまま整合性を保ったまま新しいアプリケーションでも使用することができます。

http://my-media.com/wp-content/uploads/2016/08/image-sample.jpg

サムネイル

サムネイル画像は、後々そのままWordpressでuploadしたURLが挿入することを考慮して、そのまま画像のデータをURLの整合性を保てるようにRailsのpublicディレクトリの静的ファイルに移行します。

ということで、記事に対応するサムネイルのURLをstringで取り出すことを目指します。

Wordpressfeatured_imageの構造がちょっとややこしかったです。以下が分かりやすかった。

Where is the post featured image link stored in the WordPress database? - Stack Overflow

The featured image ID is stored in wp_postmeta with a meta_key called _thumbnail_id. The actual thumbnail link is then contained in wp_posts with a post_type of attachment.

ということで、wp_postmetameta_key_thumbnail_idのものだけを抽出すればよさそうですね。

その中のpost_idmeta_valueが、記事とサムネイルのrelationになってます。(サムネイルの画像はwp_postsに、post_typeattachmentのデータとして保存されています。)

僕の場合は以下のような感じでwp_posts内の記事とサムネイルだけのrelationを取り出しました。

SELECT wp_postmeta_relation.ID AS id, wp_posts.guid AS featured_image
FROM (
    SELECT ID, meta_value
    FROM wp_posts AS wp_posts1
    JOIN wp_postmeta ON wp_posts1.ID = wp_postmeta.post_id
    WHERE wp_postmeta.meta_key =  "_thumbnail_id"
) AS wp_postmeta_relation
JOIN wp_posts ON wp_postmeta_relation.meta_value = wp_posts.ID;

ということで、これでほしいCSVファイルを全てdumpできました。

2. mysqlでimportする

あとはこれらをMySQLでimportすれば作業完了です。

【MySQL】CSVファイルをインポートするコマンド - Qiita

以下のようなエラーが出たときは、

The used command is not allowed with this MySQL version

下のとこを参考にしてみてください。

MySQL: Enable LOAD DATA LOCAL INFILE - Stack Overflow

記事データ

mysql> LOAD DATA LOCAL INFILE '~/wp_posts.csv' INTO TABLE articles FIELDS TERMINATED BY ',' ENCLOSED BY '"' (id, title, body_source, published_at, status);

カテゴリデータ

mysql> LOAD DATA LOCAL INFILE '~/wp_terms_categories.csv' INTO TABLE categories FIELDS TERMINATED BY ',' ENCLOSED BY '"' (id, name, name_en);

記事とカテゴリの関連付けデータ

mysql> LOAD DATA LOCAL INFILE '~/wp_term_category_relationships.csv' INTO TABLE category_relations FIELDS TERMINATED BY ',' ENCLOSED BY '"' (article_id, category_id);

タグデータ

mysql> LOAD DATA LOCAL INFILE '~/wp_terms_tags.csv' INTO TABLE tags FIELDS TERMINATED BY ',' ENCLOSED BY '"' (id, name);

記事とタグの関連付けデータ

mysql> LOAD DATA LOCAL INFILE '~/wp_term_tag_relationships.csv' INTO TABLE tag_relations FIELDS TERMINATED BY ',' ENCLOSED BY '"' (article_id, tag_id);

Primary Category

Tagのようにひとつの記事に対して複数のCategoryを設定できるようになっていて、パンくずなどにも用いるためのprimary categoryも同時に設定できるようにしています。

こちらは、articlesのテーブルにprimary_category_idというカラムがあるみたいな構造なので、一度temporaryのtableをcreateして、そちらを用いてarticlesのレコードをupdateしていきます。

mysql> CREATE TABLE temp_table (article_id int(10), primary_category_id int(10));

つくったtableにimportします。

mysql> LOAD DATA LOCAL INFILE '~/wp_postmeta_primary_category.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' (article_id, primary_category_id);

INNER JOINを使ってUPDATE文を書きます。

mysql> UPDATE articles
INNER JOIN temp_table ON temp_table.article_id = articles.id
SET articles.primary_category_id = temp_table.primary_category_id;

そしたらさきほどのtableを削除します。

mysql> DROP TABLE temp_table;

参考:

mysql - Import CSV to Update rows in table - Stack Overflow

サムネイル画像

こちらもPrimary Categoryと同じような方法で移行していきます。

mysql> CREATE TABLE temp_table (article_id int(10), featured_image varchar(255));

つくったテーブルにimportします。

mysql> LOAD DATA LOCAL INFILE '~/wp_featured_images.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' (article_id, featured_image);

INNER JOINを使ってUPDATE文を書きます。

mysql> UPDATE articles
INNER JOIN temp_table ON temp_table.article_id = articles.id
SET articles.featured_image = temp_table.featured_image;

そしたらさきほどのtableを削除します。

mysql> DROP TABLE temp_table;

参考サイト

データベース構造 - WordPress Codex 日本語版

データベースの基本構造とWordPressのテーブル設計に見るデータモデリング | 株式会社LIG