いくつかの重要な MySQL パフォーマンス インデックスの計算および最適化方法のコードの概要
- 黄舟オリジナル
- 2017-03-23 14:02:161513ブラウズ
以下のエディターは、MySQL の重要なパフォーマンス インデックスの計算と最適化方法の概要を示します。編集者はこれがとても良いものだと思ったので、皆さんの参考として今から共有します。エディターをフォローして見てみましょう
1 QPS 計算 (1 秒あたりのクエリ数)
MyISAM エンジンに基づく DB の場合
MySQL> show GLOBAL status like 'questions'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | Questions | 2009191409 | +---------------+------------+ 1 row in set (0.00 sec) mysql> show global status like 'uptime'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 388402 | +---------------+--------+ 1 row in set (0.00 sec)
QPS=questions/uptime=5172、mysql の開始以来の平均 QPS 、特定の期間内の QPS を計算したい場合は、ピーク期間の間隔 t2-t1 を取得し、t2 と t1 での q 値をそれぞれ計算します。QPS=(q2-q1)/( t2-t1)
InnnoDB エンジン
mysql> show global status like 'com_update'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Com_update | 87094306 | +---------------+----------+ 1 row in set (0.00 sec) mysql> show global status like 'com_select'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | Com_select | 1108143397 | +---------------+------------+ 1 row in set (0.00 sec) mysql> show global status like 'com_delete'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Com_delete | 379058 | +---------------+--------+ 1 row in set (0.00 sec) mysql> show global status like 'uptime'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 388816 | +---------------+--------+ 1 row in set (0.00 sec)
QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076 に基づく DB の場合、一定期間内の QPS クエリ方法は上記と同じです。
2 TPSの計算(1秒あたりのトランザクション数)
mysql> show global status like 'com_commit'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Com_commit | 7424815 | +---------------+---------+ 1 row in set (0.00 sec) mysql> show global status like 'com_rollback'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | Com_rollback | 1073179 | +---------------+---------+ 1 row in set (0.00 sec) mysql> show global status like 'uptime'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Uptime | 389467 | +---------------+--------+ 1 row in set (0.00 sec) TPS=(com_commit+com_rollback)/uptime=22
3 スレッド接続数とヒット率
mysql> show global status like 'threads_%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 480 | //代表当前此时此刻线程缓存中有多少空闲线程 | Threads_connected | 153 | //代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数 | Threads_created | 20344 | //代表从最近一次服务启动，已创建线程的数量 | Threads_running | 2 | //代表当前激活的（非睡眠状态）线程数 +-------------------+-------+ 4 rows in set (0.00 sec) mysql> show global status like 'Connections'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | Connections | 381487397 | +---------------+-----------+ 1 row in set (0.00 sec) 线程缓存命中率=1-Threads_created/Connections = 99.994% 我们设置的线程缓存个数 mysql> show variables like '%thread_cache_size%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | thread_cache_size | 500 | +-------------------+-------+ 1 row in set (0.00 sec)
Threads_connectedによれば、一般的に、thread_cache_sizeの値をどれくらいの大きさに設定すべきかを見積もることができます。 250 が適切な値です。メモリが十分に大きい場合は、thread_cache_size 値を thread_connected 値と同じに設定することもできます
または、threads_created 値を観察して、値が大きいか増加し続けている場合は、値を増やすこともできます。スリープ状態では、thread_cache_size の値を適切に設定します。各スレッドは約 256KB のメモリを占有するため、十分なメモリがある場合は、値が数千を超えない限り、値を小さくしすぎてもメモリを節約できません。
4 テーブルキャッシュ
mysql> show global status like 'open_tables%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_tables | 2228 | +---------------+-------+ 1 row in set (0.00 sec)
開いているテーブルに対して設定したキャッシュとテーブル定義キャッシュ
mysql> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 16384 | +------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'table_defi%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | table_definition_cache | 2000 | +------------------------+-------+ 1 row in set (0.00 sec)
MyISAMの場合:
mysqlがテーブルを開くたびに、一部のデータが読み込まれますtable_open_cache キャッシュ、mysql がこのキャッシュ内に対応する情報を見つけられない場合、ディスクから直接読み取るため、テーブル定義を再度開いたり再解析したりする必要がないように、値を十分に大きく設定する必要があります。一般に、この値は設定されます。 max_connections 倍の 10 までですが、10,000 以内に保つのが最善です。
もう 1 つの基準は、ステータス open_tables の値に応じて設定することです。open_tables の値が秒ごとに大きく変化する場合は、table_open_cache の値を増やす必要があるかもしれません。
table_diction_cache は、数万のテーブルがある場合を除き、通常、サーバーに存在するテーブルの数に単純に設定されます。
InnoDB の場合:
MyISAM とは異なり、InnoDB の開いているテーブルと開いているファイルは直接関連していません。つまり、frm テーブルが開かれるときに対応する ibd ファイルが閉じられる可能性があります
したがって、InnoDB は table_definiton_cache のみを使用します。 、table_open_cache は使用されません。
frm ファイルは table_definition_cache に保存され、idb は innodb_open_files によって決定されます (innodb_file_per_table がオンになっている場合)。サーバーがすべての .ibd を保持できるように、innodb_open_files を十分な大きさに設定するのが最善です。ファイルを同時に開きます。
5 最大接続数
mysql> show global status like 'Max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 1785 | +----------------------+-------+ 1 row in set (0.00 sec)
設定した max_connections サイズ
mysql> show variables like 'max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 4000 | +-----------------+-------+ 1 row in set (0.00 sec)
通常、max_connections のサイズは、Max_used_connections ステータス値より大きく設定する必要があります。Max_used_connections ステータス値は、サーバーにスパイクがあるかどうかを反映します。この値が max_connections 値より大きい場合は、クライアントが少なくとも 1 回拒否されたことを示しており、次の条件を満たすように単純に設定できます: Max_used_connections/max_connections=0.8
6 Innodb キャッシュ ヒット率
mysql> show global status like 'innodb_buffer_pool_read%'; +---------------------------------------+--------------+ | Variable_name | Value | +---------------------------------------+--------------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 268720 | //预读的页数 | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 480291074970 | //从缓冲池中读取的次数 | Innodb_buffer_pool_reads | 29912739 | //表示从物理磁盘读取的页数 +---------------------------------------+--------------+ 5 rows in set (0.00 sec)
バッファ プール ヒット率 = (Innodb_buffer_pool_read_requests )/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=99.994%
値が 99.9% 未満の場合は、増やすことをお勧めしますinnodb_buffer_pool_size の値。値は通常、合計メモリ サイズの 75% ～ 85% に設定されるか、システムに必要なキャッシュ + 各 mysql 接続に必要なメモリ (ソート バッファや一時テーブルなど) + MyISAM キー キャッシュを計算して計算されます。残りのメモリは innodb_buffer_pool_size に与えられますが、あまり大きく設定しないでください。頻繁にメモリ交換とウォームアップが発生し、シャットダウン時間が長くなります。
7 MyISAM キーバッファのヒット率とバッファ使用率
mysql> show global status like 'key_%'; +------------------------+-----------+ | Variable_name | Value | +------------------------+-----------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 106662 | | Key_blocks_used | 107171 | | Key_read_requests | 883825678 | | Key_reads | 133294 | | Key_write_requests | 217310758 | | Key_writes | 2061054 | +------------------------+-----------+ 7 rows in set (0.00 sec) mysql> show variables like '%key_cache_block_size%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | key_cache_block_size | 1024 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%key_buffer_size%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | key_buffer_size | 134217728 | +-----------------+-----------+ 1 row in set (0.00 sec)
バッファ使用率=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size)=18.6%
読み取りヒット率=1-Key_reads /Key_read_requests= 99.98%
書くhit rate = 1-Key_writes / Key_write_requests =99.05%
長時間経ってもすべてのキー バッファーが使い尽くされていない場合は、バッファー ターンの使用を検討できます。少し下がった。
キー キャッシュ ヒット率はアプリケーションに関連するため、あまり意味がない場合があります。アプリケーションによっては 95% のヒット率でうまく動作する場合もあれば、99.99% を必要とする場合もあるため、経験上、1 秒あたりのキャッシュ ミス数の方が重要です。 、スタンドアロン ディスクが 1 秒あたり 100 回のランダム読み取りを実行できると仮定すると、1 秒あたり 5 回のバッファ ミスはビジー I/O を引き起こさない可能性がありますが、1 秒あたり 80 回の場合は問題が発生する可能性があります。
1 秒あたりのキャッシュミス数=Key_reads/uptime=0.33
8 临时表使用情况
mysql> show global status like 'Created_tmp%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Created_tmp_disk_tables | 19226325 | | Created_tmp_files | 117 | | Created_tmp_tables | 56265812 | +-------------------------+----------+ 3 rows in set (0.00 sec) mysql> show variables like '%tmp_table_size%'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 67108864 | +----------------+----------+ 1 row in set (0.00 sec)
可看到总共创建了56265812 张临时表，其中有19226325 张涉及到了磁盘IO，大概比例占到了0.34，证明数据库应用中排序，join语句涉及的数据量太大，需要优化SQL或者增大tmp_table_size的值，我设的是64M。该比值应该控制在0.2以内。
9 binlog cache使用情况
mysql> show status like 'Binlog_cache%'; +-----------------------+----------+ | Variable_name | Value | +-----------------------+----------+ | Binlog_cache_disk_use | 15 | | Binlog_cache_use | 95978256 | +-----------------------+----------+ 2 rows in set (0.00 sec) mysql> show variables like 'binlog_cache_size'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | binlog_cache_size | 1048576 | +-------------------+---------+ 1 row in set (0.00 sec)
Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数
Binlog_cache_use 表示 用binlog_cache_size缓存的次数
当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值
10 Innodb log buffer size的大小设置
mysql> show variables like '%innodb_log_buffer_size%'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | innodb_log_buffer_size | 8388608 | +------------------------+---------+ 1 row in set (0.00 sec) mysql> show status like 'innodb_log_waits'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Innodb_log_waits | 0 | +------------------+-------+ 1 row in set (0.00 sec)
innodb_log_buffer_size我设置了8M，应该足够大了；Innodb_log_waits表示因log buffer不足导致等待的次数，如果该值不为0，可以适当增大innodb_log_buffer_size的值。
11 表扫描情况判断
mysql> show global status like 'Handler_read%'; +-----------------------+--------------+ | Variable_name | Value | +-----------------------+--------------+ | Handler_read_first | 19180695 | | Handler_read_key | 30303690598 | | Handler_read_last | 290721 | | Handler_read_next | 51169834260 | | Handler_read_prev | 1267528402 | | Handler_read_rnd | 219230406 | | Handler_read_rnd_next | 344713226172 | +-----------------------+--------------+ 7 rows in set (0.00 sec)
Handler_read_first：使用索引扫描的次数，该值大小说不清系统性能是好是坏
Handler_read_key：通过key进行查询的次数，该值越大证明系统性能越好
Handler_read_next：使用索引进行排序的次数
Handler_read_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC
Handler_read_rnd：该值越大证明系统中有大量的没有使用索引进行排序的操作，或者join时没有使用到index
Handler_read_rnd_next：使用数据文件进行扫描的次数，该值越大证明有大量的全表扫描，或者合理地创建索引，没有很好地利用已经建立好的索引
12 Innodb_buffer_pool_wait_free
mysql> show global status like 'Innodb_buffer_pool_wait_free'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | Innodb_buffer_pool_wait_free | 0 | +------------------------------+-------+ 1 row in set (0.00 sec)
该值不为0表示buffer pool没有空闲的空间了，可能原因是innodb_buffer_pool_size设置太大，可以适当减少该值。
13 join操作信息
mysql> show global status like 'select_full_join'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Select_full_join | 10403 | +------------------+-------+ 1 row in set (0.00 sec)
该值表示在join操作中没有使用到索引的次数，值很大说明join语句写得很有问题
mysql> show global status like 'select_range'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | Select_range | 22450380 | +---------------+----------+ 1 row in set (0.00 sec)
该值表示第一个表使用ranges的join数量，该值很大说明join写得没有问题，通常可查看select_full_join和select_range的比值来判断系统中join语句的性能情况
mysql> show global status like 'Select_range_check'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | Select_range_check | 0 | +--------------------+-------+ 1 row in set (0.00 sec)
如果该值不为0需要检查表的索引是否合理，表示在表n+1中重新评估表n中的每一行的索引是否开销最小所做的联接数，意味着表n+1对该联接而言并没有有用的索引。
mysql> show GLOBAL status like 'select_scan'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | Select_scan | 116037811 | +---------------+-----------+ 1 row in set (0.00 sec)
select_scan表示扫描第一张表的连接数目，如果第一张表中每行都参与联接，这样的结果并没有问题；如果你并不想要返回所有行但又没有使用到索引来查找到所需要的行，那么计数很大就很糟糕了。
14 慢查询
mysql> show global status like 'Slow_queries'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Slow_queries | 114111 | +---------------+--------+ 1 row in set (0.00 sec)
该值表示mysql启动以来的慢查询个数，即执行时间超过long_query_time的次数，可根据Slow_queries/uptime的比值判断单位时间内的慢查询个数，进而判断系统的性能。
15表锁信息
mysql> show global status like 'table_lock%'; +-----------------------+------------+ | Variable_name | Value | +-----------------------+------------+ | Table_locks_immediate | 1644917567 | | Table_locks_waited | 53 | +-----------------------+------------+ 2 rows in set (0.00 sec)
这两个值的比值：Table_locks_waited /Table_locks_immediate 趋向于0，如果值比较大则表示系统的锁阻塞情况比较严重
以上がいくつかの重要な MySQL パフォーマンス インデックスの計算および最適化方法のコードの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。