MySQL Bugs: #27271: Repeating a query slows it down

Bug #27271	Repeating a query slows it down
Submitted:	19 Mar 2007 18:38	Modified:	25 Mar 2007 13:23
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.32-Debian_7-log	OS:	Linux (Debian Etch 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	repeat, slow down

Description:
Hi,
i'm experiencing so strange things on my new installed mysql-server-machine: its an opteron machine with 4GB ram and 2 SATA harddrives. Its running an up-to-date 64bit debian etch installation with software raid1.

Now heres the problem:
I have one table:
CREATE TABLE `test2` (
`id` int(11) NOT NULL auto_increment,
`who` int(11) NOT NULL,
`whom` int(11) NOT NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`hide` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `who` (`who`,`timestamp`)
) ENGINE=MyISAM AUTO_INCREMENT=12291 DEFAULT CHARSET=utf8

When execution this query something very strange (for me) happends:
SELECT * FROM test2 WHERE whom=14 AND `timestamp`>'2007-02-26 03:00';

When executed for the first time:
67 rows in set (0.50 sec)
Okay, everything fine!

Now the same query repeated, nothing else is done on the server:
67 rows in set (0.79 sec)
67 rows in set (1.34 sec)
67 rows in set (2.25 sec)
67 rows in set (3.78 sec)
67 rows in set (6.35 sec)
67 rows in set (10.67 sec)
67 rows in set (17.91 sec)
67 rows in set (30.06 sec)
67 rows in set (50.49 sec)
67 rows in set (1 min 24.76 sec)
67 rows in set (2 min 0.01 sec)

SELECT * FROM test2 WHERE whom=14; and SELECT * FROM test2 WHERE `timestamp`>'2007-02-26 03:00';
work fine, no slowing down.

SELECT * FROM test2 WHERE who=14 AND `timestamp`>'2007-02-26 03:00';
Also shows the slow-down-behavior.
When execution some of the "whom=14 AND `timestamp`>'2007-02-26 03:00'"-querys first and executing a "who=14 AND `timestamp`>'2007-02-26 03:00'" query then the first "who"-query is also slow.

Turning on and off the query cache doesn't change anything, but there seems to be no slowing down, when i connect to the server over socket instead of the network connection. It also works when i disconnect and reconnect after each query-execution.
Both, mysql-commandline-interface and PHP-Connections, show the same behavior.

How to repeat:
Execute SELECT * FROM test2 WHERE whom=14; and SELECT * FROM test2 WHERE `timestamp`>'2007-02-26 03:00'; over a network connection.

Suggested fix:
None

Table-Dump

Attachment: test2.zip (application/x-zip-compressed, text), 111.23 KiB.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.3/5.0.37, and inform about the results.

I tested again: deactivating the raid did NOT help. I also tried a 32bit-Debian installation, but it didn't help.
Only one thing changed now: Only every second query gets slow now:
    * Query 0: 0.04s
    * Query 1: 2.46s
    * Query 2: 0.04s
    * Query 3: 4.46s
    * Query 4: 0.04s
    * Query 5: 8.15s
    * Query 6: 0.04s
    * Query 7: 14.90s
    * Query 8: 0.04s
    * Query 9: 27.21s

Looks like i have to put some more effort into installing 5.0.37

Yes, please, try to repeat with 5.0.37 and inform about the results. In case of the same problem send also your my.cnf file content.

Changing from cross-over to switched connection solved this issue - no bug, sorry.