0x00 前言
機緣巧合，公司突然要搞一波大量資料的分析。屬於客流類的分析。
資料量級也還算不錯，經過 gzip 壓縮，接近 400 個 點位的 SQL 檔案 (MySQL innoDB)，大小接近 100GB 左右，原始記錄資料估測在 180 億左右。
解壓後…… 差不多一個 T 吧。
如果是人民幣玩家，自然是直接購置幾十臺高配置機器，做個 mysql shard 或者直接上大資料全家桶比如 hadoop 和 hive 之類，讓程式設計師去往死裡折騰吧。
嗯，然而對於我這種非人民幣玩家，就要用單機硬扛。
那就硬扛唄。
我手上的機器配置如下：
-
區域網伺服器 （ Ubuntu 16.04 LTS ）
- Xeon(R) CPU E3-1225 v5 @ 3.30GHz
- 16G 記憶體
- 1T 硬碟
-
蘋果電腦 2016 年 15 寸 最高配
- 1T 硬碟
- i7 四核
0x01 準備資料階段
用低配機器分析大資料的首要原則，就是不要分析大資料。
何也？
就是儘可能的抽取所得結論所需分析資料的最小超集
小機器是無法完成海量計算的，但通過一定的過濾和篩選可以將資料篩選出到一臺機器能扛得住的計算量。從而達到可以可以分析海量資料的目的。
1.1 將資料匯入 MySQL 中
我們先不管三七二十一，既然給了 SQL 檔案，肯定要入庫的，那麼問題來了：
將大象關進冰箱要幾個步驟
將資料匯入資料庫中需要幾個步驟
或者說，如何更快的匯入 400 張不同表的資料。
大致步驟如下：
- 新增硬碟，並初始化
- 配置 MySQL 的 datadir 到新增硬碟上
- 匯入資料 (PV & MySQL)
新增硬碟，並初始化
首先，購買並插入硬碟
使用 lshw 檢視硬碟資訊
root@ubuntu:~# lshw -C disk
*-disk
description: SCSI Disk
product: My Passport 25E2
vendor: WD
physical id: 0.0.0
bus info: scsi@7:0.0.0
logical name: /dev/sdb
version: 4004
serial: WX888888HALK
size: 3725GiB (4TB)
capabilities: gpt-1.00 partitioned partitioned:gpt
configuration: ansiversion=6 guid=88e88888-422d-49f0-9ba9-221db75fe4b4 logicalsectorsize=512 sectorsize=4096
*-disk
description: ATA Disk
product: WDC WD10EZEX-08W
vendor: Western Digital
physical id: 0.0.0
bus info: scsi@0:0.0.0
logical name: /dev/sda
version: 1A01
serial: WD-WC888888888U
size: 931GiB (1TB)
capabilities: partitioned partitioned:dos
configuration: ansiversion=5 logicalsectorsize=512 sectorsize=4096 signature=f1b42036
*-cdrom
description: DVD reader
product: DVDROM DH1XXX8SH
vendor: PLDS
physical id: 0.0.0
bus info: scsi@5:0.0.0
logical name: /dev/cdrom
logical name: /dev/dvd
logical name: /dev/sr0
version: ML31
capabilities: removable audio dvd
configuration: ansiversion=5 status=nodisc
複製程式碼使用 fdisk 格式化硬碟，並且分割槽
fdisk /dev/sdb
#輸入 n
#輸入 p
#輸入 1
#輸入 w
sudo mkfs -t ext4 /dev/sdb1
mkdir -p /media/mynewdrive
vim /etc/fstab
# /dev/sdb1 /media/mynewdrive ext4 defaults 0 2
# 直接掛載所有，或者 reboot
mount -a
複製程式碼至此為止，硬碟就格式化完成了。
關於安裝硬碟，可以參考 https://help.ubuntu.com/community/InstallingANewHardDrive
配置 MySQL
篇幅有限，只簡介具體在 Ubuntu 16.04 上面 配置 MySQL 的 DataDIR ，省去安裝和基本登入認證的配置。
mysql 在 ubuntu 下面預設的路徑如下：
/var/lib/mysql/
複製程式碼我們開始配置 DataDIR
systemctl stop mysql
rsync -av /var/lib/mysql /mnt/volume-nyc1-01
mv /var/lib/mysql /var/lib/mysql.bak
vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 修改至 datadir=/mnt/volume-nyc1-01/mysql
vim /etc/apparmor.d/tunables/alias
# alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/
sudo systemctl restart apparmor
vim /usr/share/mysql/mysql-systemd-start
# 修改成
if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
echo "MySQL data dir not found at /var/lib/mysql. Please create one."
exit 1
fi
if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
echo "MySQL system database not found. Please run mysql_install_db tool."
exit 1
fi
# 接下來
sudo mkdir /var/lib/mysql/mysql -p
sudo systemctl restart mysql
# 最後 my.conf 修改相關檔案路徑
複製程式碼詳細請參考這篇文章 https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04
將 DataDIR 配置完成之後，就可以匯入資料了。嗯，經過這麼麻煩的事情之後，我決定下次遇到這種情況首選 Docker 而不是在 Ubuntu Server 上面搞這個。
站在現在看，如果重來的話，我肯定會用 Docker 然後把資料盤掛載到新硬碟到。
比如直接 Docker 命令執行
docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
複製程式碼匯入資料 之 MySQL + PV
我們使用 mysql 匯入指令碼的時候，有幾種匯入方式
- source 命令，然而這個命令容易在資料量很大的時候直接卡掉。（印象中是直接把 sql 檔案載入到記憶體中，然後執行，然而，只要涉及到大量文字列印出來並且執行，速度一定會變慢很多）
- mysql 命令
# mysql 命令的典型匯入場景就是這樣
mysql -uadmin -p123456 some_db < tb.sql
複製程式碼加上 PV 命令的話，比較神奇了。有進度條了！!
# 附加進度條的匯入場景
pv -i 1 -p -t -e ./xxxx_probe.sql | mysql -uadmin -p123456 some_db
複製程式碼然後，可以檢視一下磁碟 CPU 記憶體的佔用情況。如果負載（著重注意 IO，記憶體）還不夠滿，使用 tmux 多開幾個程式匯入資料。
因為每個 SQL 檔案對應的表不一樣，所以多開幾個程式批量 insert 的話並不會鎖表，這樣可以顯著提升匯入速度。
1.2 匯出資料
既然已經匯入了資料，為什麼需要匯出資料呢？
因為資料量比較大，需要進行初步清洗。而我們最後肯定使用 Pandas 進行分析，從區域網資料庫中讀取大量的資料的時候，pandas 速度會非常的慢（具體是因為網路傳輸速度？)。所以，為了後面分析省事，我批量匯出了資料，然後按照我的習慣進行了歸類。
在這個過程中，我還進行了一小部分的資料過濾，比如：
- 只選取對自己有用的行與列。
- 化整為零，拆分資料為最小單元的 CSV 檔案
只選取對自己有用的行與列
select col_a , col_b from some_table where Acondition and bcondition and col_c in (`xx`,`yy`,`zz`);
複製程式碼這裡面有一些值得注意的地方
- 儘量把簡單的判斷寫在左邊。
- 如果不是反覆查詢，則沒有必要建立索引。直接走全表，篩選出必要的資料存 CSV 即可。
儘量拆分資料為最小單元的 CSV 檔案
如果按照某類，某段時間進行拆分可以在分析的時候隨時取隨時分析那就進行拆分。
比如，某個大的 CSV 包含瓊瑤裡面各種人物情節地點的位置就可以拆分為：
201712_大明湖畔_夏雨荷_還珠格格_你還記得嗎.csv
201711_老街_可雲_情深深雨濛濛_誰來救我.csv
201710_屋子裡_雲帆_又見一簾幽夢_你的腿不及紫菱的愛情.csv
複製程式碼當我們需要取這坨資料的時候，可以直接 glob 一下，然後 sort, 接著二分查詢。就可以快速讀取這塊資料了。
1.3 校驗資料完備性
第三方給的資料多多少少會有這些或者那些的問題，一般情況下，可以通過檢查資料完備性來儘可能的減少資料的不靠譜性。
我習慣性在這樣的表裡面詳細記錄資料完備性的各種引數與進度。
比如：
- 資料的提供情況和實際情況
- 階段性的記錄條數和點位的統計值
- max，min，mean，median 用來避免異常值
- 如果是分年份，則必須要統計每一天的情況，否則也不知道資料的缺失程度。
0x02 分析階段
經過上一步處理，資料的檔案總大小大約從 1000GB (uncompressed) -> 30GB 左右 （拆分成若干個檔案 compressed) 。每個檔案大約是幾百兆。
2.1 效能要點 1：檔案系統
如果統計邏輯很簡單，但是數量多，首選使用讀取檔案。讀取檔案進行統計速度是非常快的。（人民幣玩家走開）
像 linux 裡面的 wc,grep,sort,uniq 在這種場景有時候也能用到。
但，注意，如果檔案特別大，一定要迭代器一個一個讀取。
2.2 效能要點 2：化整為零，map reduce filter
化整為零這個已經在上面的 1.2 節講過了。
map/reduce/filter 可以極大的減少程式碼。
collection 中有個 Counter , 在進行簡單程式碼統計的時候用起來可以極大的減少程式碼。
2.3 效能要點 3：程式池的兩種作用
我們都知道，當 用 Python 執行計算密集的任務時，可以考慮使用多程式來加速：
即為了加速計算，此為作用一。如下：
def per_item_calc(item):
df = pd.read.....
# complex calc
return result
with ProcessPoolExecutor(3) as pool:
result_items = pool.map(per_item_calc,all_tobe_calc_items)
reduce_results = ....
複製程式碼其實程式的銷燬本身就可以給我帶來第二個作用管理記憶體。
具體會在 2.6 中的 DataFrame 裡面解釋。
2.4 效能要點 4：List 和 Set , itertools
有 400 組 UUID 集合，每個列表數量在 1000000 左右，列表和列表之間重複部分並不是很大。我想拿到去重之後的所有 UUID，應該怎麼處理
在去重的時候，自然而然想到了使用集合來處理。
最初的做法是
list_of_uuid_set = [ set1 , set2 ... set400 ]
all_uuid_set = reduce(lambda x: x | y, list_of_uuid_set)
複製程式碼1 小時過去了。 突然之間，四下裡萬籟無聲。公司內外聚集數百之眾，竟不約而同的誰都沒有出聲，便有人想說話的，也為這寂靜的氣氛所懾，話到嘴邊都縮了回去。似乎硬碟的指示燈也熄滅了，發出輕柔異常的聲音。我心中忽想：
小師妹這時候不知在幹甚麼？臥槽，程式是不是又卡死了？
SSH 上去 htop 一下機器。發現實存和記憶體都滿了。直覺告訴我，CPython 的集合運算應該是挺耗記憶體的。
嗯，這怎麼行，試試用列表吧。列表佔用記憶體應該是比較小的。
def merge(list1,list2):
list1.append(list2)
return list1
list_of_uuid_list = [ list1 , list2 ... list400 ]
all_uuid_set = set(reduce(merge, list_of_uuid_list))
複製程式碼1 小時過去了。 我一拍大腿，道：
小師妹這時候不知在幹甚麼？臥槽，程式是不是又卡死了？
最後在 StackOverFlow 上找到了更好的解決方案。
list_of_uuid_list = [ list1 , list2 ... list400 ]
all_uuid_set = set(list(itertools.chain(*list_of_uuid_list)))
複製程式碼執行一下，5s 不到出了結果（注意，包含了 Set 去重）。
itertools 裡還有很多有趣的函式可以使用。
https://docs.python.org/3/library/itertools.html
2.5 效能要點 5：IPython 給效能帶來的影響
當我們在分析資料的時候，往往使用的是 IPython, 或者 Jupyter Notebook
但是，方便的同時，如果不加以注意的話，就會帶來一點點小問題。
比如下劃線和雙下劃線分別儲存上一個 CELL 的返回值，和上上個 CELL 的返回值。
2.6 效能要點 6：DataFrame 帶來的 GC 問題
DataFrame 是我用 Pandas 的原因，在這次使用 DataFrame 的過程中，還是出現一些頭疼的問題的。比如莫名的記憶體洩露。
def per_item_calc(item):
df = pd.read.....
# complex calc
return result
result_items = []
for item in all_tobe_calc_items:
result_items.append(per_item_calc(item))
reduce_results = ....
複製程式碼我在 For 迴圈中讀取 DataFrame 賦值給 df, 然後統計出一個結果。按理來說，每次只要一個簡單的 result, 每次讀取的檔案大小一致，同樣的會佔用接近 2G 記憶體，而，當我賦值 df 的時候，按理來說，應該是把原先 df 的引用數應該為 0, 會被 gc 掉，又釋放了 2G 記憶體，所以，是不太可能出現記憶體不夠用的。
執行程式，記憶體 biubiubiubiu 的增長，當進行到約第 1000 次的循壞的時候，直到 16G 記憶體佔滿。
那麼顯式的 del 一下會不會好一點呢？程式碼如下：
def per_item_calc(item):
df = pd.read.....
# complex calc
del df
return result
複製程式碼似乎好了一點點，但是其實並沒有好到哪裡去。
然而，和前一次一樣，記憶體 biubiubiubiu 的增長，當進行到約第 1000 次的循壞的時候，直到 16G 記憶體佔滿。
只是在讀取檔案的時候，預先減少了上次迴圈沒有 del 掉的 df. 和上一個想法沒有太大區別。除了比上一個方法每次讀取檔案的提前減少了一個多 G 的記憶體。
查詢相關資料，涉及到 Python 裡面的 Pandas GC 的資料並不多，稍微整理一下，如下：
Python 程式 在 Linux 或者 Mac 中，哪怕是 del 這個物件，Python 依舊
站著茅坑不拉屎就是不把記憶體還給系統，自己先佔著，有本事你打死我啊直到程式銷燬。
嗯？這個和我要的東西不一樣嘛？具體怎麼管理 pandas 裡面的 object 的，到底是哪裡 GC 不到位呢？還是沒有說呀。
參考：
- https://stackoverflow.com/questions/23183958/python-memory-management-dictionary
- http://effbot.org/pyfaq/why-doesnt-python-release-the-memory-when-i-delete-a-large-object.htm
不過有一點啟示了我。
直到程式銷燬。
Python 裡面不是有個 ProcessPoolExecutor 模組麼。
那麼問題來了，ProcessPoolExecutor 是動態建立程式並且分配任務的呢，為每一個 item 分配一個程式來運算？還是建立完三個程式之後把 item 分配給空閒程式的進行運算呢？
- 如果是後者，則是正經的程式池。似乎 map 過去，除非任務執行完畢或者異常退出，否則程式不銷燬。並不能給我們解決 記憶體洩露 的問題。
- 如果是前者，則是並不是執行緒池，但是可以幫我解決記憶體洩露的問題。
你說，程式池肯定是前者咯。可是你在驗證之前，這是程式池只是你的從其他語言帶來的想法，這是不是一個執行緒池，是一個什麼樣子的程式池，如果程式執行過程中掛掉了，這個時候就少了一個執行緒，會不會再補充一個程式呢？？
怎麼看驗證呢？
- 執行程式，進入 Htop 看程式 PID
- 看原始碼
# https://github.com/python/cpython/blob/3.6/Lib/concurrent/futures/process.py#L440
def _adjust_process_count(self):
for _ in range(len(self._processes), self._max_workers):
p = multiprocessing.Process(
target=_process_worker,
args=(self._call_queue,
self._result_queue))
p.start()
self._processes[p.pid] = p
複製程式碼從原始碼得出在主執行緒建立了管理程式的執行緒，管理程式的執行緒建立了 max_workers 個程式（在我的例子裡面就只有 3 個 worker).
是個程式池。
好，如果是程式池，似乎 map 過去，除非任務執行完畢或者異常退出，否則程式不銷燬。並不能給我們解決 記憶體洩露 的問題。
等等，如果用多程式池不就好咯？
def per_item_calc(item):
df = pd.read.....
# complex calc
return result
result_items = []
step = 300
for idx in range(0,len(all_tobe_calc_items),step):
pieces_tobe_calc_items = all_tobe_calc_items[idx:idx+step]
with ProcessPoolExecutor(3) as pool:
pieces_result_items = pool.map(per_item_calc,pieces_tobe_calc_items)
result_items.append(pieces_result_items)
reduce_results = list(itertools.chain(*result_items))
複製程式碼當然，這是一種讓作業系統幫我 GC 的方法。即 Python 不能幫我 GC 的，作業系統幫我 GC
PS: 其實用 multiprocessing 模組也行，只是執行緒池可以稍微控制一下程式建立的數量。
總結一下，對於大量的 DataFrame 處理：
- 多個程式池是一種處理的方式。
- 儘量減少 DataFrame 的數量
- 儘量減少賦值導致的 COPY, 修改時帶上 inplace=True
- 讀取 CSV 的時候指定相關列的型別 {‘col_a’: np.float64, ‘col_b’: np.int32}，否則 pandas 會產生大量的 object
0xDD 番外篇
在分析這次的資料過程中，自己的 Mac 主機板也壞掉了，幸好還在保修期，送到蘋果店維修了一下。給蘋果的售後點個贊。
0xEE 更新
- 2017-12-07 初始化本文
- 2017-12-16 增加分析階段的文字
- 2017-12-26 去掉一些 TODO, 釋出到我的小站
- 2017-12-31 正式釋出