Q&A
遅いときと起動直後の
SHOW ENGINE INNODB STATUS\G の結果を質問欄に追記してください。
MYSQLについての質問です。
環境-------------
MYSQL5.7 x64 innoDB
OS windows2012R2
CPU XEON E55042
メモリ 90GB
SSD2
mysql odbc 5.2にて接続
上記環境でMYSQLサーバーを運用中です。
MYSQL起動直後に簡単なSELECT文を投げると、１秒以内にレスポンスが返ってくるのですが、
起動後３時間ほどするとレスポンスに３分以上かかります。
簡単なコマンドを投げても3分ほどかかります。
環境では起動直後より、MYSQLに対して大量の接続があります。
なお、遅いときの状況は、
CPU使用率はおおむね１０％程度
メモリは50Gほど使用中
ディスクアクセスは10MB/s程度
CPUメモリなどまだまだ余裕はあると思うのですが・・・・
my.iniでの設定は、
innodb_buffer_pool_size=50G
innodb_log_file_size=10G
などにしています。
サーバーを再起動すると、1秒以内のレスポンス状態に復旧します。
show processlistにて調べたりしているのですが、現状はお手上げ状態です。
宜しくお願いいたします。
---TRANSACTION 570452973, ACTIVE 6042 sec
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 1448591, OS thread handle 31896, query id 172367513 localhost 12
7.0.0.1 root altering table
optimize table `category`, `category_data`, `crow_host`, `crow_list`, `image_dat
a`, `item_index`, `log_data`, `page_data`, `rakuma_list_rapid`, `rate_data`, `ra
te_index`, `store_info`, `system_check`, `yahoo_proxy_crow_tmp`
Trx read view will not see trx with id >= 570452984, sees < 570451079
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
2690676 OS file reads, 19810553 OS file writes, 673639 OS fsyncs
0 pending preads, 1 pending pwrites
47.73 reads/s, 405602 avg bytes/read, 573.19 writes/s, 32.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 5, free list len 17482, seg size 17488, 233907 merges
merged operations:
insert 556294, delete mark 23677, delete 10165
discarded operations:
insert 40, delete mark 1, delete 0
Hash table size 13281221, node heap has 1 buffer(s)
Hash table size 13281221, node heap has 2656 buffer(s)
Hash table size 13281221, node heap has 2018 buffer(s)
Hash table size 13281221, node heap has 1271 buffer(s)
Hash table size 13281221, node heap has 2 buffer(s)
Hash table size 13281221, node heap has 5153 buffer(s)
Hash table size 13281221, node heap has 56292 buffer(s)
Hash table size 13281221, node heap has 404 buffer(s)
1892.77 hash searches/s, 4089.23 non-hash searches/s
---
LOG
---
Log sequence number 356781479876
Log flushed up to 356781258839
Pages flushed up to 354630751690
Last checkpoint at 354630591734
0 pending log flushes, 0 pending chkp writes
16127603 log i/o's done, 420.53 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 54923362304
Dictionary memory allocated 525165
Buffer pool size 3276800
Free buffers 8176
Database pages 3200827
Old database pages 1181394
Modified db pages 1044085
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1392396, not young 32845733
182.70 youngs/s, 81.76 non-youngs/s
Pages read 2636386, created 708809, written 3248770
29.73 reads/s, 4.29 creates/s, 122.73 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3200827, unzip_LRU len: 0
I/O sum[55480]:cur[1040], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 409600
Free buffers 1021
Database pages 400105
Old database pages 147676
Modified db pages 132879
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 250216, not young 4516646
18.44 youngs/s, 2.62 non-youngs/s
Pages read 325654, created 89025, written 407604
1.06 reads/s, 0.29 creates/s, 21.73 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400105, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 409600
Free buffers 1021
Database pages 400130
Old database pages 147684
Modified db pages 128154
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 165544, not young 3842938
24.44 youngs/s, 9.94 non-youngs/s
Pages read 328708, created 88987, written 393108
4.03 reads/s, 0.18 creates/s, 12.85 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400130, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 409600
Free buffers 1024
Database pages 400087
Old database pages 147668
Modified db pages 129887
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 162936, not young 4147472
25.97 youngs/s, 15.65 non-youngs/s
Pages read 329386, created 89857, written 408751
4.74 reads/s, 1.21 creates/s, 12.23 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 14 / 1000 not 8 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400087, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 409600
Free buffers 1019
Database pages 400047
Old database pages 147653
Modified db pages 130205
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 167180, not young 3859430
29.29 youngs/s, 13.32 non-youngs/s
Pages read 332355, created 88825, written 416835
4.88 reads/s, 0.32 creates/s, 14.44 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 5 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400047, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 409600
Free buffers 1020
Database pages 400085
Old database pages 147668
Modified db pages 129633
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 166197, not young 3952670
25.68 youngs/s, 13.12 non-youngs/s
Pages read 332836, created 87911, written 401653
4.88 reads/s, 0.41 creates/s, 13.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 8 / 1000 not 4 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400085, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 409600
Free buffers 1023
Database pages 400125
Old database pages 147682
Modified db pages 127782
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 38333, not young 3570795
10.56 youngs/s, 8.47 non-youngs/s
Pages read 327580, created 86857, written 407080
3.47 reads/s, 0.09 creates/s, 18.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400125, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 409600
Free buffers 1024
Database pages 400209
Old database pages 147713
Modified db pages 130664
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 166181, not young 4521525
26.03 youngs/s, 15.73 non-youngs/s
Pages read 332800, created 88098, written 408923
5.32 reads/s, 0.32 creates/s, 14.23 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400209, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 409600
Free buffers 1024
Database pages 400039
Old database pages 147650
Modified db pages 134881
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 275809, not young 4434257
22.29 youngs/s, 2.91 non-youngs/s
Pages read 327067, created 89249, written 404816
1.35 reads/s, 1.47 creates/s, 15.91 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 400039, unzip_LRU len: 0
I/O sum[6935]:cur[130], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=94140, Main thread ID=87332, state: sleeping
Number of rows inserted 4250720, updated 13483323, deleted 0, read 6535654456
129.70 inserts/s, 300.43 updates/s, 0.00 deletes/s, 204790.01 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
抜粋となりますが、この内容でよろしいでしょうか？
遅いときの内容を記載させていただきました。起動直後については今再起動できませんので、後ほどアップさせていただきます。
起動直後はもう大丈夫です。
多分遅くなり始めて間もないときの結果ですね。
負荷分散のためにハード増強が必要だと感じました。
それも1～2台の追加程度ではなく、MySQL Cluster によるクラスタ構成も視野に入れたほうがいいかもしれません。
ご回答ありがとうございます。
この結果より、CPU、メモリ、ディスク どの部分がボトルネックとなっているのでしょうか？
（結果数値を見てどうなのか理解できていません）
メモリ以外は使い切っていないので問題ないのかな？と思っていますが・・・
バッファ(ストレージキャッシュ)をほぼ使い切ってますね。
innodb_buffer_pool_size を増やせば遅くなるまでの時間は伸びると思いますが、
倍にして遅くなるまでの時間が倍になったとしても根本的な解決にはならないではないでしょうか？
最近のInnoDBの性能は分かりませんが、InnoDBが効率よく扱えるメモリもそんなに多くないと思います。
ただしそれで単純なSELECTが3分もかかる理由には弱いので、何か別の原因があるかもしれません。
また、Buffer pool hit rate がとても高いので
DBとフロントエンドとの間にキャッシュする仕組みを作る。
や
フロントエンド側で問い合わせ減らす。
などの解決方法もあるかもしれません。
あとすべてのテーブルのデータ量の合計も記載してください。
書き忘れましたが、不要なデータを削除してないなんてことはないですよね。
データベースのデータ量は90GBになります。
最も大きなテーブルで約１億レコードです。
インデックスを張っているので最大テーブルの検索でも起動直後は時間が掛からず結果が返ってきています。
不要なデータを削除とはDELETEした部分ということでしょうか？
基本的にINSERTかUPDATEのみのデータベースになります。
> データベースのデータ量は90GBになります。
思ったより小さいので、メモリーが増やせるなら、innodb_buffer_pool_sizeを90GB取れれば改善するかもしれません。
> 不要なデータを削除とはDELETEした部分ということでしょうか？
要らないデータをため込んでいる可能性があるかもと思ったのですが、そんなことはなさそうですね。
いろいろ書きましたが、なにしろ１台で処理できるアクセス量を大きく超えていると思います。
細かく色々手間をかけるより負荷分散のためにサーバ増設が一番手っ取り早くて確実で、多分安上がりだと思います。
CPU性能はそんなに要らないと思います。
回答1件
0
ベストアンサー
その後も行ロックなども色々調べましたが、解決には至りませんでした。
結論としてSQLSERVERに乗り換えを行い、デフォルト設定でも問題なく動作しました。
その後、チューニングを行い、同じ構成のCPU、メモリなどで問題なく動作しましたので、理由が気になりますが、SQLSERVERを使用する事にいたします。
投稿2019/09/26 03:11
退会済みユーザー
総合スコア0
あなたの回答
tips
プレビュー
