通常以 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
這個問題的解決方法有兩種:
- 如果可以,用 JOIN 語法或 FROM T1, T2 方式改寫成相同目的的 SQL,只要查詢一次就能得到結果。
- 如果不能合併 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 分享於
玩物尚誌。