COSCUP 2015 Hands-on MySQL 入門 by gslin 筆記

gistfile1.txt

https://github.com/gslin/coscup2015-mysql-hands-on

開場

# Transaction 
## 原子不可分割性
要碼全部成功不然就全部失敗 - 銀行轉帳 (林益世 v.s. 陳啟祥)


持久性 (D)
資料庫告訴你成功後，資料就不會掉 (就算當機後恢復也要不會掉）
jono files system:  ext4 / xfs
設 transaction log
各種 write base 機制
只要一 commit 就要寫一份資料到硬碟

除了 EC 做到完整 ACID 以外，一般都會做 trade-off 妥協
A 基本都會做到

通常 dirty read 有最少 lock, 效能比較好

在 MySQL   read committed 跟 read uncommitted 其實各有千秋


unrepeatable read
多讀幾次無法保證一樣    一般 MySQL 會設到最低等級的 level

repeatable read  會遇到幻讀 phantom read 問題
->  T1 讀取範圍資料， 3 筆
T2 寫入符合該範圍資料
T1 變成讀到4筆
如果不能接受幻讀，要開到最高 isolation level - serializable

DK 個人建議，新的系統，平常放到最低
MySQL 放到 read commited



MyISAM 不保證原子性
更新 100 萬筆，中途掛掉，修好 table 後，發現只改到 50 萬筆
->沒有所謂用 engine 什麼比較好

# MySQL 最常見的 ACID 方案 - InnoDB
講古 - 2005 Innobase 被 Oracle 買
2008 MySQL 被 Sun 買
2010 Sun 被 Oracle 買

MySQL 名稱：創辦人女兒名字 (My)
第二個女兒名字：Maria

當你爆炸的時候你什麼都願意做 XDD

MyISAM 最大問題是不 crash safe 當機後會掉資料
MariaDB 試著在 MyISAM 做 crash safe 的工具

MySQL 你會用，他就是成熟軟體

YouTube / Flickr backend 還是 MySQL
對於不會用的人來說就會牽扯到 support 問題

如果錢很多就先用 Percona 寫信去問
如果錢不多，還是用 Percona ..
MySQL standard 的也 okay

如果需要 MyISAM 那麼快的讀取速度又要 crash safe，MariaDB 可能就是你的選擇

B+ Tree 重點
一個節點很多資料  很多個節點互相指來指去
 
平衡二元樹：紅黑樹 , AVL tree
實作上簡單很多，但為什麼資料庫很少看到這兩種？

B+ Tree 特性
Linear Order 裡面所有資料都是有順序的，任何兩個相比都可以
->任何一串要馬就是小，不然就是大  
很重要 很重要 很重要
Linear Order
Linear Order
Linear Order


定義 Index 就是定義 Order


為什麼不使用 Binary search Tree
深度差異與 Randome seek 次數

20層 100萬
硬碟一個 block 以前 512 byte現在是 4k

什麼事都沒做，光查一個值就要 0.1s  所以不可能用 紅黑樹跟 AVL tree




PK 
Seconday index

在 InnoDB 強烈建議 避免 Compound PK  (康乓 PK)   只用數字而且單一欄位 
-> 如果可以用 INT 而不用 BIGINT 就用 INT (越小越好)

InnoDB 特性是 Clusterd index
* 我的資料在硬碟裡放的順序就是 PK index
包含 Range Search 都很快

如果不是這樣實作，兩百筆資料可能就得用兩百個 page 抓 很慢


設一個 secondary index 年齡
查 age= 33  -> 指到 PK=1 講師   所以要再找一次，慢一點點

InnoDB 永遠都有 PK，如果沒有，他會自己去找一個數字欄位來用

舉個不好例子，用身分證字號當 PK，10 bytes
age 33 對應到 F125xxx
order 會 order 在 33  

我想要找 age = 33  

“你想不到的方法電腦就做不到"

secondary index
* UNIQUE key，可以 NULL，如果不是 NULL 就不能重複，NULL 不理他
* SQL Server 不是這樣實作，NULL 只能存一筆
UNIQUE key 對資料庫寫入效能很傷，得先檢查有沒有存在
如果很多人寫入就有 LOCK 問題 （因為需要確保唯一)

有多少欄位就設多少個 index :   (col_a)  (col_b)
如果找 where a= 123 And b =456
MySQL 作法會個別找一次再做聯集/交集

(col_a, col_b, col_c) 意義是什麼：
如果 a 不一樣就拿 a 比較就好 -> 依此類推  線性關係
在硬碟上會是循序存放，就會很快

Innodb :  PK 跟 phsycal 順序是一樣的

 依照需求而設計的是正確 Index
* 非常商業導向的東西 XD

InnoDB 好處是 寫不卡讀，讀不卡寫
如果可接受很慢就不必加 index

發問：PK 也是一個 Cluster Index


# HA

1. Master + Slave
MySQL 3.23 好像就有了   
Async Repliacation 非同步複製
所有操作 master 做的時候，有空的時候往 slave 丟

Log:   STATEMENT 與 ROW
STATMENT: 記錄 SQL 本身
ROW: 記錄變更本身

e.g. DLEETE FROM TBL 
STATMENET: 記錄 SQL ，送到 Slave
ROW: 本來有 200 筆，跟 Slave 講有哪兩百筆不見.

做多台 Master 架構時會需要 ROW 
PXC 強迫一定要用 ROW

好處是對資料一致性確保
壞處是 size 大很多

假設一開始兩邊不同步，用 STATEMENT 兩邊會一樣
用 ROW 會傳說本來 200 筆試什麼樣子，變成另一個樣子 0
有錯誤的話可以人為介入找原因

傳統 Master / Slave 架構缺點：Replication Lag
S 速度可能沒 M 快，會 Lag
mytop 會看到 lag 幾秒
解法之一：多 master，沒有 lag 問題

ACI (D) 特性 -  commit 後確保資料不會掉，容許 crash   (Crash-safe)


# DRBD + Heartbeat (兩個不同軟體)
後者 歷史悠久到用下一代產品 pasmaker
ubuntu 一裝好就有
DRBD ： RAID 1 走網路
複製是 block level 一模一樣

Heartbeat 偵測機制


Floating IP (Virtual IP)
當 hearbeat 偵測到那個 IP 死掉後，另一個去搶他的 IP


Q&A
Q: MySQL 是否有支援 NOSQL ?
A:MySQL 5.6 有支援 memcached 
Q2: JSON format?
A2: 很殘廢 ;   pgSQL 強在可以 index 可以指向月份/星座
pgSQL 9.4 以後才好用，比 MongoDB 快很多


Q3: MySQL Cluster 很慢
A3: 這是完全新的東西
大家的評價都差不多，能用而已 效能不好  目前 Oracle 沒有做太多動作
如果效能是重要考量，不建議用 MySQL Cluster

Q4: 現在是否還會有讀寫不同步問題
A4: KKBOX  PXC 最大是10台   寫入時去另外9台確認是否成功   ACCPET 後開始讓各台 commit 進去
Garaia CLuster 可以設定等到全部機器做完才 return 回來
就可以 100% 確認不會因為讀寫分離造成不一致

Q5: 記錄 Log 的 Table，依月份切分可否提昇讀取速度
A5: 確認需求是否有即時讀取 Log 的需求?  如果沒有根本不需要 MySQL

Q6: MySQL sharding 有無建議自動化方案
A6: 不應該有自動化方案，如果該問題擺了二十年沒人解掉就表示不適合解
FB 用很深，直接把 MySQL 當 stoage，只用 PK，不太用 其他 index
所以 FB PK ID 超長


VirtualBox License 很有趣，只要是”真人"去安裝就是個人用途

======================================================================================================

# 下半場實作
VirtualBox 開三台機器
要手動切硬碟
1G 給根目錄 / Ext4
剩下的 
SWAP  128M

除第一張網路卡 NAT 以外，再加第二張網路卡 “內部網路” ，設定混合模式 => 允許全部

ifconfig -a  看 eth1 沒 IP 是正常的，待會設定


於機器 db1、db2 裝 drbd 工具包
$ apt-get install drbd8-utils
會先設定 drbd 再設 heatbeat

Ubuntu 慣例 /srv 放 service
把整個 /srv 目錄丟給 drbd，MySQL 用到的通通放進去就可以 HA


fdish /dev/sda

以下看不太懂，順序有點亂 ...
n
l
n
p
value 3

apt-get install  gparted


Q: 為什麼不事先設定硬碟？
A:
先買兩個便宜的 SATA 硬碟當系統碟
接下來會買八個比較好的硬碟當資料碟 (/srv)
所以這邊會另外設定

global_common 用途：DRBD 可能不只一組，只是我們範例較單純

Protool A 的效能最好，C 的效能最差   A 非同步
C 最安全，每台都寫成功時才 return
一般使用 case 都選 C，不用怕掉任何資料

@ db 1 server
inerfaces 檔

auto eth1
iface eth1 inet static
      address 192.168.50.101
     netmask 255.255.255.0
     broadcast 192.168.50.255

設完後 /etc/init.d/networking restart （或是重開機）

#  cat /proc/drbd
兩邊都說他自己是 secondary
ping 對方機器試試看，確認連通
可是沒有人認為自己是 primary

步驟 0
drbdadm create-md r0

使用drbd primary 指令強制某一台變 primary

# drbdadm — —overwrite-data-of-peer primary r0
# drbdadm primary —force r0

(以上這段 DK 太久沒用卡住, 查了 KKBOX wiki，但指令太多不太好記錄)

db2 server 要做步驟0、步驟1即可：
step 0: create-md r0
step 1: up r0

完成後，於任db1機器手動數次 cat /proc/drbd  可以看到同步進度增加 sync: xx%

mkfs.ext4 /dev/drbd0 做 format 

sync 跑完後可以在 cat /proc/drbd 看到 “UpToDate"
機器一邊 primary，一邊 secondary

示範 db2 變成 primary
於 db1
# drbdadm down r0

於 db2
可以看到對方db1狀態變 Unknown
# drbdadm primary r0

在 db1 重起後可以看到 primary 被搶走，自己變成 secondary

有些情況兩台都是 secondary，但資料是 up to date，that’s fine  資料是 OK 的
可以手動介入把其中一台變成 primary 即可，不用讓 heartbeat 介入

在 db2 server
mount /dev/drbd0 /srv     把 drbd 掛到 /srv
新增一個 coscup.txt
unmount /srv
drbdadm secondary r0  

於 db1 看到 coscup.txt

在資料庫這件事不需要帶一個 acess time (讀資料時不需要再寫一個讀取時間)
於 db1

--------vim /etc/fstab-----------
/dev/drbd0  /srv    ext4    rw,noatime,nodiratime,noauto  
————end vim-----------
(rw: 讀寫都要同步)
(noauto: 開機不要掛他, 給heatbeat 管)


裝 Percona server
https://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html
依步驟安裝 key 並 apt-get update

上述完成後：
# apt-get install percona-server-server-5.6

故意把兩台機器的 mysql user uid與gid 設成一樣  習慣上用 3306
vim /etc/shadow
vim /etc/group

mv /var/lib/mysql   /srv/mysql  整個目錄搬到 /srv/mysql

ln -s /srv/mysql      /var/lib 下的 mysql link 到 /srv/ 下的 mysql

因為我們的 innodb 有 tune 過，所以需要砍掉預設的 ib系列檔案
rm ibdata1  (ib系列的檔案)  

my.conf 放哪裡有兩派說法：
(1) 兩台自己分開設
(2) 都放在 /srv/mysql 同步

DK 推薦一，彈性較大
假設實體機器二故障，換了一台，設備買不到規格  
希望硬體較好的機器平常是 active，用比較好的參數跑它

InnoDB 參數建議去網路上查
幾個重點
* innodb_buffer_pool_size 12G 要改： 看經驗， 這裡示範只設 256M
* transaction_isolation   改成 READ-COMMITTED，隔離層級筆預設低一點
* innodb_log_file_size 也開 16M
* innodb_data_file_path 也開 16M

*** MySQL 跑不起來，debug 一段時間 ***
tmp 目錄也要能共用，原因是 failover 時裡面的資訊也會是有意義的
tmpdir = /srv/tmp
chmod 1777 tmp

重開後成功進 mysql，停止後 umount /srv ，並讓出 DRBD

接著開始設定另一台機器的 Percona ，例如 mysql user uid/gid
copy my.cnf
試著跑起來，然後 fail 掉
重新在機器 db2 link mysql 目錄
還是 fail
cat db2.error 找原因
* 沒辦法 bind socket
   ps ax | grep 3306  mysql 沒有跑起來

mysqld.lock 的檔案權限可能不對，chown mysql mysqld
that’s OK

習慣上讓每一台 debian.cnf 的帳密相同，以後 mysql status 就沒有問題


# heartbeat 是設定最複雜的一塊
1. 有一把 authkeys 要認證  放一個隨機字串進去即可
2. hareosurces   裡面設定多個資源，例如第一件事是寄信
若是機器要下掉，上述的多個資源會依照相反順序做，例如先讓出 IP .. 最後寄信 blah

場地示範無法寄信，拿掉
-----
#
db1 drbddisk::r0 Filesystem::/dev/drbd0::/srv::ext4::noatime,nodiratime mysql 192.168.50.104
--------

## ha.cf
* keep alive兩秒檢查一次還有沒有活著 
* deadtime10 秒沒回應當做死了
* initdead  有一些 case 兩台都死掉，例如機房跳電
開機的時候等 20 秒，確認有無問題 不會馬上搶
* auto_failback 設 off 的意思是當 db1 掛掉跳到 db2，等 db1 回來後要不要再跳回去
通常業界慣例是 off，如果設成 on 一回來就搶資源，結果又可能當掉導致服務不穩
一般是手動確認再跳回 db1 (policy 上的 primary)
* ucast 打的機器，對方是哪個 IP

ls -l /etc/heartbeat
每一個 resource 對應一支程式，mysql 要另外裝，兩台機器都要，且 chmod 755

驗收，把兩台的 heartbeat 跑起來
看 Log   # tail -f /var/log/ha.log

實戰 db1 reboot

到 db2 看 ha.log
開始收到資訊，db1死了，db2接手

在 db1用 telnet db2 IP + 3306 port  檢查 failover 到 db2


Q1: heartbeat 是不是沒有官方網站文件可以參考？
A1: 會建議你換成 pasmaker；我推薦它還是因為它非常單純 ； KKBOX 還有用在 HA proxy
pasmaker 最大好處是 multi-node；能解決問題就是好工具，如果在意 down time 可以去估算出來，做到幾個 9

Q2: 這樣的設定會不會碰到 split brain 問題?
A2: 會，最直接狀況是先把 db2 關閉，人工確認後再重新開起來

Q3: 效能跟 dual master 配 hearbeat
A3:  Dual-master 問題是資料不一致，其實 DRBD-heartbeat 很浪費，但如果你不太在意預算就無所謂
* Mobile01 全站搜尋用 LIKE，卡三秒 (MyISAM 讀卡寫，寫卡讀)

Q4: 如果兩台用 Galera Cluster / MariaDB … (聽不清楚)
A4: 我們內部 Heartbeat 只做死掉的時候把 floating IP 讓出去這件事情
(LIVE DEMO 連 KKBOX kdb)

Q5: standby 在切換的時候怎麼解決 MySQL warm up 問題
A5: DRBD  沒得解；如果凌晨四點死掉，說不定切過去馬上 OK，如果晚上十點說不定要 15mins 熱機