SHOW ENGINE INNODB STATUS, production: left, development: right
144 removals
| Words removed | 553 |
| Total words | 910 |
| Words removed (%) | 60.77 |
172 lines
56 additions
| Words added | 66 |
| Total words | 423 |
| Words added (%) | 15.60 |
87 lines
MariaDB [phs_p]> SHOW ENGINE INNODB STATUS;
MariaDB [phs]> SHOW ENGINE INNODB STATUS;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 480138
Current database: phs_p
=====================================
=====================================
2021-09-05 22:31:55 0x7f161ccff700 INNODB MONITOR OUTPUT
2021-09-06 02:33:31 0x7f7c3c262700 INNODB MONITOR OUTPUT
=====================================
=====================================
Per second averages calculated from the last 47 seconds
Per second averages calculated from the last 52 seconds
-----------------
-----------------
BACKGROUND THREAD
BACKGROUND THREAD
-----------------
-----------------
srv_master_thread loops: 19380 srv_active, 0 srv_shutdown, 238953 srv_idle
srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 5809 srv_idle
srv_master_thread log flush and writes: 258267
srv_master_thread log flush and writes: 5809
----------
----------
SEMAPHORES
SEMAPHORES
----------
----------
OS WAIT ARRAY INFO: reservation count 33070
OS WAIT ARRAY INFO: reservation count 10
OS WAIT ARRAY INFO: signal count 1466283
OS WAIT ARRAY INFO: signal count 17
RW-shared spins 39974, rounds 28662, OS waits 287
RW-shared spins 3, rounds 120, OS waits 4
RW-excl spins 598238, rounds 368357, OS waits 3914
RW-excl spins 5, rounds 105, OS waits 3
RW-sx spins 27, rounds 76, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.72 RW-shared, 0.62 RW-excl, 2.81 RW-sx
Spin rounds per wait: 40.00 RW-shared, 21.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-09-05 00:46:51 0x7f161d371700 Transaction:
TRANSACTION 110561045, ACTIVE 1 sec inserting
mysql tables in use 2, locked 2
7902 lock struct(s), heap size 925816, 529625 row lock(s)
MySQL thread id 384758, OS thread handle 139732956157696, query id 30407393 localhost oss_p Sending data
INSERT IGNORE INTO customer_location (customer_id, location_id)
SELECT
le.customer_id,
le.location_id
FROM `lead` AS le
Foreign key constraint fails for table `oss_p`.`customer_location`:
,
CONSTRAINT `customer_location_customer_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON UPDATE CASCADE in parent table, in index PRIMARY tuple:
DATA TUPLE: 4 fields;
0: len 4; hex 00000000; asc ;;
1: len 4; hex 00000000; asc ;;
2: len 6; hex 000006970715; asc ;;
3: len 7; hex b500000202086b; asc k;;
But in parent table `oss_p`.`customer`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 26; compact format; info bits 0
0: len 4; hex 00000014; asc ;;
1: len 6; hex 000000000000; asc ;;
2: len 7; hex 80000000000000; asc ;;
3: len 7; hex 43303030303230; asc C000020;;
4: SQL NULL;
5: SQL NULL;
6: len 11; hex 536f6d792054686f6d6173; asc Somy Thomas;;
7: SQL NULL;
8: len 12; hex 3431362d3839372d33383335; asc 416-897-3835;;
9: len 12; hex 3634372d3835352d33383335; asc 647-855-3835;;
10: SQL NULL;
11: SQL NULL;
12: SQL NULL;
13: SQL NULL;
14: SQL NULL;
15: SQL NULL;
16: len 23; hex 474153204d45544552204e4f2e20323637313537345355; asc GAS METER NO. 2671574SU;;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: len 4; hex 54190783; asc T ;;
24: len 4; hex 587c5350; asc X|SP;;
25: SQL NULL;
------------
------------
TRANSACTIONS
TRANSACTIONS
------------
------------
Trx id counter 110982587
Trx id counter 52065
Purge done for trx's n:o < 110981493 undo n:o < 0 state: running but idle
Purge done for trx's n:o < 51490 undo n:o < 0 state: running but idle
History list length 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421225622731312, not started
---TRANSACTION 421646540120272, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622727056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622722800, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622718544, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622714288, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622710032, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622705776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622701520, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622697264, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622693008, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622688752, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622684496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622680240, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622675984, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622671728, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421225622667472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
--------
FILE I/O
FILE I/O
--------
--------
Pending flushes (fsync) log: 0; buffer pool: 0
Pending flushes (fsync) log: 0; buffer pool: 0
288579 OS file reads, 81284 OS file writes, 81013 OS fsyncs
4628 OS file reads, 11 OS file writes, 13 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
-------------------------------------
Ibuf: size 12, free list len 3017, seg size 3030, 1108 merges
Ibuf: size 44, free list len 6107, seg size 6152, 1 merges
merged operations:
merged operations:
insert 3924, delete mark 593, delete 167
insert 0, delete mark 1, delete 0
discarded operations:
discarded operations:
insert 0, delete mark 0, delete 0
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 10.17 non-hash searches/s
0.00 hash searches/s, 0.00 non-hash searches/s
---
---
LOG
LOG
---
---
Log sequence number 141419971381
Log sequence number 19030538776
Log flushed up to 141419971381
Log flushed up to 19030538776
Pages flushed up to 140937806791
Pages flushed up to 19030502756
Last checkpoint at 140937806779
Last checkpoint at 19030502744
0 pending log flushes, 0 pending chkp writes
0 pending log flushes, 0 pending chkp writes
80977 log i/o's done, 0.00 log i/o's/second
13 log i/o's done, 0.00 log i/o's/second
----------------------
----------------------
BUFFER POOL AND MEMORY
BUFFER POOL AND MEMORY
----------------------
----------------------
Total large memory allocated 17213423616
Total large memory allocated 285212672
Dictionary memory allocated 110281776
Dictionary memory allocated 1882752
Buffer pool size 1032320
Buffer pool size 16130
Free buffers 736381
Free buffers 11496
Database pages 295939
Database pages 4634
Old database pages 109223
Old database pages 1730
Modified db pages 23075
Modified db pages 34
Percent of dirty pages(LRU & free pages): 2.235
Percent of dirty pages(LRU & free pages): 0.211
Max dirty pages percent: 90.000
Max dirty pages percent: 90.000
Pending reads 0
Pending reads 0
Pending writes: LRU 0, flush list 0
Pending writes: LRU 0, flush list 0
Pages made young 6405469, not young 547385
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
0.00 youngs/s, 0.00 non-youngs/s
Pages read 286584, created 9354, written 0
Pages read 4501, created 132, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
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
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 295939, unzip_LRU len: 0
LRU len: 4634, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
--------------
ROW OPERATIONS
ROW OPERATIONS
--------------
--------------
0 read views open inside InnoDB
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 44632, updated 64728, deleted 8454, read 10533833592
Number of rows inserted 2, updated 2, deleted 1, read 35095
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
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
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
----------------------------
END OF INNODB MONITOR OUTPUT
END OF INNODB MONITOR OUTPUT
============================
============================