MySQL Bugs: #712: slow indexes in 4.0.13 vs. 3.23

Bug #712	slow indexes in 4.0.13 vs. 3.23
Submitted:	24 Jun 2003 10:33	Modified:	27 Aug 2003 4:43
Reporter:	Sergei Golubchik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
From: Paul Coldrey <paul@ensigma.com.au>
To: "bugs@lists.mysql.com" <bugs@lists.mysql.com>
Subject: Indexes in 4.0.13
Date: Mon, 23 Jun 2003 13:49:12 +0930

I have two machines, one runs RedHat 9.0 and MySQL 4.0.13 and the other
runs RedHat 7.3 and MySQL 3.23.49. The hardware is identical (they are
primary and backup servers for an intranet site and we are currently
upgrading the software - one at a time).

I have noticed that scripts that execute quickly on the 3.23 machine
take a long time on the 4.0 machine and it appears to be tied to indexes.

I have the same table on both machines and it has three INT(11) values
in each row, none are primary keys but they are all indexed.

Here is the description of the table from desc.....

+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| PeopleId   | int(11) | YES  | MUL | NULL    |       |
| MenuId     | int(11) | YES  | MUL | NULL    |       |
| SequenceId | int(11) | YES  | MUL | NULL    |       |
+------------+---------+------+-----+---------+-------+

When I create and index on a table containing 176,000 rows it takes 6.0
seconds on the 4.0 server but for the same index on the 3.23 server it
takes 2.2 seconds.

Both servers have MyISAM tables (and just to be sure I dumped the table
from the 3.23 server and loaded it onto the 4.0 machine to make sure
that data was the same). I created many different indexes on both
machinie and the effect was evident in all cases.

Cheers,

   Paul

How to repeat:
create a table (e.g. create table t (a int)), populate it, run alter table t add inxed (a);

Note that this problem also appears to slow down all inserts on indexed tables.

Plus, it appears the table does not be allowing selects to occur while the insert is in progress.

This bug also appears in MySQL 4.1.0-alpha. Further to previous comments, the table is definitely not responding to selects during an insert. The rest of the database is still responsive (ie I can select on other tables within the same database).

Reason of the bug is found.
changeset 1.1517

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

A couple of last comments to this thread.

It's normal with MyISAM tables that inserts blocks selects.
If you have long running selects or massive multi-line inserts it's better
to move to innodb tables.

Note that a lot of speed issues with both MyISAM tables and InnoDB tables can
be solved by setting up the caches to use a significant amount of the available memory.  The default memory setup used by MySQL is to not take up too much memory as there is probably other applications running on the computer.

Especailly when using innodb tables, it's important to set up the innodb_buffer_pool_size to get decent performance with big tables under heavy load.

Take a look at distributed my-medium.cnf or my-large.cnf files for some ideas of  how to set up buffering.

A note about the bug fix; In MySQL 4.0.13 we didn't use caches properly for tables that disk based temporary tables.  Please try 4.0.15 when it comes out (should be out VERY soon).

Another thing that would be nice to know how the MySQL 4.0 server works on the RedHat 7.3 machine.  The bug report #914 seams to indicate that this could be an  OS issue and not a MySQL issue.

We will continue to work on the #914 bug from here and hope that it also solves this one.