… 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… different optimizer, etc. This point was hammered home during the LOAD DATA INFILE, when I looked to see what was taking so long…
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 or… 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: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements… INSERT… SELECT statements that were running against the table, selecting data from it and inserting into another table. Let’s look… use the old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead. Or you can do something…
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…, then piping the results back up to the master with LOAD DATA INFILE, which kind of emulates row-based replication in a way…
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… 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’ INTO TABLE ontime FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘” ontime The load time for…
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… parsing the file but in practice overhead is minimal: mysql> load data infile ‘/tmp/z.txt’ into table two; Query OK, 7340032 rows…
Comment: Why MySQL could be slow with large tables ?
August 14, 2006 By 141 Comments
… TABLE normally rebuilds indexes by sort, so does LOAD DATA INFILE (Assuming we’re speaking about MyISAM table) so such difference is quite unexpected… in your tests, this would explain it. ALTER TABLE and LOAD DATA INFILE should nowever look on the same settings to decide which…
Comment: Quick comparison of MyISAM, Infobright, and MonetDB
September 30, 2009 By 30 Comments
… by those LOAD DATA INFILE results. I didn’t catch how many rows you ended up loading, but from the size of the MyISAM table…. I wouldn’t have expected it to be slower than MyISAM, though. On the other hand, if that result came from… easy typical case is incremental time-series data where the timestamp essentially automatically partitions the data set. EXPLAIN out of Infobright is…
Comment: Quick comparison of MyISAM, Infobright, and MonetDB
September 30, 2009 By 30 Comments
… LOAD DATA INFILE. It is a million rows from a flat comma-delimited file, the same file I loaded into MyISAM and into MonetDB. The data‘s random distribution surely works against the “knowledge grid” and “data pack” layout… results merits more investigation. My superficial knowledge of the Infobright data layout led me to believe the SUM(), MIN() and MAX…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… read to be Innodb – even if writes are done in MyISAM table. So why was this done, being pretty bad for… to use: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO… is still going. In this case write is done to MyISAM table so we’ll not see any write activity. Other…
