List:General Discussion« Previous MessageNext Message »
From:Karl Pielorz Date:June 8 1999 1:27pm
Subject:Inserts on reasnably large tables get slower...
View as plain text  
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
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