Bitrix24の別の12個のMySQL「レシピ」

前回の記事「Bitrix24の11 MySQL Cooking Recipes」では 、主にアーキテクチャソリューションについて検討しました。クラウドサービス（Amazon RDSなど）を使用する価値があるか、MySQLを選択するかなどです。



レビューから判断すると、大規模な「高負荷」プロジェクトでのMySQLの有能な運用のトピックは非常に大きく重要です。 そのため、 Bitrix24の開発中に遭遇し、日常的に使用しているデータベースのチューニングと管理の微妙な違いについて、さらに話し合うことにしました。



この記事（および以前の記事）は、あらゆる場面で完璧なMySQLチューニングを行うための普遍的な「レシピ」ではないことを改めて思い出します。 :)これは起こりません。 :)しかし、私はあなたが個々の特定の問題を解決することが役立つと心から信じています。



そして、記事の終わりに-ほとんどの患者の読者のための驚き。 :)





1. QUERY CACHEのセットアップ



MySQLでクエリキャッシュがどのように機能するか、およびそれを設定して使用する方法を説明する記事が数多く書かれています。



それでも、データベースをセットアップするシステム管理者の最も一般的な誤解は、「 キャッシュ用のメモリを多く提供するほど良い 」 です 。



そうではありません。



MySQLは大きなクエリキャッシュを処理しません。 実際には、 query_cache_sizeが512Mを超えると、 「クエリキャッシュロックの待機」状態（SHOW PROCESSLISTで表示）で短時間ハングするプロセスが頻繁に表示されるという事実に遭遇しました。



さらに、すべてのリクエストがキャッシュに落ちた場合、それを増やすことは賢明ではありません。 システム内のRAMは貴重です！



したがって、プロジェクトで何が起こっているのか、現在の設定でクエリキャッシュがどの程度効果的に使用されているのかを理解することが常に重要です。



主な情報は次のとおりです。

mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 10541 | | Qcache_free_memory | 36381984 | | Qcache_hits | 18888719 | | Qcache_inserts | 5677585 | | Qcache_lowmem_prunes | 1725258 | | Qcache_not_cached | 6096307 | | Qcache_queries_in_cache | 36919 | | Qcache_total_blocks | 97285 | +-------------------------+----------+ 8 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最も重要なのは、Qcache_hitsとQcache_inserts、Qcache_insertsとQcache_not_cached、およびQcache_lowmem_prunes（キャッシュからキャッシュされた要求の数）とQcache_free_memoryの比率です。



これらの統計をときどき見るのではなく、分析を手元に用意することをお勧めします。 さまざまな監視ツールを使用して収集できます。 たとえば、Munin：











ダイナミクスのグラフを見ると、たとえば1日に1回、最終的に1つのサーバーのこれらの設定で十分であるという結論に達しました。

 query_cache_size = 128M query_cache_limit = 2M
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

もちろん、おそらくあなたのプロジェクトにとっては状況は異なるでしょう。



2. innodb_buffer_pool_size



バッファープールのサイズは、InnoDBの最も重要な設定の1つです。 これは、データキャッシュとテーブルインデックスの作業中にMySQLが使用するメモリバッファーのサイズです（MySQLとオペレーティングシステムによるダブルキャッシュを回避するために、設定でinnodb_flush_method = O_DIRECTを指定する価値があることをすぐに思い出します）。



MySQL開発者の側から見た最大の犯罪の1つは、デフォルト値を8Mに設定することです。 :)



実際、理想的には、 innodb_buffer_pool_sizeの値は、データベース全体がメモリに収まるようにする必要があります。 同時に、システムはメモリとバランスが取れていることを覚えておくことが重要です（これについては前回の記事で説明しました ） -innodb_buffer_pool_sizeの値を「引き裂いて」、システム全体がスワップするようになると、何もうまくいきません。



適切なセットアップの良い指標は、バッファプールのヒット率です。

 mysql> SHOW ENGINE InnoDB STATUS\G ... ---------------------- BUFFER POOL AND MEMORY ---------------------- ... Buffer pool hit rate 994 / 1000, young-making rate 6 / 1000 not 0 / 1000 ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

値が「1000/1000」に近い場合-すべては順調です。 それ以外の場合は、 innodb_buffer_pool_sizeを増やす必要があります。 十分なメモリがない場合は、メモリを追加します。



3. innodb_buffer_pool_instances



デフォルトでは、InnoDBはバッファープールに1つのインスタンスを使用します。



同時に、複数のブロックを選択することができます。MySQLは、InnoDBでそれらのブロックをより効率的に使用する場合があります。



バッファープールは、2 GBを超える場合、いくつかのインスタンスに分割する必要があります。 各インスタンスのサイズは1 GB以上の価値があります。



ここに、多くの人が混同している重要な質問があります： innodb_buffer_pool_sizeはプールの合計サイズですか、それとも1つのインスタンスのサイズですか？



答えはドキュメントにあります-これは合計サイズです。 したがって、たとえば、次のような構成があります。

 innodb_buffer_pool_size = 4096M innodb_buffer_pool_instances = 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

...各インスタンスが1 GBを占有すると言います。



インシデントがないことに注意してください（MySQL DBAにサブスクライブしている友人の1人が、設定innodb_buffer_pool_size = 1024M、innodb_buffer_pool_instances = 64-64 GBの割り当てを計画してMySQLを操作した経験を説明しました。



4. innodb_io_capacity



興味深いパラメータ（公式ドキュメントを信じている場合:)）、パフォーマンスにはあまり影響しませんが、実際には正しく設定されていればある程度の利益が得られます。



innodb_io_capacityは、バックグラウンドで実行されるInnoDB操作（たとえば、バッファープールからディスクへのページのダンプ）の入出力操作（IOPS）の制限を設定します。



デフォルト値は200です。



値が小さすぎると、これらの操作が「遅れ」ます。 大きすぎると、バッファプールからのデータがすぐにフラッシュされます。



理想的には、ディスクシステムの実際のパフォーマンスに対応する値を設定する必要があります（再び、IOPSで）。



5. innodb_file_per_table



デフォルトでは、InnoDBのMySQLはすべてのテーブルのデータとインデックスを1つのテーブルスペース（ibdata1ファイル）に保存します。



設定でinnodb_file_per_tableオプションが使用されている場合、この場合、データとインデックスが保存されているテーブルごとに個別のファイルtable_name.ibdが作成されます。



これは理にかなっていますか？



私個人の意見では、標準のMySQLを使用する場合、使用する必要はありません。 テーブルを別々のファイルに保存すると、パフォーマンスが低下する可能性があります。これは、システム内でリソースを大量に消費する「高価な」操作が劇的に増えるためです。



私たちはPercona Serverを使用しています。 そして、 innodb_file_per_tableを使用します 。

• 通常のMySQLで何らかの理由で1つまたは複数のテーブルが破られた場合、データベース全体が「立ち上がり」ます。 Perconでは、 innodb_corrupt_table_action = assertオプションを使用できます。innodb_file_per_tableを使用すると、 「壊れた」テーブルがマークされますが、データベース全体は引き続き機能します。
• innodb_file_per_tableを使用すると、パフォーマンスが明らかに低下する操作を「スピードアップ」する機会がすでにあります。 たとえば、テーブルの削除。 そのような目的のために、Perconにはオプションinnodb_lazy_drop_table = 1があります。これは、バックグラウンドでこのような操作を許可し、システム全体のパフォーマンスを低下させません。
• innodb_file_per_tableオプションをオンにすると、XtraBackupを使用して、テーブルの高速バイナリインポート/エクスポートを実行できます。

6. max_connect_errors



別の犯罪者:) MySQL開発者の陰謀。 max_connect_errorsのデフォルト値は10です。



これは、多かれ少なかれアクティブなプロジェクトで、予期しない障害が発生した場合-短期的なもの（たとえば、スクリプトに誤ったパスワードが入力された、またはネットワークの問題が発生した場合）-ブロックされます。 MySQLサーバーが再起動されるか、FLUSH HOSTSコマンドが実行されるまで。



これは、手動介入の前（FLUSH HOSTSを数分ごとに実行するスクリプトを事前にcronに投稿しなかった場合のみ:)）プロジェクトが機能しないことを意味します。 これが夜間に発生し、24時間監視されていない場合は不快です。



事前に自分自身を保護し、 max_connect_errorsの値を大きく設定することをお勧めします。 例：

 max-connect-errors = 10000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

7.一時テーブル



システム内のRAMの容量が許す場合は、常にメモリ内の一時テーブルを操作することをお勧めします。



これを整理するのは簡単です。 MySQLの設定：

 tmpdir = /dev/shm
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ファイルシステムとパーティションの設定（Linuxの場合-/ etc / fstabファイル）：

 # <file system> <mount point> <type> <options> <dump> <pass> tmpfs /dev/shm tmpfs defaults 0 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

8.一時テーブルのサイズ



メモリ内のテーブルのサイズを担当する同様のパラメーターが2つあります。

 max_heap_table_size = 64M tmp_table_size = 64M
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

max_heap_table_size-ユーザーが作成できるMEMORYタイプのテーブルの最大サイズ。

tmp_table_size-メモリ内に作成される一時テーブルの最大サイズ（ディスク上）。



ディスクアクティビティが少ないほど優れています。 したがって、システム内のRAMの量が許容する場合（メモリバランスを覚えておいてください）、メモリ内のすべての一時テーブルを操作することをお勧めします。



9. table_cacheおよびtable_definition_cache

 table_cache = 4096 table_definition_cache = 4096
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これらの2つのオプションは、開いているテーブルのキャッシュに保存されるテーブルの最大数を決定します。



table_cacheの値は、システム内のテーブルの数、クエリで開くテーブルの数（たとえば、JOINを介してリンクされる）、およびデータベースへの開いている接続の数に直接依存します。



table_definition_cacheは、テーブル構造（.frm）ファイルのキャッシュサイズを定義します。 システム内にあるほど、 table_definition_cacheの値を大きく設定する必要があります。



10.長いリクエストの闘争



前回の記事では、Percona Serverがシステム全体のパフォーマンスを判断するための優れたツール（ SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME ）を持っていることを既に述べました。



さらに、どのMySQLでも、すべての「遅い」クエリをログに記録し、個別に解析することができます。



Percona Serverを使用すると、遅いクエリのログがより有益になります。

 log_output = FILE slow_query_log = 1 slow_query_log_file = mysql_slow.log long_query_time = 1 #percona log_slow_verbosity = microtime,query_plan,innodb
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

1秒より長く実行されるすべてのクエリは、mysql_slow.logに書き込まれます。 標準ログとは異なり、次のようになります。

 # Time: 120712 9:43:47 # User@Host: user[user] @ [10.206.66.207] # Thread_id: 3513565 Schema: user Last_errno: 0 Killed: 0 # Query_time: 1.279800 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 1 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 52 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # InnoDB_trx_id: 33E7689B # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # InnoDB_IO_r_ops: 0 InnoDB_IO_r_bytes: 0 InnoDB_IO_r_wait: 0.000000 # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 4 UPDATE b_user_option SET 'COMMON' = 'N', 'VALUE' = 'a:19', 'NAME' = 'openTab', 'CATEGORY' = 'IM' WHERE ID=1719;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリの実行時間、「スキャンされた」行の数などだけでなく、より詳細な情報-フルスキャン、一時テーブルの使用、InnoDBのステータスも確認できます。



これはすべて、遅いクエリとそのデバッグの分析に大いに役立ちます。



11. Perconaを使用しない詳細な統計



標準のMySQLを使用している場合でも、優れたクエリデバッグツールもあります（もちろん、既に「キャッチ」して識別している場合:)。たとえば、スロークエリの同じログを使用します）。



プロファイルのようなものがあります。 それらを使用しますか？ いや？ 無駄だ！

 mysql> SHOW PROFILES; Empty set (0.02 sec) mysql> SHOW PROFILE; Empty set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

プロファイリングをオンにして、リクエストを確認します。

 mysql> SET PROFILING=1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT COUNT(*) FROM mysql.user; +----------+ | COUNT(*) | +----------+ | 3024 | +----------+ 1 row in set (0.09 sec) mysql> SHOW PROFILES; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.09104400 | SELECT COUNT(*) FROM mysql.user | +----------+------------+---------------------------------+ 1 row in set (0.00 sec) mysql> SHOW PROFILE; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000018 | | Waiting for query cache lock | 0.000004 | | Waiting on query cache mutex | 0.000004 | | checking query cache for query | 0.000041 | | checking permissions | 0.000007 | | Opening tables | 0.090854 | | System lock | 0.000013 | | init | 0.000012 | | optimizing | 0.000007 | | executing | 0.000010 | | end | 0.000005 | | query end | 0.000004 | | closing tables | 0.000031 | | freeing items | 0.000029 | | logging slow query | 0.000003 | | cleaning up | 0.000004 | +--------------------------------+----------+ 16 rows in set (0.00 sec)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ネットワーク、ディスクの操作、キャッシュの使用など、ボトルネックが何であるかがすぐにわかります。



12.プロファイルの情報を使用する方法は？



単一の長いクエリを見つけることを学んだ場合（それほど難しくない-遅いクエリのログとSHOW PROCESSLISTが役立つ）、システム全体の状態を正しく評価する場合（ SELECT * FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME 、システムの外部監視、たとえばnagios- munin-分析）、システムのパフォーマンスに影響を与えるものを理解することは非常に重要です：

• システムのすべての内部リソース-ベースには、CPU、RAM、ディスクシステムのいずれも「多すぎる」ことはありません。
• ロック（テーブルレベル-MyISAMの場合が多く、行レベル-InnoDB）;
• 内部ロック（たとえば、「クエリキャッシュロックの待機」）。

これを理解し、プロファイルまたは低速クエリの拡張ログなどのデータを分析すると、データベースを操作するさらなる戦略を常に正しく評価できます-その場合、ハードウェアをアップグレードする必要があります-これらまたは他の設定を変更し、おそらく-再編成データおよびクエリ構造。



* * *



あらゆるボリュームのあらゆる負荷でのデータベースの正常なデバッグと正常な操作！ :)



* * *



この場所を読んでくれてありがとう！ :)







MySQLのヒントがお役に立てば幸いです！



そして、 Bitrix24プロジェクトでMySQLを操作した経験について話しているので、 Habréのブログのすべての読者にささやかな贈り物をしたいと思います。



指定されたリンクでBitrix24に登録すると、2倍のディスク（10 GB）が無料で入手できます！



突然Bitrix24が何であるかわからない場合は、 詳細な説明がWebサイトにあります。 :)