… thing I tried doing was loading the data with SQL statements. I wanted to see how fast MyISAM vs. MonetDB would interpret really…. I used LOAD DATA INFILE instead (MonetDB’s version of that is COPY INTO). This is the only way to get data into Infobright… plugged into MySQL. It’s a complete server with a different optimizer, etc. This point was hammered home during the LOAD DATA INFILE, when…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… to the cost of repairing huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables… it write out a CSV file and import that with LOAD DATA INFILE. Keep those big fat log files out of the… MySQL 5.0 and earlier, doing the work on a slave, then piping the results back up to the master with LOAD DATA INFILE…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
…be set for optimal load speed. Depending on load type MyISAM may benefit from bulk_insert_tree_size increase myisam_sort_buffer_size …main 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 can be…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… yet know answer for – for example will LOAD DATA INFILE be optimized same way as for MyISAM tables by separate phase of building Indexes… static record format ? (static record format for sort used by MyISAM may cause sort files to be much larger than table… patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would like to see…
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 17 Comments
… for data which generally fits in memory. Last time I worked with similar system it used MyISAM and…=8 was very important. Second thing – Partitioning. Unfortunately MySQL 5.5 leaves the huge bottleneck for write… get much lower numbers. In fact we used load data infile which is one of the ways to get…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
…an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently … in a different order of execution. The MySQL manual actually says this, but not in …old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL …myisam_data_pointer_sizeblogpercona.commanual myisam_max_sort_file_sizeblogpercona.commanual myisam_mmap_sizeblogpercona.commanual myisam_recoverblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam… slave_load_tmpdirblogpercona…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…/quick-comparison-of-myisam-infobright-and-monetdb/) this week. And following Baron’s example I also run the same load against MonetDB… to quote each field. After that load statement is: mysql -S /tmp/mysql-ib.sock -e “LOAD DATA INFILE ‘/data/d1/AirData_ontime/${YEAR}_$i.txt.tr…
Comment: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… both by LOAD DATA INFILE and INSERT INTO … SELECT if target is empty MyISAM table. In such case the speed is very close. LOAD DATA INFLE in theory needs to do a bit more job by parsing the file but in practice overhead is minimal: mysql> load data infile….61 sec) Records: 7340032 Deleted: 0 Skipped: 0 Warnings: 0 mysql> truncate table two; Query OK, 0 rows affected (0.20…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… in MyISAM table. So why was this done, being pretty bad for MySQL Performance and concurrency ? The reason is – replication. In MySQL before… to use: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO… to delete file after it is loaded back (it has to be done outside of MySQL Server) as otherwise the script will…
