2012年9月9日

PHP 與 MySQL 資料庫存取效能調校筆記

通常以 LAMP(Linux+Apache+MySQL+PHP)基礎建構的網站系統,都會有不錯的效能,因此開發者即使忽略掉一些影響效能的設計細節,在網站上線的初期都不會察覺問題。但是少許嚴重影響效能的未爆彈,很可能在瀏覽量、資料量變大的時候,就會有明顯的效能問題浮現。

最近著手一項舊系統調校的任務,其伺服器環境是標準的LAMP:
  • RedHat 9
  • apache-2.0.40
  • mysql-3.23.54a
  • php-4.2.2
  • 512MB RAM

因為此系統的負載對 Apache 來說只是小CASE,所以只要簡單檢查 httpd.conf 有無異常的設定。

/etc/httpd/conf/httpd.conf

StartServers       8
MinSpareServers    5
MaxSpareServers   20
MaxClients       150
MaxRequestsPerChild  1000

這些設定值保留預設就已足夠。在執行一段時間後,httpd 的 process 數量會達到 20 個,為節省一點伺服器資源消耗,我便將非必要的 LoadModule 註解。

#LoadModule proxy_module modules/mod_proxy.so
#LoadModule proxy_ftp_module modules/mod_proxy_ftp.so
#LoadModule proxy_http_module modules/mod_proxy_http.so
#LoadModule proxy_connect_module modules/mod_proxy_connect.so

然後再重新啟動 Apache 伺服器。

apachectl restart

再來是 MySQL 的部份,因為臆測拖累網站速度的主因之一,極可能是設計不當的 SQL 查詢,因此我直接將 MySQL 的記錄功能開啟,log-slow-queries 會將查詢消耗過多時間的 SQL 記錄到 log 檔案,其中 long_query_time=1 代表時間超過 1 秒才記錄。

*調校完需要再次將此記錄功能停用,避免影響效能。

[mysqld]
log-slow-queries
set-variable = long_query_time=1
#set-variable = log_long_queries=1
#log-queries-not-using-indexes

MySQL 有許多參數可以調整,使用「show variables」與「show status」可以查詢目前的伺服器設定及狀態。

$ mysql -uroot
mysql > show variables;
mysql > show status;

對於已經運作一段時間的系統,MySQL 資料表(db table)在磁碟中的檔案可能已經出現許多碎片,這需要使用 optimize table 語法將資料表重整最佳化。

mysql > use DBNAME;
mysql > optimize table TABLENAME;

關於 MySQL 的參數調校可以參考:

http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
http://portable.easylife.tw/2074

接下來重新啟動 MySQL 伺服器。

sudo /etc/init.d/mysqld restart
sudo service mysqld restart

接下來就可以另開一個新的 Terminal,觀察 slow log 以瞭解 PHP 程式哪些 SQL 語法花費太多時間。 

tail -f /var/lib/mysql/localhost-slow.log

針對 PHP 連線存取 MySQL 資料庫的設定,有些簡易但是對效能有些許幫助的細節可以注意:

使用 mysql_pconnect 取代 mysql_connect,差別是多一個「p」表示使用持續連線(persistent connection),減少 PHP 程式每次執行都需要重新建立資料庫連線的成本。

$conn = mysql_pconnect($dbhost, $dbuser, $dbpassword);

另外,如果資料庫是在同一部伺服器,則可以指定 mysql.sock 的路徑,用系統的 Socket 通道取代一般的 TCP/IP 連線。但如果網站與資料庫伺服器分別獨立於不同機器,就無法這樣做,當然將機器分開本來就對整體效能比較好。

$dbhost = "localhost:/var/lib/mysql/mysql.sock";

在開始改善 SQL 語法設計前,開發者也需要知道查詢結果會佔記憶體,當某個查詢在迴圈中存取 N 次,若沒有適時釋放記憶體空間,也會造成不良的影響。這部份需要養成好習慣,就是在一個 Result 不再需要使用時,就用 mysql_free_result 函式解放它。

$result = mysql_query("SELECT ...");
//mysql_fetch_array...
mysql_free_result($result);

接下來是 SQL 觀念的部份,好與不好的 SQL 設計結果可能相差數倍或數十、數百倍,有經驗的開發者應該避免偷懶而用上危險的設計。

雖然書上經常這樣寫,許多程式剛開始也這麼做,但還是要千萬記住「SELECT *」很危險不適合在地球上使用;因為你在查詢一個資料表時,不可能不知道自己要什麼欄位,即使是 ORM 也會清楚定義 Data Object 的 Properties,所以在正式上線的程式要避免直接用「*」這種偷懶的查詢寫法。

SELECT * FROM ... 

比較好的作法,應該是加上明確指定的欄位名稱及查詢筆數限制:

SELECT COL1, COL2 FROM ... LIMIT 100

加上資料筆數限制,因為將「所有資料」都顯示在頁面上的設計並不合理,當某個資料表有十萬筆資料時,不可能未經篩選就全部顯示。因為畫面能顯示的資料筆數有限,所以通常一定會加上分頁、筆數的設定,這部份不太會有問題。

但是初學者經常犯的一個嚴重錯誤,就是為了計算資料總筆數(需要換算成分頁的頁數),就真的把無限筆數的「*」查詢給用上。

$result = mysql_query("SELECT * FROM ..."); //無限筆數的恐怖查詢
$total = mysql_num_rows($result); //只是為了取得分頁所需的總筆數

因為 PHP + MySQL 跑得很快,在資料量小於 1000 筆時不會有什麼感覺,但是等系統上線資料量達到上萬筆,就會有明顯的差異。

正確的作法是使用 SQL 的 COUNT(*) 語法計算總筆數,直接將計算結果取出:

$result = mysql_query("SELECT COUNT(*) AS C1 FROM ...");
$row = mysql_fetch_array($result);
$total = $row['C1'];

在比較新版的 MySQL 可以用 Sub Queries(子查詢),讓一句 SQL 可以做更多事:

SELECT C1, (SELECT COUNT(*) FROM T2 WHERE ... ) FROM T1 WHERE ...

或是:

SELECT C1 FROM T1 WHERE (SELECT COUNT(*) FROM T2 WHERE ... ) > 0 ...

但是舊版 MySQL 並不支援子查詢,這時候可能就產生一個問題,當主查詢的比數有 10,000 筆時,子查詢可能被執行 10,000 次。

mysql_query("SELECT C1 FROM T1 ..."); //call once, return 10,000 rows

若不支援子查詢,子查詢需要獨立做查詢,但其查詢次數等於主查詢的筆數:

mysql_query("SELECT COUNT(*) FROM T2 WHERE ..."); //call 10,000 times

這個問題的解決方法有兩種:
  1. 如果可以,用 JOIN 語法或 FROM T1, T2 方式改寫成相同目的的 SQL,只要查詢一次就能得到結果。
  2. 如果不能合併 SQL 語法,就可以考慮將資料預查、快取等方式。
第二種作法需要視實際的情況設計,例如可以先利用一個子查詢將某個鍵值欄位快取。

$keys = array();
$result = mysql_query("SELECT K1 FROM T2 WHERE ...");
while ($row = mysql_fetch_array($result)) {
  $keys[] = $row['K1'];
}

再利用 WHERE ... IN 語法製作主查詢(這個作法需要考慮 SQL 內容長度的限制)。

$keystr = implode(",", $keys);
$result = mysql_query("SELECT C1, C2 FROM T1 WHERE ... AND T1.K1 IN (".$keystr.") ...");

雖然無法合併為同一個 SQL 查詢,但只用了兩個查詢仍然可以在合理的時間內處理完。

@作者 lyhcode 分享於玩物尚誌

2 則留言:

  1. 經驗分享,謝謝。

    回覆刪除
  2. 不好意思~ 想請教一下大大(可能跟本文沒有直接關係)
    我有一個網站使用PHP+MySQL(apache)(windows server 2008)
    每日網站的資料新增、更新會達上萬筆(可登入之使用者有上千位)
    有單位反映有時候明明資料更新了,但稍後來看或隔天來看又會是舊的資料
    但我端測試始終沒有這樣的問題...>"<(伺服器放置在單位的縣市,我是在外縣市測試)

    有考慮是SQL cache數值過高,但因使用量大若不採用cahce網站開啟速度會非常慢或開不起來
    但cache也有分很多種,不懂究竟哪個才是我真正該動的
    因為本人對這些環境設定實在陌生,網上查詢過很多可能該調整的參數
    但也不知是否真正對症下藥,實在毫無頭緒

    搜尋都沒有找到這樣子的類似問題(還是我關鍵字打得不好?)
    還請大大提供些建議或想法
    或者是否有認識熟悉這方面的高手>"<

    回覆刪除

lyhcode by lyhcode
歡迎轉載,請務必註明出處!