Diamond Notes

First, thanks for all the input everyone. It has helped immensely. I ran more test with the suggested ideas. Some of them helped a great deal. Some, not so much, but that could be the hardware I am running the test on. The server is running 32-bit Linux, but has 4 gigs of RAM. All of the RAM is not visible to the OS. I think that is why raising the buffer on Innodb actually slowed it down.

One other note. I didn’t take the time to average the test results of each group run.
All of the following tests were run with the Innodb buffer set to one gig.

importing icengine3_2 with mysqlimport -u root -p –use-threads=2 database3 /data2/database3/*.txt

importing icengine3_2 with mysqlimport -u root -p –use-threads=4 database3 /data2/database3/*.txt

importing icengine3_2 with mysqlimport -u root -p database2 /data2/database3/*.txt

The following tests were run with innodb_flush_log_at_trx_commit = 2 in the my.cnf file.

start time	stop time	elapsed time
17:52:33	18:08:54:	16:27
18:20:15	18:36:40	16:25
18:36:50	18:53:18	16:28
18:53:32	19:09:56	16:28
19:10:06	19:26:40	16:34
19:26:51	19:43:23	16:32
19:43:34	20:00:03	16:29
20:00:13	20:16:38	16:25
20:16:48	20:33:18	16:30
20:33:29	20:50:08	16:37

importing icengine3_2 with mysqlimport -u root -p –use-threads=4 database3 /data2/database3/*.txt

The following tests were run with innodb_flush_log_at_trx_commit = 2 in the my.cnf file.

start time	stop time	elapsed time
20:54:07	21:06:00	11:53
21:06:07	21:17:57	11:50
21:18:05	21:29:57	11:52
21:30:05	21:41:52	11:47
21:42:00	21:53:59	11:59
21:54:07	22:06:02	11:55
22:06:10	22:18:02	11:52
22:18:09	22:30:06	11:57
22:30:14	22:42:08	11:54
22:42:16	22:54:03	11:47

Conclusions

Obviously the mysqlimport command (which really maps to �load data infile�) is the fastest. In its basic form it speeds up an import by around 29% average over the mysql -u root -p database < database.sql command. The innodb buffer variable change of 1 gigabyte to 2 gigabytes actually hurt the performance. I think this was because the 2 gig buffer size was simply to large for a machine with 3.75 gigs of RAM. Also, this was a 32-bit server (the server really has 4 gigs but doesn�t map it properly).

The �use-threads option in mysqlimport helped quit a bit. In fact, going from one (the default) to two threads decreased load time almost 25%. Increasing it from two threads to four threads doesn�t make much of a difference - about 30 seconds over a 1.3 gig import. One caveat, this option is only available on the mysqlimport binaries from the 5.1.x server series. I downloaded the 32-bit version and just grabbed the single binary for mysqlimport. I copied the old binary to mysqlimport.old in case there was any problems and copied over the new version. It worked as advertised.

Using the innodb_flush_log_at_trx_commit = 2 in the my.cnf options didnt’ really make any significant difference in my setup. Make sure that this is set to zero after done with the importing to make certain your data is safe.

One interesting note. Moving from 4.1 to 5.0 shrinks the size of the database. I expected this somewhat, but was supprised at how much. In the testing case it went from 1468M of data to 1315M. This is a 10.4% reduction in size just from the move. Please note that the 4.1 data which was replicated from had been imported into the server from production data. Because of the import before replication the database was at optimum size.

For our uses the optimal command would be: mysqimport -u root -p –use-threads=4 database3 /data2/database3/*.txt