MySQL Forums
Forum List  »  Newbie

Re: Upgrade from 5.5 to 5.6 is causing 'MySQL server has gone away'
Posted by: Kieran Kelly
Date: June 28, 2022 11:34AM

Hi, I started logging the innodb engine status and here are the entries right before it crashed at 18:06

=====================================
2022-06-28 18:05:41 2b6d38505700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 555 srv_active, 0 srv_shutdown, 2977 srv_idle
srv_master_thread log flush and writes: 3532
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 48093
OS WAIT ARRAY INFO: signal count 72165
Mutex spin waits 91686, rounds 1318720, OS waits 37187
RW-shared spins 18324, rounds 228557, OS waits 5069
RW-excl spins 30327, rounds 420365, OS waits 5156
Spin rounds per wait: 14.38 mutex, 12.47 RW-shared, 13.86 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 38268391652
Purge done for trx's n:o < 38268361661 undo n:o < 0 state: running but idle
History list length 11669
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 38268391651, ACTIVE 45 sec
mysql tables in use 2, locked 0
99 lock struct(s), heap size 13864, 210 row lock(s), undo log entries 10
MySQL thread id 1013, OS thread handle 0x2b6da0040700, query id 389382 localhost midasco_ATC Sending data
SELECT branch, repcode, pac1code,SUM(sales) FROM salesorders LEFT JOIN product ON product.code = salesorders.prodcode where ordtype = 'QT' AND status NOT IN('DEL','LOS') AND pipelinestage IN ('01','02','03','04') AND quotefolldate BETWEEN '2022-06-01' AND '2022-06-30' GROUP BY repcode, pac1code
Trx read view will not see trx with id >= 38268391652, sees < 38268357428
---TRANSACTION 38268357428, ACTIVE 296 sec fetching rows
mysql tables in use 1, locked 0
6880 lock struct(s), heap size 620072, 287 row lock(s), undo log entries 287
MySQL thread id 976, OS thread handle 0x2b6d74081700, query id 336291 localhost midasco_DEV3 Sending data
SELECT ordtype, repcode, progress, snapstatus, snapdate, sales, branch, status, prodcode, quotefolldate, pipelinestage, datein, orderno FROM salesorders FORCE INDEX(status) WHERE status NOT IN ( 'DEL', 'LOS' )
Trx read view will not see trx with id >= 38268361661, sees < 38268349017
--------
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
1262469 OS file reads, 245007 OS file writes, 114734 OS fsyncs
2 pending preads, 0 pending pwrites
244.59 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 6142, seg size 6144, 1144 merges
merged operations:
insert 4698, delete mark 1938, delete 361
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 141 buffer(s)
604.77 hash searches/s, 619.42 non-hash searches/s
---
LOG
---
Log sequence number 11863936109048
Log flushed up to 11863936109048
Pages flushed up to 11863936109048
Last checkpoint at 11863936109048
0 pending log writes, 0 pending chkp writes
109569 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 6572329
Buffer pool size 8191
Free buffers 0
Database pages 8013
Old database pages 2938
Modified db pages 0
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 161593, not young 31288034
3.15 youngs/s, 3496.78 non-youngs/s
Pages read 1260910, created 12035, written 132235
244.69 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 963 / 1000, young-making rate 0 / 1000 not 531 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8013, unzip_LRU len: 0
I/O sum[14747]:cur[320], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
Main thread process no. 11023, id 47748098320128, state: sleeping
Number of rows inserted 3249, updated 3258315, deleted 1487, read 27829185
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1224.34 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
2022-06-28 18:05:57 684 [Note] Plugin 'FEDERATED' is disabled.
2022-06-28 18:05:57 684 [Note] InnoDB: Using atomics to ref count buffer pool pages

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.