… then recreated using fast index creation; dumps produced by mysqldump first create tables with all secondary indexes and then load the data, which… index, and adding the secondary indexes after the data is loaded.” mysqldump in Percona Server supports the new option –innodb-optimize-keys…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
…. Table Size This is the worst trouble maker. Typically load starts fast but as it progresses it may slow down dramatically, typically… ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA…
Comment: Why MySQL could be slow with large tables ?
November 29, 2006 By 141 Comments
Normally MySQL is rather fast loading data in MyISAM table, but there is exception, which is … my play box I tried to load data into MyISAM table (which was previously dumped as mysqldump –tab) The data was some… call it trivial fast task, unfortunately I had unique key on varchar(128) as part of the schema. The load took some…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…. Mydumper for logical backups Strong Points: Very fast for logical backups – compared to mysqldump Consistent backups between myisam and innodb tables. Global… has the triggers # mysqldump … -d -t How to restore mydumper data: Load the schema file Run myloader –threads=x Load the schema-post…
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
… with mysqldump, final size 30286M. To create InnoDB compression table I use ENGINE=InnoDB KEY_BLOCK_SIZE=8 and to test fast…) Also interesting to note that fast index creation allows to load time two time faster than usual load. Ok, There are a lot… baseline So in conclusion – Fast Index creation allows to speedup load 2 times (even indexes fit into memory) – Load in compress format slower…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… blocking purge by mistake. Block startup until LRU dump is loaded Added a new boolean option, –innodb-blocking-lru-restore, which… a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (Alexey Kopytov). After adding an index using Fast Index…’t respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The mysqldump option –innodb-optimize-keys did not work correctly with tables…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… blocking purge by mistake. Block startup until LRU dump is loaded Added a new boolean option, –innodb-blocking-buffer-pool-restore… a mysqldump output, so –innodb-optimize-keys should ignore foreign key constrains. #859078 (Alexey Kopytov). After adding an index using Fast Index…’t respect QUERY_RESPONSE_TIME_STATS . #855312 (Oleg Tsarev). The mysqldump option –innodb-optimize-keys did not work correctly with tables…
Post: State of the art: Galera - synchronous replication for InnoDB
October 27, 2009 By 15 Comments
… Release 0.7pre. In current version propagation is done by mysqldump from one of nodes (“donor”). In next release Codership is… time for quick maintenance, after start, the node has to load whole mysqldump, like it is new empty node. I hope Codership… data. Although it also has serious drawback – our cluster is fast as fast the “weakest” node in the chain. So if one…
Post: How would you compress your MySQL Backup
June 5, 2008 By 29 Comments
… data being compressed but it looks like LZO uses relatively fast compression by default already. With real server grade CPU deployment… higher load which can be the issue if you’re not using dedicated server for backups. I should also note for mysqldump backup typically tools with better and slower compression make sense because it takes longer to dump and much longer to load…
Post: Replication of MEMORY (HEAP) Tables
October 15, 2010 By 7 Comments
… you can do ? Use Innodb Table Instead Innodb is quite fast when it fits in memory so for most applications this… SLAVE STOP; dump all your memory tables using MySQLDump; Restart the MySQL As planned; Load Dumped tables; run SLAVE START; Remove skip… case you will need to disable binary logging while loading data from mysqldump as you may not want these changes to be…
