… a customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting… name a few: the big LOAD DATA INFILE clogs the binary log and slows replication down. If the load takes 4 hours on the… 1; done Note that the file mentioned in LOAD DATA INFILE is /tmp/my-fifo, not infile.txt! After I was done, I ran…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
…load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which… unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution. Note disabling binary… to use: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
… following link: http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:generating_a_table_definition With the table definition… the data in our hands the last step is to import it in our database: mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries…
Post: Interested to learn about New Innodb Plugin Performance ?
April 15, 2008 By 2 Comments
… Innodb for) – for example neither of standard data load tools (mysqldump import, using LOAD DATA INFILE or ALTER TABLE from other storage engine) would use Fast Innodb… and Ken which will be followed by my presentation on Innodb Scalability Limits. I will not go a lot into this… cool. P.S I’m also very happy to see Innodb continues to provide full Server functionality under GPL License.
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 16 Comments
… over 200K of inserts/sec. This time I worked with Innodb tables… it was a different system with different table structure… what I used for start. Using multiple buffer pools with innodb_buffer_pool_instances=8 was very important. Second thing – Partitioning… you would get much lower numbers. In fact we used load data infile which is one of the ways to get a great…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… me start with most popular transactional storage engine for MySQL – Innodb. Innodb Storage Engine was covered in a lot of talks, many… size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are… I do not yet know answer for – for example will LOAD DATA INFILE be optimized same way as for MyISAM tables by separate…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…/O capacity. What does this have to do with InnoDB? Data clustering. InnoDB‘s primary keys define the physical order rows are… it write out a CSV file and import that with LOAD DATA INFILE. Keep those big fat log files out of the…, then piping the results back up to the master with LOAD DATA INFILE, which kind of emulates row-based replication in a way…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… shared storage or DRBD, although those provide stronger guarantees against data loss if mysqld crashes. And you can use the standby… information_schema.innodb_buffer_pool_content group by 1, 2, 3 into outfile ‘/tmp/buffer-pool-contents.txt’; I loaded this file into a table on my laptop with LOAD DATA INFILE and kept it for later. I…
Post: Recover BLOB fields
July 1, 2010 By 4 Comments
… not supported by Percona InnoDB Recovery Tool. The reason consists in a special way InnoDB stores BLOBs. An InnoDB table is stored in…` varchar(120), `N_FIELDS` int(10), PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Here COMPACT format is used, which is… upload the table back you should utilize UNHEX function: mysql> LOAD DATA INFILE ‘/path/to/datafile’ REPLACE INTO TABLE FIELDS TERMINATED BY ‘\t…
