pt-archive提速的實踐經驗

　　最近遇到很多業務需求，需要進行資料導出工作，由于有格式要求，故之前一直使用mysqldump的方法。

mysqldump -uuser -ppassword -S mysql.sock -t db table -T /data1/dbatemp/      

　　當然可以根據需求增加分隔符和行結束符。--fields-terminated-by和--lines-terminated-by，其他也可以增加where條件進行檢索，可以自行使用--help查詢。

　　但是後續由于業務需求比較頻發，同僚需求資料容量越來越大，已經不适合在localhost進行操作，需要一台中心管理機來統一進行管理，這時候mysqldump加-T參數導出CSV格式隻能在本地操作的局限性就不能滿足要求了。于是開始轉而需求其他方法，研究了一下percona的工具pt-archive，發現可以滿足我們的需求，于是開始使用，但是在實際使用過程中發現一個問題，導緻pt-archinve完全無法使用，這就是速度問題。同mysqldump對比，pt-archive的速度完全無法接受，經過實際測試，不加參數的pt-archive比mysqldump要慢很多，屬于完全無法使用狀态。

　　我們的實驗環境如下，mysql版本5.5，伺服器是12塊盤的SAS伺服器，目标資料庫表大小872M。

　　使用mysqldump的導出資訊如下：

time mysqldump -uroot -p -S /tmp/mysql10010.sock -t gemini table_definition_20130821 -T /data1/dbatemp/mysql10010/ 

real    0m9.679s
user    0m0.004s
sys     0m0.001s      

　　使用pt-archive的導出資訊如下：

time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --file=/data1/dbatemp/mysql10010/4.txt  --statistics

real    9m5.620s
user    3m58.810s
sys     0m38.124s      

　　一個9s多，一個9m多，相差近60倍，導緻pt-archive完全無法使用。根據--statistics的輸出結果我們可以看到select占了很大一部分。

Action          Count       Time        Pct
select        1065539   294.1826      52.01
commit        1065539    54.3843       9.62
print_file    1065538     8.0095       1.42
other               0   209.0001      36.95      

　　進而我們的加速思路即為如何減少select占用的時間，開啟general log之後，發現為一個大select後跟着一個commit，衆所周知，大select的查詢效率非常慢。那麼我們嘗試這将一個大select分片成很多個小select，看看會不會降低查詢時間。這裡就要使用--limit參數了。

time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --file=/data1/dbatemp/mysql10010/4.txt --limit=1000 --statistics

real    3m13.553s
user    2m15.873s
sys     0m26.648s

Action          Count       Time        Pct
commit        1065539    46.1518      23.86
print_file    1065538     6.2581       3.24
select           1067     4.6308       2.39
other               0   136.3800      70.51      

　　從上面可以看出增加了--limit參數之後，速度快了很多，基本是原來不加參數的1/3，但是和dump比較還是相差很多，仍然有将近20倍的差距，還處于不可用狀态。根據狀态分析，這次commit所占的時間比較多。再次檢視general log，發現一次select後，跟着n個commit，導緻commit的時間非常大。思考采用--txn-size參數來控制commit的次數。

time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --file=/data1/dbatemp/mysql10010/4.txt --limit=1000 --txn-size=1000 --statistics      

real    1m57.196s
user    1m41.504s
sys     0m10.627s

Action          Count       Time        Pct
print_file    1065538     4.9122       4.19
select           1067     4.4760       3.82
commit           1066     0.1161       0.10
other               0   107.5997      91.88      

　　增加txn-size之後，速度再次提高，提升幅度在30%，雖然标準值仍和mysqldump比有較大差距。從狀态分析結果看，主要時間消耗再other上了，但是由于輸出沒有明确指向，故有很多可能。隻能在從pt-archive的參數中查找看是否還有優化的選項。

　　首先，嘗試加入--buffer參數，并沒明顯提高

Action          Count       Time        Pct
select           1067     5.1447       4.40
print_file    1065538     0.3666       0.31
commit           1066     0.1133       0.10
flush            1066     0.0173       0.01
other               0   111.2178      95.17

real    1m56.989s
user    1m45.411s
sys     0m7.626s      

　　然後加入--ascend-first參數測試

Action          Count       Time        Pct
select           1067     4.6041       4.31
commit           1066     0.1501       0.14
flush            1066     0.0101       0.01
print_file    1065538    -0.4222      -0.40
other               0   102.4029      95.93

real    1m46.876s
user    1m34.415s
sys     0m6.143s      

　　可以看出仍然變化不大，經過多次測試之後，添加隻使用主鍵參數可以将時間縮減近1m之内。

time pt-archiver --source u=root,p=,h=localhost,S=/tmp/mysql10010.sock,D=gemini,t=table_definition_20130821 --no-delete --where "1=1" --no-check-charset --statistics --buffer --limit=10000 --commit-each --no-check-charset --primary-key-only --share-lock --file=/data1/dbatemp/mysql10010/12.txt

Action          Count       Time        Pct
select            108     1.1020       1.94
commit            108     0.0358       0.06
flush             108     0.0009       0.00
print_file    1065538    -5.2057      -9.18
other               0    60.7444     107.18

real    0m56.810s
user    0m54.604s
sys     0m0.629s       

轉載于:https://www.cnblogs.com/billyxp/p/3312244.html