Re: MySQL server crash after running SELECT queries with a connection pool of 60
Posted by: Chinmay Ram
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';

------------------------------------------------------------------------------------------------------------------------
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.

Options: ReplyQuote


Subject
Written By
Posted
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.