727 阿裏雲技術社區[雲棲]

每秒百萬查詢：MySQL與PG在苛刻負載下的和平之戰

Anastasia：開源數據庫能應付每秒數百萬次的查詢嗎？許多開源倡導者會回答“是的”，但是，斷言是不夠有理有據的證明。這就是為什麼在這篇文章中，我們將分享Alexander Korotkov（CEO，Postgres專家）和Sveta Smirnova（首席技術服務工程師，Percona）的基準測試結果。而且，PostgreSQL 9.6和MySQL 5.7的性能對比研究對於多數據庫環境特別有價值。

這項研究背後的想法是客觀地比較兩個流行的數據庫。Sveta和Alexander想在相同的具有挑戰性的工作負載中，並使用相同配置參數（如果可能的話）的情況下，用同一個工具分別對最新版本的MySQL和PostgreSQL進行測試。然而，由於PostgreSQL和MySQL的生態係統發展的獨立性，所以要使用標準的測試工具（pgbench和SysBench）來測試這兩種數據庫的話，將會是一次不輕鬆的測試之旅。

這個任務交給了有多年實踐經驗的數據庫專家手上。Sveta作為首席高級技術支持工程師，在Oracle MySQL技術支持小組的bug驗證組工作了八年以上，2015年以來一直擔任Percona的首席技術服務工程師。Alexander Korotkov是PostgreSQL的主要貢獻者，助力於PostgreSQL功能的開發，包括CREATE ACCESS METHOD命令、通用WAL接口、lockfreePin/unpinbuffer、基於索引的正則表達式搜索等等。所以，在我們這個特別的遊戲中有著相當不錯的參與者。

Sveta：Dimitri Kravtchuk定期出版對於MySQL的詳細測試基準，所以我的主要任務不是確認MySQL是否可以查詢每秒百萬。正如我們的圖表顯示，我們早已突破了這個限度。作為一個技術支持工程師，我經常在客戶的多種異構數據庫環境中工作，並想知道從一個數據庫遷移到另一個的影響。所以，我找到了機會跟專業的Postgres公司合作，並發現這對於了解這兩種數據庫的優缺點來說是極好的機會。

我們想在相同的硬件上使用相同的工具和方法測試這兩個數據庫。我們想測試基礎功能，然後進行更詳細的比較。這樣就可以比較出現實中不同的案例場景和流行的配置。

劇透：我們離最終的結果還很遠，這隻是一個係列文章的開始。

開源數據庫在Big Machine上

係列1：“很相近...”

Postgres專家使用Freematiq提供的兩個新型號，為測試提供了強大的機器。

硬件配置：

處理器：數量=4，核數=72，虛擬核數=144，超線程已開啟
內存：3.0T
硬盤速度：大概3K IOPS
OS：CentOS 7.1.1503
文件係統：XFS

我也使用了一台較小的Percona服務器。

硬件配置：
處理器：數量=2，核數=12，虛擬核數=24，超線程已開啟
內存：251.9G
硬盤速度：大概33K IOPS
OS：Ubuntu 14.04.5 LTS
文件係統：EXT4

注意，部署MySQL的設備中，使用配備CPU核數較少並且硬盤更快的服務器比配備CPU核數高的服務器更普遍。

首先我們需要對使用哪種工具達成共識，公平地比較隻有在工作負載盡可能接近時才更有意義。

標準的PostgreSQL性能測試工具是pgbench，在MySQL中用SysBench。SysBench支持多種數據庫驅動和Lua編程語言腳本測試，所以我們決定使用SysBench作為兩種數據庫的測試工具。

最初的計劃是將pgbench測試轉換成SysBench lua語法，然後在兩種數據庫上運行標準測試。經過初步的測試結果，我們修改了測試方法來更好地研究特定的MySQL和PostgreSQL功能。

我把pgbench測試轉換成了SysBench語法，並把測試上傳到了一個開源數據庫測試的GitHub知識庫中。

然後我們都麵臨了一些困難。

當我寫好之後，我在Percona服務器上也進行了測試。對於這個轉換測試，結果幾乎相同：

Percona服務器：

Freematiq服務器：

我開始研究，Percona的機器比Freematiq要好的唯一之處在於磁盤速度，所以我開始運行pgbench隻讀測試，這跟SysBench測試的內存中全表掃描的結果一致，但這一次SysBench使用了可用CPU資源50%：

Alexander，相應的，遇到了SysBench的問題，在使用準備好的語句時無法創造出PostgreSQL上的高負荷：

我們聯係了SysBench的作者Alexey Kopytov，他修正了MySQL的問題。解決辦法是：

使用SysBench的參數 --percentile=0 --max-requests=0 （合理的CPU使用率）
使用concurrency_kit分支（更好的並發和Lua處理）
重寫了Lua腳本來支持的準備好的語句（pull請求：https://github.com/akopytov/sysbench/pull/94）
啟動SysBench和mysqld的時候使用jemalloc或tmalloc庫預加載

PostgreSQL的修正方案在準備中。現在，Alexander將一個標準的SysBench測試轉換成了pgbench格式，並且我們堅持做下來了。沒有太多對於MySQL方麵的調整，但至少我們有一個比較的基線。

我麵臨的下一個困難是默認操作係統參數。長話短說，我把它們改成了推薦的值：

相同的參數同樣也對PostgreSQL的性能更好。Alexander同樣設置了他的機器。

解決這些問題後，我們學習並繼續進行測試：

我們不能使用單一的工具（現在）
Alexander寫了pgbench測試，模仿標準的SysBench測試
我們仍然不能編寫自定義測試，因為我們使用不同的工具

但是我們可以使用這些測試作為基線。由Alexander完成工作後，我們堅持做了標準的SysBench測試。我把它們轉換成使用事先準備好的語句，Alexander將其轉化為pgbench格式。

應該注意的是，我不能得到和Dimitri做的隻讀和point select選擇測試相同的結果。它們接近，但稍微有點慢。我們需要調查這是由於硬件不同而導致的結果，還是我缺乏性能測試能力的結果。從讀寫測試返回的結果是相似的。

另一個區別是PostgreSQL和MySQL測試。MySQL用戶通常有許多連接。設置變量max_conenctions的值，並且限製並行連接總數在上千的級別並不少見。雖然不建議，但是人們使用這個選項，即使沒有線程池插件。在真實生活中，絕大多數的這種連接大多是休眠狀態。但總會遇到機會，在網站活動增長的時候他們都會被使用。

MySQL我測試了1024個連接。我用2的冪次方和多核：1，2，4，8，16，32，36，64，72，128，144，256，512和1024線。

對於Alexander來說，更重要的是在線程較小的步驟中進行測試。他從一個線程開始，增加了10個線程，直到達到250個並行的線程。所以你會看到一個更詳細的關於PostgreSQL的圖，但250個線程之後沒有結果。

以下是我們的比較結果：

Point SELECTs

pgsql-9.6 是標準的PostgreSQL
pgsql-9.6 + pgxact-align是PostgreSQL的補丁包（更多的細節可在本文中看到）
MySQL-5.7 Dimitri是Oracle's MySQL服務器
MySQL-5.7 Sveta是Percona服務器，版本5.7.15

OLTP RO

OLTP RW

Sync commit是PostgreSQL的一個功能，類似於InnoDB中的innodb_flush_log_at_trx_commit = 1，async commit類似innodb_flush_log_at_trx_commit = 2。

結果非常相似：兩個數據庫都發展很快，並且與現代硬件很好地兼容。

MySQL使用1024個線程測試的參考結果。

Point SELECT and OLTP RO