ibdata1 keeps growing forever....
Posted by:
Fanos Te
Date: April 30, 2018 07:39AM
Good day to everyone
I have an Ubuntu server (10.04) with mysql (5.1)
The machine is running a nagios / OPSView monitor application so I can monitor various services/servers/networks etc.
Its been a month now that ibdata1 (under mysql workdir "/var/lib/mysql") is gorowing.
I looked up how to shrink but all I got is that I have to dump, delete ibdata1 and the log and import back all dbs so the ibdata1 gets resetted.
Ok I got it.
My question is why is ibdata1 growing?
I mean even if I do the procedure to dump and re-import the problem will still be there.
The normal size of ibdata1 was always around 15GB (its been at 15GB for years...now within a month it reached 40!!!!
the output of the transactions / SHOW ENGINE INNODB STATUS;
| InnoDB | |
=====================================
180430 15:46:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 101837238, signal count 101487976
Mutex spin waits 0, rounds 2293097597, OS waits 18553277
RW-shared spins 81151728, OS waits 71442532; RW-excl spins 5196102, OS waits 3937231
------------
TRANSACTIONS
------------
Trx id counter 6 206838765
Purge done for trx's n:o < 6 206838142 undo n:o < 0 0
History list length 252
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 10460, OS thread id 139973218871040
MySQL thread id 18029, query id 37537978 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 6 206838764, not started, process no 10460, OS thread id 139971874244352
MySQL thread id 17926, query id 37537977 localhost nagios
---TRANSACTION 6 205399998, not started, process no 10460, OS thread id 139971876108032
MySQL thread id 17416, query id 37536811 localhost opsview
---TRANSACTION 0 0, not started, process no 10460, OS thread id 139971923662592
MySQL thread id 17410, query id 37534556 localhost opsview
---TRANSACTION 6 204727185, not started, process no 10460, OS thread id 139971875841792
MySQL thread id 17405, query id 37528024 localhost opsview
---TRANSACTION 0 0, not started, process no 10460, OS thread id 139971921024768
MySQL thread id 17360, query id 37532705 localhost opsview
---TRANSACTION 0 0, not started, process no 10460, OS thread id 139971875043072
MySQL thread id 17201, query id 37536826 localhost opsview
---TRANSACTION 6 206837450, not started, process no 10460, OS thread id 139973220202240
MySQL thread id 16976, query id 37537156 localhost nagios
---TRANSACTION 6 206837453, not started, process no 10460, OS thread id 139971876374272
MySQL thread id 16974, query id 37537169 localhost opsview
---TRANSACTION 6 206832275, not started, process no 10460, OS thread id 139971923130112
MySQL thread id 16949, query id 37536807 localhost nagios
---TRANSACTION 6 206836923, not started, process no 10460, OS thread id 139971874510592
MySQL thread id 16948, query id 37536812 localhost opsview
---TRANSACTION 6 206838139, not started, process no 10460, OS thread id 139971921557248
MySQL thread id 16929, query id 37537596 localhost nagios
---TRANSACTION 6 206838140, not started, process no 10460, OS thread id 139971875309312
MySQL thread id 16928, query id 37537597 localhost opsview
---TRANSACTION 6 206834452, not started, process no 10460, OS thread id 139971923928832
MySQL thread id 16912, query id 37535290 localhost nagios
---TRANSACTION 6 206834453, not started, process no 10460, OS thread id 139971875575552
MySQL thread id 16911, query id 37535291 localhost opsview
---TRANSACTION 6 206835732, not started, process no 10460, OS thread id 139971922597632
MySQL thread id 16711, query id 37537173 localhost nagios
---TRANSACTION 6 206837822, not started, process no 10460, OS thread id 139971921291008
MySQL thread id 16710, query id 37537394 localhost opsview
---TRANSACTION 6 206494208, not started, process no 10460, OS thread id 139973219137280
Any advise is appreciated!
Thx in advance!