MySQL Forums
Forum List  »  MyISAM

What make MyISAM insert operation of MSQL 5.0.19 10 times slower than 5.0.15?
Posted by: Martin Feng
Date: March 28, 2006 05:26AM

I was using MySQL 5.0.15 installed from MySQL-server-5.0.15-0.i386.rpm and MySQL-Max-5.0.15-0.i386.rpm. (Linux, CentOS 4.3). Ever since I upgraded it to MySQL-Max-5.0.19-0.i386.rpm and MySQL-server-5.0.19-0.i386.rpm, insert operation becomes about 10 times slower than using 5.0.15. I tried with 5.0.18, it's as slow as 5.0.19.

What makes me think it's insert that is slow is because I watched the join and query operation seems become faster when upgraded from 5.0.15 to 5.0.19, and in some operations that mainly insert involved, it becomes very slow. About 10 times slower.

I tried on two other Linux boxes running Fedora Core 3, it's same much slower.

Detailed information:

-- MyISAM tables, insert operation on both fixed length row and dynamic length row are slow.

-- I have only a single client connecting to MySQL server running on the same machine. The client connects to the server using mysql-connector-java-3.1.12.tar.gz. No other clients.

-- All insert operations are in store procedures...actually in some cases the client only call a store procedure once and the store procedure just run all the insert operations.

-- There is no much data transfer from client to server; the store procedures retrieve data from one table then insert to another table.

-- Since I need the LAST_INSERT_ID because of the algorithm, I cannot use INSERT...SELECT syntax. I insert one row by each insert statement.

-- I set key_buffer_size=512M (total RAM 2G), no obvious change. The machine is mainly run MySQL server.

-- I tried LOCK TABLES, no obvious change.

-- the insert is slow ever since the tables grow from empty.

-- Here are some of the tables of which rows are inserted: they are those with fixed row length.


CREATE TABLE TBL_SHINGLES
(
COL_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
COL_SHINGLEHASH CHAR(32) NOT NULL,
COL_SHINGLEREFCOUNT INT NOT NULL DEFAULT 1,
PRIMARY KEY (COL_ID),
UNIQUE INDEX IDX_SHINGLEHASH (COL_SHINGLEHASH)
) ENGINE = MyISAM,
DELAY_KEY_WRITE = 1;


CREATE TABLE TBL_DOCS
(
COL_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
COL_DOCHASH CHAR(32) NOT NULL,
COL_CATID INT UNSIGNED NOT NULL,
COL_TEXTLENGTH INT UNSIGNED NOT NULL,
PRIMARY KEY (COL_ID),
UNIQUE INDEX IDX_DOCHASHCAT (COL_DOCHASH, COL_CATID)
) ENGINE = MyISAM,
DELAY_KEY_WRITE = 1;


CREATE TABLE TBL_SHINGLEDOCS
(
COL_SHINGLEID INT UNSIGNED NOT NULL,
COL_DOCID INT UNSIGNED NOT NULL,
PRIMARY KEY (COL_SHINGLEID, COL_DOCID),
INDEX IDX_DOCID (COL_DOCID)
) ENGINE = MyISAM,
DELAY_KEY_WRITE = 1;




Does anyone run into similar problems ?

Thanks in advance.



Edited 2 time(s). Last edit at 03/28/2006 06:38AM by Martin Feng.

Options: ReplyQuote




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.