… about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting it into the binary… 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: Predicting how long data load would take
May 24, 2007 By 14 Comments
…binary form. MySQL Configuration Different storage engines have different settings which need to be set for optimal load speed. Depending on load… to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can …
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
…. The statements have to be serialized for the binary log; otherwise replaying the binary log can result in a different order of… clearest way. It just says If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE… use the old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something…
Post: read_buffer_size can break your replication
June 6, 2012 By 12 Comments
… the replication I’m going to load the 4 million rows with LOAD DATA INFILE: MasterA (test) > LOAD DATA INFILE ‘/tmp/data‘ INTO TABLE t; Query OK, 4510080… to check the binary logs of both masters. MasterA: masterA> mysqlbinlog data/mysql-bin.000004 | grep block_len #Begin_load_query: file_id…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
May 9, 2012 By 29 Comments
….5 (vanilla version). “The big LOAD DATA INFILE clogs the binary log and slows replication down. If the load takes 4 hours on the master… master LOAD DATA INFILE ‘/tmp/data.out’ INTO TABLE some_data_table; Using this strategy, replication continues to happen without a hitch and the LOAD DATA can…
Post: Filling the tmp partition with persistent connections
August 6, 2012 By 14 Comments
… just recycle old connections. The application starts to load data into the database with “LOAD DATA INFILE“. After some minutes the server runs out of… big UPDATE, INSERT or LOAD DATA INFILE MySQL send the rows to the Binary Log Cache before writing it to the Binary Log Files. If… space. This can be a bad practice because if you LOAD DATA for a file bigger than available memory, you could accidentally…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
….SYS_TABLES -4Df pages-1345219305/FIL_PAGE_INDEX/0-1 LOAD DATA INFILE ‘/root/src/recovery-tool/s_tools/dumps/default/SYS_INDEXES…-1345219305/FIL_PAGE_INDEX/0-3| grep 15 | grep PRIMARY LOAD DATA INFILE ‘/root/src/recovery-tool/s_tools/dumps/default/SYS_INDEXES… the pages: # grep -r “PENELOPEGUINESS” pages-1345220873/FIL_PAGE_INDEX/ Binary file pages-1345220873/FIL_PAGE_INDEX/0-18/9733-00009792…
Post: CVE-2012-4414 strikes back in MySQL 5.5.29 (and what we're doing in Percona Server 5.5.29)
January 13, 2013 By 4 Comments
….5.29 (the Oracle provided binaries); it also failed. Basically, the following snippet from rpl_mdev382: eval LOAD DATA INFILE ‘$MYSQLTEST_VARDIR/tmp/f… query. Default database: ‘db1`; SELECT ‘oops!”. Query: ‘LOAD DATA INFILE ‘../../tmp/SQL_LOAD-2-1-1.data‘ INTO TABLE `t“1` FIELDS TERMINATED BY ‘,’ ENCLOSED…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
May 9, 2012 By 29 Comments
…, The LOAD DATA INFILE command isn’t replicated verbatim. The file that’s loaded on the master is actually inlined into the binary log, and the replica writes out a copy of the file into the directory indicated by slave_load_tmpdir. Then the LOAD DATA INFILE…
Post: Announcing Percona Server 5.5.25a-27.1
July 21, 2012 By Leave a Comment
…. When used with no argument, the LOAD_FILE() function will always return NULL. The LOAD DATA INFILE and SELECT INTO OUTFILE statements will fail… authentication plugin was in different directories in 32bit and 64bit binary tarballs. Bug fixed #1007271 (Ignacio Nin). Querying I_S.GLOBAL…
