毎秒数百万件のクエリ：今日のワークロード要件に対応したPostgreSQLとMySQLの平和的な戦い

今年、 PG Day'17ロシア会議のテーマが大幅に拡大したことは既に述べました。 Perconaと共同で、 MySQL / NoSQLに関するプレゼンテーションの別のストリームを形成しました。 オープンデータベースの主要な専門家からのレポートとSQLソリューションの提供に加えて、Perconaの主要な専門家からの2つの排他的なマスタークラス、 Peter ZaitsevとSveta Smirnovaも会議中に開催されます。







マスタークラスは、MySQLデータベースに関するさまざまなトピックをカバーします。テストサーバーの作成と使用、遅いクエリのデバッグの微妙さ、ロックシステムの機能、パフォーマンスへの機器と構成の影響、サーバーへの最小負荷でのデータ収集。



本日、 Sveta Smirnova 、Perconaテクニカルサポートシニアエンジニア、およびAnastasia Raspopina 、マーケティングスペシャリストが、PostgreSQLとMySQLが1秒間に何百万ものクエリを処理する方法を比較する短いレビューの翻訳に注目します。



7月5日、 PG Day'17の参加者のために、 SvetlanaはMySQLサーバーのアーキテクチャと、オプティマイザー、テーブルエンジン、ロックシステムなどのさまざまなパーツの操作の詳細について詳しく話します。



アナスタシア ：オープンソースデータベースは毎秒数百万のクエリを処理できますか？ 多くのオープンソース支持者はイエスと答えます。 しかし、その主張は実証された証拠には十分ではありません。 そのため、この記事では、Alexander Korotkov（開発ディレクター、 Postgres Professional ）とSveta Smirnova（チーフメンテナンスエンジニア、Percona）のテスト結果を共有しています。 PostgreSQL 9.6およびMySQL 5.7のベンチマークパフォーマンスは、マルチデータベース環境で特に役立ちます。



この調査の目的は、2つの一般的なDBMSの正直な比較を提供することです。 SvetaとAlexanderは、同じ複雑なワークロードの下で同じツールを使用し、同じ構成パラメーター（可能な場合）を使用して、MySQLとPostgreSQLの最新バージョンをテストしたいと考えていました。 ただし、PostgreSQLおよびMySQLエコシステムが独立して進化し、各データベースに標準テストツール（ pgbenchおよびSysBench ）が使用されたため、これは簡単ではありませんでした。



タスクは、長年の実務経験を持つデータベースの専門家に委ねられました。 Svetaは、OracleのMySQLサポートチームエラーチェックチームのシニアチーフテクニカルサポートエンジニアとして8年以上働き、2015年以降、Perconaのチーフテクニカルエンジニアとして働いていました。 Alexander Korotkovは、PostgreSQLの主要な開発者の1人であり、CREATE ACCESS METHODコマンド、一般的なWALインターフェイス、ノンブロッキングPin / UnpinBuffer、正規表現のインデックス検索など、多くのPostgreSQL関数の開発者です。 だから、私たちはこの演劇のためにかなりまともなキャストを得ました！



Sveta ： Dmitry Kravchukは定期的にMySQLの詳細なテスト結果を公開しているため、MySQLが1秒あたり数百万のクエリを実行できることを確認することはできませんでした。 グラフが示すように、すでにこのマークを克服しています。 サポートエンジニアとして、異なるデータベースを持つ異種環境で作業しているクライアントに出くわすことがよくあり、あるデータベースから別のデータベースにタスクを転送した場合の影響を理解したいと考えました。 したがって、Postgres Professionalと連携して、これら2つのデータベースの長所と短所を特定できたことを嬉しく思いました。



同じツールとテストを使用して、同じハードウェアで両方のデータベースをテストしたかったのです。 基本機能をテストしてから、より詳細な比較に取り組みました。 したがって、さまざまな実際の使用シナリオとそれらの最も一般的なバリエーションを比較できます。



ネタバレ ：最終結果にはまだほど遠い。 これが一連の記事の始まりです。



大型マシンのオープンソースデータベース、シリーズ1：「それは近かった...」

Postgres ProfessionalとFreematiqは、テスト用に2つの強力な最新マシンを提供しました。



ハードウェア構成：

プロセッサー：物理= 4、コア= 72、仮想= 144、ハイパースレッディング=はい

メモリー：3 TB

ディスク速度：3K IOPSについて

OS：CentOS 7.1.1503

ファイルシステム：XFS

効率の悪いPerconaマシンも使用しました。

ハードウェア構成：

プロセッサー：物理= 2、コア= 12、仮想= 24、ハイパースレッディング=はい

メモリー：251.9 GB

ドライブ速度：約33K IOPS

OS：Ubuntu 14.04.5 LTS

ファイルシステム：EXT4

MySQLのインストールでは、コアが多いマシンよりもプロセッサコアが少なく、ディスクが速いマシンの方が頻繁に使用されることに注意してください。

最初に同意する必要があるのは、使用するツールです。 公平な比較は、ワークロードができるだけ近い場合にのみ意味があります。



標準のPostgreSQLベンチマークツールはpgbenchで、MySQLの場合はSysBenchです。 SysBenchは、Luaプログラミング言語のテスト用にいくつかのデータベースドライバーとスクリプトをサポートしているため、両方のデータベースにこのツールを使用することにしました。



最初の計画は、pgbenchテストをLuaのSysBench構文に変換してから、両方のデータベースに対して標準テストを実行することでした。 最初の結果を受け取ったので、MySQLとPostgreSQLの特定の機能をよりよく研究するためにテストを修正しました。

pgbenchテストをSysBench構文に変換し、テストをGitHubのopen-database-benchリポジトリに配置しました。



そして、私たち二人とも困難に直面しました。



すでに書いたように、Perconaマシンでもテストを実行しました。 この変換されたテストでは、結果はほぼ同じでした：



パーコナマシン：

OLTP test statistics: transactions: 1000000 (28727.81 per sec.) read/write requests: 5000000 (143639.05 per sec.) other operations: 2000000 (57455.62 per sec.)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Freematiqマシン：

 OLTP test statistics: transactions: 1000000 (29784.74 per sec.) read/write requests: 5000000 (148923.71 per sec.) other operations: 2000000 (59569.49 per sec.)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

私は理解し始めました。 PerconaがFreematiqより優れていたのは、ディスクの速度だけでした。 そこで、読み取り専用のpgbenchテストの実行を開始しました。これは、メモリ内のデータの完全なセットを使用したポイント選択SysBenchテストと同一でした。 ただし、今回は、SysBenchは使用可能なCPUリソースの50％を使用しました。

 PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 4585 smirnova 20 0 0,157t 0,041t 9596 S 7226 1,4 12:27.16 mysqld 8745 smirnova 20 0 1266212 629148 1824 S 7126 0,0 9:22.78 sysbench
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

一方、AlexanderはSysBenchに問題があり、準備されたステートメントを使用するときにPostgreSQLに高い負荷をかけることができませんでした。

 93087 korotkov 20 0 9289440 3,718g 2964 S 242,6 0,1 0:32.82 sysbench 93161 korotkov 20 0 32,904g 81612 80208 S 4,0 0,0 0:00.47 postgres 93116 korotkov 20 0 32,904g 80828 79424 S 3,6 0,0 0:00.46 postgres 93118 korotkov 20 0 32,904g 80424 79020 S 3,6 0,0 0:00.47 postgres 93121 korotkov 20 0 32,904g 80720 79312 S 3,6 0,0 0:00.47 postgres 93128 korotkov 20 0 32,904g 77936 76536 S 3,6 0,0 0:00.46 postgres 93130 korotkov 20 0 32,904g 81604 80204 S 3,6 0,0 0:00.47 postgres 93146 korotkov 20 0 32,904g 81112 79704 S 3,6 0,0 0:00.46 postgres
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SysBenchの作者Alexei Kopytovに連絡し、MySQLに対して次のソリューションを提案しました。



•パラメーター--percentile = 0および--max-requests = 0 （CPUの合理的な使用）でSysBenchを使用します。

• concurrency_kitブランチを使用する（最適な同時実行性とLua処理）。

•準備されたステートメントをサポートするようにLuaスクリプトを書き換えます（プルリクエスト： github.com/akopytov/sysbench/pull/94 ）。

•プリロードされたjemallocまたはtmallocライブラリを使用してSysBenchとmysqldの両方を実行します。



PostgreSQLの修正が進行中です。 現時点では、Alexanderは標準のSysBenchテストをpgbench形式に変換しました。 MySQLにとってそれほど新しいものではありませんが、少なくとも比較の出発点がありました。



私が遭遇した次の困難は、デフォルトのオペレーティングシステム設定です。 要するに、私はそれらを推奨されるものに変更しました（以下で説明します）：

 vm.swappiness=1 cpupower frequency-set --governor performance kernel.sched_autogroup_enabled=0 kernel.sched_migration_cost_ns= 5000000 vm.dirty_background_bytes=67108864 vm.dirty_bytes=536870912 IO scheduler [deadline]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同じパラメーターがPostgreSQLのパフォーマンスに優れていました。 アレクサンダーも同じように車をセットアップしました。



これらの問題を解決した後、次のことを学び、実装しました。



•1つのツールを（まだ）使用することはできません。

•Alexanderは、標準のSysBenchテストをシミュレートして、pgbenchのテストを作成しました。

•さまざまなツールを使用しているため、カスタムテストを作成できません。



しかし、これらのテストを出発点として使用できます。 アレクサンダーによって行われた作業の後、標準のSysBenchテストで立ち往生しました。 私はそれらを準備されたステートメントを使用するように変換し、アレクサンダーはそれらをpgbench形式に変換しました。



読み取り専用テストとポイント選択テストでは、Dmitryと同じ結果を得ることができなかったことに言及する価値があります。 それらは似ていますが、少し遅いです。 これが異なるハードウェアを使用した結果なのか、それともパフォーマンステストのスキルが不足しているのかを把握する必要があります。 読み取りと書き込みのテスト結果が一致します。



PostgreSQLとMySQLのテストには別の違いがありました。 MySQLユーザーは通常、多くの接続を持っています。 max_connections変数の値を設定して、同時接続の総数を数千に制限することは最近では珍しくありません。 推奨されていませんが、 スレッドプールプラグインがなくてもこの機能を使用します 。 実際には、これらの化合物のほとんどは不活性です。 ただし、ウェブサイトのアクティビティが増加した場合は、全員が関与する可能性が常にあります。



MySQLの場合、最大1024個の接続をテストしました。 2のべき乗とコア数の係数を使用しました：1、2、4、8、16、32、36、64、72、128、144、256、512、および1024スレッド。



アレクサンダーにとっては、より小さなステップでテストを実施することがより重要でした。 彼は1つのスレッドから始め、250の並列スレッドに達するまで10スレッド増加しました。 したがって、PostgreSQLのより詳細なグラフが表示されますが、250スレッド後に結果はありません。

比較結果は次のとおりです。



ポイント選択







pgsql-9.6-標準のPostgreSQL

pgsql-9.6 + pgxact-align- このパッチを適用した PostgreSQL（詳細はこの記事に記載されています ）

MySQL-5.7 Dimitri-Oracle MySQLサーバー

MySQL-5.7 Sveta-Percona 5.7.15サーバー



OLTP RO







OLTP RW







PostgreSQLの同期コミット関数は、InnoDBのinnodb_flush_log_at_trx_commit = 1に似ており、非同期コミットはinnodb_flush_log_at_trx_commit = 2に似ています。

結果は非常に似ていることがわかります。両方のデータベースは非常に高速に開発されており、最新の機器でうまく機能しています。



参照用に1024スレッドを示すMySQL結果。

ポイントSELECTおよびOLTP RO







innodb_flush_log_at_trx_commitが1および2に設定されたOLTP RW







これらの結果が得られた後、いくつかの特別なテストを実施しましたが、これらは別の記事で検討されます。

追加情報

OLTP ROおよびPoint SELECTテストのMySQLオプション：

 # general table_open_cache = 8000 table_open_cache_instances=16 back_log=1500 query_cache_type=0 max_connections=4000 # files innodb_file_per_table innodb_log_file_size=1024M innodb_log_files_in_group=3 innodb_open_files=4000 # Monitoring innodb_monitor_enable = '%' performance_schema=OFF #cpu-bound, matters for performance #Percona Server specific userstat=0 thread-statistics=0 # buffers innodb_buffer_pool_size=128000M innodb_buffer_pool_instances=128 #to avoid wait on InnoDB Buffer Pool mutex innodb_log_buffer_size=64M # InnoDB-specific innodb_checksums=1 #Default is CRC32 in 5.7, very fast innodb_use_native_aio=1 innodb_doublewrite= 1 #https://www.percona.com/blog/2016/05/09/percona-server-5-7-parallel-doublewrite/ innodb_stats_persistent = 1 innodb_support_xa=0 #(We are read-only, but this option is deprecated) innodb_spin_wait_delay=6 #(Processor and OS-dependent) innodb_thread_concurrency=0 join_buffer_size=32K innodb_flush_log_at_trx_commit=2 sort_buffer_size=32K innodb_flush_method=O_DIRECT_NO_FSYNC innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 innodb_lru_scan_depth=4000 innodb_page_cleaners=4 # perf special innodb_adaptive_flushing = 1 innodb_flush_neighbors = 0 innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_io_capacity=2000 innodb_io_capacity_max=4000 innodb_purge_threads=4 innodb_max_purge_lag_delay=30000000 innodb_max_purge_lag=0 innodb_adaptive_hash_index=0 (depends on workload, always check)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

OLTP RWのMySQLオプション：

 #Open files table_open_cache = 8000 table_open_cache_instances = 16 query_cache_type = 0 join_buffer_size=32k sort_buffer_size=32k max_connections=16000 back_log=5000 innodb_open_files=4000 #Monitoring performance-schema=0 #Percona Server specific userstat=0 thread-statistics=0 #InnoDB General innodb_buffer_pool_load_at_startup=1 innodb_buffer_pool_dump_at_shutdown=1 innodb_numa_interleave=1 innodb_file_per_table=1 innodb_file_format=barracuda innodb_flush_method=O_DIRECT_NO_FSYNC innodb_doublewrite=1 innodb_support_xa=1 innodb_checksums=1 #Concurrency innodb_thread_concurrency=144 innodb_page_cleaners=8 innodb_purge_threads=4 innodb_spin_wait_delay=12 Good value for RO is 6, for RW and RC is 192 innodb_log_file_size=8G innodb_log_files_in_group=16 innodb_buffer_pool_size=128G innodb_buffer_pool_instances=128 #to avoid wait on InnoDB Buffer Pool mutex innodb_io_capacity=18000 innodb_io_capacity_max=36000 innodb_flush_log_at_timeout=0 innodb_flush_log_at_trx_commit=2 innodb_flush_sync=1 innodb_adaptive_flushing=1 innodb_flush_neighbors = 0 innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 innodb_lru_scan_depth=4000 innodb_adaptive_hash_index=0 innodb_change_buffering=none #can be inserts, workload-specific optimizer_switch="index_condition_pushdown=off" #workload-specific
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

MySQL SysBenchオプション：

 LD_PRELOAD=/data/sveta/5.7.14/lib/mysql/libjemalloc.so /data/sveta/sbkk/bin/sysbench [ --test=/data/sveta/sysbench/sysbench/tests/db/oltp_prepared.lua | --test=/data/sveta/sysbench/sysbench/tests/db/oltp_simple_prepared.lua ] --db-driver=mysql --oltp-tables-count=8 --oltp-table-size=10000000 --mysql-table-engine=innodb --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5715.sock --num-threads=$i --max-requests=0 --max-time=300 --percentile=0 [--oltp-read-only=on --oltp-skip-trx=on]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

PostgreSQL pgbenchオプション：

 $ git clone https://github.com/postgrespro/pg_oltp_bench.git $ cd pg_oltp_bench $ make USE_PGXS=1 $ sudo make USE_PGXS=1 install $ psql DB -f oltp_init.sql $ psql DB -c "CREATE EXTENSION pg_oltp_bench;" $ pgbench -c 100 -j 100 -M prepared -f oltp_ro.sql -T 300 -P 1 DB $ pgbench -c 100 -j 100 -M prepared -f oltp_rw.sql -T 300 -P 1 DB
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

パフォーマンスを大幅に改善したMySQL 5.7の機能：

• InnoDB：トランザクションリストの最適化：
  
  
  • InnoDBチームの記事 ;
  • WL＃6047 ;
• InnoDB：lock_sys_tの競合の削減:: mutex：
  • WL＃6899 ;
• InnoDB：競合インデックスの修正->ロック：
  • WL＃6326 ;
• InnoDB：ディスクへのページのより高速で並列なフラッシュ：
  • マルチスレッドページクリーニング： WL＃6642 ;
  • リセットするページ数の削減： WL＃7047 ;
  • 適応型ページリセットの改善： WL＃7868 ;
• MDLスケーラビリティ（メタデータロック）：
  • THR_LOCKを削除:: InnoDBのミューテックス：Wl ＃6671 ;
  • 分割されたLOCK_grant;
  • パーティションの数は一定です。
  • ストリームIDを使用してパーティションを割り当てる：
    • Wl＃8355 ;
    • バグ＃72829 ;
  • DMLの非ロックMDLロック：
    • WL＃7306 ;
    • WL＃7305

アナスタシア ：この研究の最初の結果は、 2016年Percona Live Amsterdamで発表されました 。 モスクワHighLoad ++ 2016で発表された同じスピーチの2番目のバージョンに、新しい興味深い結果が追加されました。



PG Day'17のSvetaワークショップのすべての参加者は、この研究のさらなるバージョンを入手できます。 PostgreSQLとMySQLのパフォーマンスのどの側面について詳しく知りたいかという質問や提案がある場合は、コメントを残してください。私たちはあなたの希望を考慮に入れます！