Announcement

Announcement Module
Collapse
No announcement yet.

Queries are super slow

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

  • Queries are super slow

    Hey guys, I have a little problem.

    I have noticed that my queries are getting slower.
    Some update queries take about 48-50 seconds.

    Here's how it looks:



    Code:
        [root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log
        # Time: 131113 17:09:48
        # User@Host: J[J] @  [10.xxx.xxx.35]
        # Query_time: 61.013294  Lock_time: 0.000409 Rows_sent: 0  Rows_examined: 0
        SET timestamp=1384380588;
        UPDATE b SET nf=0, lt=0, ud=1384380501, a='Trademarks' WHERE pid=36019263 AND l=3780235;
        # Time: 131113 17:09:49
        # User@Host: J[J] @  [10.xxx.xxx.35]
        # Query_time: 18.145112  Lock_time: 0.000043 Rows_sent: 0  Rows_examined: 0
        SET timestamp=1384380589;
        SELECT id FROM b WHERE pid=62921670 AND l=4808881;
        [root@db-1 ~]#
    Here are the tables that are working together:

    Code:
       mysql> explain b;
        
        +-------------+--------------+------+-----+---------+----------------+
        | Field       | Type         | Null | Key | Default | Extra          |
        +-------------+--------------+------+-----+---------+----------------+
        | id          | int(30)      | NO   | PRI | NULL    | auto_increment |
        | l           | int(30)      | NO   | MUL | NULL    |                |
        | pid         | int(30)      | NO   | MUL | NULL    |                |
        | nf          | int(1)       | NO   |     | NULL    |                |
        | lt          | int(1)       | NO   |     | NULL    |                |
        | fd          | int(11)      | NO   |     | NULL    |                |
        | ud          | int(11)      | NO   |     | NULL    |                |
        | le          | int(1)       | NO   | MUL | NULL    |                |
        | a           | varchar(150) | NO   |     | NULL    |                |
        +-------------+--------------+------+-----+---------+----------------+
        9 rows in set (0.00 sec)
        
        mysql> explain l;
        +------------+--------------+------+-----+---------+----------------+
        | Field      | Type         | Null | Key | Default | Extra          |
        +------------+--------------+------+-----+---------+----------------+
        | id         | int(30)      | NO   | PRI | NULL    | auto_increment |
        | u          | varchar(255) | NO   | UNI | NULL    |                |
        | ld         | int(11)      | YES  | MUL | NULL    |                |
        +------------+--------------+------+-----+---------+----------------+
        3 rows in set (0.00 sec)
    Here another explain:

    Code:
        mysql> EXPLAIN SELECT id FROM b WHERE pid=8857932 AND l=3971963;
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+-----------------------------------------------------------+
        | id | select_type | table     | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                                     |
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+-----------------------------------------------------------+
        |  1 | SIMPLE      | b         | index_merge | pid,l          | pid,l          | 4,4     | NULL |    1 | Using intersect(pid,l); Using where; Using index          |
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+-----------------------------------------------------------+
        1 row in set (0.00 sec)
    
        mysql> EXPLAIN SELECT nf=0, lt=0, ud=1384380501, a='Trademarks' FROM b WHERE pid=8857932 AND l=3971963;
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
        | id | select_type | table     | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                        |
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
        |  1 | SIMPLE      | b         | index_merge | pid,l          | pid,l          | 4,4     | NULL |    1 | Using intersect(pid,l); Using where          |
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
    
    
        mysql> EXPLAIN UPDATE b SET nf=0, lt=0, ud=1384474869, a='Order Status' WHERE pid=8858794 AND l=4997886;
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
        | id | select_type | table     | type        | possible_keys  | key            | key_len | ref  | rows | Extra                                        |
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
        |  1 | SIMPLE      | b         | index_merge | pid,l          | pid,l          | 4,4     | NULL |    1 | Using intersect(pid,l); Using where          |
        +----+-------------+-----------+-------------+----------------+----------------+---------+------+------+----------------------------------------------+
        1 row in set (0.08 sec)
    Here's my config file:

    http://pastebin.com/HQFpxHSA

    My server has:

    2 x Intel E5506 2.1 Ghz
    24GB RAM

    It would be wonderful if you guys could help me out. Thank you

  • #2
    Hi,

    As per your explain plan of those queries, it looks like your queries are using proper indexes. Still if you want to check where exactly it's taking time, I would suggest to use profiling. Please check below links for how to use it.
    http://dev.mysql.com/doc/refman/5.5/...w-profile.html
    http://www.mysqlperformanceblog.com/...-real-profile/

    Comment


    • #3
      Jaan,

      Could you share with us the SHOW VARIABLES and SHOW STATUS of this MySQL instance in which you are running the query?

      Cheers, WB

      Comment


      • #4
        Do you have a RAID controller? Didn't it fall into battery training cycle? Write-back cache maybe off in this case.

        Comment

        Working...
        X