MySQL Forums
Forum List  »  MyISAM

INSERT performance on MyISAM (Record Size vs. Field Count)
Posted by: Gary Slovikosky
Date: September 03, 2006 07:29PM

Hi all,

(I have also posted this message under PERFORMANCE and I sincerely hope that this second posting is not inappropriate!)

I would like to know if someone could tell me how to optimize either the client or server to improve INSERT INTO statements. (btw... It must be done using single inserts)

I have benchmarked the following performance under the following conditions.

I have a 5.0.15 MySQL database (local machine) and I am inserting records into a single MyISAM table. The development environment is Visual Studio 2003 Managed C++.NET and 1.07 .NET connector. 2 Gigs of RAM on the machine. The table starts as empty. To insert the data I am createing an SQL Command and assign the data to parameters for each field. Then finally run an ExecuteNonQuery to insert a single record. Then I loop and do the next record.

The table has about 250 fields with a mix of 64 bit Doubles, 32 bit Integers, 16 bit Integers, byte fields and a few small strings. Total record size is about 600 bytes for the record. There is only one Primary Key field and no other indexes.

I am running about 60 inserts per second with this record. To find out why things were running slow I tried the following combinations to benchmark the INSERT speed.

I created a new table with only 100 fields but each field is a 64 bit Double. Total record size was then 808 bytes with the Primary Key. This ran at about 200 inserts per second. So less fields but more data.

Then I changed the table to 200 fields of 64 bit Doubles, Total record size was 1608 bytes with PK and the insert dropped to about 70 inserts per second.

I then changed the 200 fields to 32 bit Integers (INTEGER), total record size was 808 bytes with PK and the insert ran at about 71 inserts per second.

I then changed the 200 fields to 16 bit Integers (SMALLINT(5)), total record size was 408 bytes with PK and the insert ran at about 71 inserts per second.

I then changed the 200 fields to 8 bit Bytes (CHAR(1)), total record size was 208 bytes with PK and the insert ran at about 71 inserts per second.

Next test was changed back to 100 fields of 8 bit Bytes (CHAR(1)), total record size was 108 bytes with PK and the insert ran at about 260 inserts per second.

Last test was changed back to 50 fields of 8 bit Bytes (CHAR(1)), total record size was 58 bytes with PK and the insert ran at about 900 inserts per second.


It seems that the actual record size in bytes is not driving the insert speed but primarily the number of fields. How can I optimize the database for faster performance up to 300 fields? Any help is appreciated.

Regards,

Gary Slovikosky

Options: ReplyQuote


Subject
Views
Written By
Posted
INSERT performance on MyISAM (Record Size vs. Field Count)
4878
September 03, 2006 07:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.