一次系統升級的過程

【問題背景】公司的一個銷售系統執行了有5年的時間了，最近需要對該系統升級，其中有個問題一直導致整個升級過程一直停滯；升級的過程中，需要對一個表做新增欄位的操作，直接執行alter語句進行操作，需要消耗大概8個小時，由於操作的過程中會進行鎖表的操作，但是業務允許的停機操作只有4個小時；經過了一系列的測試終於完成了系統的更新，以下是更新過程中的一些經驗。

【1】環境說明：

資料庫版本：5.5.36

資料庫架構：主從架構

資料庫引擎：innodb

需要修改的表大小：300W行，大小4GB

計算表佔用的空間，方法如下

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB   

   from information_schema.tables 

      where  table_schema='schema_name'

           and table_name = 'table_name'; 

alter  table add column的原理：整個過程跟oracle的線上重定義表很像，首先建立一個臨時表，再把資料從源表一條條insert到這個臨時表中，當所有的資料都insert完成後，再把運來的表drop掉，把臨時表rename成源表；

• 整個過程會對源表進行加鎖的操作，避免這個過程被鎖定；
• 臨時表會寫到資料庫的tmp空間裡，當這部分的空間不足時，再寫入到磁碟中；

知道了我們要處理的物件和整個操作的原理之後，初步的最佳化如下：

1、引數的調優

修改引數tmp_table_size和max_heap_table_size的值

max_tmp_tables 預設值為32M

tmp_table_size預設值為16M

調整後的引數為

max_tmp_tables 值為256M

tmp_table_size值為128M

調整結果：經過調整後，整個過程的更新速度提升了30%；

2、使用pt-online-schema-change工具

透過上面的最佳化後，還是不能達到要求，萬能的谷歌告訴我，這個mysql線上增加欄位其實很多大的網際網路公司都會碰到的，Percona公司早就為這種業務場景提供了相應的解決方法：使用pt-online-schema-change進行新增欄位，這種方法跟oracle的線上重定義表很像。這樣再進行增加列的同時，不會影響當前業務的插入操作；

詳細的操作如下：

2.1 pt-online-schema-change的下載，透過下載percona-toolkit軟體即可，軟體的下載方法已經介紹過，麻煩找下本部落格的連線；

2.2 設定可以直接執行命令，在/usr/local/percona-toolkit/bin目錄下執行， ln pt-table-checksum    /usr/bin/pt-table-checksum

2.3 使用方法，

pt-online-schema-change的執行分成試執行和執行兩個步驟

• 試執行

• 執行

【2】經過以上操作，在兩個夜深人靜的晚上，順利的完成了系統的升級；有時候我們遇到的很多問題，其實網上都有了很好的解決方法，學以致用，感謝網友們的智慧；

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

本文作者：JOHN，某上市公司DBA，業餘時間專注於資料庫的技術管理，從管理的角度去運用技術。

技術部落格：獵人筆記                                                資料庫技術群：367875324 （請備註資料庫型別）

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++