i-generation

注意事項¶

http://nippondanji.blogspot.jp/2009/02/myisaminnodb.html
・消費するディスクスペースはMyISAMの２〜３倍
・更新処理でMyISAMとInnoDBを混在させない→要検討

my.cnf¶

現在の設定

innodb_buffer_pool_size	8388608
key_buffer_size	268435456

skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
##query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connections = 512
default-character-set = sjis
#slave-skip-errors=1062
#skip-character-set-client-handshake

改修後

innodb_buffer_pool_size = メモリの70%
key_buffer_size = 128M

MyISAM→InnoDB変更¶

mysql> alter table click_log_201412 ENGINE=InnoDB;
Query OK, 932650 rows affected (5 min 14.38 sec)
Records: 932650 Duplicates: 0 Warnings: 0

click_log_YYYYMM
5分 x 12ヶ月 x 5年 = 5時間

action_log_YYYYMM
access_action_log_YYYYMM
stat_log_YYYYMM

ディスク使用量¶

[root@db3 admin]# ll h /var/lib/mysql/
合計 2.0G
drwx----- 2 mysql mysql 128K 11月 1 00:00 aff
drwx------ 2 mysql mysql 132K 11月 30 17:50 aff_test
rw-rw--- 1 mysql mysql 5.0M 11月 30 17:51 ib_logfile0
rw-rw--- 1 mysql mysql 5.0M 11月 30 17:50 ib_logfile1
rw-rw--- 1 mysql mysql 1.9G 11月 30 17:51 ibdata1
drwx------ 2 mysql mysql 4.0K 1月 22 2015 mysql
srwxrwxrwx 1 mysql mysql 0 2月 2 2015 mysql.sock

ibdata→100万レコードで400MB増加

[root@db2 admin]# du -h /usr/local/mysql/aff/bkup/
232G /usr/local/mysql/aff/bkup/

[root@db2 admin]# df -h
Filesystem サイズ 使用 残り 使用% マウント位置
/dev/sda2 450G 314G 113G 74% /
/dev/sda1 99M 12M 82M 13% /boot
tmpfs 3.9G 0 3.9G 0% /dev/shm

[root@db2 admin]# du -h /var/lib/mysql/aff/
50G /var/lib/mysql/aff/

50G x 3 = 150G

※dumpファイル保存期間を縮小して外部ストレージサーバへ保存

アプリケーション参照ファイル¶

[click_log_YYYYMM]

ad/www/api/list.php: $sql = "select * from click_log_merge where aff_id = '$afid' and c_id = '$c_id' and (status = '0' or status = '3')";
ad/www/cl/app.php: $sql = "select click_log_id from click_log_".$stat_month." $click_log_query";
ad/www/cl/click_asp.php.bk20110615:$table = "click_log_".date("Ym");
ad/www/cl/click_listing.php:$table = "click_log_".date("Ym");
ad/www/ac/resign.php: $sql = "select * from click_log_".$stat_month." where aff_id = '$aff_id' limit 1";
ad/www/ac/action_manual.php: $sql = "select * from click_log_".$stat_month." $click_log_query";
ad/www/ac/action.php: $sql = "select * from click_log_".$stat_month." $click_log_query";

stat/stat.php: $sql_indirect = $sql_indirect."SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_".$stat_month." WHERE c_id = '$c_id' AND subscriber = '$subscriber' AND status = '0' UNION ALL ";
stat/stat_asp.php: $sql_indirect = $sql_indirect."SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_".$stat_month." WHERE c_id = '$c_id' AND subscriber = '$subscriber' AND status = '0' UNION ALL ";
stat/stat_listing.php: $sql_indirect = $sql_indirect."SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_".$stat_month." WHERE c_id = '$c_id' AND subscriber = '$subscriber' AND status = '0' UNION ALL ";

admin/www/stat/click_log_detail.php: $sql .= "select * from click_log_".$stat_month." ".$query;
admin/www/stat/click_log_detail_csv.php: $sql .= "select click_log_id from click_log_".$stat_month." ".$query;
admin/www/stat/listing/report.php: $tsql = "select count() from click_log_". date("Ym")." where listing_id = '$listing_id' and status = 3";
admin/www/stat/asp/report.php:$tsql = "select count() from click_log_". date("Ym")." where asp_id = '$asp_id' and status = 3";
admin/www/stat/media/report.php: $tsql = "select count(*) from click_log_".$i." where m_id = '$m_id' and status = 3";
admin/www/content/search/log_reset.php:$sql = "delete from click_log_merge where c_id = '$c_id'";
admin/www/media_owner/search/rm_db.php: $tsql = "delete from click_log_merge where m_id = '".addslashes($m_id)."'";
admin/www/asp/search/rm_db.php: $sql = "delete from click_log_merge where asp_id = '".addslashes($asp_id)."'";
admin/www/listing/search/rm_db.php: $sql = "delete from click_log_merge where listing_id = '".addslashes($listing_id)."'";
admin/www/media/search/rm_db.php: $tsql = "delete from click_log_merge where m_id = '".addslashes($m_id)."'";
admin/www/client_owner/search/rm_db.php: $tsql = "delete from click_log_merge where c_id = '".addslashes($c_id)."'";

user/www/merchant/referer/index.php: $sql = "select click_log_id,asp_id,referer,user_id,click_cost,action_cost_set,action_ratio_set,click_date,status from click_log_".$stat_month." $query union ";
user/www/merchant/referer/csv.php: $sql .= "select count(*) from click_log_".$stat_month." $query union ";
user/www/merchant/admit/admit_ok.php: $tsql = "select * from click_log_merge where aff_id = '$aff_id_db'";
user/www/merchant/report/userbrows.php: click_log_".$searchStartYM ." tb_c"
user/www/merchant/report/timely.php:$status_click = "click_log_".$searchStartYM .".status";
user/www/merchant/report/userrep.php: $sql = $sql . $select_state . "from click_log_" . $stat_month_array[$i] . $where_state;
user/www/partner/report/user.php: $sql = "select count(click_log_id) from click_log_merge $query";

[action_log_YYYYMM]

ad/www/ac/pointback.php:$sql = "select * from action_log_merge where aff_id = '$aff_id'";
ad/www/ac/resign.php: $sql = "select * from action_log_".$i." where c_id = '$c_id' and aff_id = '$afid' and status != 4 and status != 2 limit 1";
ad/www/ac/action_manual.php: $action_log_table = "action_log_".date("Ym");
ad/www/ac/action_convert.php: $action_log_table = "action_log_".date("Ym");
ad/www/ac/resign_convert_date.php: $sql = "select * from action_log_".$i." where aff_id = '$afid' and status = 1";
ad/www/ac/action_admit.php: $sql = "select * from action_log_merge where c_id = '$c_id' and aff_id = '$afid' and (status = '0' or status = '4')";
ad/www/ac/action.php: $action_log_table = "action_log_".date("Ym");
ad/www/ac/resign_convert.php: $sql = "select * from action_log_".$i." where aff_id = '$afid' and status = 4";
ad/www/cl/click.php: $sql = "select order_date from action_log_".$stat_month." where c_id = '$c_id' and subscriber = '$subscriber'";
ad/www/cl/click_app.php: $sql = "select m_id,agent from action_log_".$stat_month." where aff_id = '$aff_id'";
ad/www/cl/redirect.php: $sql = "select * from action_log_".$stat_month." $action_log_query";
ad/www/cl/click_check.php: $sql = "select order_date from action_log_".$stat_month." where c_id = '$c_id' and subscriber = '$subscriber'";
ad/www/api/list.php: $table = "action_log_".date("Ym");

admin/www/stat/asp_back.php:$table = "action_log_".$action_month;
admin/www/stat/content/csv_resign.php: $table = "action_log_".$stat_month;
admin/www/stat/content/report_day.php: $tsql2 = "select count(action_log_id) from action_log_".$month." where entry_date LIKE '$stat_date%' and c_id = '$c_id' and p_id = '$j'";
admin/www/stat/content/csv_action_admit_db.php: $tsql = "update action_log_".$order_month." set
admin/www/stat/content/csv_resign_db.php: $tsql = "update action_log_".$order_month." set
admin/www/stat/content/report.php: $tsql = "select count(action_log_id) from action_log_".$i." where c_id = '$c_id' and p_id = '$j'";
admin/www/stat/listing/report.php: $tsql = "select count() from action_log_". date("Ym")." where listing_id = '$listing_id' and status = 3";
admin/www/stat/asp/report.php:$tsql = "select count() from action_log_". date("Ym")." where asp_id = '$asp_id' and status = 3";
admin/www/stat/asp_back_ex.php:$table = "action_log_".$action_month;
admin/www/stat/action_log_detail.php: $sql .= "select action_log_id from action_log_".$stat_month." ".$query;
admin/www/stat/stat_client.php: $tsql = "select count(action_log_id) from action_log_".$month." where c_id = '$c_id_db[$i]' and p_id = '$j'";
admin/www/stat/action_log_detail_csv.php:$table = "action_log_".$syy.sprintf("%02d",$smm);
admin/www/stat/media/report.php: $tsql = "select count(*) from action_log_".$i." where m_id = '$m_id' and status = 3";
admin/www/content/search/log_reset_asp.php: $sql = "delete from access_action_log_$month where c_id = '$c_id' and asp_id = '$asp_id'";
admin/www/content/search/log_reset_listing.php:$sql = "delete from action_log_merge where c_id = '$c_id' and listing_id = '$listing_id'";
admin/www/content/search/log_reset.php:$sql = "delete from action_log_merge where c_id = '$c_id'";
admin/www/keyword/add/csv_admit.php: $tsql = "update action_log_".$order_month." set
admin/www/media_owner/search/rm_db.php: $tsql = "delete from action_log_merge where m_id = '".addslashes($m_id)."'";
admin/www/asp/search/rm_db.php: $sql = "delete from action_log_merge where asp_id = '".addslashes($asp_id)."'";
admin/www/listing/search/rm_db.php: $sql = "delete from action_log_merge where listing_id = '".addslashes($listing_id)."'";admin/www/log/csv_cost_update_db.php: $tsql = "update action_log_".$admit_month." set
admin/cron/point_back_retry.php:$sql = "select * from action_log_merge where point_back_response = 2";

user/www/partner/report/user_csv.php:$sql = "select m_id,c_id,m_user_id,p_id,click_cost,action_cost_set,action_ratio_set,commission_cost,click_date,order_date,status,amount from action_log_merge $query $order_query";
user/www/partner/report/day.php: $tsql2 = "select count(action_log_id),sum(commission_cost) from action_log_".$month." where m_owner_id = '$m_owner_id' and admit_date LIKE '$stat_date%' and c_id = '$c_id' and p_id = '$j' and status = '1'";
user/www/partner/report/index.php: $tsql = "select count(action_log_id) from action_log_".$month." where c_id = '$c_id' and m_owner_id = '$m_owner_id' and p_id = '$j' and status = '2'";
user/www/partner/report/banner.php: $tsql2 = "select count(action_log_id) from action_log_".$month." where m_owner_id = '$m_owner_id' and c_id = '$c_id_db[$i]' and p_id = '$j'".$entry_date_query;
user/www/partner/report/user.php: $sql = "select count() from action_log_merge $query";
user/www/merchant/referer/index.php: $sql = "select action_log_id,asp_id,referer,m_user_id,click_cost,action_cost_set,action_ratio_set,commission_cost,click_date,order_date,status,amount,point from action_log_".$stat_month." $query union ";
user/www/merchant/referer/index.php: $sql .= "select count() from action_log_".$stat_month." $query union ";
user/www/merchant/referer/csv.php: $sql .= "select count() from action_log_".$stat_month." $query union ";
user/www/merchant/admit/admit_ng.php: $tsql = "update action_log_".$order_month." set
user/www/merchant/admit/index.php: $sql .= "select action_log_id from action_log_".$stat_month." $query union ";
user/www/merchant/admit/admit_ok.php: $tsql = "update action_log_".$order_month." set
user/www/merchant/admit/csv_admit.php: $tsql = "update action_log_".$order_month." set
user/www/merchant/admit/csv.php: $sql .= "select action_log_id,asp_id,listing_id,m_id,user_id,click_cost_cl,action_cost_cl_set,action_ratio_cl_set,commission_cost_cl,click_date,order_date,status,amount from action_log_".$stat_month." $query union ";
user/www/merchant/index.php: $ssql = "select count() from action_log_".date("Ym")." where c_id = '$c_id[$i]' and status = '0'";
user/www/merchant/report/userbrows.php: $action_sql = $action_sql." SELECT m_owner_id, asp_id, listing_id, referer, status, entry_date, resign_date from action_log_".$stat_month."
user/www/merchant/report/userrep.php: $sql = $sql . $select_state . "from action_log_" . $stat_month_array[$i] . $where_state;
user/www/merchant/report/top.php: $resign_sql = $resign_sql."SELECT tb.m_owner_id, tb.asp_id, tb.listing_id, DATE_FORMAT(tb.resign_date,'%Y%m') as resign_month from action_log_".$stat_month." as tb
user/www/merchant/report/resign.php: $sql = "select count(*) from action_log_".$stat_month." ".$sql_query2;
user/www/merchant/report/creative.php: $resign_sql = $resign_sql."SELECT tb.m_owner_id, tb.asp_id, tb.listing_id, SUBSTR as item_id, DATE_FORMAT(tb.resign_date,'%Y%m') as resign_month from action_log_".$stat_month." as tb

[stat_log_*_YYYYMM]

ad/www/ac/resign_convert.php: $tsql = "update stat_log_".$resign_month." set
ad/www/ac/resign_convert_date.php: $tsql = "select * from stat_log_asp_".$resign_month." where stat_date = '$resign_date' and c_id = '$c_id' and asp_id = '$asp_id'";

stat/stat.php: $sql = "insert ignore into stat_log_shop_".$log_month." set
stat/stat_am_asp.php: $sql = "insert into stat_log_client_".$log_month." set
stat/stat_am_listing.php: $sql = "insert into stat_log_client_".$log_month." set
stat/stat_asp.php: $sql = "insert ignore into stat_log_item_".$log_month." set
stat/stat_listing.php: $sql = "insert ignore into stat_log_listing_".$log_month." set
stat/stat_rs.php: $sql = "insert ignore into stat_log_".$log_month." set
stat/stat_rs_asp.php: $sql = "insert ignore into stat_log_asp_".$log_month." set
stat/stat_rs_listing.php: $sql = "insert ignore into stat_log_listing_".$log_month." set

admin/www/stat/stat_client_group.php: from client_owner as co left join stat_log_client_".$month." as st on co.c_owner_id = st.c_owner_id $sql_query group by co.g_id";
admin/www/stat/stat_client_group_owner.php: from client_owner as co left join stat_log_client_".$month." as st on co.c_owner_id = st.c_owner_id $sql_query group by co.c_owner_id";
admin/www/stat/content/report_day.php: from stat_log_listing_".$month." where c_id = '$c_id' and stat_date = '$stat_date'";
admin/www/stat/content/report.php: from stat_log_listing_".$i." where c_id = '$c_id'";
admin/www/stat/content/report_media.php: from stat_log_client_".date("Ym")." where c_owner_id = '$c_owner_id'";
admin/www/stat/resign_convert_db.php: $sql2 = "update stat_log_asp_".$resign_month." set
admin/www/stat/stat_total.php: from stat_log_client_".$i;
admin/www/stat/stat_asp.php: from asp left join stat_log_asp_".$month." as st on asp.asp_id = st.asp_id $sql_query group by asp.asp_id";
admin/www/stat/stat_total_day.php: from stat_log_client_".$month." where stat_date = '".$syy."-".$smm."-".sprintf("%02d",$i)."'";
admin/www/stat/listing/report.php: from stat_log_listing_".date("Ym")." where listing_id = '$listing_id'";
admin/www/stat/listing/report_banner.php: from stat_log_listing_".$month." where listing_id = '$listing_id' and c_id = '$c_id_db[$i]'";
admin/www/stat/listing/report_day.php:$tsql = "select * from stat_log_listing_".$month." where listing_id = '$listing_id'";
admin/www/stat/asp/report.php: from stat_log_asp_".date("Ym")." where asp_id = '$asp_id'";
admin/www/stat/asp/report_banner.php: from stat_log_asp_".$month." where asp_id = '$asp_id' and c_id = '$c_id_db[$i]'";
admin/www/stat/asp/report_day.php:$tsql = "select * from stat_log_asp_".$month." where asp_id = '$asp_id'";

user/www/partner/index.php: from stat_log_media_".date("Ym")." where m_owner_id = '$m_owner_id'";
user/www/partner/report/day.php: from stat_log_".date("Ym")." where m_id = '$m_id'
user/www/partner/report/index.php: from stat_log_".date("Ym")." where m_owner_id = '$m_owner_id'
user/www/partner/report/media.php: from stat_log_".$month." where m_owner_id = '$m_owner_id'
user/www/partner/report/shop.php: from stat_log_".$month." where m_owner_id = '$m_owner_id'

※myisamとinnodbテーブルロック負荷検証→本番と同じシナリオ想定

レプリケーション¶

稼働中のMySQLに無停止でスレーブを追加する
http://muramasa64.fprog.org/diary/?date=20111017
mysqlで既存の状態からレプリケーション構築 - innodb編
http://hogem.hatenablog.com/entry/20080924/1222266257
MySQLのMyISAMが混在する環境のバックアップについて注意事項
http://blog.isao.co.jp/mysqldump_myisam/

スレーブ停止
マスターmysqldump
スレーブデータリストア
スレーブ起動

※mysqldumpの時間見積り必要

レプリケーションテストサーバ構築¶

db3: master
web2: slave

①db3マスター設定

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.0/255.255.255.0' IDENTIFIED BY 'ign8020';

/etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1001

②web2スレーブ設定

web2

/etc/my.cnf

[mysqld]
server-id=1002

③db3スナップショット取得

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      3867173 |              |                  |

[root@db3 bkup]# date
2015年 12月 16日 水曜日 18:32:19 JST

[root@db3 bkup]# mysqldump u root -p --all-databases --lock-all-tables > dbdump.db
-rw-r--r- 1 root root 36G 12月 4 18:32 dbdump.db

mysql > UNLOCK TABLES;

④web2スナップショット適用

[root@web2 bkup]# mysql -u root -p < dbdump.db

mysql > CHANGE MASTER TO
MASTER_HOST='192.168.2.105',
MASTER_USER='repl',
MASTER_PASSWORD='ign8020',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS= 3867173;

⑤db3のmyisam→innodb変換

mysql > alter table stat_log_** order by stat_log_id;
mysql > alter table stat_log_** ENGINE = INNODB;

⑥アプリケーション動作テスト
⑦マスター、スレーブともmy.cnfをinnodb用に設定変更後、mysqld再起動

参考
http://wadslab.net/wiki/index.php?MySQL%20%A5%EC%A5%D7%A5%EA%A5%B1%A1%BC%A5%B7%A5%E7%A5%F3%A4%CE%A5%BB%A5%C3%A5%C8%A5%A2%A5%C3%A5%D7%BC%EA%BD%E7

https://major.io/2007/10/03/convert-myisam-tables-to-innodb/

primary keyでソートしてからalterすると速いらしい...

http://dba.stackexchange.com/questions/14856/preventing-replication-of-alter-table-command-to-change-engine

masterだけクエリー適用したい場合は

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

こうするとslaveにバイナリログが転送されない