MySQL Forums
Forum List  »  InnoDB

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
ibdata1 keeps growing forever....
226
April 30, 2018 07:39AM


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.