PGCONF.RUSSIA 2015カンファレンスでの私のプレゼンテーションを見越して、MySQLとPostgreSQLの重要な違いに関するいくつかの所見を共有します。 この資料は、MySQLの機能にもはや満足していないすべての人々、およびPostgresで最初の一歩を踏み出している人々に役立ちます。 もちろん、この投稿を完全な違いのリストと見なすべきではありませんが、特定のDBMSに有利な決定を下すのに十分です。

複製

私の講演のトピックは「無修正の非同期複製、またはPostgreSQLが世界を征服する理由」であり、複製はMySQLを使用する忙しいプロジェクトにとって最も苦痛なトピックの1つです。 多くの問題があります-正しい操作、安定性、パフォーマンス-そして一見、それらは無関係に見えます。 歴史的な文脈を見ると、興味深い結論が得られます。MySQLレプリケーションは考え抜かれていなかったため非常に多くの問題を抱えており、「ジャーナルについてはどうですか？」という質問に答えずにストレージエンジン（プラグインエンジン）をサポートしていましたさまざまなストレージエンジンがレプリケーションに参加する方法。」 2004年、PostgreSQLメーリングリストで、ユーザーはPostgreSQLソースコードでストレージエンジンを「見つけよう」としましたが、そこにないことに非常に驚きました。 議論の中で、誰かがこの機能をPostgreSQLに追加することを提案し、開発者の1人は、「みんな、これを行うと、レプリケーションとエンジン間のトランザクションに問題が発生します」と答えました。

問題は、多くのストレージ管理システムが...しばしば独自のWALとPITRを行うことです。 独自のバッファ管理、ロック、レプリケーション/負荷管理も行う人もいます。 だから、あなたが言うように、インターフェイスはどこにあるべきかという難しい発言

抽象化。

postgresqlメーリングリストのこの手紙へのリンク



10年以上が経過しましたが、何が見えますか？ MySQLには異なるストレージエンジンのテーブル間のトランザクションの問題があり、MySQLにはレプリケーションの問題があります。 過去10年間、PostgreSQLにはプラグインのデータ型とインデックスがあり、レプリケーションもあります。つまり、MySQLのアーキテクチャ上の問題が残っており、生活を妨害している間、MySQLの利点は平準化されました。 MySQL 5.7では、レプリケーションパフォーマンスの問題を並列化することで解決しようとしました。 作業中のプロジェクトは、そのサイズのためにレプリケーションのパフォーマンスに非常に敏感なので、改善されたかどうかをテストしてみました。 5.7の同時レプリケーションは、5.5のシングルスレッドよりも動作が遅く、場合によってはほぼ同じであることがわかりました。 現在MySQL 5.5を使用しており、より新しいバージョンにアップグレードする場合、レプリケーションは単に実行を停止するため、高負荷のプロジェクトでは移行ができないことに注意してください。



高負荷に関するレポートの後、Oracleは私が開発したテストに注目し、問題の修正を試みることを報告しました。 最近、彼らはテストで並列性を確認できたと書いて、設定を送信しました。 誤解しない限り、16スレッドでは、シングルスレッドバージョンと比較して若干の加速がありました。 残念ながら、特にそのような結果が生じた場合でも問題は依然として関連するため、提供された設定でテストを繰り返していません。



このパフォーマンスの回帰の正確な理由は不明です。 いくつかの仮定がありました-たとえば、MariaDBの開発者の1人であるChristian Nelsenは、スレッド同期に関するパフォーマンススキームに問題があるかもしれないとブログに書いています。 このため、40％の回帰が観察され、これは従来のテストで確認できます。 Oracle開発者はこれに異議を唱え、それが存在しないことを確信さえしました。どうやら、他の問題があります（そして、そのうちのどれだけですか？）。



MySQLレプリケーションでは、ストレージエンジンの問題は、選択されたレベルのレプリケーションによって悪化します-それらは論理的ですが、PostgreSQLでは-物理的です。 原則として、論理複製には独自の利点があり、興味深いことよりも多くのことができるようになります。これについてもレポートで説明します。 しかし、PostgreSQLは、物理的な複製の一部であっても、これらの利点をすべて無効にします。 言い換えれば、MySQLにあるほとんどすべてのことは、PostgreSQLですでに実行できます（または近い将来可能になります）。



MySQLでの低レベルの物理レプリケーションの実装を期待することはできません。 問題は、1つのログ（PostgreSQLの場合）の代わりに、数える方法に応じて2つまたは4つのログがあることです。 PostgreSQLは単純にクエリをコミットし、ログに記録します。このログはレプリケーションで使用されます。 PostgreSQLレプリケーションは、クラッシュリカバリ操作中と同じログブックを使用するため、非常に安定しています。 このメカニズムは長い間書かれ、十分にテストされ、最適化されています。



MySQLでは、状況は異なります。 独立したInnoDBジャーナルとレプリケーションジャーナルがあり、そこにコミットする必要があります。 これはログ間の2フェーズコミットであり、定義上低速です。 つまり、単にそれを取得して、InnoDBログからトランザクションを繰り返していると言うことはできません。どのようなリクエストであるかを把握し、再度実行する必要があります。 これが論理レプリケーションである場合でも、行レベルで、これらの行をインデックスで検索する必要があります。 また、リクエストを満たすために多くの作業を行う必要があるだけでなく、レプリカ上のInnoDBログに再び書き込まれますが、これは明らかにパフォーマンスに良くありません。



PostgreSQLでは、この意味で、アーキテクチャははるかに考え抜かれ、より適切に実装されています。 最近、Logical Decodingと呼ばれる機会を発表しました。これにより、物理ジャーナルのフレームワークでは非常に困難なあらゆる種類の興味深いことができます。 PostgreSQLでは、これは上記のアドオンであり、論理デコードにより、物理ログを論理的なものとして扱うことができます。 おそらくログのサイズ（ステートメントベースのMySQLレプリケーションが勝つ）を除いて、MySQLレプリケーションのすべての利点をすぐに取り去るのはこの機能です。報告書でもそれだけです）



PostgreSQLにはトリガーレプリケーションもあります。これはタングステンで、同じことができます。 トリガーレプリケーションは次のように機能します。トリガーが設定され、テーブルにデータが入力されるかファイルが書き込まれ、結果がレプリカに送信されてレプリカに適用されます。 私の知る限り、タングステンを介してMySQLからPostgreSQLに、またはその逆に移行します。 MySQLでは、論理レプリケーションはエンジンレベルで正しく機能しますが、今はもう1つできません。

ドキュメント

PostgreSQLには、はるかに優れたドキュメントがあります。 MySQLでは、正式に存在しているようにも見えますが、個々のオプションの意味を理解することは困難です。 彼らがやっていることを書いているようですが、正しく設定する方法を理解するには、非公式のドキュメントを使用して、これらのトピックに関する記事を探す必要があります。 多くの場合、MySQLのアーキテクチャを理解する必要があります。これを理解しないと、設定は何らかの魔法のように見えます。



たとえば、Percona社はこのように「解雇」しました。MySQLパフォーマンスブログを運営し、このブログにはMySQL操作の特定の側面を扱った多くの記事がありました。 これは大人気をもたらし、クライアントをコンサルティングに導き、リソースを集めてPercona-Serverの独自のフォークの開発を開始することができました。 MySQL Performance Blogの存在と関連性は、公式文書だけでは不十分であることを証明しています。



PostgreSQLのドキュメントには、ほぼすべての答えがあります。 一方、PostgreSQLのドキュメントを「大人」のOracleと比較するとき、多くの批判を耳にしました。 しかし、これは実際、非常に重要な指標です。 MySQLを大人のOracleと比較しようとする人は誰もいません-これはとんでもない、とんでもないことです-PostgreSQLはすでにかなり真剣に比較し始めています。PostgreSQLコミュニティはこの批判を聞き、製品の改善に取り組んでいます。 これは、その能力とパフォーマンスにおいて、MySQLがWebサイトのニッチのままである一方で、モバイルオペレーターと銀行を運営するOracleなどの強力なシステムと競合し始めることを示唆しています。 そして、大量のデータとユーザーに成長した巨大なプロジェクトは、大きなスプーンでMySQLに悲しみを吹き込み、その制限と、合理的な時間と労力を費やさなければ修正できないアーキテクチャ上の問題に常に休みます。



PostgreSQLでのそのような大規模なプロジェクトの例は1Cです。PostgreSQLはMicrosoft SQLの代わりにオプションとして提供され、Microsoft SQLは本当に素晴らしいDBMSであり、最も強力なものの1つです。 PostgreSQLはMS SQLを置き換えることができ、それをMySQLで置き換えようとします... Mark Twainが書いたように、このシーンでの哀れみのベールを下げましょう。

基準

PostgreSQLは、SQL-92、SQL-98、SQL-2003標準に準拠しており（すべての妥当な部分が実装されています）、SQL-2011で既に動作しています。 これはとてもクールです。 比較すると、MySQLはSQL-92もサポートしていません。 MySQLでは、この目標は開発者によって設定されたものではないと誰かが言うでしょう。 しかし、標準のバージョン間の違いは小さな変更ではないことを理解する必要があります-これらは新しい機能です。 つまり、MySQLが「標準に準拠しません」と言った時点で、若干の違いが生じ、MySQLの保守が困難になっただけでなく、多くの必要かつ重要な機能の実装方法が閉じられました。 通常のオプティマイザーはまだありません。 PostgreSQLで最適化と呼ばれるものは、「パーサー」プラス正規化と呼ばれます。 MySQLでは、これは分離のない単なるクエリ実行計画です。 また、MySQLは、下位互換性の負荷に圧迫されているため、標準をすぐにサポートするようになります。 はい、彼らは望んでいますが、約5年後には何かが思いつくでしょう。 PostgreSQLにすべてが揃っています。

パフォーマンスと管理の複雑さ

管理の容易さの観点から、比較はPostgreSQLを支持していません。 MySQLは管理がはるかに簡単です。 そして、この意味で考えたほうが良いというわけではありませんが、単にできることがはるかに少ないからです。 したがって、より簡単に構成できます。



MySQLには複雑なクエリに関する問題があります。 たとえば、MySQLはグループ化をすべての結合の個別の部分に下げる方法を知りません。 2つのクエリの違い-この例では、上記の個別のテーブルとユニオンによるグループ化は、ユニオンオールよりも15倍速く動作し、その後グループ化しましたが、オプティマイザは両方のクエリを同じ効率的なクエリ実行プランに入れる必要があります。 このようなクエリの生成は、手作業で行う必要があります。つまり、開発者がベースですべきことに時間を費やす必要があります。



上記のように、MySQLの「シンプルさ」は、非常に貧弱な機能に起因します。MySQLの動作はさらに悪く、開発中により多くの時間と労力が必要です。 対照的に、PostrgreSQLにはヒストグラムと通常のオプティマイザーがあり、このようなクエリを効率的に実行します。 しかし、ヒストグラムがある場合、それらの設定があります-少なくともバケットサイズ。 設定について知る必要があり、場合によっては変更する必要があります。したがって、設定の種類、責任、理解できるようにし、最適なパラメーターの選択方法を確認する必要があります。



時折、PostrgreSQLのスキルが助けになるのではなく、妨げになることがあります。 95％のケースでは、すべてがうまく機能し（MySQLよりも優れています）、一部のバカなクエリがはるかに遅くなります。 または、すべてがうまく機能し、その後突然（ユーザーの観点から）、プロジェクトが成長するにつれて、一部のリクエストがうまく機能しなくなりました（より多くのデータがあり、リクエストを実行するための別の計画が選択され始めました）。 ほとんどの場合、修正するには、分析を実行するか、設定を少し変更します。 しかし、あなたは何をすべきか、それをどのように行うべきかを知る必要があります。 少なくとも、このトピックに関するPostgreSQLのドキュメントを読む必要があります。何らかの理由で、ドキュメントを読むのが好きではありません。 おそらく、MySQLがそれをほとんど助けていないからでしょうか？ :)



この意味でのPostgreSQLは悪化することはなく、問題を延期することができるだけであり、MySQLはすぐに問題から脱落し、問題を解決するために時間とお金を費やす必要があることを強調します。 この意味で、MySQLは常に安定してうまく動作せず、開発段階であっても、人々はこれらの機能を考慮に入れます。可能な限り簡単な方法ですべてを実行します。 これは、生産性、より正確には、その達成方法とその予測可能性にのみ適用されます。 正確さと利便性の観点から、PostgreSQLはMySQLよりも優れています。

それで、何を選ぶべきですか？

特定のプロジェクトでMySQLとPostgreSQLを決定するには、最初に他の質問に答える必要があります。



まず、チームにはどのような経験がありますか？ チーム全体でMySQLの使用経験が10年あり、できるだけ早く開始する必要がある場合は、使い慣れたツールを使い慣れていないツールに変更する価値があるという事実ではありません。 ただし、タイミングが重要でない場合は、PostgreSQLを試してください。



第二に、操作の問題を忘れてはなりません。 負荷の高いプロジェクトがない場合、パフォーマンスの観点から、2つのDBMSに違いはありません。 しかし、PostgreSQLにはもう1つの重要な利点があります。それは、より厳密で、より多くのチェックを行い、ミスを犯す機会が少なくなることです。これは将来の大きな利点です。 たとえば、MySQLでは、独自のツールを作成して、データベースの通常の参照整合性を検証する必要があります。 そして、これも問題になる可能性があります。 この意味で、PostgreSQLツールはより強力で柔軟性が高く、開発がより快適です。 しかし、これは開発者の経験に大きく依存します。



要約すると、シンプルなオンラインストアを所有している場合、管理者にお金がなく、大きなプロジェクトに成長するという深刻な野心はなく、MySQLを使用した経験があり、MySQLを使用します。 プロジェクトが人気があると仮定した場合、プロジェクトが大規模な場合、複雑なロジックとテーブル間の関係がある場合、書き換えることは困難です。PostgreSQLを使用してください。 箱から出しても、それはあなたのために働き、開発を助け、時間を節約し、あなたが成長しやすくなります。