Re: File full error in simple select statement
Here is the show engine innodb status.
I'm still researching the swapping to understand how to investigate it.
Type Name Status
InnoDB
=====================================
2021-05-02 18:03:35 0x598 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 62977 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6
OS WAIT ARRAY INFO: signal count 5
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 1, rounds 1, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 1.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 21548
Purge done for trx's n:o < 20230 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 282954258763936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 282954258762272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 282954258761440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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
3013 OS file reads, 338 OS file writes, 89 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.69 writes/s, 0.31 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 176, seg size 178, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 6109417, node heap has 2 buffer(s)
Hash table size 6109417, node heap has 2 buffer(s)
Hash table size 6109417, node heap has 2 buffer(s)
Hash table size 6109417, node heap has 3 buffer(s)
Hash table size 6109417, node heap has 1 buffer(s)
Hash table size 6109417, node heap has 6 buffer(s)
Hash table size 6109417, node heap has 115 buffer(s)
Hash table size 6109417, node heap has 21 buffer(s)
4406.81 hash searches/s, 1376.20 non-hash searches/s
---
LOG
---
Log sequence number 7922315095
Log buffer assigned up to 7922315095
Log buffer completed up to 7922315095
Log written up to 7922315095
Log flushed up to 7922315095
Added dirty pages up to 7922315095
Pages flushed up to 7922315095
Last checkpoint at 7922315095
45 log i/o's done, 0.15 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 25202524160
Dictionary memory allocated 3929409
Buffer pool size 1507328
Free buffers 1504048
Database pages 3128
Old database pages 210
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2982, created 155, written 223
0.00 reads/s, 0.00 creates/s, 0.00 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: 3128, unzip_LRU len: 0
I/O sum[272]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 188416
Free buffers 188046
Database pages 352
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 351, created 1, written 15
0.00 reads/s, 0.00 creates/s, 0.00 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: 352, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 188416
Free buffers 187930
Database pages 467
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 464, created 3, written 4
0.00 reads/s, 0.00 creates/s, 0.00 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: 467, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 188416
Free buffers 187989
Database pages 407
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 405, created 11, written 35
0.00 reads/s, 0.00 creates/s, 0.00 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: 407, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 188416
Free buffers 188124
Database pages 273
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 272, created 1, written 3
0.00 reads/s, 0.00 creates/s, 0.00 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: 273, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 188416
Free buffers 188121
Database pages 276
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 275, created 1, written 1
0.00 reads/s, 0.00 creates/s, 0.00 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: 276, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 188416
Free buffers 188007
Database pages 391
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 327, created 64, written 74
0.00 reads/s, 0.00 creates/s, 0.00 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: 391, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 188416
Free buffers 187957
Database pages 441
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 376, created 65, written 73
0.00 reads/s, 0.00 creates/s, 0.00 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: 441, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 188416
Free buffers 187874
Database pages 521
Old database pages 210
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 512, created 9, written 18
0.00 reads/s, 0.00 creates/s, 0.00 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: 521, unzip_LRU len: 0
I/O sum[34]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=7268, Main thread ID=0000000000003050 , state=sleeping
Number of rows inserted 3501, updated 0, deleted 0, read 25586190
89.76 inserts/s, 0.00 updates/s, 0.00 deletes/s, 204107.15 reads/s
Number of system rows inserted 0, updated 315, deleted 0, read 96365
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================