Joe Horn 的啟示錄

我利用前兩天的空閒時間寫了個 PHP 的小程式，運用 PHP 的 PDO 元件，對 MySQL 進行小測試。

Server 作業系統與硬體：

• 作業系統：FreeBSD 8.0-RELEASE
• CPU：Intel(R) Pentium(R) 4 CPU 3.00GHz (3042.62-MHz 686-class CPU)，Hyper-Threading 開啟
• RAM：2G DDR2 800
• 放資料庫的 HDD：WDC WD800JB-00JJA0，UDMA 100

MySQL 5.1.44 的設定（/etc/my.cnf）：

# defult setting (maybe changed)
key_buffer_size = 128M
max_allowed_packet = 4M
table_open_cache = 4096
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# added for tunning by Joe Horn
skip-name-resolve
connect_timeout = 60
join_buffer_size = 1M
max_connect_errors = 10000
max_connections = 100
max_heap_table_size = 1G
query_cache_size = 16M
slave_net_timeout = 30
sync_binlog=1
thread_cache_size = 512
thread_concurrency = 8
tmp_table_size = 1G
table_definition_cache = 512
# choose one ( depend on query amount )
#concurrent_insert=2
low_priority_updates=1

這台機器跑 Super Smack 的結果：

# super-smack -d mysql update-select.smack 80 1000
Query Barrel Report for client smacker
connect: max=14ms  min=1ms avg= 4ms from 80 clients
Query_type      num_queries  max_time  min_time  q_per_s
select_index    80000        101       6         717.91
update_index    80000        108       1         717.91

我用測試程式產生了一百萬筆資料的 MyISAM table，各種測試各循環 1000 次，產生如下的測試結果（後面的數字單位是秒）：

SELECT BY PK_COL WITH_QUERY_CACHE:              0.058
SELECT BY PK_COL WITHOUT_QUERY_CACHE:           0.062
SELECT BY PK_COL LIMIT WITH_QUERY_CACHE:        0.058
SELECT BY PK_COL LIMIT WITHOUT_QUERY_CACHE:     0.062
SELECT BY UNIQUE_COL WITH_QUERY_CACHE:          0.058
SELECT BY UNIQUE_COL WITHOUT_QUERY_CACHE:       0.062
SELECT BY UNIQUE_COL LIMIT WITH_QUERY_CACHE:    0.058
SELECT BY UNIQUE_COL LIMIT WITHOUT_QUERY_CACHE: 0.062
SELECT BY INDEX_COL WITH_QUERY_CACHE:           0.058
SELECT BY INDEX_COL WITHOUT_QUERY_CACHE:        0.061
SELECT BY INDEX_COL LIMIT WITH_QUERY_CACHE:     0.058
SELECT BY INDEX_COL LIMIT WITHOUT_QUERY_CACHE:  0.063
SELECT BY COL WITH_QUERY_CACHE:                 0.786
SELECT BY COL WITHOUT_QUERY_CACHE:              0.063
SELECT BY COL LIMIT WITH_QUERY_CACHE:           0.059
SELECT BY COL LIMIT WITHOUT_QUERY_CACHE:        0.062
UPDATE BY PK_COL WITH_QUERY_CACHE:              1.139
UPDATE BY PK_COL WITHOUT_QUERY_CACHE:           1.199
UPDATE BY PK_COL LIMIT WITH_QUERY_CACHE:        0.125
UPDATE BY PK_COL LIMIT WITHOUT_QUERY_CACHE:     0.142
UPDATE BY UNIQUE_COL WITH_QUERY_CACHE:          2.734
UPDATE BY UNIQUE_COL WITHOUT_QUERY_CACHE:       1.203
UPDATE BY UNIQUE_COL LIMIT WITH_QUERY_CACHE:    0.147
UPDATE BY UNIQUE_COL LIMIT WITHOUT_QUERY_CACHE: 0.163
UPDATE BY INDEX_COL WITH_QUERY_CACHE:           1.183
UPDATE BY INDEX_COL WITHOUT_QUERY_CACHE:        1.063
UPDATE BY INDEX_COL LIMIT WITH_QUERY_CACHE:     0.138
UPDATE BY INDEX_COL LIMIT WITHOUT_QUERY_CACHE:  0.154
UPDATE BY COL WITH_QUERY_CACHE:                 4704.859
UPDATE BY COL WITHOUT_QUERY_CACHE:              4641.191
UPDATE BY COL LIMIT WITH_QUERY_CACHE:           0.156
UPDATE BY COL LIMIT WITHOUT_QUERY_CACHE:        0.167

根據測試結果，大概可以看到出以下幾個要點：

1. 根據第 21 行與 22 行的差異看來，MySQL Query cache 還是有點用處，不過效用不大。
2. 根據第 13 行與第 29 行的結果看來，有沒有設定 Primary Key、UNIQUE、INDEX 的影響不小。
3. 根據 UPDATE 語法的測試結果看來，有沒有 LIMIT 頗重要。

Technorati Tags: FreeBSD, INDEX, LIMIT, MySQL, PDO, PHP, Primary Key, Query Cache, Super Smack, UNIQUE

幾天前，跟 Solaris 叔叔 談到 MySQL 的 ENUM，回家後上網找了些資料，剛好找出了一些使用 MySQL 時，與效能有關的 tips。

稍微整理過後，放在這裡：

• 善用 EXPLAIN SELECT 分析 SQL statement，以下列舉狀態解讀：
  

  EQ_REF	一對一比對
  REF	一對多比對
  RANGE	特定範圍的資料會被傳回
  INDEX	使用 INDEX 裡的資料
  ALL	對整個 TABLE 作掃瞄（最差）

• 善用 INDEX，以這個 SQL statement 為例：

  SELECT `col_a`
  FROM `table_a` LEFT JOIN `table_b`
  ON `table_a`.`col_b` = `table_b`.`col_c`
  WHERE `col_d` = ....;
  
  -- SELECT 的欄位不需 INDEX，ex： `col_a`。
  -- JOIN 的欄位需要 INDEX，ex： `table_a`.`col_b` 與 `table_b`.`col_c`。
  -- WHERE 的欄位需要 INDEX，ex： `col_d`。
  

• 儘量使用固定大小的欄位，MyISAM 搜尋固定大小欄位比較快；也就是說，能用 CHAR 就別用 VARCHAR。
• 儘量將欄位指定為 NOT NULL。
• ENUM 格式的欄位，資料處理速度很快（實測結果在這）。
• 不要取不需要的資料，下面這種語法就是不好的例子：

  SELECT * ...

• 把 BLOB 與 TEXT 拆開，減少 MySQL 開啟單一大檔（table 資料檔案過大）的機會。
• JOIN 用的欄位資料格式最好是相同的，以免 MySQL 作 FULL TABLE SCAN。
• 使用 LIKE 時，避免把 % 放在字串開頭，以免 INDEX 利用率不佳。

  -- 下列這種語法無法利用 INDEX
  SELECT ... WHERE `col` LIKE '%string%';
  
  -- 下列這種語法會利用 INDEX
  SELECT ... WHERE `col` LIKE 'string%';

• 注意 WHERE 子句內的運算式。

  -- 下列這種語法會使用 INDEX，速度快。
  SELECT ... WHERE `col` < 100 / 10;
  
  -- 下列這種語法會導致 FULL TABLE SCAN，速度慢。
  SELECT ... WHERE `col` * 10 < 100;

Technorati Tags: BLOB, CHAR, ENUM, EXPLAIN, INDEX, MyISAM, MySQL, NOT NULL, performance, TEXT, VARCHAR

幾個小時前，PTT 的 Database 板 又有人問到 MySQL 的 "SELECT ... LIMIT m,n" 相關問題。
其實，在大多數的網頁系統中，"SELECT ... LIMIT m,n" 應該是一定要用的語法，尤其是在使用者驗證時。

簡單舉例，MySQL 的 "SELECT ..." 語法運作可說是拿著籃子去裝東西，若以 LIMIT 語法限制籃子裝東西的數量，MySQL 會在籃子裝滿後就停止動作，並傳回結果。
在網頁系統中，因為使用者帳號都是獨立的，所以，進行使用者登入驗證時，只需要抓出一筆資料來進行比對。

因此，在網頁系統中，我時常用這種語法來進行使用者登入的驗證：

SELECT `使用者身份` FROM `users` WHERE `username`='使用者名稱' AND `password`='雜湊後的密碼' LIMIT 1;

如果找不到資料，代表使用者的帳號/密碼錯誤。
而直接把密碼加入 WHERE 子句，不挑出來用程式比對則是為了杜絕 SQL Injection 導致的密碼外洩。

另外，這樣作也不錯：

SELECT `使用者身份` FROM `users` WHERE `username`='使用者名稱' AND `password`=PASSWORD( '雜湊前或雜湊後的密碼' ) LIMIT 1;

Technorati Tags: MySQL, performance

除了已知的效能差異（以前用 Ubench 測過的，gaod 長輩也貢獻了一些資訊，都 紀錄在這）、預設支援的記憶體容量之外，這兩天又找出另外一項。

某台機器跑 FreeBSD i386 platform，搭配 PAE 與 SMP。
根據以往的經驗，這種記憶體容量超過 4G 的機器，我會選 amd64 platform。
（至少經手過的幾台機器到現在都很健康...）

近來這台機器的 MySQL daemon process 時常會掛掉...
追蹤 error log 之後，發現這種錯誤訊息：

Out of memory (Needed 16391 bytes)

我把錯誤訊息丟上 Google 後，才發現 FreeBSD i386 platform 版本，其預設之單 process 記憶體容量限制為 512 MB，而 MySQL daemon process 使用超過 512 MB 後就會噴出上述的錯誤訊息，接著就是嗝屁...

我沒碰過這種瓶頸，似乎是 amd64 platform 沒有這種限制。
i386 platform 遭遇到這種瓶頸的解法是在 /boot/loader.conf 裡面丟進類似這三行：

kern.maxdsiz="2G"
kern.dfldsiz="2G"
kern.maxssiz="256M"

（拉高單 process 的記憶體容量限制，變為 2G）
然後 reboot，準備驗收成果...

Technorati Tags: amd64, FreeBSD, i386, MySQL

幾個月前，我幫人家建置了一套網頁系統，用以觀察網站瀏覽趨勢。
想當然爾，這種系統勢必會對資料庫產生大量 INSERT 或 UPDATE 的 query，而且會保留大量歷史性資料。

為了使系統能快速存取後端資料庫，我將存放 raw data 資料表的 storage engine 設定為 InnoDB。
（因為 InnoDB 支援了 row lock。 ）
並搭配定時執行的 script 整理資料，進行統計，並存放在 MyISAM 格式的資料表中。

最近，這套系統的後端資料庫伺服器，mysqld 三不五時會自行 restart，系統狀態也看似正常（根據他們的說法）。
追蹤後，發現 InnoDB 的運作狀態被忽略了...

光是其中一塊放置 raw data 的資料表就存放了三百多萬筆資料，佔用了 3xx MB 的空間，可是 innodb_buffer_pool_size 只有 256 MB，連 mysqldump 都無法完全撈出這塊資料表的內容...
追查程式後，發現原本用以刪除過期 raw data 的那些程式碼片段都被註解掉..

這段故事給我們幾個啟示：

• InnoDB 拿來放 raw data 真的不錯，但是要定時整理，否則維護難度會提高...
• 沒事別雞婆，胡亂更改人家的設計...

Technorati Tags: InnoDB, MySQL

我在 之前的文章 提過 MySQL 裡面，協助我們處理時間資料的 functions，現在來講一下該注意的事。

可能很多人還沒發現到 Year 2038 Problem 。
2007 年 12 月，我在開發某個網頁系統時碰上了這個問題。

在 PHP5 裡面， mktime(11, 14, 7, 1, 19, 2038) 傳回 2147483647，mktime(11, 14, 8, 1, 19, 2038) 卻丟出空白值，而 2147483647 正好是 C 語言中，signed long int 的最大值。
而且，MySQL 的 UNIX timestamp 也有這種問題，以下這串語法傳回來的數值是 0 。

SELECT UNIX_TIMESTAMP( NOW( ) + INTERVAL 50 YEAR );

目前，就我測試過的部份，PHP4 與 JAVA 都沒有這種問題。
可是，我們不知道 PHP4 開發出來的系統，會不會被拿到 PHP5 上面跑。
所以，在 MySQL 裡面使用 datetime 或 timestamp 來紀錄時間資料，並搭配處理時間資料的 functions 來處理時間型資料，不僅方便我們辨識時間，也可以省去不必要的麻煩。

有些人可能會覺得，使用 datetime 或 timestamp 來紀錄時間資料，取出來的資料型態是字串，如果只需要年、月、日，就得作字串分割。
其實，MySQL 的 DATE_FORMAT 就足以應付這種狀況了。

最後，用 MySQL 處理 datetime 或 timestamp 型態的資料，請多注意一件事：

SELECT '2008-01-31 00:00:00' + INTERVAL 1 MONTH;

其結果為 2008-02-29 00:00:00。

SELECT '2008-01-31 00:00:00' + INTERVAL 30 DAY;

其結果則是 2008-03-01 00:00:00。

以上，我的報告完畢，謝謝收看。

Technorati Tags: MySQL

一、兩年以前，我會直接在 MySQL 中，以 varchar 型態，直接把 IP address 存入。

直到我在某次進行系統開發時，翻過 MySQL 的 Operator and Function Reference ，才發現這兩個好用的 functions ：

• INET_ATON()
• INET_NTOA()

透過這兩個 functions ，我開始用 unsigned int 型態來存 IP address。

跟以往的 varchar 比較，使用 unsigned int ，搭配這兩個 functions 有著以下這兩項優勢：

1. 資料型態設定成 unsigned int ，實體儲存空間僅需要 4 bytes 。
2. 進行 IP range 的比對與判斷更方便。

尤其是第二項，以往使用 varchar 型態儲存 IP address 時，我必須先取出資料，再使用字串處理函式，甚至是正規表示式對字串進行切割。

而今，運用 INET_ATON() ，把原本的 IP address 轉成數字存成 unsigned int 之後，我只要把區段的頭、尾 IP address 用 INET_ATON() 轉換成數字，就可以利用大於、小於，甚至是 MySQL 的 between 來作比對或判斷了。

如果遇到的是以 CIDR 表示的區段，使用 PHP 開發程式或系統的人也可以選用 Pear 的 Net_IPv4 套件，呼叫 parseAddress() ，取得該區段的頭、尾 IP 等資訊，再運用 INET_ATON() ，一樣可以利用大於、小於、MySQL 的 between 來作比對或判斷。

Technorati Tags: IP address, MySQL

以前，我會習慣用 PHP 的 date() 把時間字串整理好再放進資料庫，不然就是直接存 UNIX timestamp 。
直到前一陣子，我才發現 MySQL 有些巧妙的函式，方便我們處理理時間型態的資料。

如果在 TABLE_NAME 中，有個名為 TIME_COL 的欄位，其型態為 (unsigned) int，儲存的資料為 UNIX timestamp 。
那麼，在 MySQL 中，有些好用的 function 可供我們進行資料的操作：

• 新增一筆資料，其 TIME_COL 為現在時間：

  INSERT INTO `TABLE_NAME` (`TIME_COL`) VALUES ( UNIX_TIMESTAMP( NOW() ) );

• 這串 SQL 語法可以找出 TIME_COL 所紀錄的時間在五分鐘內的資料：

  SELECT * FROM `TABLE_NAME` WHERE `TIME_COL` > UNIX_TIMESTAMP( NOW() - INTERVAL 5 MINUTE );

• 這串 SQL 語法可以讓所有資料的 TIME_COL 值往後延 2 個月：

  UPDATE `TABLE_NAME` SET `TIME_COL`=UNIX_TIMESTAMP( FROM_UNIXTIME(`TIME_COL`, '%Y-%m-%d %H:%i:%s') + INTERVAL 2 MONTH ) WHERE 1;

如果 TIME_COL 欄位的型態為 datetime 或 timestamp，事情處理起來會更容易：

• 新增一筆資料，其 TIME_COL 為現在時間：

  INSERT INTO `TABLE_NAME` (`TIME_COL`) VALUES ( NOW() );

• 這串 SQL 語法可以找出 TIME_COL 所紀錄的時間在五分鐘內的資料：

  SELECT * FROM `TABLE_NAME` WHERE `TIME_COL` > NOW() - INTERVAL 5 MINUTE;

• 這串 SQL 語法可以讓所有資料的 TIME_COL 值往後延 2 個月：

  UPDATE `TABLE_NAME` SET `TIME_COL`=(`TIME_COL` + INTERVAL 2 MONTH) WHERE 1;

雖然 datetime 跟 timestamp 的資料顯示出來都是一樣的，但兩者是不同的，詳情可以參考 MySQL 官方手冊對 timestamp 資料型態的說明 。

Technorati Tags: MySQL

剛剛看機器狀態發現到的。

都在 my.cnf 開好了 query cache ， phpMyAdmin 的伺服器狀態頁也正常無誤。

mytop 搭配 MySQL 5.0 看不到 query cache 的 status 。
相反地，在 4.0 上面卻能夠顯示出來。

4.0 的訊息：

MySQL on localhost (4.0.27-log)                          up 0+00:14:48 [03:14:48]
 Queries: 108.3k  qps:  125 Slow:     0.0         Se/In/Up/De(%):    92/00/01/00
             qps now:  169 Slow qps: 0.0  Threads:   15 (   2/   2) 97/01/00/01
 Cache Hits: 90.3k Hits/s: 104.1 Hits now: 157.6 Ratio: 90.5% Ratio now: 96.2%
 Key Efficiency: 98.6%  Bps in/out: 24.9k/51.6k   Now in/out: 41.7k/18.8k

5.0 的訊息：

MySQL on localhost (5.0.27-log)                         up 0+07:19:32 [03:14:53]
 Queries: 976.9k  qps:   38 Slow:     0.0         Se/In/Up/De(%):    57/00/00/00
             qps now:    0 Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.1/ 19.4   Now in/out:   8.4/ 1.2k

好詭異啊......

Technorati Tags: MySQL, mytop