maria-discuss team mailing list archive

[Peter Mclarty <peter.mclarty@xxxxxxxxxxxxxxxxxxxxxxx>]

Hi Ljr

You might want to read through this to help you understand why it might
have grown
https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

As to it being 4GB, that isn't really a problem in itself and if the cause
was a long running transaction, which it looks like with the undo files
being present then you will want to find the source of that transaction to
avoid a repeat. FYI i had a DB server with a 90GB ibdata1 before I moved to
file_per_table and cleaned it up.

Run show explain for xxx being the processid for all current processes to
see if you can find what might have a stalled transaction holding the undo.
Have a look through the above page for additional help to identify the
culprit and if it is still there only dormant waiting for some action


Cheers

Peter


On Thu, Aug 25, 2016 at 11:46 PM, Ljr Yang <dbmsprog@xxxxxxxxx> wrote:

> Hi, All
>   we use MariaDB 10.1.16 ,  about have 30 machines.  Recently ,there one
> master and slave's ibdat1 growed  to about 4GB, others machine are normal.
>
> mainly parameter in my.cnf :
> innodb_data_home_dir = /data/mysql/data
> innodb_data_file_path = ibdata1:2G:autoextend
> innodb_log_files_in_group=3
> innodb_log_file_size = 1G
> innodb_undo_logs=128
> innodb_undo_tablespaces=4
> innodb_file_per_table= 1
>
> ls -l  /data/mysql/data
> -rw-r--r-- 1 mysql mysql 4429185024 Aug 25 21:38 ibdata1
> -rw-r--r-- 1 mysql mysql 1073741824 Aug 25 21:38 ib_logfile0
> -rw-r--r-- 1 mysql mysql 1073741824 Aug 25 21:38 ib_logfile1
> -rw-r--r-- 1 mysql mysql 1073741824 Aug 25 20:13 ib_logfile2
> -rw-r--r-- 1 mysql mysql  616562688 Aug 25 21:36 undo001
> -rw-r--r-- 1 mysql mysql  411041792 Aug 25 21:27 undo002
> -rw-r--r-- 1 mysql mysql  708837376 Aug 25 21:34 undo003
> -rw-r--r-- 1 mysql mysql  981467136 Aug 25 21:34 undo004
>
>
> Today,  when found the problem,  there are not some big transaction in
> show processlist.
> We guess ibdata1  had growed to 4GB  in previous some time, but we want
> to know how confirm the cause and shrink ibdate1 file ?
> Thank .
>
>
> --
> Ljr
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
*Peter McLarty*
*DBA *
*WHSR*
Compare The Market
P: +61 7 3377 8952
M: +61 4 0209 4238
http://www.comparethemarket.com.au

“Progress is impossible without change, and those who cannot change their
minds cannot change anything.” - George Bernard Shaw

-- 


Compare The Market is a brand and trading name of Compare The Market (Pty) Ltd (CTM). This email is for the intended addressee and is confidential and subject to copyright.
If you are not the intended addressee, confidentiality has not been waived and any use, interference with, or disclosure of this email is unauthorised. 
If you are not the intended addressee please immediately notify CTM and then delete the email. CTM does not warrant that this email is error or virus free.