>>>>> "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
