List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 8 1999 7:09pm
Subject:Re: Inserts on reasnably large tables get slower...
View as plain text  
Karl Pielorz wrote:
> 
> Hi All,
> 
> I have a MySQL database, and a seperate C program that imports records into
> that database... At the start of the import the system imports around 1000
> records per seconds, but as time goes on - the insert speed slows down
> (typically down to around 200/sec - after importing around 300,000 records)...
> 
> I've removed all the Unique indexes in the system, and all but 1 table follow
> a simple:
> 
> CREATE TABLE test (
>         test_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
>         test_name CHAR(255) NOT NULL,
>         PRIMARY KEY (test_id),
>         INDEX idx_test_name (test_name)
> );
> 
> (ditto for 'sibling', 'path', 'other', 'parent' etc.)
> 
> There is one other 'main' table which is simply,
> 
> CREATE TABLE main_table (
>         record_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
>         sibling_id MEDIUMINT UNSIGNED NOT NULL,
>         parent_id MEDIUMINT UNSIGNED NOT NULL,
>         path_id MEDIUMINT UNSIGNED NOT NULL,
>         other_id MEDIUMINT UNSIGNED NOT NULL,
>         PRIMARY KEY (record_id),
>         INDEX idx_sibling_id (sibling_id),
>         INDEX idx_parent_id (parent_id),
>         INDEX idx_path_id (path_id),
>         INDEX idx_other_id (other_id)
> );
> 
> Each of the 'test' type tables have around 40,000 records in (fully
> populated), and the main table has around 1.2 million records in (fully
> populated).
> 
> It seems as the tables get more populate the speed slows way down. We are
> currently running MySQL 3.22.22 (compiled from the FreeBSD port, using native
> threads under FreeBSD 4.0-Current).
> 
> Is there anything I can do to stop this apparent slowdown?
> 
> Also, are 'mediumints' only going to save space, rather than time in the
> database? (i.e. 24 bit compares/work being more CPU costly than 32 bit/native
> compares/work?)
> 
> The machine running this is a P2-450, with 512Mb of RAM. MySQL and the import
> program aren't memory starved, and the machine doesn't swap... :)
> 
> Regards,
> 
> Karl Pielorz

Hi Karl

To speed up inserts you should drop all INDEX's and KEY's before starting the import.
After you are done, add each Index again.

MEDIUMINT is faster loaded from/saved to disk. 
I don't think you will gain something on processing by the CPU.

Tschau
Christian

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