List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 8 1999 6:28pm
Subject:Inserts on reasnably large tables get slower...
View as plain text  
>>>>> "Karl" == Karl Pielorz <kpielorz@stripped> writes:

Karl> Hi All,
Karl> I have a MySQL database, and a seperate C program that imports records into
Karl> that database... At the start of the import the system imports around 1000
Karl> records per seconds, but as time goes on - the insert speed slows down
Karl> (typically down to around 200/sec - after importing around 300,000 records)...

Karl> I've removed all the Unique indexes in the system, and all but 1 table follow
Karl> a simple:

Karl> CREATE TABLE test (
Karl> test_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
Karl> test_name CHAR(255) NOT NULL,
Karl> PRIMARY KEY (test_id),
Karl> INDEX idx_test_name (test_name)
Karl> );

Karl> (ditto for 'sibling', 'path', 'other', 'parent' etc.)

Karl> There is one other 'main' table which is simply,

Karl> CREATE TABLE main_table (
Karl> record_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
Karl> sibling_id MEDIUMINT UNSIGNED NOT NULL,
Karl> parent_id MEDIUMINT UNSIGNED NOT NULL,
Karl> path_id MEDIUMINT UNSIGNED NOT NULL,
Karl> other_id MEDIUMINT UNSIGNED NOT NULL,
Karl> PRIMARY KEY (record_id),
Karl> INDEX idx_sibling_id (sibling_id),
Karl> INDEX idx_parent_id (parent_id),
Karl> INDEX idx_path_id (path_id),
Karl> INDEX idx_other_id (other_id)
Karl> );

Karl> Each of the 'test' type tables have around 40,000 records in (fully
Karl> populated), and the main table has around 1.2 million records in (fully
Karl> populated).

Karl> It seems as the tables get more populate the speed slows way down. We are
Karl> currently running MySQL 3.22.22 (compiled from the FreeBSD port, using native
Karl> threads under FreeBSD 4.0-Current).

Karl> Is there anything I can do to stop this apparent slowdown?

Karl> Also, are 'mediumints' only going to save space, rather than time in the
Karl> database? (i.e. 24 bit compares/work being more CPU costly than 32 bit/native
Karl> compares/work?)

Karl> The machine running this is a P2-450, with 512Mb of RAM. MySQL and the import
Karl> program aren't memory starved, and the machine doesn't swap... :)

Hi!

Each additional index will create a lot more seeks on the disk when
inserting rows.  You can avoid some if these by increasing your key
buffer (-O key_buffer=64M) or/and inserting many rows at the same time with
multi-row-inserts.

Regards,
Monty
Thread
Inserts on reasnably large tables get slower...Karl Pielorz8 Jun
  • Inserts on reasnably large tables get slower...Michael Widenius8 Jun
  • Re: Inserts on reasnably large tables get slower...Christian Mack8 Jun