Not the answer you need?
Register and ask your own question!

Query slows down, when repeated

NitekNitek EntrantInactive User Role Participant
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

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    1.
    Use EXPLAIN SELECT [your query here].
    That will give you a hint about what mysql is doing to solve your query.

    2.
    Why do you not have an index on (whom, timestamp) since that is what you are serching on?
    Add it and you shall see that your query is speeding up.

    3.
    Is there any risk that you are running out of memory on that server?

    4.
    Are you freeing the result set after each query?

    5.
    Please don't use 'timestamp' as the column name.
    Although you can use backticks to quote them, Reserved words are reserved for a reason.
  • NitekNitek Entrant Inactive User Role Participant
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">sterin wrote on Mon, 19 March 2007 14:21</td></tr><tr><td class="quote">
    1.
    Use EXPLAIN SELECT [your query here].
    That will give you a hint about what mysql is doing to solve your query.
    </td></tr></table>

    +----+
    +
    +
    +
    +
    +-
    +
    +
    +
    +
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+
    +
    +
    +
    +
    +-
    +
    +
    +
    +
    | 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 10822 | Using where |
    +----+
    +
    +
    +
    +
    +-
    +
    +
    +
    +

    I can't see anything unusal here (except that there is no key, but i don't think thats the point)


    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">sterin wrote on Mon, 19 March 2007 14:21</td></tr><tr><td class="quote">
    2.
    Why do you not have an index on (whom, timestamp) since that is what you are serching on?
    Add it and you shall see that your query is speeding up.
    </td></tr></table>

    There is no key yet, but it doesn't make a difference: The same query using "who" instead auf "whom" in the where-clause has the same problem: it keeps slowing down.

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">sterin wrote on Mon, 19 March 2007 14:21</td></tr><tr><td class="quote">
    3.
    Is there any risk that you are running out of memory on that server?
    </td></tr></table>

    No way. Its a complete fresh installation, there is only the mysql-server runing and no traffic except of my test-queries. Nothing that could use 4GB Ram.
    The query also works fine on the old server, which has only 1GB RAM and the webserver running, so i suppose its neither the query itself nor the table structure which is responsible for that.

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">sterin wrote on Mon, 19 March 2007 14:21</td></tr><tr><td class="quote">
    4.
    Are you freeing the result set after each query?
    </td></tr></table>

    I'm just executing the query in the command-line interface, i don't think there is a need to free the result (how would i do that in the command-line interface?)

    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">sterin wrote on Mon, 19 March 2007 14:21</td></tr><tr><td class="quote">
    5.
    Please don't use 'timestamp' as the column name.
    Although you can use backticks to quote them, Reserved words are reserved for a reason.

    </td></tr></table>

    Thats right, this will be changed.
  • tanjtanj Contributor Inactive User Role Advisor
    What is your MySQL version?

    On the other hand, this behavior has to be related to server code performance issue, so maybe you should issue a bug on MySQL community bugtracker. The dev guys should be able to give you a more accurate answer.
  • NitekNitek Entrant Inactive User Role Participant
    Its Version: '5.0.32-Debian_7-log'

    I just wanted to make sure i did nothing stupid before bothering the developers, but may be i should give it a try.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.