Post view

相簿的查詢MySQL指令,幾乎都是超過1秒以上

目前insoler社群網站相簿,大約有「128384」張照片左右。這個數字其實只要打開insoler首頁,在最新公開照片的左下角就會看到「檢視全部 (128384)」這樣的數字。這個數字就是目前已經上傳的照片。當然,被刪除的照片不會列入計算,伺服器主機也不會保存或是備份刪除的照片。

大約12.8萬張照片,也就是大約12.8萬筆左右的資料,會導致MySQL的SELECT查詢、INSERT新增、UPDATE更新... 指令的執行時間超過1秒以上嗎?如果是使用一台老舊的Intel Core Duo、Core 2 Duo電腦主機,記憶體只有2GB,最多只有8GB記憶體,再加上使用傳統「硬碟」而不是目前最快的SSD固態硬碟的話,確實是有可能需要一些時間。

然而insoler目前是使用2台Mac mini主機。兩台都是4核心、8執行緒的Intel Core i7 2.6GHz處理器、16GB記憶體、250GB的Apple原廠SSD固態硬碟。一台專門負責Web Server(不執行其他任務),另外一台專門負責MySQL Server(除了資料庫以外,也不執行其他任務)。所以每一個Server都有一顆「4核心、8執行緒處理器」,以及16GB記憶體可以使用。

insoler網站上的user並不多,理論上應該是還算充裕,但事實上卻不是如此!我開啟MySQL的「long_query_time = 1」記錄功能,看看有哪些指令的處理時間需要1秒以上?結果全部都是「相簿相關指令」。

 

/usr/local/mysql/bin/mysqld, Version: 5.6.35 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 170217 10:32:39
# Query_time: 1.335483  Lock_time: 0.000350 Rows_sent: 1  Rows_examined: 474884
use insoler;
SET timestamp=1487295159;
SELECT `bx_photos_main`.`ID` as `medID`, `bx_photos_main`.`Categories` as `Categories`, `bx_photos_main`.`Owner` as `medProfId`, `bx_photos_main`.`Title` as `medTitle`, `bx_photos_main`.`Uri` as `medUri`, `bx_photos_main`.`Desc` as `medDesc`, `bx_photos_main`.`Tags` as `medTags`, `bx_photos_main`.`Date` as `medDate`, `bx_photos_main`.`Views` as `medViews`, `bx_photos_main`.`Status` as `Approved`, `bx_photos_main`.`Featured` as `Featured`, `bx_photos_main`.`Rate` as `Rate`, `bx_photos_main`.`RateCount` as `RateCount`, `bx_photos_main`.`Ext` as `medExt`, `bx_photos_main`.`Size` as `medSize`, `bx_photos_main`.`Hash` as `Hash`,  COUNT(`share1`.`ID`) as `Count`,  `sys_albums`.`ID` as `albumId`, `sys_albums`.`Caption` as `albumCaption`, `sys_albums`.`Uri` as `albumUri`, `sys_albums`.`AllowAlbumView`, `sys_albums_objects`.`obj_order`
                     FROM `bx_photos_main`
                     LEFT JOIN `bx_photos_main` as `share1` USING (`Owner`)
                    
                INNER JOIN `sys_albums_objects` ON `sys_albums_objects`.`id_object`=`bx_photos_main`.`ID`
                INNER JOIN `sys_albums` ON (`sys_albums`.`ID`=`sys_albums_objects`.`id_album` AND `sys_albums`.`Type`='bx_photos')
           
                     WHERE `bx_photos_main`.`Uri`='14405226653176' GROUP BY `share1`.`Owner` LIMIT 1;

 

從上面「Query_time: 1.335483」需要花1.3秒來看就可以知道,還不到15萬筆資料,竟然搜尋一筆資料要花費1.3秒!難怪有時候MySQL Server主機的CPU會幾乎100%忙碌!在4核心的情況下,CPU的忙碌時間超過200%以上!

這也可能會導致上傳照片的時候,每上傳一張照片,就需要多等待1-2秒,甚至是更久的時間才能完成一張照片的處理動作。

要解決MySQL Server花很多運算時間的問題,大概有這幾種方法:

1. 目前是使用最新的「Version: 5.6.35 (MySQL Community Server」版本,升級到MySQL 5.7.17最新版,再測試看看。

2. 檢查與實際測試Web Server與MySQL Server的「1Gbps網路線」的實際資料傳輸速度,看看瓶頸是不是卡在這裡?

3. 備份MySQL全部資料,匯入到另外一台Mac mini伺服器電腦主機,使用預設的my.cnf檔案,或是試著修改my.cnf的設定,看看能否改善效能?會需要轉移MySQL資料庫到另外一台「測試電腦」來測試看看,是不希望因為進行各種測試,導致影響正式運作的網站。

4. 修改SELECT或是精簡SELECT資料庫查詢指令,試著另外建立「INDEX」索引表,看看能不能提升資料的搜尋速度。

5. 各種「軟體」的手段都用盡,剩下的唯一辦法當然是只好升級MySQL Server的伺服器主機,使用更快的CPU、更快更多的RAM記憶體、讀寫速度更快的SSD固態記憶體。當然,「RAW記憶體磁碟」也是一種可以利用的手段。

目前我已經完成了前面3項的測試,得到的結果是:

1. 升級到最新版MySQL 5.7.17不會變得更快,甚至還可能會更慢一點。

2. 透過「1Gbps網路線」的話,網路封包資料的傳送速度可以達到「110MB/s」左右。其實只SELECT資料庫查詢指令的結果,只有簡單的純文字而已,不可能因為需要傳送超過「110MB/s」的大量資料到MySQL Server主機,或是從MySQL傳給Web Server主機,導致兩台電腦主機的CPU其實很空閒,但「網路線」非常擁擠、塞爆網路線頻寬的問題!

3. 使用預設的my.cnf的設定,或是修改my.cnf的設定值,增加Cache快取記憶體等,都看不出可以大幅改善MySQL的SELECT查詢效能!

以上3個方式都沒有明顯的改善或是影響,接下來有空的時候,只好想辦法用建立「INDEX」索引表、簡化SELECT資料庫查詢指令等其他方式來測試看看。當然,這些測試動作都只能在另外一台「測試電腦」來進行,以免影響正式運作的網站。

請繼續閱讀這篇文章:

終於解決了瀏覽相簿的MySQL查詢指令,幾乎超過1秒〜4.7秒以上的頭痛問題

蘇言霖 02/19/2017 2 537
Comments
Order by: 
Per page:
 
  •  ayaka: 
     

    這個select太多fields了,又join了,還是生成view比較好

     
     02/19/20171 replies1 replies 
    0 points
     
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
02/19/2017 (543 days ago)
Actions