Announcement

Announcement Module
Collapse
No announcement yet.

Query slows down, when repeated

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query slows down, when repeated

    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

  • #2
    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.

    Comment


    • #3
      sterin wrote on Mon, 19 March 2007 14:21

      1.
      Use EXPLAIN SELECT [your query here].
      That will give you a hint about what mysql is doing to solve your query.


      +----+-------------+--------+------+---------------+------+- --------+------+-------+-------------+
      | 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)


      sterin wrote on Mon, 19 March 2007 14:21

      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.


      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.

      sterin wrote on Mon, 19 March 2007 14:21

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


      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.

      sterin wrote on Mon, 19 March 2007 14:21

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


      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?)

      sterin wrote on Mon, 19 March 2007 14:21

      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.



      Thats right, this will be changed.

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment

          Working...
          X