… 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 master… 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: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… I’m not using replication and have my binary log disabled ? If replication is not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on… you’re using replication ? The most general one is to use: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… most popular transactional storage engine for MySQL – Innodb. Innodb Storage Engine was covered in a lot of… yet know answer for – for example will LOAD DATA INFILE be optimized same way as for MyISAM … of others due to statement level MySQL Replication which among other requirements, needs all auto-…
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… results back up to the master with LOAD DATA INFILE, which kind of emulates row-based replication in a way. When you’re updating…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… availability is master-master replication, which is just like normal master-slave replication except that you …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…
Post: Announcing Percona Server 5.5.25a-27.1
July 21, 2012 By Leave a Comment
… no argument, the LOAD_FILE() function will always return NULL. The LOAD DATA INFILE and SELECT INTO … crash if userstats were enabled with any replication configured. This was a regression introduced … Server started with skip-innodb crashes on SELECT * FROM INNODB_TABLE_STATS or INNODB_INDEX_STATS. …
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… INSERT… SELECT statements that were running against the table, selecting data from it and inserting into another table. Let’s look… for replication.) There are workarounds, though. You can use the old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_data_pending_fsyncsblogpercona.commanual Innodb_data_pending_readsblogpercona.commanual Innodb_data_pending_writesblogpercona.commanual Innodb_data_readblogpercona.commanual Innodb_data_readsblogpercona.commanual Innodb_data_writesblogpercona.commanual Innodb_data…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…loaded with the data used in the previous Shard-Query blog post. The data…innodb-read-ahead-threshold=8 innodb-read-io-threads=16 innodb-recovery-stats innodb-recovery-update-relay-log innodb-replication-delay=# innodb-rollback-on-timeout innodb…
Comment: MySQL Partitioning - can save you or kill you
July 2, 2012 By 45 Comments
… the two nodes mentioned above b) InnoDb/MyISAM on the replicated DRBD partition on these two nodes. (…does the job of only providing some static data from a bunch of saved files on disk…number of records into the new InnoDb table as mentioned before, using load infile. Query result ( during inserts …
