logo标签列表

如何在MySQL中清理临时表

mysqlinnodbtemporary
5
我们在过程中创建临时表，并且对于MySQL我们不使用持久连接。在MySQL重启后，许多临时表会缓慢增加，然后innodb_buffer_pool_size就会用尽。
该应用程序正在该服务器上运行；
CPU E3-1245 v5 @ 3.50GHz - 8核 16GB内存
下图显示了tmp表的增长情况： The photo shows tmp tables how increasing is My.cnf：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip_name_resolve

join_buffer_size = 4M
sort_buffer_size = 1M
read_rnd_buffer_size = 1M

# MyISAM #
key_buffer_size                 = 1G
# SAFETY #
max_allowed_packet              = 1G
# CACHES AND LIMITS #
tmp_table_size                 = 16M
max_heap_table_size            = 16M
query_cache_type               = 1
query_cache_size               = 1M
query_cache_limit              = 1M
max_connections                = 400
thread_cache_size              = 100
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 10G
innodb_buffer_pool_instances=8
thread_pool_size=36

InnoDB状态：

=====================================
2017-05-31 15:52:22 7fbabc7be700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 59 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9224 srv_active, 0 srv_shutdown, 9 srv_idle
srv_master_thread log flush and writes: 9233
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 134197
OS WAIT ARRAY INFO: signal count 204301
Mutex spin waits 1288150, rounds 3214506, OS waits 81031
RW-shared spins 54484, rounds 1299121, OS waits 39945
RW-excl spins 11585, rounds 514527, OS waits 12875
Spin rounds per wait: 2.50 mutex, 23.84 RW-shared, 44.41 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 11882875981
Purge done for trx's n:o < 11882875606 undo n:o < 0 state: running but idle
History list length 1889
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 11882874682, not started
MySQL thread id 73473, OS thread handle 0x7fbaafdb6700, query id 100862972 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882874410, not started
MySQL thread id 73472, OS thread handle 0x7fbaca279700, query id 100862026 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875975, not started
MySQL thread id 73447, OS thread handle 0x7fbacf446700, query id 100867583 XXX.XXX.XXX.XXX root closing tables
select * from `sportsbook_maindb`.`feed_event_odds` where `sportsbook_maindb`.`feed_event_odds`.`feed_event_id` in ('1811069')
---TRANSACTION 0, not started
MySQL thread id 73442, OS thread handle 0x7fbab09f7700, query id 100839940 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882867252, not started
MySQL thread id 73426, OS thread handle 0x7fbacfb21700, query id 100836223 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875822, not started
MySQL thread id 73428, OS thread handle 0x7fbab9b7d700, query id 100866992 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 73423, OS thread handle 0x7fbaafbef700, query id 100831913 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882872555, not started
MySQL thread id 73325, OS thread handle 0x7fbac77be700, query id 100855753 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875960, not started
MySQL thread id 73326, OS thread handle 0x7fbacfef0700, query id 100867542 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882843879, not started
MySQL thread id 73320, OS thread handle 0x7fbab37be700, query id 100746169 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882853250, not started
MySQL thread id 73229, OS thread handle 0x7fbac76ba700, query id 100786064 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875978, not started
MySQL thread id 73231, OS thread handle 0x7fbabf2fb700, query id 100867594 XXX.XXX.XXX.XXX root Opening tables
select * from `sportsbook_maindb`.`feed_events` where (`feed_id` = ? and `provider_event_id` = ? and `type` = ? and `sport_id` = ? and `category_id` = ? and `tournament_id` = ?) limit 1
---TRANSACTION 11882822134, not started
MySQL thread id 73227, OS thread handle 0x7fbacfa1d700, query id 100661334 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882820779, not started
MySQL thread id 73051, OS thread handle 0x7fbac2679700, query id 100655871 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882830983, not started
MySQL thread id 73136, OS thread handle 0x7fbac27be700, query id 100698647 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 73133, OS thread handle 0x7fbab0b7d700, query id 100549256 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875805, not started
MySQL thread id 73052, OS thread handle 0x7fbab27ff700, query id 100867298 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882782336, not started
MySQL thread id 73046, OS thread handle 0x7fbab0bbe700, query id 100427035 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882820807, not started
MySQL thread id 73031, OS thread handle 0x7fbaaffbe700, query id 100656320 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882777845, not started
MySQL thread id 73024, OS thread handle 0x7fbac273c700, query id 100398964 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882753941, not started
MySQL thread id 72930, OS thread handle 0x7fbac26fb700, query id 100251756 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875973, not started
MySQL thread id 72931, OS thread handle 0x7fbac3fff700, query id 100867580 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882752465, not started
MySQL thread id 72923, OS thread handle 0x7fbac4fff700, query id 100241832 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875977, not started
MySQL thread id 72832, OS thread handle 0x7fbaafb6d700, query id 100867592 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882734325, not started
MySQL thread id 72825, OS thread handle 0x7fbac3fbe700, query id 100112673 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882732381, not started
MySQL thread id 72809, OS thread handle 0x7fbac777d700, query id 100100554 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875893, not started
MySQL thread id 72810, OS thread handle 0x7fbac75b6700, query id 100867279 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 72806, OS thread handle 0x7fbab0afb700, query id 100089215 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882861085, not started
MySQL thread id 72712, OS thread handle 0x7fbabf2ba700, query id 100815085 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875962, not started
MySQL thread id 72713, OS thread handle 0x7fbaca238700, query id 100867543 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 72709, OS thread handle 0x7fbac76fb700, query id 99960334 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882861460, not started
MySQL thread id 72694, OS thread handle 0x7fbacf342700, query id 100816387 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875979, not started
MySQL thread id 72695, OS thread handle 0x7fbabf33c700, query id 100867609 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882711034, not started
MySQL thread id 72692, OS thread handle 0x7fbac7679700, query id 99941737 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882861262, not started
MySQL thread id 72673, OS thread handle 0x7fbab37ff700, query id 100816282 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875804, not started
MySQL thread id 72674, OS thread handle 0x7fbacb5b6700, query id 100867297 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 72670, OS thread handle 0x7fbabf37d700, query id 99922018 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11881700879, not started
MySQL thread id 66986, OS thread handle 0x7fbabc7ff700, query id 93448633 185.81.238.240 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 55432, OS thread handle 0x7fbaafdf7700, query id 75618608 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 7548, OS thread handle 0x7fbabc7be700, query id 100867611 185.81.238.240 root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 7538, OS thread handle 0x7fbac773c700, query id 100635363 185.81.238.240 root cleaning up
---TRANSACTION 11882245426, not started
MySQL thread id 31645, OS thread handle 0x7fbabf3ff700, query id 96827866 185.81.238.240 root cleaning up
---TRANSACTION 11882679493, not started
MySQL thread id 14852, OS thread handle 0x7fbac3f7d700, query id 99698982 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11873625990, not started
MySQL thread id 14125, OS thread handle 0x7fbaca1f7700, query id 99540454 185.81.238.240 root cleaning up
---TRANSACTION 0, not started
MySQL thread id 14109, OS thread handle 0x7fbac75f7700, query id 99547660 185.81.238.240 root cleaning up
---TRANSACTION 11868523434, not started
MySQL thread id 9205, OS thread handle 0x7fbac4fbe700, query id 12801623 185.81.238.240 root cleaning up
---TRANSACTION 11882248288, not started
MySQL thread id 1212, OS thread handle 0x7fbacff72700, query id 96849893 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11867848641, not started
MySQL thread id 6329, OS thread handle 0x7fbac27ff700, query id 8328197 185.81.238.240 root cleaning up
---TRANSACTION 11882672192, not started
MySQL thread id 1285, OS thread handle 0x7fbaca2fb700, query id 99644576 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882860394, not started
MySQL thread id 1392, OS thread handle 0x7fbacb5f7700, query id 100812088 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882868595, not started
MySQL thread id 1273, OS thread handle 0x7fbacb7ff700, query id 100841204 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875605, not started
MySQL thread id 1213, OS thread handle 0x7fbacfba3700, query id 100866459 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882559726, not started
MySQL thread id 1220, OS thread handle 0x7fbacfdab700, query id 100859394 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882873636, not started
MySQL thread id 1226, OS thread handle 0x7fbacb73c700, query id 100859395 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882248273, not started
MySQL thread id 1215, OS thread handle 0x7fbacfae0700, query id 96848359 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882875772, not started
MySQL thread id 1214, OS thread handle 0x7fbacf1bc700, query id 100866766 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11866588339, not started
MySQL thread id 1207, OS thread handle 0x7fbacf793700, query id 451924 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 1202, OS thread handle 0x7fbacfd29700, query id 450403 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882874899, not started
MySQL thread id 1210, OS thread handle 0x7fbacf8d8700, query id 100863723 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11866588171, not started
MySQL thread id 1208, OS thread handle 0x7fbacf856700, query id 450371 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 1203, OS thread handle 0x7fbacf3c4700, query id 447224 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11866588173, not started
MySQL thread id 1199, OS thread handle 0x7fbacfe2d700, query id 449824 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 1198, OS thread handle 0x7fbacfbe4700, query id 445625 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 1193, OS thread handle 0x7fbacfc66700, query id 445271 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 1191, OS thread handle 0x7fbacf919700, query id 444828 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 0, not started
MySQL thread id 1187, OS thread handle 0x7fbacf509700, query id 444521 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11866586787, not started
MySQL thread id 1180, OS thread handle 0x7fbacf815700, query id 435638 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882847402, not started
MySQL thread id 1165, OS thread handle 0x7fbacfb62700, query id 100755663 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882846956, not started
MySQL thread id 1174, OS thread handle 0x7fbacfe6e700, query id 100751802 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882854010, not started
MySQL thread id 1164, OS thread handle 0x7fbacf2c0700, query id 100783528 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882868602, not started
MySQL thread id 1175, OS thread handle 0x7fbacf383700, query id 100835995 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882867289, not started
MySQL thread id 1173, OS thread handle 0x7fbacf60d700, query id 100836224 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882868383, not started
MySQL thread id 1170, OS thread handle 0x7fbacf27f700, query id 100840305 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882852181, not started
MySQL thread id 1171, OS thread handle 0x7fbacf1fd700, query id 100780084 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882870222, not started
MySQL thread id 1172, OS thread handle 0x7fbacf4c8700, query id 100847395 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882852652, not started
MySQL thread id 1167, OS thread handle 0x7fbacf897700, query id 100782309 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882846493, not started
MySQL thread id 1166, OS thread handle 0x7fbacf487700, query id 100751540 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882851661, not started
MySQL thread id 1168, OS thread handle 0x7fbacfeaf700, query id 100776989 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882860508, not started
MySQL thread id 1169, OS thread handle 0x7fbacf23e700, query id 100809213 XXX.XXX.XXX.XXX root cleaning up
---TRANSACTION 11882874001, not started
MySQL thread id 1163, OS thread handle 0x7fbacf5cc700, query id 100857358 XXX.XXX.XXX.XXX root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
32109 OS file reads, 293736 OS file writes, 232781 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 43.93 writes/s, 22.91 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 35016, seg size 35018, 24 merges
merged operations:
 insert 22, delete mark 33, delete 28
discarded operations:
 insert 0, delete mark 0, delete 0
33987.71 hash searches/s, 12676.14 non-hash searches/s
---
LOG
---
Log sequence number 5161042928416
Log flushed up to   5161042928396
Pages flushed up to 5160903870455
Last checkpoint at  5160903858993
Max checkpoint age    869019772
Checkpoint age target 841862905
Modified age          139057961
Checkpoint age        139069423
0 pending log writes, 0 pending chkp writes
150467 log i/o's done, 14.02 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 11177820160; in additional pool allocated 0
Total memory allocated by read views 6056
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 225887008   (169996984 + 55890024)
    Page hash           1329176 (buffer pool 0 only)
    Dictionary cache    44357318    (42500624 + 1856694)
    File system         1016392     (812272 + 204120)
    Lock system         26592536    (26563016 + 29520)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 1856694
Buffer pool size        655352
Buffer pool size, bytes 10737287168
Free buffers            516293
Database pages          135648
Old database pages      6591
Modified db pages       4137
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 35523, not young 435533
0.00 youngs/s, 0.00 non-youngs/s
Pages read 31888, created 103783, written 81574
0.00 reads/s, 6.53 creates/s, 24.59 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 135648, unzip_LRU len: 0
I/O sum[11240]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            63808
Database pages          17688
Old database pages      860
Modified db pages       738
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4203, created 13485, written 13119
0.00 reads/s, 1.24 creates/s, 3.66 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17688, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64232
Database pages          17262
Old database pages      839
Modified db pages       562
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9792, not young 109004
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4346, created 12919, written 10360
0.00 reads/s, 0.39 creates/s, 3.47 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17262, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64815
Database pages          16669
Old database pages      810
Modified db pages       476
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8543, not young 77383
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3728, created 12949, written 9935
0.00 reads/s, 0.34 creates/s, 2.61 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16669, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64752
Database pages          16742
Old database pages      813
Modified db pages       505
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3869, created 12873, written 10276
0.00 reads/s, 0.37 creates/s, 3.02 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16742, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64542
Database pages          16953
Old database pages      824
Modified db pages       485
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9168, not young 130820
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4085, created 12872, written 10275
0.00 reads/s, 0.34 creates/s, 2.71 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16953, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64649
Database pages          16842
Old database pages      818
Modified db pages       503
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3936, created 12906, written 9149
0.00 reads/s, 0.88 creates/s, 2.97 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16842, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64770
Database pages          16725
Old database pages      812
Modified db pages       369
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4005, created 12720, written 8895
0.00 reads/s, 1.44 creates/s, 2.95 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16725, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size        81919
Buffer pool size, bytes 1342160896
Free buffers            64725
Database pages          16767
Old database pages      815
Modified db pages       499
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7998, not young 118326
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3716, created 13059, written 9565
0.00 reads/s, 1.53 creates/s, 3.20 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16767, unzip_LRU len: 0
I/O sum[1405]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 11882876173
Read view up limit trx id 11882876173
Read view low limit trx id 11882876173
Read view individually stored trx ids:
-----------------
Main thread process no. 9910, id 140440435156736, state: sleeping
Number of rows inserted 345653, updated 2558529, deleted 1158, read 1100668819
18.75 inserts/s, 560.38 updates/s, 0.00 deletes/s, 125493.04 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

我该如何解决这个问题？我读了很多关于MySQL的文章，试图杀死连接，尝试删除临时表，但是我无法解决它。

请提供 SHOW CREATE TABLE feed_events。 - Rick James
2个回答
2
我注意到你的许多InnoDB线程都处于“清理”状态。有一个关于线程陷入该状态的错误https://bugs.mysql.com/bug.php?id=78777。据报道，它已在MySQL 5.6.35、5.7.17和8.0.1中得到修复。如果你还没有升级到这些版本，你应该尽快升级。
另一个建议是增加table_open_cache的大小。监视状态变量Open_tables和Opened_tables。
更多阅读：
嗨，比尔，谢谢你的回答，是的，我升级了mysql版本，mysql运行良好，我检查了open_tables，但它最多只有2039个，我的配置是4096，但opened_tables非常高。 - Tan
如果 Opened_tables / Uptime < 2（每秒），那么 table_open_cache 可能 足够大。 - Rick James
@BillKarwin - 我同意。你对这些的实用性有什么看法：Table_open_cache_misses / Uptime > 1 和 Table_open_cache_overflows / Uptime > 1。显然，0/second是可以接受的。但也许在短期内出现峰值时，它们会频繁报警，因此更好地发出警报？ - Rick James
我认为我的table_open_cache变量对于数据库来说已经足够了，但我们在许多表中使用外键，你认为这会使查询过程变慢吗？ - Tan
@Tan，那是一个单独的问题，不应该在评论中讨论。但简短的答案是否定的，外键并不会使查询变慢，就像索引一样。它们确实会引入一些锁定行为，因此可能会导致锁等待。但锁等待时间与查询执行时间是分开的。 - Bill Karwin
显示剩余3条评论
1
SHOW GLOBAL STATUS LIKE 'Qc%'

展示给我们结果，然后增加：
query_cache_size = 50M   -- but not more than this

假设这还不够，请提供更多信息。
RAM size (16GB?)
SHOW VARIABLES;
SHOW GLOBAL STATUS; -- but only after being up at least 24 hours.

这样我们就可以讨论在Innodb状态中可见的2个查询中可能出现的加速。

SHOW CREATE TABLE feed_events;

您是否有其他经常出现的查询？

请讨论连接的创建。频率如何？等等。我想知道线程池是否值得拥有。

这样改变是一个“快速胜利”，但牺牲了一些安全性：

innodb_flush_log_at_trx_commit = 2
显示类似于“Qc%”的全局状态：链接feed_event表： 链接变量和状态很多，您想要查看哪个变量？ - Tan
当没有明显的答案时（就像你的情况一样），我喜欢看到其中大约一半。我会运行它们通过大约150个公式（例如我在比尔的回答中评论的那个）。永远无法预测会出现什么！请使用post.it（或其他服务）以便我可以获得机器可读数据。 - Rick James
另一个例子：Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) -- QC的命中率。如果低于30％，则QC并不是非常有用。(Qcache_hits / Qcache_inserts) 看起来很好。 - Rick James
我上传了文件并查询了结果。 https://files.fm/u/urbvugk7 - Tan
无法加载该链接。 - Rick James
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接