… medium and large size databases – for databases below 100GB in size compression performance is usually not the problem (though backup impact on… the bottleneck for all the backup process. Recently I did a little benchmark compressing 1GB binlog file with GZIP (compression done from… MySQL binary log file which often contains plenty of similar events, which could be the reason for so large size difference based…
Post: Living with backups
November 6, 2008 By 28 Comments
Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily… over random I/O coming from MySQL, but also such large I/O requests take significant time to complete and the… make a backup application that would not interfere with the system cache. By specifying O_DIRECT flag when opening a file, an…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… same as original file system Why snapshot based MySQL backups are great ? There are number of reasons: Almost Hot backup In most cases… to compress backup ? Backup it to tape, FTP or any network backup software – it is easy as you just need to copy files. Fast… backup process – I’ve specified 16GB in this case. If your undo size is not large enough snapshot will get invalidated and backup…
Post: Thoughs on Innodb Incremental Backups
November 10, 2008 By 8 Comments
… having incremental backups remain the problem. First why do you need incremental backups at all ? Why not just take the full backups daily… to restore to, having huge amount of full copies of large database is not efficient. Especially if it only changes couple… file) stored in the page. Using this data it should be easy to implement very efficient and yet simple for Incremental backup…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… Com_assign_to_keycacheblogpercona.commanual Com_backup_tableblogpercona.commanual Com_beginblogpercona.commanual Com_…Key_writesblogpercona.commanual languageblogpercona.commanual large_files_supportblogpercona.commanual large_page_sizeblogpercona.commanual large_pagesblogpercona.commanual last_…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 69 Comments
… 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would… Linux setup LVM for database directory to get more efficient backup. EXT3 file system works OK in most cases, though if you… out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed needs but 256M…
Comment: Disaster: LVM Performance in Snapshot Mode
February 6, 2009 By 55 Comments
… files (<16k) creation, no much difference; For large files creation, LVM causes the speed drops from 1300000 to 900000. Random Read: For small files (<16k) creation, no much difference; For large files… bother me too much and I use LVM snapshot to backup 200G databases. In the production, RAID 5 + LVM (IBM x3650…
Comment: How would you compress your MySQL Backup
June 6, 2008 By 29 Comments
… the source is closed. I want to avoid staging files on disk during backup (write to a socket). I know people who…. I also want parallel IO (many threads) running for both backup and restore to copy in/out the large files.
Post: Spreading .ibd files across multiple disks; the optimization that isn't
December 25, 2010 By 22 Comments
…frequently – “If you enable innodb_file_per_table, each table is it’s own .ibd file. You can then relocate …unbalance one disk overloaded, and many idle. You restrict your backup methods. You can’t LVM snapshot across logical volumes…;) This is much easier to do than more one large table around.
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
… I need 5MB of undo space per second of backup. Now say my backup takes 1 hour which is 3600 second – This… in the backup and you do not want query log and general logs anyway. However DO place Innodb Log Files on the… free space and you would like to setup replication for large database with little no – connect USB hard drive place LVM…
