Re: MySQL server crash after running SELECT queries with a connection pool of 60
Posted by: Chinmay Ram
Date: June 14, 2022 09:04AM
Date: June 14, 2022 09:04AM
The queries are run using Visual studio in C# code. The SELECT queries execute properly with a single MySQL connection under the sql_mode='ANSI_QUOTES,STRICT_TRANS_TABLES';
------------------------------------------------------------------------------------------------
the result of this calculation: innodb_buffer_pool_size + innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + max_connections * ( thread_stack + max( max_allowed_packet, net_buffer_length ) + net_buffer_length + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size )
27917287424+640000000+0+16777216+0+16777216+8388608+1000*(286720+max(16777216+16384)+16384+131072+262144+262144+262144)= 46579884032
------------------------------------------------------------------------------------------------
total RAM= 32465552kb
--------------------------------------------------------------------------------------------------
whether the query induces swapping
No, swap memory was used.
Edited 1 time(s). Last edit at 06/14/2022 10:29AM by Peter Brawley.
------------------------------------------------------------------------------------------------------------------------ show engine innodb status details: mysql> show engine innodb status \G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2022-06-13 17:44:31 140110895781632 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 30 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 132 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6 OS WAIT ARRAY INFO: signal count 2 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 287170 Purge done for trx's n:o < 286983 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421586037001432, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421586037000624, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421586036999816, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- 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] , 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 1869 OS file reads, 220 OS file writes, 43 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.47 writes/s, 0.17 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 3, seg size 5, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 6906283, node heap has 0 buffer(s) Hash table size 6906283, node heap has 2 buffer(s) Hash table size 6906283, node heap has 1 buffer(s) Hash table size 6906283, node heap has 1 buffer(s) Hash table size 6906283, node heap has 2 buffer(s) Hash table size 6906283, node heap has 3 buffer(s) Hash table size 6906283, node heap has 4 buffer(s) Hash table size 6906283, node heap has 8 buffer(s) 17.00 hash searches/s, 33.03 non-hash searches/s --- LOG --- Log sequence number 181561785486 Log buffer assigned up to 181561785486 Log buffer completed up to 181561785486 Log written up to 181561785486 Log flushed up to 181561785486 Added dirty pages up to 181561785486 Pages flushed up to 181561785486 Last checkpoint at 181561785486 21 log i/o's done, 0.07 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 28504293376 Dictionary memory allocated 2424025 Buffer pool size 1703783 Free buffers 1702202 Database pages 1560 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 1418, created 142, written 165 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: 1560, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 212967 Free buffers 212724 Database pages 241 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 240, created 1, 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: 241, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 212967 Free buffers 212810 Database pages 154 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 152, created 2, written 2 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: 154, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 212966 Free buffers 212836 Database pages 127 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 124, created 3, written 9 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: 127, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 212990 Free buffers 212912 Database pages 75 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 74, created 1, written 1 0.00 reads/s, 0.00 creates/s, 0.00 writes/s 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 LRU len: 75, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 212969 Free buffers 212845 Database pages 121 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 120, 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: 121, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 212990 Free buffers 212809 Database pages 178 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 114, created 64, written 64 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: 178, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 212968 Free buffers 212600 Database pages 366 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 301, created 65, written 71 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: 366, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 212966 Free buffers 212666 Database pages 298 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 293, created 5, written 13 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: 298, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=18787, Main thread ID=140080336709376 , state=sleeping Number of 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 Number of system rows inserted 0, updated 315, deleted 0, read 60250 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 124.36 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) ----------------------------------------------------------------------------------------------- running Explain Analyze on the queries, esp. on the query that's running when the crash occurs +----+--------------------+----------------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+----------+--------------------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key1> | <auto_key1> | 4 | const | 1 | 100.00 | NULL | | 2 | DERIVED | <subquery7> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary | | 2 | DERIVED | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 4 | <subquery7>.id | 2 | 100.00 | NULL | | 2 | DERIVED | <derived4> | NULL | ref | <auto_key0> | <auto_key0> | 4 | <subquery7>.id | 2 | 100.00 | Using where; Not exists | | 7 | MATERIALIZED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | | 3 | DERIVED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary | | 4 | DERIVED | d | NULL | ref | PRIMARY,FK_Document_Organization3,document_isdeleted_indx,IX_Document_Composite | IX_Document_Composite | 5 | const,const | 5 | 100.00 | Using temporary | | 4 | DERIVED | dr | NULL | ref | PRIMARY,FK_Document_EntityType1,FK_DocumentRev_Document,FK_DocumentRev_ReleaseStatus,IX_DocumentRev_IsWorkingDoc | FK_DocumentRev_Document | 4 | ngease.d.ID | 3 | 25.01 | Using where | | 4 | DERIVED | ast | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ngease.dr.DocumentSubtype | 1 | 75.00 | Using where | | 4 | DERIVED | rs | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7 | 14.29 | Using where; Using join buffer (hash join) | | 4 | DERIVED | dr | NULL | eq_ref | PRIMARY,FK_DocumentRev_Document | PRIMARY | 4 | ngease.dr.ID | 1 | 100.00 | NULL | | 4 | DERIVED | d | NULL | eq_ref | PRIMARY,FK_Document_Scope | PRIMARY | 4 | ngease.dr.DocumentID | 1 | 100.00 | Using where | | 4 | DERIVED | sl | NULL | ref | FK_ScopeLocation_Scope,FK_ScopeLocation_Location,IX_ScopeLocation_ScopeID | FK_ScopeLocation_Scope | 4 | ngease.d.ScopeID | 1 | 100.00 | NULL | | 4 | DERIVED | l | NULL | eq_ref | PRIMARY,IX_Location_ID | PRIMARY | 4 | ngease.sl.LocationID | 1 | 100.00 | NULL | | 6 | DEPENDENT SUBQUERY | documentteammemberapproval | NULL | ref | FK_DocumentTeamMemberApproval_DocumentTeamMember,FK_DocumentTeamMemberApproval_DocumentRev,FK_DocumentTeamMemberApproval_Organization | FK_DocumentTeamMemberApproval_DocumentTeamMember | 4 | const | 1 | 5.00 | Using where | | 5 | DEPENDENT SUBQUERY | documentteammember | NULL | ref | FK_DocumentTeamMember_User,FK_DocumentTeamMember_Organization,FK_DocumentTeamMember_DocumentRev | FK_DocumentTeamMember_User | 4 | const | 1 | 5.00 | Using where | | 17 | UNION | <subquery22> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary | | 17 | UNION | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 4 | <subquery22>.id | 2 | 100.00 | NULL | | 17 | UNION | <derived4> | NULL | ref | <auto_key0> | <auto_key0> | 8 | <subquery22>.id,const | 2 | 100.00 | FirstMatch(<derived3>) | | 22 | MATERIALIZED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | | NULL | UNION RESULT | <union2,17> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+----------------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+----------+--------------------------------------------+ +----+--------------------+----------------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+---------------------------------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+----------------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+---------------------------------+------+----------+--------------------------------------------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key1> | <auto_key1> | 4 | const | 1 | 100.00 | NULL | | 2 | DERIVED | <subquery7> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary | | 2 | DERIVED | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 4 | <subquery7>.id | 4 | 100.00 | NULL | | 2 | DERIVED | <derived4> | NULL | ref | <auto_key0> | <auto_key0> | 8 | <subquery7>.id,const | 4 | 100.00 | Using where; Not exists | | 7 | MATERIALIZED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 47 | 10.00 | Using where | | 3 | DERIVED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 47 | 100.00 | Using temporary | | 4 | DERIVED | d | NULL | ref | PRIMARY,FK_Document_Organization3,document_isdeleted_indx,IX_Document_Composite | IX_Document_Composite | 5 | const,const | 41 | 100.00 | Using temporary | | 4 | DERIVED | dr | NULL | ref | PRIMARY,FK_Document_EntityType1,FK_DocumentRev_Document,FK_DocumentRev_ReleaseStatus,IX_DocumentRev_IsWorkingDoc | FK_DocumentRev_Document | 4 | ngease.d.ID | 3 | 25.01 | Using where | | 4 | DERIVED | ast | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ngease.dr.DocumentSubtype | 1 | 75.00 | Using where | | 4 | DERIVED | rs | NULL | ALL | PRIMARY | NULL | NULL | NULL | 7 | 14.29 | Using where; Using join buffer (hash join) | | 4 | DERIVED | dr | NULL | eq_ref | PRIMARY,FK_DocumentRev_Document | PRIMARY | 4 | ngease.dr.ID | 1 | 100.00 | NULL | | 4 | DERIVED | d | NULL | eq_ref | PRIMARY,FK_Document_Scope | PRIMARY | 4 | ngease.dr.DocumentID | 1 | 100.00 | Using where | | 4 | DERIVED | sl | NULL | ref | FK_ScopeLocation_Scope,FK_ScopeLocation_Location,IX_ScopeLocation_ScopeID | FK_ScopeLocation_Scope | 4 | ngease.d.ScopeID | 1 | 100.00 | NULL | | 4 | DERIVED | l | NULL | eq_ref | PRIMARY,IX_Location_ID | PRIMARY | 4 | ngease.sl.LocationID | 1 | 100.00 | NULL | | 6 | DEPENDENT SUBQUERY | documentteammemberapproval | NULL | ref | FK_DocumentTeamMemberApproval_DocumentTeamMember,FK_DocumentTeamMemberApproval_DocumentRev,FK_DocumentTeamMemberApproval_Organization | FK_DocumentTeamMemberApproval_DocumentTeamMember | 4 | const | 1 | 5.00 | Using where | | 5 | DEPENDENT SUBQUERY | documentteammember | NULL | ref | FK_DocumentTeamMember_User,FK_DocumentTeamMember_Organization,FK_DocumentTeamMember_DocumentRev | FK_DocumentTeamMember_DocumentRev | 5 | func | 1 | 3.66 | Using where | | 17 | UNION | <subquery22> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary | | 17 | UNION | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 4 | <subquery22>.id | 4 | 100.00 | NULL | | 17 | UNION | <derived4> | NULL | ref | <auto_key0> | <auto_key0> | 8 | <subquery22>.id,<subquery22>.id | 4 | 10.00 | Using where; FirstMatch(<derived3>) | | 22 | MATERIALIZED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 47 | 10.00 | Using where | | NULL | UNION RESULT | <union2,17> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+----------------------------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+---------+---------------------------------+------+----------+--------------------------------------------+
------------------------------------------------------------------------------------------------
the result of this calculation: innodb_buffer_pool_size + innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + max_connections * ( thread_stack + max( max_allowed_packet, net_buffer_length ) + net_buffer_length + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size )
27917287424+640000000+0+16777216+0+16777216+8388608+1000*(286720+max(16777216+16384)+16384+131072+262144+262144+262144)= 46579884032
------------------------------------------------------------------------------------------------
total RAM= 32465552kb
--------------------------------------------------------------------------------------------------
whether the query induces swapping
No, swap memory was used.
Edited 1 time(s). Last edit at 06/14/2022 10:29AM by Peter Brawley.
Subject
Written By
Posted
June 10, 2022 06:37AM
June 10, 2022 10:21AM
Re: MySQL server crash after running SELECT queries with a connection pool of 60
June 14, 2022 09:04AM
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.