表構造mysql 5をオンラインで変更する.5バージョンとpt-online-schema-change
11080 ワード
一、テスト環境
システム:Centos 6.2
データベース:mysql Ver 14.14 Distrib 5.5.5.18,for Linux(x 86_64)using readline 5.1
perconaツール:percona-toolkit-2.2.12
データベース・サイズのテスト:tx_ljxz_71--16G、t_log_item--3G
二、オンラインで表構造を修正する過程
mysqlオンラインテーブル構造の変更
1元のテーブル(original_table)のテーブル構造とDDL文に従って、非表示のテンポラリ・テーブル(tmp_table)を新規作成します.
2元のテーブルにwrite lockを付け、すべての更新操作(insert、delete、updateなど)をブロックする
3 insert into tmp_の実行table select * from original_table
4 rename original_tableとtmp_table,最後drop original_table
5 write lockを解放します.
InnoDBでDDLを実行する場合、元のテーブルは読み書きしかできないことがわかります.
pt-online-schema-changeオンラインテーブル構造の変更
1 alter操作を実行するテーブルと同じ空のテーブル構造を作成します.
2テーブル構造の変更を行い、元のテーブルのデータからcopyからテーブル構造の変更後のテーブルに、
3元のテーブルにフリップフロップを作成するcopyデータを作成する過程で、元のテーブルの更新操作が新しいテーブルに更新される.
注意:テーブルにトリガというツールが定義されている場合は、作業できません.
4 copyが完了したら、元のテーブルの代わりにrename tableの新しいテーブルを使用し、デフォルトで元のテーブルを削除します.
三、インストール構成percona-toolkit
1.インストール
公式サイト
2.テストインストール成功
pt-online-schema-change -uroot --alter="modify buff_id int(20)"--dry-run D=tx_ljxz_71,t=t_world_buff
エラー:
その後、仮想マシンのcentos 6.5にテストをインストールしたが、同じ問題は発見されず、検出され、カスタマイズシステムの問題である.
perl-DVD-MySQLのインストールディレクトリを/usr/lib 64/perl 5/に要求し、元のディレクトリを移動し、再インストールすればよい
再測定する
四、オンラインで表構造を修正することをテストする
1.表構造をオンラインで変更する時間の比較:
mysqlオンラインテーブル構造の変更
real 18m51.198s
real 18m17.492s
real 19m53.119s
innodb_buffer_pool_size予熱後、オンラインでテーブル構造を変更する時間は:
12m9.507s
11m23.653s
11m51.561s
負荷:
pt-online-schema-changeオンラインテーブル構造の変更
real 18m56.473s
real 19m27.950s
real 18m58.556s
innodb_buffer_pool_size予熱後、オンラインでテーブル構造を変更する時間は:
14m10.936s
13m41.146s
13m56.238s
負荷:
この場合、sql文によるデータベースの操作は実行されません.
2.オンラインで表構造を修正する時、データベースに対して添削を行う
mysqlオンラインテーブル構造の変更
テストスクリプトを実行すると、select文を除いて、テーブル構造の変更が完了するまで追加削除を行う操作は続行できません.
テーブル構造をオンラインで変更する時間は、次のとおりです.
11m43.975s
11m37.980s
11m22.229s
この場合、変更を追加する時間は次のとおりです.
14m36.781s
13m40.872s
13m29.913s
pt-online-schema-changeオンラインテーブル構造の変更
a.percona-toolkitデフォルトinnodb_lock_wait_timeoutは1、retries値は3でmysqlのプロファイルでinnodb_を変更する必要がありますlock_wait_timeout値、innodb_の場合lock_wait_timeoutの値が十分ではありません.mysql文を実行すると同時に、データテーブルをコピーすると、エラーが発生します.
b.表構造をオンラインで変更する場合、sql文を優先的に実行し、表構造の変更を一時停止し、一時停止時間がinnodb_より大きい場合lock_wait_timeout値は上記のエラーを報告し、一時停止時間がsql文の実行より小さい場合は、sql文の実行が完了した後、テーブル構造の変更を継続し、テーブル構造の変更が完了した時間は、その間にsql文を実行した時間とフックします.
テーブル構造をオンラインで変更する時間は、次のとおりです.
15m8.163s
14m55.007s
14m58.613s
この場合、変更を追加する時間は次のとおりです.
2m23.536s
2m10.319s
2m13.728s
五、結論 sql文が実行されていない場合、mysqlがオンラインでテーブル構造を変更する時間はpt-online-schma-changeと基本的に等しく、大きな差はありません.pt-online-schma-change方式はメモリを多く消費し、負荷も よりやや高くなります. mysql予熱後、オンラインでテーブル構造を修正する時間は、pt-online-schema-change方式よりやや速く に直接修正されます.オンラインで表構造を修正すると同時にmysql文を実行し、mysqlが直接修正する方式はまず表構造を修正してからsql文を実行し、pt-online-schema-change方式はsql文を優先的に実行し、データテーブルをコピーし、コピーが完了してからsql文を実行した結果を新しいテーブルに更新するので、時間的に、直接テーブル構造を変更するとpt-online-schema-change方式よりも速くなり、実際に使用する場合、直接変更すると よりやや速くなります.
テストスクリプト:
転載先:https://blog.51cto.com/8721349/1611203
システム:Centos 6.2
データベース:mysql Ver 14.14 Distrib 5.5.5.18,for Linux(x 86_64)using readline 5.1
perconaツール:percona-toolkit-2.2.12
データベース・サイズのテスト:tx_ljxz_71--16G、t_log_item--3G
二、オンラインで表構造を修正する過程
mysqlオンラインテーブル構造の変更
1元のテーブル(original_table)のテーブル構造とDDL文に従って、非表示のテンポラリ・テーブル(tmp_table)を新規作成します.
2元のテーブルにwrite lockを付け、すべての更新操作(insert、delete、updateなど)をブロックする
3 insert into tmp_の実行table select * from original_table
4 rename original_tableとtmp_table,最後drop original_table
5 write lockを解放します.
InnoDBでDDLを実行する場合、元のテーブルは読み書きしかできないことがわかります.
pt-online-schema-changeオンラインテーブル構造の変更
1 alter操作を実行するテーブルと同じ空のテーブル構造を作成します.
2テーブル構造の変更を行い、元のテーブルのデータからcopyからテーブル構造の変更後のテーブルに、
3元のテーブルにフリップフロップを作成するcopyデータを作成する過程で、元のテーブルの更新操作が新しいテーブルに更新される.
注意:テーブルにトリガというツールが定義されている場合は、作業できません.
4 copyが完了したら、元のテーブルの代わりにrename tableの新しいテーブルを使用し、デフォルトで元のテーブルを削除します.
三、インストール構成percona-toolkit
1.インストール
公式サイト
tar zxvf percona-toolkit-2.2.12.tar.gz
perl Makefile.PL
make
make test
make install2.テストインストール成功
pt-online-schema-change -uroot --alter="modify buff_id int(20)"--dry-run D=tx_ljxz_71,t=t_world_buff
エラー:
Cannot connect to MySQL: install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.
Compilation failed in require at (eval 21) line 3.
at /usr/local/bin/pt-online-schema-change line 2261その後、仮想マシンのcentos 6.5にテストをインストールしたが、同じ問題は発見されず、検出され、カスタマイズシステムの問題である.
/usr/local/lib64/perl5/Bundle/DBD/mysql.pm
/usr/local/lib64/perl5/DBD/mysql.pmperl-DVD-MySQLのインストールディレクトリを/usr/lib 64/perl 5/に要求し、元のディレクトリを移動し、再インストールすればよい
mv /usr/local/lib64/perl5/ /data/backup/
yum install perl-DBD-MySQL再測定する
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Starting a dry run. `tx_ljxz_71`.`t_world_buff` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table tx_ljxz_71._t_world_buff_new OK.
Altering new table...
Altered `tx_ljxz_71`.`_t_world_buff_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2015-01-28T17:00:22 Dropping new table...
2015-01-28T17:00:22 Dropped new table OK.
Dry run complete. `tx_ljxz_71`.`t_world_buff` was not altered.四、オンラインで表構造を修正することをテストする
1.表構造をオンラインで変更する時間の比較:
mysqlオンラインテーブル構造の変更
time mysql -uroot tx_ljxz_71 -e "alter table t_log_item modify role_level int(20)"real 18m51.198s
real 18m17.492s
real 19m53.119s
innodb_buffer_pool_size予熱後、オンラインでテーブル構造を変更する時間は:
12m9.507s
11m23.653s
11m51.561s
負荷:
top - 10:32:56 up 111 days, 18:40, 2 users, load average: 1.50, 1.42, 0.77
Tasks: 223 total, 1 running, 222 sleeping, 0 stopped, 0 zombie
Cpu(s): 9.2%us, 1.0%sy, 0.0%ni, 85.0%id, 4.8%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16315920k total, 16162676k used, 153244k free, 51036k buffers
Swap: 12287992k total, 245416k used, 12042576k free, 6720696k cachedpt-online-schema-changeオンラインテーブル構造の変更
time pt-online-schema-change --user=root --critical-load Threads_running=100 --alter="modify role_level int(20)" --execute D=tx_ljxz_71,t=t_log_item--critical-load Threads_running 50， ：
Error copying rows from `tx_ljxz_71`.`t_log_item` to `tx_ljxz_71`.`_t_log_item_new`: Threads_running=51 exceeds its critical threshold 50real 18m56.473s
real 19m27.950s
real 18m58.556s
innodb_buffer_pool_size予熱後、オンラインでテーブル構造を変更する時間は:
14m10.936s
13m41.146s
13m56.238s
負荷:
top - 10:32:19 up 111 days, 18:53, 2 users, load average: 2.29, 1.75, 0.83
Tasks: 230 total, 1 running, 229 sleeping, 0 stopped, 0 zombie
Cpu(s): 12.2%us, 1.2%sy, 0.0%ni, 65.5%id, 21.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 12179948k total, 12011356k used, 168592k free, 37348k buffers
Swap: 12287992k total, 165424k used, 12122568k free, 2597832k cachedこの場合、sql文によるデータベースの操作は実行されません.
2.オンラインで表構造を修正する時、データベースに対して添削を行う
mysqlオンラインテーブル構造の変更
time mysql -uroot tx_ljxz_71 -e "alter table t_log_item modify role_level int(20)"テストスクリプトを実行すると、select文を除いて、テーブル構造の変更が完了するまで追加削除を行う操作は続行できません.
テーブル構造をオンラインで変更する時間は、次のとおりです.
11m43.975s
11m37.980s
11m22.229s
この場合、変更を追加する時間は次のとおりです.
14m36.781s
13m40.872s
13m29.913s
pt-online-schema-changeオンラインテーブル構造の変更
time pt-online-schema-change --user=root --critical-load Threads_running=100 --alter="modify role_level int(20)" --execute D=tx_ljxz_71,t=t_log_itema.percona-toolkitデフォルトinnodb_lock_wait_timeoutは1、retries値は3でmysqlのプロファイルでinnodb_を変更する必要がありますlock_wait_timeout値、innodb_の場合lock_wait_timeoutの値が十分ではありません.mysql文を実行すると同時に、データテーブルをコピーすると、エラーが発生します.
2015-02-03T16:22:48 Error copying rows from `tx_ljxz_71`.`t_log_item` to `tx_ljxz_71`.`_t_log_item_new`: 2015-02-03T16:22:48 DBD::mysql::st execute failed: Lock wait timeout exceeded; try restarting transaction [for Statement "INSERT LOW_PRIORITY IGNORE INTO `tx_ljxz_71`.`_t_log_item_new` (`pid`, `agent_id`, `server_id`, `role_id`, `role_level`, `action`, `item_id`, `amount`, `bag_amount`, `zero_dateline`, `year`, `month`, `day`, `equip_id`, `color`, `fineness`, `start_time`, `end_time`, `bind_type`, `super_unique_id`, `from`) SELECT `pid`, `agent_id`, `server_id`, `role_id`, `role_level`, `action`, `item_id`, `amount`, `bag_amount`, `zero_dateline`, `year`, `month`, `day`, `equip_id`, `color`, `fineness`, `start_time`, `end_time`, `bind_type`, `super_unique_id`, `from` FROM `tx_ljxz_71`.`t_log_item` FORCE INDEX(`PRIMARY`) WHERE ((`pid` >= ?)) AND ((`pid` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 20031 copy nibble*/" with ParamValues: 0='13794706058943', 1='13794714479917'] at /usr/local/bin/pt-online-schema-change line 10385.b.表構造をオンラインで変更する場合、sql文を優先的に実行し、表構造の変更を一時停止し、一時停止時間がinnodb_より大きい場合lock_wait_timeout値は上記のエラーを報告し、一時停止時間がsql文の実行より小さい場合は、sql文の実行が完了した後、テーブル構造の変更を継続し、テーブル構造の変更が完了した時間は、その間にsql文を実行した時間とフックします.
Altering `tx_ljxz_71`.`t_log_item`...
Creating new table...
Created new table tx_ljxz_71._t_log_item_new OK.
Altering new table...
Altered `tx_ljxz_71`.`_t_log_item_new` OK.
2015-02-03T16:25:31 Creating triggers...
2015-02-03T16:25:31 Created triggers OK.
2015-02-03T16:25:31 Copying approximately 14221411 rows...
Copying `tx_ljxz_71`.`t_log_item`: 0% 01:01:13 remain
Copying `tx_ljxz_71`.`t_log_item`: 1% 59:04 remain
Pausing because Threads_running=33.
Pausing because Threads_running=30.
Copying `tx_ljxz_71`.`t_log_item`: 1% 01:57:15 remain
Copying `tx_ljxz_71`.`t_log_item`: 5% 44:33 remain
......
2015-02-03T16:40:48 Copied rows OK.
2015-02-03T16:40:48 Swapping tables...
2015-02-03T16:40:50 Swapped original and new tables OK.
2015-02-03T16:40:50 Dropping old table...
2015-02-03T16:40:59 Dropped old table `tx_ljxz_71`.`_t_log_item_old` OK.
2015-02-03T16:40:59 Dropping triggers...
2015-02-03T16:40:59 Dropped triggers OK.
Successfully altered `tx_ljxz_71`.`t_log_item`.テーブル構造をオンラインで変更する時間は、次のとおりです.
15m8.163s
14m55.007s
14m58.613s
この場合、変更を追加する時間は次のとおりです.
2m23.536s
2m10.319s
2m13.728s
五、結論
テストスクリプト:
#!/usr/local/bin/python
import threading
from os import system
from random import randint
import time
import MySQLdb
MYSQL = "/usr/local/bin/mysql -uroot"
cxn = MySQLdb.connect(host='localhost', user='root', port=3306, db='tx_ljxz_71')
def run1(db):
role_level = randint(40, 70)
start_time = int(time.time())
system('%s %s -e "select count(*) from t_log_item where role_level=%s"' % (MYSQL, db, role_level))
print '%s select count(*) from t_log_item where role_level=%s use %d sec.' % (db, role_level, int(time.time()) - start_time)
def run2(db):
month = randint(9, 10)
day = randint(20, 30)
start_time = int(time.time())
system('%s %s -e "select sum(use_unbind) from t_log_consume_gold where year=2013 and month=%s and day=%s"' % (MYSQL, db, month, day))
print '%s select sum(use_unbind) from t_log_consume_gold where year=2013 and month=%s and day=%s use %d sec.' % (db, month, day, int(time.time()) - start_time)
def run3(db):
p_id = randint(139249095950, 139349095950)
start_time = int(time.time())
try:
system('%s %s -e "insert into t_log_item (pid) values(%s)"' % (MYSQL, db, p_id))
except:
print "insert into t_log_item failed"
else:
print '%s insert into t_log_item (pid) values(%s) use %d sec.' % (db, p_id, int(time.time()) - start_time)
def run4(db):
role_level = randint(1, 15)
start_time = int(time.time())
try:
system('%s %s -e "delete from t_log_item where role_level=%s;commit"' % (MYSQL, db, role_level))
except:
print "delte from t_log_item failed"
else:
print '%s delete from t_log_item where role_level=%s use %d sec.' % (db, role_level, int(time.time()) - start_time)
def run5(db):
role_level = randint(16, 20)
server_id = randint(1, 100)
start_time = int(time.time())
try:
system('%s %s -e "update t_log_item set server_id=%s where role_level=%s;commit"' % (MYSQL, db, server_id, role_level))
"""cxn.commint()
cxn.close()"""
except:
print "update t_log_item failed"
else:
print '%s update t_log_item set server_id=%s where role_level=%s use %d sec.' % (db, server_id, role_level, int(time.time()) - start_time)
if __name__ == '__main__':
threads = []
funcs = [run1, run2, run3, run4, run5]
dbs = ['tx_ljxz_71', 'tx_ljxz_71', 'tx_ljxz_71','tx_ljxz_71', 'tx_ljxz_71']
for i in range(20):
threads.append(threading.Thread(target=funcs[randint(0,4)], args=(dbs[randint(0,4)],)))
for i in range(20):
threads[i].start()
for i in range(20):
threads[i].join()転載先:https://blog.51cto.com/8721349/1611203