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.
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.
Serversettings are:
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 16M
myisam_sort_buffer_size = 64M
max_connections = 1000
read_buffer_size = 4M
read_rnd_buffer_size = 16M
thread_cache = 64
query_cache_size = 32M
tmp_table_size = 128M
thread_stack = 128K
wait_timeout = 120
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
I have no clue whats wrong here. Any hints?
Thx
Claus