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...
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
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@lists.launchpad.net 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.