… background. Things are different with MySQL. There are many successful applications, using tens of thousands of tables per host and archiving great… do backup and restore if you need only portion of your data recovered. There are some performance problems with many tables some… loosing data etc. Read more in my Why MySQL Could be slow with Large Tables article. 5. The n+1 pattern This probably…
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
…Now back to our question of space requirements for MySQL Backups using LVM. As you can see from the … lot of undo space. Running ALTER TABLE or OPTIMIZE TABLE will cause table rebuild and use a lot of … and you would like to setup replication for large database with little no – connect USB hard drive…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… large data sets in mySQL teach you ? Of course you have to learn a lot about query optimization, art of building summary tables… working with large data things in MySQL is patience and careful planning. Both of which relate two single property of large data sets… backup. You need Patience simply because things are going to take a lot of time. Think about 500GB table for example – ALTER TABLE…
Post: Should you move from MyISAM to Innodb ?
January 12, 2009 By 59 Comments
…, background flushes while on the drawbacks side we see significantly large table size (especially if data size is close to memory size… complicates operations tasks (backups, balancing, performance analyzes) as well as it exercises not so common paths in the MySQL server – in particular…
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 copies… I/O scheduler over random I/O coming from MySQL, but also such large I/O requests take significant time to complete… -c -z -v -f – mysql | ssh backup@storage-host /root/backup-tools/write_backup.sh This will read /mnt/snapshot/mysql directory at 15000000 bytes…
Post: Percona XtraBackup 1.6.4
December 19, 2011 By 9 Comments
…, leading to a failed backup. With this bug fixed, instead of setting a large wait_timeout for the MySQL connection, innobackupex will regularly…, innobackupexnow safely ignores file not found errors for non-InnoDB tables. Bug Fixed: #859546 (Lachlan Mulcahy) When the –incremental and –incremental…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
…big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough …even for backup recovery, for example dealing with temporary tables (unless they are used to populate normal tables using SQL)….would be waste to pull binary logs to large variety of slaves? This is the case …
Post: Percona Toolkit Webinar followup Q&A
December 19, 2012 By 1 Comment
… would be pt-query-digest –review S=/tmp/mysql_sandbox5527.sock,u=msandbox,p=msandbox,D=…large tables can make your ibdata file grow a lot. As is known, this space won’t be recovered when the auxiliary table… If you’re using Innodb only, streaming a backup with xtrabackup could be a good and safe …
Post: MySQL Crash Recovery
July 30, 2006 By 30 Comments
… which may seriously affect MySQL Performance. MyISAM Corruption – If you’re writting to MyISAM tables there is very large chance of them becoming… significant time if you have very large number of tables (Some users have hundreds of thousands of tables per database host). This one… is table open which is important. There are other problems which you may experience related to MySQL Crash Recovery – Restoring data from backup…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 75 Comments
…with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what … LVM for database directory to get more efficient backup. EXT3 file system works OK in most cases, … you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. …
