Groups keyboard shortcuts have been updated
Dismiss
See shortcuts
TRANSACTION not started
56 views
Skip to first unread message
Guntis Bringulis
Nov 12, 2019, 2:22:22 AM11/12/19
to lv...@googlegroups.com
Sveiki !
mysql ir pa laikam met paziņojumu par
TRANSACTION not started
ir kā DB strādā neviens nesūdzās
SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-11-12 09:15:10 7f16c4c58700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 13175035 srv_active, 0 srv_shutdown, 1821253 srv_idle
srv_master_thread log flush and writes: 14995935
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 456852793
OS WAIT ARRAY INFO: signal count 738888391
Mutex spin waits 171542481, rounds 1635252430, OS waits 32662098
RW-shared spins 230329036, rounds 7460220485, OS waits 193106119
RW-excl spins 191000718, rounds 7878782306, OS waits 219670132
Spin rounds per wait: 9.53 mutex, 32.39 RW-shared, 41.25 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 10519390235
Purge done for trx's n:o < 10518723463 undo n:o < 0 state: running but idle
History list length 53519
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 10519390234, not started
MySQL thread id 141257090, OS thread handle 0x7f16c4413700, query id 3200833847 10.21.146.172 chrudb cleaning up
---TRANSACTION 10519390233, not started
MySQL thread id 141257089, OS thread handle 0x7f16cc090700, query id 3200833846 10.21.146.186 chrudb cleaning up
---TRANSACTION 10519188227, not started
MySQL thread id 141238445, OS thread handle 0x7f16c46ed700, query id 3200472690 10.21.253.198 chrudb cleaning up
---TRANSACTION 0, not started
MySQL thread id 141192156, OS thread handle 0x7f16c4c58700, query id 3200833848 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 8397263363, not started
MySQL thread id 1, OS thread handle 0x7f282dcb7700, query id 0 Waiting for background binlog tasks
--------
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
4103694269 OS file reads, 641910251 OS file writes, 426086046 OS fsyncs
300.98 reads/s, 16384 avg bytes/read, 180.34 writes/s, 139.28 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 86396, seg size 86398, 9034841 merges
merged operations:
insert 11931160, delete mark 24559035, delete 292
discarded operations:
insert 0, delete mark 0, delete 0
270607.60 hash searches/s, 4493.11 non-hash searches/s
---
LOG
---
Log sequence number 4229755838052
Log flushed up to 4229755838017
Pages flushed up to 4229729880249
Last checkpoint at 4229728777999
Max checkpoint age 80826164
Checkpoint age target 78300347
Modified age 25957803
Checkpoint age 27060053
0 pending log writes, 0 pending chkp writes
380694979 log i/o's done, 134.64 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 72655831040; in additional pool allocated 0
Total memory allocated by read views 8568
Internal hash tables (constant factor + variable factor)
Adaptive hash index 13374457360 (1150547368 + 12223909992)
Page hash 8989528 (buffer pool 0 only)
Dictionary cache 288169493 (287638544 + 530949)
File system 851752 (812272 + 39480)
Lock system 172657304 (172655144 + 2160)
Recovery system 0 (0 + 0)
Dictionary memory allocated 530949
Buffer pool size 4259832
Buffer pool size, bytes 69793087488
Free buffers 8128
Database pages 3505616
Old database pages 1293940
Modified db pages 1525
Percent of dirty pages(LRU & free pages): 0.043
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19543770129, not young 1326311422135
1388.69 youngs/s, 273314.48 non-youngs/s
Pages read 4103704524, created 30970974, written 247699753
301.98 reads/s, 22.93 creates/s, 44.50 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 327 / 1000
Pages read ahead 72.99/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3505616, unzip_LRU len: 0
I/O sum[108120]:cur[1056], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438201
Old database pages 161737
Modified db pages 341
Percent of dirty pages(LRU & free pages): 0.078
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2566213385, not young 164549579715
208.27 youngs/s, 44089.99 non-youngs/s
Pages read 519867318, created 5129563, written 44820687
44.35 reads/s, 1.79 creates/s, 9.14 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 346 / 1000
Pages read ahead 9.14/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438201, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438214
Old database pages 161742
Modified db pages 142
Percent of dirty pages(LRU & free pages): 0.032
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2351444745, not young 168626078995
126.63 youngs/s, 30381.19 non-youngs/s
Pages read 515581220, created 3716668, written 27644044
32.00 reads/s, 4.57 creates/s, 2.86 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 352 / 1000
Pages read ahead 4.50/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438214, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438188
Old database pages 161732
Modified db pages 158
Percent of dirty pages(LRU & free pages): 0.036
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2442009210, not young 161670903021
162.27 youngs/s, 19152.42 non-youngs/s
Pages read 502286854, created 3674291, written 27610564
25.07 reads/s, 4.50 creates/s, 1.86 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 230 / 1000
Pages read ahead 4.57/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438188, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438179
Old database pages 161729
Modified db pages 164
Percent of dirty pages(LRU & free pages): 0.037
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2450484551, not young 166470083768
197.34 youngs/s, 40789.37 non-youngs/s
Pages read 513920267, created 3737999, written 31746422
40.71 reads/s, 5.14 creates/s, 6.50 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 341 / 1000
Pages read ahead 13.64/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438179, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 962
Database pages 438242
Old database pages 161792
Modified db pages 162
Percent of dirty pages(LRU & free pages): 0.037
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2612931844, not young 167441857932
138.92 youngs/s, 53662.17 non-youngs/s
Pages read 522832562, created 3713532, written 32773795
61.21 reads/s, 5.00 creates/s, 4.86 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 493 / 1000
Pages read ahead 18.28/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438242, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1023
Database pages 438205
Old database pages 161739
Modified db pages 202
Percent of dirty pages(LRU & free pages): 0.046
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2358458198, not young 171384414995
199.77 youngs/s, 42817.94 non-youngs/s
Pages read 522793536, created 3675514, written 27288358
48.57 reads/s, 1.64 creates/s, 2.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 347 / 1000
Pages read ahead 13.71/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438205, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1023
Database pages 438205
Old database pages 161739
Modified db pages 74
Percent of dirty pages(LRU & free pages): 0.017
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2290762439, not young 158411835175
180.42 youngs/s, 24047.50 non-youngs/s
Pages read 497338267, created 3666437, written 26145031
29.28 reads/s, 0.29 creates/s, 1.57 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 240 / 1000
Pages read ahead 4.57/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438205, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438182
Old database pages 161730
Modified db pages 282
Percent of dirty pages(LRU & free pages): 0.064
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2471465757, not young 167756668534
175.06 youngs/s, 18373.90 non-youngs/s
Pages read 509084500, created 3656970, written 29670852
20.78 reads/s, 0.00 creates/s, 15.71 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 208 / 1000
Pages read ahead 4.57/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438182, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 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 10518723460
Read view up limit trx id 10518723425
Read view low limit trx id 10518723460
Read view individually stored trx ids:
Read view trx id 10518723425
Read view trx id 10518723438
Read view trx id 10518723441
Read view trx id 10518723443
Read view trx id 10518723445
Read view trx id 10518723449
Read view trx id 10518723450
Read view trx id 10518723451
Read view trx id 10518723452
-----------------
Main thread process no. 2596, id 139734781761280, state: sleeping
Number of rows inserted 265217566, updated 344850279, deleted 75084336, read 5420275387483
182.06 inserts/s, 58.00 updates/s, 0.86 deletes/s, 274359.05 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.08 sec)
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2019-11-12 09:15:10 7f16c4c58700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 13175035 srv_active, 0 srv_shutdown, 1821253 srv_idle
srv_master_thread log flush and writes: 14995935
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 456852793
OS WAIT ARRAY INFO: signal count 738888391
Mutex spin waits 171542481, rounds 1635252430, OS waits 32662098
RW-shared spins 230329036, rounds 7460220485, OS waits 193106119
RW-excl spins 191000718, rounds 7878782306, OS waits 219670132
Spin rounds per wait: 9.53 mutex, 32.39 RW-shared, 41.25 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 10519390235
Purge done for trx's n:o < 10518723463 undo n:o < 0 state: running but idle
History list length 53519
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 10519390234, not started
MySQL thread id 141257090, OS thread handle 0x7f16c4413700, query id 3200833847 10.21.146.172 chrudb cleaning up
---TRANSACTION 10519390233, not started
MySQL thread id 141257089, OS thread handle 0x7f16cc090700, query id 3200833846 10.21.146.186 chrudb cleaning up
---TRANSACTION 10519188227, not started
MySQL thread id 141238445, OS thread handle 0x7f16c46ed700, query id 3200472690 10.21.253.198 chrudb cleaning up
---TRANSACTION 0, not started
MySQL thread id 141192156, OS thread handle 0x7f16c4c58700, query id 3200833848 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 8397263363, not started
MySQL thread id 1, OS thread handle 0x7f282dcb7700, query id 0 Waiting for background binlog tasks
--------
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
4103694269 OS file reads, 641910251 OS file writes, 426086046 OS fsyncs
300.98 reads/s, 16384 avg bytes/read, 180.34 writes/s, 139.28 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 86396, seg size 86398, 9034841 merges
merged operations:
insert 11931160, delete mark 24559035, delete 292
discarded operations:
insert 0, delete mark 0, delete 0
270607.60 hash searches/s, 4493.11 non-hash searches/s
---
LOG
---
Log sequence number 4229755838052
Log flushed up to 4229755838017
Pages flushed up to 4229729880249
Last checkpoint at 4229728777999
Max checkpoint age 80826164
Checkpoint age target 78300347
Modified age 25957803
Checkpoint age 27060053
0 pending log writes, 0 pending chkp writes
380694979 log i/o's done, 134.64 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 72655831040; in additional pool allocated 0
Total memory allocated by read views 8568
Internal hash tables (constant factor + variable factor)
Adaptive hash index 13374457360 (1150547368 + 12223909992)
Page hash 8989528 (buffer pool 0 only)
Dictionary cache 288169493 (287638544 + 530949)
File system 851752 (812272 + 39480)
Lock system 172657304 (172655144 + 2160)
Recovery system 0 (0 + 0)
Dictionary memory allocated 530949
Buffer pool size 4259832
Buffer pool size, bytes 69793087488
Free buffers 8128
Database pages 3505616
Old database pages 1293940
Modified db pages 1525
Percent of dirty pages(LRU & free pages): 0.043
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19543770129, not young 1326311422135
1388.69 youngs/s, 273314.48 non-youngs/s
Pages read 4103704524, created 30970974, written 247699753
301.98 reads/s, 22.93 creates/s, 44.50 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 327 / 1000
Pages read ahead 72.99/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3505616, unzip_LRU len: 0
I/O sum[108120]:cur[1056], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438201
Old database pages 161737
Modified db pages 341
Percent of dirty pages(LRU & free pages): 0.078
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2566213385, not young 164549579715
208.27 youngs/s, 44089.99 non-youngs/s
Pages read 519867318, created 5129563, written 44820687
44.35 reads/s, 1.79 creates/s, 9.14 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 346 / 1000
Pages read ahead 9.14/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438201, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438214
Old database pages 161742
Modified db pages 142
Percent of dirty pages(LRU & free pages): 0.032
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2351444745, not young 168626078995
126.63 youngs/s, 30381.19 non-youngs/s
Pages read 515581220, created 3716668, written 27644044
32.00 reads/s, 4.57 creates/s, 2.86 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 352 / 1000
Pages read ahead 4.50/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438214, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438188
Old database pages 161732
Modified db pages 158
Percent of dirty pages(LRU & free pages): 0.036
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2442009210, not young 161670903021
162.27 youngs/s, 19152.42 non-youngs/s
Pages read 502286854, created 3674291, written 27610564
25.07 reads/s, 4.50 creates/s, 1.86 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 230 / 1000
Pages read ahead 4.57/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438188, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438179
Old database pages 161729
Modified db pages 164
Percent of dirty pages(LRU & free pages): 0.037
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2450484551, not young 166470083768
197.34 youngs/s, 40789.37 non-youngs/s
Pages read 513920267, created 3737999, written 31746422
40.71 reads/s, 5.14 creates/s, 6.50 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 341 / 1000
Pages read ahead 13.64/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438179, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 962
Database pages 438242
Old database pages 161792
Modified db pages 162
Percent of dirty pages(LRU & free pages): 0.037
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2612931844, not young 167441857932
138.92 youngs/s, 53662.17 non-youngs/s
Pages read 522832562, created 3713532, written 32773795
61.21 reads/s, 5.00 creates/s, 4.86 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 493 / 1000
Pages read ahead 18.28/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438242, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1023
Database pages 438205
Old database pages 161739
Modified db pages 202
Percent of dirty pages(LRU & free pages): 0.046
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2358458198, not young 171384414995
199.77 youngs/s, 42817.94 non-youngs/s
Pages read 522793536, created 3675514, written 27288358
48.57 reads/s, 1.64 creates/s, 2.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 347 / 1000
Pages read ahead 13.71/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438205, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1023
Database pages 438205
Old database pages 161739
Modified db pages 74
Percent of dirty pages(LRU & free pages): 0.017
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2290762439, not young 158411835175
180.42 youngs/s, 24047.50 non-youngs/s
Pages read 497338267, created 3666437, written 26145031
29.28 reads/s, 0.29 creates/s, 1.57 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 240 / 1000
Pages read ahead 4.57/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438205, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 532479
Buffer pool size, bytes 8724135936
Free buffers 1024
Database pages 438182
Old database pages 161730
Modified db pages 282
Percent of dirty pages(LRU & free pages): 0.064
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2471465757, not young 167756668534
175.06 youngs/s, 18373.90 non-youngs/s
Pages read 509084500, created 3656970, written 29670852
20.78 reads/s, 0.00 creates/s, 15.71 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 1 / 1000 not 208 / 1000
Pages read ahead 4.57/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 438182, unzip_LRU len: 0
I/O sum[13515]:cur[132], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 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 10518723460
Read view up limit trx id 10518723425
Read view low limit trx id 10518723460
Read view individually stored trx ids:
Read view trx id 10518723425
Read view trx id 10518723438
Read view trx id 10518723441
Read view trx id 10518723443
Read view trx id 10518723445
Read view trx id 10518723449
Read view trx id 10518723450
Read view trx id 10518723451
Read view trx id 10518723452
-----------------
Main thread process no. 2596, id 139734781761280, state: sleeping
Number of rows inserted 265217566, updated 344850279, deleted 75084336, read 5420275387483
182.06 inserts/s, 58.00 updates/s, 0.86 deletes/s, 274359.05 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.08 sec)
Andrejs Vorobjovs
Nov 12, 2019, 5:47:48 AM11/12/19
to Latvian Oracle User Group
Sveiki,
Vai visai DB ir InnoDB dzīneis?
Vai tabulai arī InnoDB dzīneis?
kādi ir my.ini parametri?
paskaties innodb_thread_concurrency parametru? cik viņš ir?
Andrejs.
Guntis Bringulis
Nov 12, 2019, 6:07:29 AM11/12/19
to lv...@googlegroups.com
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0 |
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0 |
+---------------------------+-------+
problēma ir ar InnoDB Monitor, kurš pēkšņi naktī ik pa 20 sec sāka innodb_status_output uz mysql error logu rakstīt
--
--
Jūs saņēmāt šo ziņojumu, jo abonējāt Google grupu "Latvian Oracle User
Group" grupa.
Lai nosūtītu ziņojumu šai grupai, sūtiet e-pastu uz
lv...@googlegroups.com
Lai redzētu papildiespējas, apmeklējiet šo grupu
http://lvoug.lv
---
Jūs saņēmāt šo ziņojumu, jo pakalpojumā Google grupas esat abonējis grupu Latvian Oracle User Group.
Lai anulētu šīs grupas abonementu un vairs nesaņemtu tās e-pasta ziņojumus, nosūtiet e-pasta ziņojumu uz adresi lvoug+un...@googlegroups.com.
Lai šo diskusiju skatītu tīmeklī, apmeklējiet lapu https://groups.google.com/d/msgid/lvoug/11e67d82-d1df-4264-a4cf-13461312a5cf%40googlegroups.com.
Andrejs Vorobjovs
Nov 13, 2019, 6:56:17 AM11/13/19
to Latvian Oracle User Group
Sveiki,
select * from information_schema.innodb_trx order by trx_rows_modified desc \G
vai gadijumā ap to laiku neiet backup?
Es domāju var būt backup laikā viņš palaiž kaut ko šadu un ierakstā logā:
Manuprat šis ir informativs paziņojums par transakcijam kuram netika uztaisīts COMMIT/ROLLBACK;
Respektīvi MYSQL nezin ko darīt ar trasakcijam, jo nav AUTOcommit vai nu gaida kamēr backup beigsies un ierakstīs no UNDO uz tabulu.
Te ir apraksts ko es papetīju
Vel var papildus ap to laiku palaist un paskatities uz transakcijam:
un
SELECT * FROM sys.innodb_lock_waits\G
Andrejs.
Lai anulētu šīs grupas abonementu un vairs nesaņemtu tās e-pasta ziņojumus, nosūtiet e-pasta ziņojumu uz adresi lvoug+unsubscribe@googlegroups.com.
Guntis Bringulis
Nov 13, 2019, 7:28:46 AM11/13/19
to lv...@googlegroups.com
Sveiki,
DB netiek veikts backup jo ir replikācija
SHOW ENGINE INNODB STATUS\G izmantoju, lai paskatītos, kas notiek
šeit es redzu, kad
---TRANSACTION 2301048588, not started
bet problēma ir tāda, kad šis outputs tiek rakstīts uz /var/log/mysql.log, kurš pa nakti pierakstījās 2,5 GB
skatos, kad parametrs show variables like 'innodb_status_output'; ir ON
TRANSACTIONS
------------
Trx id counter 2301048595
Purge done for trx's n:o < 2301048593 undo n:o < 0 state: running but idle
History list length 1898
------------
Trx id counter 2301048595
Purge done for trx's n:o < 2301048593 undo n:o < 0 state: running but idle
History list length 1898
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 183743, OS thread handle 0x7f36de5fd700, query id 479616806 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 2301048588, not started
MySQL thread id 3, OS thread handle 0x7f3bb41b1700, query id 479616799 Waiting for work from SQL thread
---TRANSACTION 2301047338, not started
MySQL thread id 6, OS thread handle 0x7f3bb4168700, query id 479615375 Waiting for work from SQL thread
---TRANSACTION 2301047453, not started
MySQL thread id 4, OS thread handle 0x7f3bb411f700, query id 479615501 Waiting for work from SQL thread
---TRANSACTION 2301048589, not started flushing log
MySQL thread id 5, OS thread handle 0x7f3bb40d6700, query id 479616801 closing tables
---TRANSACTION 2301047456, not started
MySQL thread id 7, OS thread handle 0x7f3bb408d700, query id 479615508 Waiting for work from SQL thread
---TRANSACTION 2301047462, not started
MySQL thread id 16, OS thread handle 0x7f36e4284700, query id 479615519 Waiting for work from SQL thread
---TRANSACTION 2301048554, not started
MySQL thread id 8, OS thread handle 0x7f36e423b700, query id 479616753 Waiting for work from SQL thread
---TRANSACTION 2301047454, not started
MySQL thread id 9, OS thread handle 0x7f36e41f2700, query id 479615505 Waiting for work from SQL thread
---TRANSACTION 2301047321, not started
MySQL thread id 10, OS thread handle 0x7f36e41a9700, query id 479615356 Waiting for work from SQL thread
---TRANSACTION 2301047458, not started
MySQL thread id 12, OS thread handle 0x7f36e4160700, query id 479615510 Waiting for work from SQL thread
---TRANSACTION 2301047319, not started
MySQL thread id 11, OS thread handle 0x7f36e4117700, query id 479615352 Waiting for work from SQL thread
---TRANSACTION 2301047320, not started
MySQL thread id 14, OS thread handle 0x7f36e40ce700, query id 479615354 Waiting for work from SQL thread
---TRANSACTION 2301047313, not started
MySQL thread id 15, OS thread handle 0x7f36e4085700, query id 479615350 Waiting for work from SQL thread
---TRANSACTION 2301047451, not started
MySQL thread id 13, OS thread handle 0x7f36de7b3700, query id 479615498 Waiting for work from SQL thread
---TRANSACTION 2301047464, not started
MySQL thread id 17, OS thread handle 0x7f36de76a700, query id 479615522 Waiting for work from SQL thread
---TRANSACTION 2301047342, not started
MySQL thread id 19, OS thread handle 0x7f36de721700, query id 479615384 Waiting for work from SQL thread
---TRANSACTION 2301048587, not started flushing log
MySQL thread id 20, OS thread handle 0x7f36de6d8700, query id 479616798 closing tables
---TRANSACTION 2301047336, not started
MySQL thread id 22, OS thread handle 0x7f36de646700, query id 479615372 Waiting for work from SQL thread
---TRANSACTION 2301048594, ACTIVE 0 sec
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 2
MySQL thread id 21, OS thread handle 0x7f36de68f700, query id 479616804 Waiting for prior transaction to commit
Trx #rec lock waits 472955 #table lock waits 0
Trx total rec lock wait time 4413 SEC
Trx total table lock wait time 0 SEC
MySQL thread id 183743, OS thread handle 0x7f36de5fd700, query id 479616806 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 2301048588, not started
MySQL thread id 3, OS thread handle 0x7f3bb41b1700, query id 479616799 Waiting for work from SQL thread
---TRANSACTION 2301047338, not started
MySQL thread id 6, OS thread handle 0x7f3bb4168700, query id 479615375 Waiting for work from SQL thread
---TRANSACTION 2301047453, not started
MySQL thread id 4, OS thread handle 0x7f3bb411f700, query id 479615501 Waiting for work from SQL thread
---TRANSACTION 2301048589, not started flushing log
MySQL thread id 5, OS thread handle 0x7f3bb40d6700, query id 479616801 closing tables
---TRANSACTION 2301047456, not started
MySQL thread id 7, OS thread handle 0x7f3bb408d700, query id 479615508 Waiting for work from SQL thread
---TRANSACTION 2301047462, not started
MySQL thread id 16, OS thread handle 0x7f36e4284700, query id 479615519 Waiting for work from SQL thread
---TRANSACTION 2301048554, not started
MySQL thread id 8, OS thread handle 0x7f36e423b700, query id 479616753 Waiting for work from SQL thread
---TRANSACTION 2301047454, not started
MySQL thread id 9, OS thread handle 0x7f36e41f2700, query id 479615505 Waiting for work from SQL thread
---TRANSACTION 2301047321, not started
MySQL thread id 10, OS thread handle 0x7f36e41a9700, query id 479615356 Waiting for work from SQL thread
---TRANSACTION 2301047458, not started
MySQL thread id 12, OS thread handle 0x7f36e4160700, query id 479615510 Waiting for work from SQL thread
---TRANSACTION 2301047319, not started
MySQL thread id 11, OS thread handle 0x7f36e4117700, query id 479615352 Waiting for work from SQL thread
---TRANSACTION 2301047320, not started
MySQL thread id 14, OS thread handle 0x7f36e40ce700, query id 479615354 Waiting for work from SQL thread
---TRANSACTION 2301047313, not started
MySQL thread id 15, OS thread handle 0x7f36e4085700, query id 479615350 Waiting for work from SQL thread
---TRANSACTION 2301047451, not started
MySQL thread id 13, OS thread handle 0x7f36de7b3700, query id 479615498 Waiting for work from SQL thread
---TRANSACTION 2301047464, not started
MySQL thread id 17, OS thread handle 0x7f36de76a700, query id 479615522 Waiting for work from SQL thread
---TRANSACTION 2301047342, not started
MySQL thread id 19, OS thread handle 0x7f36de721700, query id 479615384 Waiting for work from SQL thread
---TRANSACTION 2301048587, not started flushing log
MySQL thread id 20, OS thread handle 0x7f36de6d8700, query id 479616798 closing tables
---TRANSACTION 2301047336, not started
MySQL thread id 22, OS thread handle 0x7f36de646700, query id 479615372 Waiting for work from SQL thread
---TRANSACTION 2301048594, ACTIVE 0 sec
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 2
MySQL thread id 21, OS thread handle 0x7f36de68f700, query id 479616804 Waiting for prior transaction to commit
Trx #rec lock waits 472955 #table lock waits 0
Trx total rec lock wait time 4413 SEC
Trx total table lock wait time 0 SEC
un
Lai anulētu šīs grupas abonementu un vairs nesaņemtu tās e-pasta ziņojumus, nosūtiet e-pasta ziņojumu uz adresi lvoug+un...@googlegroups.com.
Lai šo diskusiju skatītu tīmeklī, apmeklējiet lapu https://groups.google.com/d/msgid/lvoug/11e67d82-d1df-4264-a4cf-13461312a5cf%40googlegroups.com.
--
--
Jūs saņēmāt šo ziņojumu, jo abonējāt Google grupu "Latvian Oracle User
Group" grupa.
Lai nosūtītu ziņojumu šai grupai, sūtiet e-pastu uz
lv...@googlegroups.com
Lai redzētu papildiespējas, apmeklējiet šo grupu
http://lvoug.lv
---
Jūs saņēmāt šo ziņojumu, jo pakalpojumā Google grupas esat abonējis grupu Latvian Oracle User Group.
Lai anulētu šīs grupas abonementu un vairs nesaņemtu tās e-pasta ziņojumus, nosūtiet e-pasta ziņojumu uz adresi lvoug+un...@googlegroups.com.
Lai šo diskusiju skatītu tīmeklī, apmeklējiet lapu https://groups.google.com/d/msgid/lvoug/367e6769-9674-4135-b2fe-dfc1a5cfd825%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages