MySQL Forums
Forum List  »  InnoDB

innodb growing oversize mysql version 5.5.28
Posted by: mauro fattore
Date: July 09, 2015 02:48AM

Dear MySql community ,
it would be nice if you could explain me ( and possibly try to recover) the following situation:

ibdata1 is unexepectly growing oversize in mysql :

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------+
| innodb_version | 1.1.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.28 |
| version_comment | MySQL Community Server (GPL), wsrep_23.7.rXXXX |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------------------------+

[root@node-8 ~]# ls -lrth /var/lib/mysql
total 46G
drwx------ 2 mysql mysql 4.0K Jan 17 2014 performance_schema
-rw-r--r-- 1 mysql mysql 108 Jan 17 2014 RPM_UPGRADE_MARKER-LAST
-rw-r--r-- 1 root root 108 Jan 17 2014 RPM_UPGRADE_HISTORY
-rw-rw---- 1 mysql mysql 85 Jan 17 2014 GRA_29_6128.log
-rw-rw---- 1 mysql mysql 85 Jan 17 2014 GRA_22_9050.log
-rw-rw---- 1 mysql mysql 85 Jan 17 2014 GRA_25_10836.log
-rw-rw---- 1 mysql mysql 85 Jan 18 2014 GRA_6_353980.log
-rw-rw---- 1 mysql mysql 85 Jan 18 2014 GRA_17_360000.log
-rw-rw---- 1 mysql mysql 85 Jan 20 2014 GRA_29_946306.log
-rw-rw---- 1 mysql mysql 85 Jan 20 2014 GRA_1_1063645.log
-rw-rw---- 1 mysql mysql 85 Jan 20 2014 GRA_17_1073149.log
drwx------ 2 mysql mysql 4.0K May 29 2014 cinder
drwx------ 2 mysql mysql 4.0K May 29 2014 glance
drwx------ 2 mysql mysql 4.0K May 29 2014 heat
drwx------ 2 mysql mysql 4.0K May 29 2014 keystone
drwx------ 2 mysql mysql 4.0K May 29 2014 mysql
drwx------ 2 mysql mysql 4.0K May 29 2014 neutron
drwx------ 2 mysql mysql 4.0K May 29 2014 nova
drwx------ 2 mysql mysql 4.0K May 29 2014 test
srwxrwxrwx 1 mysql mysql 0 Jun 18 2014 mysql.sock
-rw-rw---- 1 mysql mysql 110 Jun 18 2014 grastate.dat
-rw-rw---- 1 mysql mysql 46G Jul 9 08:43 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Jul 9 08:43 ib_logfile0
-rw------- 1 mysql mysql 129M Jul 9 08:43 galera.cache
-rw-rw---- 1 mysql mysql 5.0M Jul 9 08:43 ib_logfile1
-------------------------------------------------------------------------------

here below an output form mysql query:

mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
150709 8:30:13 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 35 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 33161659 1_second, 33161568 sleeps, 3314249 10_second, 21876 background, 21874 flush
srv_master_thread log flush and writes: 33294933
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4141200, signal count 5216304
Mutex spin waits 8067919, rounds 21832953, OS waits 68215
RW-shared spins 4543915, rounds 119620289, OS waits 3454352
RW-excl spins 855409, rounds 30698083, OS waits 578254
Spin rounds per wait: 2.71 mutex, 26.33 RW-shared, 35.89 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 20EC5764
Purge done for trx's n:o < 20EC5760 undo n:o < 0
History list length 4231
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 6670398, OS thread handle 0x7f40d8c6c700, query id 267302330 localhost root sleeping
show engine innodb status
---TRANSACTION 20EC574E, not started
MySQL thread id 10, OS thread handle 0x7f40d9347700, query id 267302315 committed 186023132
---TRANSACTION 20EC570F, not started
MySQL thread id 22, OS thread handle 0x7f40d8fb9700, query id 267302271 committed 186023111
---TRANSACTION 20EC572D, not started
MySQL thread id 14, OS thread handle 0x7f40d91c1700, query id 267302292 committed 186023121
---TRANSACTION 20EC5730, not started
MySQL thread id 25, OS thread handle 0x7f40d8ef6700, query id 267302294 committed 186023122
---TRANSACTION 20EC5733, not started
MySQL thread id 31, OS thread handle 0x7f40d8d70700, query id 267302296 committed 186023123
---TRANSACTION 20EC5715, not started
MySQL thread id 28, OS thread handle 0x7f40d8e33700, query id 267302276 committed 186023113
---TRANSACTION 20EC5745, not started
MySQL thread id 30, OS thread handle 0x7f40d8db1700, query id 267302309 committed 186023129
---TRANSACTION 20EC5742, not started
MySQL thread id 15, OS thread handle 0x7f40d9180700, query id 267302307 committed 186023128
---TRANSACTION 20EC5736, not started
MySQL thread id 16, OS thread handle 0x7f40d913f700, query id 267302298 committed 186023124
---TRANSACTION 20EC5739, not started
MySQL thread id 9, OS thread handle 0x7f40d9388700, query id 267302300 committed 186023125
---TRANSACTION 20EC5754, not started
MySQL thread id 3, OS thread handle 0x7f40f004a700, query id 267302319 committed 186023134
---TRANSACTION 20EC574B, not started
MySQL thread id 2, OS thread handle 0x7f40f008b700, query id 267302313 committed 186023131
---TRANSACTION 20EC5751, not started
MySQL thread id 24, OS thread handle 0x7f40d8f37700, query id 267302317 committed 186023133
---TRANSACTION 20EC573F, not started
MySQL thread id 27, OS thread handle 0x7f40d8e74700, query id 267302305 committed 186023127
---TRANSACTION 20EC571B, not started
MySQL thread id 18, OS thread handle 0x7f40d90bd700, query id 267302280 committed 186023115
---TRANSACTION 20EC5718, not started
MySQL thread id 6, OS thread handle 0x7f40df39c700, query id 267302278 committed 186023114
---TRANSACTION 20EC5724, not started
MySQL thread id 23, OS thread handle 0x7f40d8f78700, query id 267302286 committed 186023118
---TRANSACTION 20EC5757, not started
MySQL thread id 11, OS thread handle 0x7f40d92c5700, query id 267302321 committed 186023135
---TRANSACTION 20EC5760, not started
MySQL thread id 29, OS thread handle 0x7f40d8df2700, query id 267302327 committed 186023138
---TRANSACTION 20EC5721, not started
MySQL thread id 8, OS thread handle 0x7f40df35b700, query id 267302284 committed 186023117
---TRANSACTION 20EC5709, not started
MySQL thread id 21, OS thread handle 0x7f40d903b700, query id 267302266 committed 186023109
---TRANSACTION 20EC570C, not started
MySQL thread id 5, OS thread handle 0x7f40d9306700, query id 267302269 committed 186023110
---TRANSACTION 20EC573C, not started
MySQL thread id 7, OS thread handle 0x7f40d9284700, query id 267302302 committed 186023126
---TRANSACTION 20EC575A, not started
MySQL thread id 17, OS thread handle 0x7f40d90fe700, query id 267302323 committed 186023136
---TRANSACTION 20EC5712, not started
MySQL thread id 32, OS thread handle 0x7f40d8d2f700, query id 267302274 committed 186023112
---TRANSACTION 20EC5763, not started
MySQL thread id 13, OS thread handle 0x7f40d9202700, query id 267302329 committed 186023139
---TRANSACTION 20EC575D, not started
MySQL thread id 4, OS thread handle 0x7f40c3fff700, query id 267302325 committed 186023137
---TRANSACTION 20EC571E, not started
MySQL thread id 33, OS thread handle 0x7f40d8cee700, query id 267302282 committed 186023116
---TRANSACTION 20EC572A, not started
MySQL thread id 12, OS thread handle 0x7f40d9243700, query id 267302290 committed 186023120
---TRANSACTION 20EC5748, not started
MySQL thread id 19, OS thread handle 0x7f40d907c700, query id 267302311 committed 186023130
---TRANSACTION 20EC5727, not started
MySQL thread id 26, OS thread handle 0x7f40d8eb5700, query id 267302288 committed 186023119
---TRANSACTION 20EC5706, not started
MySQL thread id 20, OS thread handle 0x7f40d8ffa700, query id 267302264 committed 186023108
--------
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
14932292 OS file reads, 484997238 OS file writes, 173825071 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 14.54 writes/s, 5.11 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1400, seg size 1402, 3401673 merges
merged operations:
insert 3402526, delete mark 62, delete 8
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 1 buffer(s)
3.77 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 169661160032
Log flushed up to 169661159752
Last checkpoint at 169661159384
0 pending log writes, 0 pending chkp writes
165665553 log i/o's done, 4.89 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 1215621
Buffer pool size 8192
Free buffers 0
Database pages 8191
Old database pages 3003
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 12037771, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14932290, created 4258859, written 311887394
0.00 reads/s, 0.06 creates/s, 9.43 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: 8191, unzip_LRU len: 0
I/O sum[482]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5991, id 139916554307328, state: sleeping
Number of rows inserted 3656501, updated 126656613, deleted 126, read 130928321
0.00 inserts/s, 3.77 updates/s, 0.00 deletes/s, 3.77 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.04 sec)

ERROR:
No query specified

mysql>
------------------------------------------------------------------------


any support would be greatly appreciated

regards
Mauro Fattore

Options: ReplyQuote


Subject
Views
Written By
Posted
innodb growing oversize mysql version 5.5.28
1748
July 09, 2015 02:48AM


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.