Greetings!
We're having a [reproducible] problem where a query suddenly gets very
slow. The table has a primary key and another key. Any operation that
affects the index, such as insert or update, slows down subsequent queries
that use the index.
Any of the following "fixes" the problem and makes subsequent
queries fast again -- until the next insert/update:
- mysqladmin flush-tables
- alter table formLog # with no arguments
- alter table formLog drop index client
The problem exists with version 3.23.49 both on Sparc (Solaris 7)
and FreeBSD (4.5) using the binaries downloaded from www.MySQL.com,
but it does not exist with version 3.22.19 on Sparc/Solaris 7.
In all the tested configurations above, the machines were not under
any significant load, have at least 1GB of RAM, and were running with
the default MySQL settings, with few exceptions. Here is /etc/my.cnf:
[mysqld]
skip-innodb
datadir=/mysql
user=mysql
Of note, the datadir's are on an NFS mounted filesystem served by a
Network Appliance filer. Each DB server mounts as /mysql it's own
DB area, so there is no conflict/sharing.
Here's the table:
CREATE TABLE formLog (
formLogId int(10) unsigned NOT NULL auto_increment,
client varchar(4) DEFAULT '' NOT NULL,
camp varchar(4) DEFAULT '' NOT NULL,
fid varchar(30) DEFAULT '' NOT NULL,
first int(32) DEFAULT '0' NOT NULL,
PRIMARY KEY (formLogId),
KEY (client)
);
Here is some sample data, further below is a link to the full test
dataset (only 615K) we have been using.
INSERT INTO formLog VALUES (6498,'19','0000','2170702290441014768535',1);
INSERT INTO formLog VALUES (6497,'0w','0000','2021560021341014766815',1);
INSERT INTO formLog VALUES (6494,'0w','0000','1321770882121014766556',1);
INSERT INTO formLog VALUES (6493,'0w','0000','1321770882121014766539',1);
INSERT INTO formLog VALUES (6492,'0w','0000','1321770882121014766473',1);
query:
SELECT sum(first) FROM formLog WHERE client='19'
To Reproduce:
------------
Download:
http://www.tradepub.com/mysql/test.db (615K bytes)
http://www.tradepub.com/mysql/test_bug.csh (880 bytes)
Modify the user, host, password, and database name in test_bug.csh as
appropriate and run. It's a simple c-shell script.
Any help in resolving this issue would be greatly appreciated.
--
do svidaniya,
~mitch
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
