Hi,
I am currently experiencing problems making backups of some of our “heavy write load”-databases.
Our backup strategy is making full dumps in a single transaction using mysqldump. During that time the history list is growing rapidely, which is normal due to tx_isolation = REPEATABLE-READ
It now seems that the undo space ist “swapped out” to the ibdata1
After the Backup is finished I end up with a server doing heavy disc read IO from the undo space to “feed” the purge thread to get rid of the history list.
This conflicts with the heavy default write workload on the server ending in a max_connnections situation and a more or less useless server which need time without any work load to purge the history list.
How can this problem be solved ?
Possible known options:
- Do the backup on the replication slave which i dont like because of data consistency
- Do the backup with Xtrabackup which i dont like because of wasting a lot of backup space
Server is a 24 GB Box with Raid6 (4 disc)
5.1.54-rel12.5-log (Percona Server (GPL), 12.5 , Revision 188)
According to http://www.mysqlperformanceblog.com/2008/11/21/how-to-calcul ate-a-good-innodb-log-file-size/ the peak time write load (not during backup) is
(none)@srv017224> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 2477563846622
1 row in set (0.01 sec)
1 row in set (1 min 0.04 sec)
Log sequence number 2477612161440
1 row in set (0.00 sec)
(none)@srv017224> select (2477612161440 - 2477563846622) / 1024 / 1024 as MB_per_min;
±------------+
| MB_per_min |
±------------+
| 46.07660103 |
±------------+
1 row in set (0.00 sec)
The current innodb_log_file_size=288MB is definitively to small but should not be the cause of this problem, right ?
Another “funny” thing is the following on one of the servers:
Trx id counter 2C1CAD773
Purge done for trx’s n:o < 2C1CAD6E4 undo n:o < 0
History list length 95
information_schema@srv017224> select * from INNODB_RSEG;±--------±---------±---------±--------±-----------±----------+| rseg_id | space_id | zip_size | page_no | max_size | curr_size |±--------±---------±---------±--------±-----------±----------+| 0 | 0 | 0 | 6 | 4294967294 | 2889502 |±--------±---------±---------±--------±-----------±----------+
If the history list length is on 95 how is it possible to have a such a huge RSEG ?
It´s a little bit confusing.
Best Regards