GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

log slow queries config problems.

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

  • log slow queries config problems.

    Hi Folks,

    I've created a log.cnf in /etc/mysql/conf.d which contains the following...

    [mysqld]
    log_slow_queries = /var/log/mysql/mysql-slow.log
    slow_query_log = 1
    long_query_time = 2
    log-queries-not-using-indexes

    but even though long_query_time is set to 2 ( seconds according to the docs ), I get comments such as...


    # Time: 120821 16:57:21
    # User@Host: dummy[dummy] @ localhost []
    # Thread_id: 267874 Schema: dummy Last_errno: 0 Killed: 0
    # Query_time: 0.000259 Lock_time: 0.000088 Rows_sent: 0 Rows_examined: 1 Rows_affected: 0 Rows_read: 0
    # Bytes_sent: 87 Tmp_tables: 1 Tmp_disk_tables: 0 tmp_table_sizes: 0
    # InnoDB_trx_id: 4456105

    mysql> explain SELECT `main_table`.`poll_id` FROM `poll` AS `main_table`
    -> INNER JOIN `poll_store` AS `store` ON main_table.poll_id=store.poll_id AND store.store_id = '1' WHERE (closed = 0) ORDER BY RAND() LIMIT 1;
    +----+-------------+------------+--------+------------------ -----------+---------+---------+---------------------------- ----+------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+------------------ -----------+---------+---------+---------------------------- ----+------+----------------------------------------------+
    | 1 | SIMPLE | main_table | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort |
    | 1 | SIMPLE | store | eq_ref | PRIMARY,FK_POLL_STORE_STORE | PRIMARY | 6 | dummy.main_table.poll_id,const | 1 | Using index |
    +----+-------------+------------+--------+------------------ -----------+---------+---------+---------------------------- ----+------+----------------------------------------------+
    2 rows in set (0.00 sec)


    which is clearly much faster than the threshold, and the explain plan shows it using indexes. Can anyone suggest what is wrong with my config? ( is the order by rand() causing this? ).

    # cat /etc/debian_version
    6.0.5

    # uname -a
    Linux dummy 2.6.32-5-amd64 #1 SMP Sun May 6 04:00:17 UTC 2012 x86_64 GNU/Linux

    # dpkg -l | grep percona
    ii percona-server-client 5.5.25a-rel27.1-277.squeeze Percona Server database client
    ii percona-server-client-5.5 5.5.25a-rel27.1-277.squeeze Percona Server database client binaries
    ii percona-server-common-5.5 5.5.25a-rel27.1-277.squeeze Percona Server database common files (e.g. /etc/mysql/my.cnf)
    ii percona-server-server-5.5 5.5.25a-rel27.1-277.squeeze Percona Server database server binaries
    ii percona-toolkit 2.1.3-1~dotdeb.0 Command-line tools for MySQL and system tasks
    ii percona-xtrabackup 2.0.2-461.squeeze Open source backup tool for InnoDB and XtraDB


    Cheers,

    Steve

  • #2
    Your first table isn't using any indexes. It is a full table scan (ALL). It only has one row, which isn't bad, but an index isn't used (even though PRIMARY could be used) so it is logged...

    Comment

    Working...
    X