4

I'm running an EC2 instance (c3.large) with an RDS instance (db.t2.small). I'm frequently having issues with DB Connections spikes all of a sudden.

PROBLEM:

enter image description here

As you can see, I'm getting huge amounts of DB connections suddenly, in some cases it drops quickly and in some other cases it stays for a long time until I'm forced to reboot the instance because of the poor load. During this time we have barely 100 users online, and at one time when this occurred only 30 users where online.

Analytics

CPU Utilisation

CPU

DB Connections

Db Connections

Freeable Memory(MB)

memory

My RDS Configuration:

  • MySQL 5.6.23

  • InnoDB

  • It can only be accessed via same EC2 instance security group.

Custom Parameter Group

Parameter Group

innodb status

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2015-11-12 05:25:53 2ab74f60f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 256075 srv_active, 0 srv_shutdown, 560851 srv_idle
srv_master_thread log flush and writes: 816909
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 230298
OS WAIT ARRAY INFO: signal count 244122
Mutex spin waits 6207201, rounds 3485621, OS waits 12601
RW-shared spins 209626, rounds 6203909, OS waits 205881
RW-excl spins 864, rounds 382601, OS waits 11483
Spin rounds per wait: 0.56 mutex, 29.60 RW-shared, 442.83 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 340745127
Purge done for trx's n:o < 340745126 undo n:o < 0 state: running but idle
History list length 1540
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 766548, OS thread handle 0x2ab74f60f700, query id 176166621 172.31.27.148 mdsadmin init
show engine innodb status
---TRANSACTION 340745124, not started
MySQL thread id 766549, OS thread handle 0x2ab828491700, query id 176166614 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340745122, not started
MySQL thread id 766547, OS thread handle 0x2ab812284700, query id 176166609 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340744952, not started
MySQL thread id 766543, OS thread handle 0x2ab821f2b700, query id 176166418 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340744934, not started
MySQL thread id 766540, OS thread handle 0x2ab828eb9700, query id 176166404 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340745126, not started
MySQL thread id 766539, OS thread handle 0x2ab769b03700, query id 176166619 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340743783, not started
MySQL thread id 766538, OS thread handle 0x2ab82903f700, query id 176165235 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340745119, not started
MySQL thread id 766532, OS thread handle 0x2ab8216ca700, query id 176166598 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340740112, not started
MySQL thread id 766528, OS thread handle 0x2ab828513700, query id 176161502 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340745120, not started
MySQL thread id 766527, OS thread handle 0x2ab8289a5700, query id 176166602 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340740062, not started
MySQL thread id 766524, OS thread handle 0x2ab821b1b700, query id 176161443 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340740041, not started
MySQL thread id 766523, OS thread handle 0x2ab821995700, query id 176161408 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340745090, not started
MySQL thread id 766519, OS thread handle 0x2ab821e27700, query id 176166561 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340740117, not started
MySQL thread id 766517, OS thread handle 0x2ab821da5700, query id 176161523 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340745121, not started
MySQL thread id 766516, OS thread handle 0x2ab8214c2700, query id 176166606 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340743490, not started
MySQL thread id 766512, OS thread handle 0x2ab8219d6700, query id 176164938 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340739502, not started
MySQL thread id 766510, OS thread handle 0x2ab821d23700, query id 176160839 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340740215, not started
MySQL thread id 766506, OS thread handle 0x2ab828f3b700, query id 176161627 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340737146, not started
MySQL thread id 766493, OS thread handle 0x2ab82237c700, query id 176158370 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340737337, not started
MySQL thread id 766486, OS thread handle 0x2ab82830b700, query id 176158582 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340738073, not started
MySQL thread id 766475, OS thread handle 0x2ab8123c9700, query id 176159368 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340740319, not started
MySQL thread id 766461, OS thread handle 0x2ab7afb03700, query id 176161743 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340743475, not started
MySQL thread id 766458, OS thread handle 0x2ab828923700, query id 176164923 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340738076, not started
MySQL thread id 766409, OS thread handle 0x2ab82170b700, query id 176159377 172.31.27.148 mds1098 cleaning up
---TRANSACTION 340744895, not started
MySQL thread id 206596, OS thread handle 0x2ab818376700, query id 176166356 localhost 127.0.0.1 rdsadmin cleaning up
--------
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
36921 OS file reads, 4759085 OS file writes, 3053446 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.32 writes/s, 3.16 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 34 merges
merged operations:
 insert 35, delete mark 1, delete 1
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 5843809, node heap has 993 buffer(s)
2274.33 hash searches/s, 324.28 non-hash searches/s
---
LOG
---
Log sequence number 7531796117
Log flushed up to   7531796117
Pages flushed up to 7531796117
Last checkpoint at  7531794683
0 pending log writes, 0 pending chkp writes
1410073 log i/o's done, 1.29 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3021996032; in additional pool allocated 0
Dictionary memory allocated 364618
Buffer pool size   180224
Free buffers       135275
Database pages     43956
Old database pages 16063
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 44, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 36826, created 7130, written 2651853
0.00 reads/s, 0.03 creates/s, 2.29 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: 43956, 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   22528
Free buffers       16902
Database pages     5491
Old database pages 2006
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 18, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4561, created 930, written 759590
0.00 reads/s, 0.00 creates/s, 0.53 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: 5491, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   22528
Free buffers       17081
Database pages     5326
Old database pages 1946
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4603, created 723, written 214351
0.00 reads/s, 0.03 creates/s, 0.37 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: 5326, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   22528
Free buffers       16949
Database pages     5457
Old database pages 1994
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 4491, created 966, written 66006
0.00 reads/s, 0.00 creates/s, 0.03 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: 5457, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   22528
Free buffers       17182
Database pages     5220
Old database pages 1907
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4423, created 797, written 353457
0.00 reads/s, 0.00 creates/s, 0.08 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: 5220, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   22528
Free buffers       16696
Database pages     5703
Old database pages 2085
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4812, created 891, written 358885
0.00 reads/s, 0.00 creates/s, 0.53 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: 5703, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   22528
Free buffers       16827
Database pages     5582
Old database pages 2040
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4669, created 913, written 359338
0.00 reads/s, 0.00 creates/s, 0.21 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: 5582, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   22528
Free buffers       16890
Database pages     5516
Old database pages 2016
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4522, created 994, written 353374
0.00 reads/s, 0.00 creates/s, 0.32 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: 5516, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   22528
Free buffers       16748
Database pages     5661
Old database pages 2069
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4745, created 916, written 186852
0.00 reads/s, 0.00 creates/s, 0.24 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: 5661, 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
Main thread process no. 3917, id 46970152433408, state: sleeping
    Number of rows inserted 312986, updated 523245, deleted 246719, read 1091162641460.24 inserts/s, 0.39 updates/s, 0.16 deletes/s, 72657.77 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.04 sec)

ERROR:
No query specified

Any help would be really appreciated. Thanks.

  • So where those db connections are coming from? Did you have anything in slow log in during the time period? What MySQL version is this? Your mysql has max-connections+1, so you can use root to connect and check what's going on there... You might want to use pt-stalk to monitor processlist (percona.com/blog/2013/01/03/percona-toolkit-by-example-pt-stalk) – mysql_user Nov 10 '15 at 12:35
  • I checked logs query logs, and I have done optimisation. I remember checking the process list at that time, and most where the process where not showing any queries. MySQL version 5.6.23 – Arjun Komath Nov 10 '15 at 16:42
  • Is performance schema disabled ? – mysql_user Nov 10 '15 at 18:46
  • @mysql_user It was disabled, I just enabled it. – Arjun Komath Nov 10 '15 at 21:25
  • 1
    @Mischa I moved back to EC2, couldn't fix RDS. – Arjun Komath Jun 18 '17 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.