{"id":100,"date":"2016-01-06T21:01:58","date_gmt":"2016-01-06T12:01:58","guid":{"rendered":"http:\/\/tako.nakano.net\/blog\/?p=100"},"modified":"2016-01-06T22:09:05","modified_gmt":"2016-01-06T13:09:05","slug":"myisam-%e3%81%a8-innodb-%e3%81%ae%e6%9b%b4%e6%96%b0%e3%81%ab%e3%82%88%e3%82%8b%e3%83%91%e3%83%95%e3%82%a9%e3%83%bc%e3%83%9e%e3%83%b3%e3%82%b9%e3%81%ae%e9%81%95%e3%81%84","status":"publish","type":"post","link":"https:\/\/tako.nakano.net\/blog\/2016\/01\/myisam-%e3%81%a8-innodb-%e3%81%ae%e6%9b%b4%e6%96%b0%e3%81%ab%e3%82%88%e3%82%8b%e3%83%91%e3%83%95%e3%82%a9%e3%83%bc%e3%83%9e%e3%83%b3%e3%82%b9%e3%81%ae%e9%81%95%e3%81%84\/","title":{"rendered":"MyISAM \u3068 InnoDB \u306e\u66f4\u65b0\u306b\u3088\u308b\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306e\u9055\u3044"},"content":{"rendered":"

\u3068\u3042\u308b\u300120\u4e07 PV\/\u65e5 \u306e WordPress \u30b5\u30a4\u30c8\u306e\u904b\u7528\u3092\u624b\u4f1d\u3063\u3066\u3044\u308b\u306e\u3060\u304c DB \u306e\u4e00\u90e8\u306b MyISAM \u304c\u4f7f\u308f\u308c\u3066\u3044\u308b\u3002\u5177\u4f53\u7684\u306b\u306f\u3001WordPress Popular Posts<\/a>\u00a0\u306e\u30c6\u30fc\u30d6\u30eb\u3067\u3001\u591a\u5206\u4e00\u756a\u8ca0\u8377\u304c\u9ad8\u3044\u306e\u306f\u00a0popularpostssummary \u3060\u3068\u601d\u308f\u308c\u308b\u3002<\/p>\n

\u3088\u304f\u3001\u53c2\u7167\u304c\u591a\u3044\u306a\u3089 MyISAM \u3067\u3001\u66f4\u65b0\u304c\u591a\u3044\u306a\u3089 InnoDB \u3068\u304b\u3044\u3046\u304c\u3001MySQL \u306e slave \u3067\u3001\u3057\u304b\u3082\u3001\u307b\u3068\u3093\u3069 SQL \u30a2\u30af\u30bb\u30b9\u304c\u3053\u306a\u3044\u3088\u3046\u306b\u3057\u3066\u3044\u308b\u30b5\u30fc\u30d0\u306f\u3001InnoDB \u306b\u3057\u305f\u3068\u3053\u308d\u9855\u8457\u306b\u8ca0\u8377\u304c\u5897\u3048\u305f\u3002<\/p>\n

\u30d0\u30c3\u30d5\u30a1\u30d7\u30fc\u30eb\u304c\u5c0f\u3055\u3059\u304e\u308b\u306e\u3060\u308d\u3046\u304b\u2026\uff1f\u5897\u3084\u3057\u3066\u307f\u3066\u3001\u69d8\u5b50\u3092\u898b\u305f\u3044\u3002<\/p>\n

> SHOW STATUS LIKE 'key%';\r\n+------------------------+-----------+\r\n| Variable_name          | Value     |\r\n+------------------------+-----------+\r\n| Key_blocks_not_flushed | 0         |\r\n| Key_blocks_unused      | 13383     |\r\n| Key_blocks_used        | 13396     |\r\n| Key_read_requests      | 262237806 |\r\n| Key_reads              | 143254    |\r\n| Key_write_requests     | 26889447  |\r\n| Key_writes             | 18958314  |\r\n+------------------------+-----------+\r\n\r\nSHOW ENGINE INNODB STATUS;\r\n----------------------\r\nBUFFER POOL AND MEMORY\r\n----------------------\r\nTotal memory allocated 137363456; in additional pool allocated 0\r\nDictionary memory allocated 2367377\r\nBuffer pool size 8191\r\nFree buffers 1\r\nDatabase pages 8113\r\nOld database pages 2974\r\nModified db pages 32\r\nPending reads 0\r\nPending writes: LRU 0, flush list 0, single page 0\r\nPages made young 34678, not young 0\r\n0.00 youngs\/s, 0.00 non-youngs\/s\r\nPages read 18398, created 16387, written 2271625\r\n0.00 reads\/s, 0.00 creates\/s, 10.58 writes\/s\r\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000\r\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s\r\nLRU len: 8113, unzip_LRU len: 0\r\nI\/O sum[493]:cur[0], unzip sum[0]:cur[0]<\/pre>\n
\u3068\u3044\u3046\u611f\u3058\u3067\u3042\u308b\u2026\u3002<\/p>\n
[MySQL] InnoDB\u3092\u7a4d\u6975\u7684\u306b\u4f7f\u308f\u306a\u3044\u30b1\u30fc\u30b9<\/a> \u306b\u3088\u308c\u3070\u300c\u30c7\u30fc\u30bf\uff1e\u30d0\u30c3\u30d5\u30a1\u3000\u306b\u306a\u308b\u3068\u30a2\u30a6\u30c8\uff01\u300d\u3060\u305d\u3046\u3060\u304c\u2026<\/p>\n
mysql> select sum( index_length ) from information_schema.tables where engine = 'myisam';\r\n+---------------------+\r\n| sum( index_length ) |\r\n+---------------------+\r\n| 1301504 |\r\n+---------------------+\r\n1 row in set (3.54 sec)\r\n\r\nmysql> show variables like 'key_buffer_size';\r\n+-----------------+----------+\r\n| Variable_name | Value |\r\n+-----------------+----------+\r\n| key_buffer_size | 16777216 |\r\n+-----------------+----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select sum( data_length ) from information_schema.tables where engine = 'innodb';\r\n+--------------------+\r\n| sum( data_length ) |\r\n+--------------------+\r\n| 290717696 |\r\n+--------------------+\r\n1 row in set (1.80 sec)\r\n\r\nmysql> show variables like 'innodb_buffer_pool_size';\r\n+-------------------------+-----------+\r\n| Variable_name | Value |\r\n+-------------------------+-----------+\r\n| innodb_buffer_pool_size | 134217728 |\r\n+-------------------------+-----------+\r\n1 row in set (0.00 sec)<\/pre>\n
MySQL tuner \u3059\u308b\u3068<\/p>\n
[!!] Table cache hit rate: 15% (400 open \/ 2K opened)\r\n[!!] InnoDB buffer pool \/ data size: 128.0M\/443.1M\r\n[OK] InnoDB Read buffer efficiency: 99.95% (37917204 hits\/ 37937035 total)\r\n[!!] InnoDB Write buffer efficiency: 0.00% (0 hits\/ 1 total)<\/pre>\n
\u304c\u6c17\u306b\u306a\u308b\u6240\u3060\u308d\u3046\u304b\u2026
\ninnodb_buffer_pool_size \u3092 384M \u306b\u3042\u3052\u3066\u3001\u69d8\u5b50\u3092\u898b\u3066\u304b\u3089\u3001table_open_cache \u3092 800 \u304f\u3089\u3044\u306b\u3057\u3066\u307f\u3088\u3046\u3002<\/p>\n
\u3042\u3068\u306f\u3001http:\/\/takahashikzn.root42.jp\/entry\/2015\/02\/08\/212840 \u3068\u00a0innodb_log_file_size \u304b\u3002<\/p>\n
\u30ed\u30b0\u30d5\u30a1\u30a4\u30eb\u306e\u66f4\u65b0\u304c\u7d50\u69cb\u983b\u7e41\u2026\u306a\u306e\u3067
\nhttp:\/\/d.hatena.ne.jp\/koziy\/20070911\/1189472682
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.6\/ja\/innodb-data-log-reconfiguration.html
\n\u306b\u5f93\u3063\u3066\u00a0innodb_log_file_size \u3092\u5909\u3048\u308b\u3002256M \u306b\u3057\u3066\u307f\u3088\u3046\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u3068\u3042\u308b\u300120\u4e07 PV\/\u65e5 \u306e WordPress \u30b5\u30a4\u30c8\u306e\u904b\u7528\u3092\u624b\u4f1d\u3063\u3066\u3044\u308b\u306e\u3060\u304c DB \u306e\u4e00\u90e8\u306b MyISAM \u304c\u4f7f\u308f\u308c\u3066\u3044\u308b\u3002\u5177\u4f53\u7684\u306b\u306f\u3001WordPress Popular Posts\u00a0\u306e\u30c6\u30fc\u30d6\u30eb\u3067\u3001\u591a\u5206\u4e00\u756a\u8ca0\u8377\u304c\u9ad8 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[3],"tags":[],"class_list":["post-100","post","type-post","status-publish","format-standard","hentry","category-misc"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4dIdP-1C","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/posts\/100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/comments?post=100"}],"version-history":[{"count":5,"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/posts\/100\/revisions"}],"predecessor-version":[{"id":105,"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/posts\/100\/revisions\/105"}],"wp:attachment":[{"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/media?parent=100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/categories?post=100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tako.nakano.net\/blog\/wp-json\/wp\/v2\/tags?post=100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}