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

Queries are super slow

JaanJaan EntrantCurrent User Role Beginner
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:

7bbG1.png
[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:
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:
    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

Comments

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.