So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) tablespace have grown dramatically from its starting 10MB size.
What could be the reason of this growth and what you can do about it ?
There are few things which are always stored in main tablespace – these are system tables, also known as data dictionary – table and index definitions and related stuff, double write buffer, insert buffer which temporary stores records to be inserted in the index tree and undo segment holding previous versions of changed rows.
The system tables size depends on the number and complexity of the tables you have in the system. If you have hundreds of thousands of tables it may consume gigabytes of space but for most installations we’re speaking about tens of megabytes or less. Double Write Buffer is fixed size (2MB = 128 of 16KB pages) and so will not affect growth of your main Innodb Tablespace. Insert Buffer size is also restricted to half of the buffer pool size (can be changed via innodb_ibuf_max_size option) which can be significant size for systems with large amount of memory. Finally undo space can grow unbound depending on your transaction size.
The challenge is both Insert buffer and undo space will grow and shrink during the database operation and unless you can them on being large your would not know what caused your system tablespace size to explode – they will shrink to the normal size and all what you have will be free space, which you unfortunately can’t reclaim without reloading tour database. This is where Trending can help, for example MySQL CACTI Templates will have the graphs you need.
If you’re looking at SHOW INNODB STATUS this is how you can see Insert Buffer Size:
1 | Ibuf: size 108931, free list len 64619, seg size 173551, |
In this case we can see the segment is allocated to 173551 pages which is about 2.7GB only about 2/3 of it is in use right now but for sake of monitoring tablespace size you need a full allocated number.
Lets now look at the Undo Space which is a lot more interesting (and which is the most likely cause of getting extremely large system tablespace)
The records can be stored in the undo tablespace for 2 reasons. First it is re
1 | History list length 4000567 |
In this case the History Length is about 4 million which means there are 4 million of transactions which are not yet purged. This is not very helpful as single transaction may modify single row and so be responsible for one undo entry or it may modify millions of rows. Row size can also vary a lot. For many OLTP applications though which have a lot of tiny transactions this is a very good indicator.
If you’re running Percona Server the following can be helpful:
1 2 3 4 5 6 7 | mysql> select * from innodb_rseg; +---------+----------+----------+---------+------------+-----------+ | rseg_id | space_id | zip_size | page_no | max_size | curr_size | +---------+----------+----------+---------+------------+-----------+ | 0 | 0 | 0 | 6 | 4294967294 | 20993 | +---------+----------+----------+---------+------------+-----------+ 1 row in set (0.00 sec) |
curr_size will display the current size of RSEG which defines undo segment size in pages. The 21K here is about 340M of space.
From the practical standpoint there are 3 reasons a lot of undo space may be required:
Running Transaction which does a lot of changes – If transaction modifies a lot of rows it has to use a lot of space in undo segment and there is no way around it. Be careful in particular with update or delete transactions which go over a lot of rows. In many cases doing such processes in chunks, updating/deleting may be thousands of rows per transaction may be better if your application can handle it. Note ALTER TABLE will not require excessive amount of undo space even for very large tables as it internally commits every 10000 rows.
Running Very Long Transaction If you’re running very long transaction, be it even SELECT, Innodb will be unable to purge records for changes which are done after this transaction has started, in default REPEATABLE-READ isolation mode. This means very long transactions are very bad causing a lot of garbage to be accommodated in the database. It is not limited to undo slots. When we’re speaking about Long Transactions the time is a bad measure. Having transaction in read only database open for weeks does no harm, however if database has very high update rate, say 10K+ rows are modified every second even 5 minute transaction may be considered long as it will be enough to accumulate about 3 million of row changes.
Purge Thread Falling Behind This is the most dangerous reason. It is possible for database updates happen faster than purge thread can purge records when they are no more needed which means undo space can just grow until it consumes all free space (or specified max size for ibdata1 file). “Good” thing is performance typically starts to suffer terribly well before that and it gets noticed. There are to things you can do about this problem first you can use innodb_max_purge_lag to make a threads doing modifications slow down if purge thread can’t keep up. This however does not work in all cases. If you’re running XtraDB you can also use innodb_use_purge_thread to use dedicated purge thread, which works a bit faster as it does not need to compete with other activities of main theread. You can also use more than one purge thread by setting this variable to higher values though this functionality is a bit experimental.
One related question I get asked often is why Master and Slave may get so much different table space size in the end. The answer is of course their workload is very different. On one hand slave has all updates going from one thread so it has less chance for purge thread to fall behind, on other a lot of people use slave for very long queries which may make long transactions reasons a lot more likely. So it can be both – either master or slave can have it main tablespace growing larger than its counterpart.
Writing this blog post I also discovered even in XtraDB we do not have as much transparency in regards to undo space and purging as I would like. I filed number of feature requests and I hope we’ll have a chance to fix this soon.
As someone new to MySQL, I found this article extremely helpful. Ditto for other articles written by Peter Zaitsev. Thank you.
Since 5.5.4, the variable is called “innodb_change_buffer_max_size.”
Default is 25% of innodb_buffer_pool_size.
I would very much like to know how to set this correctly on large systems.