Queries are super slow

[Jaan]

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:

[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

[niljoshi]

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/en/show-profile.html
http://www.mysqlperformanceblog.com/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/

[wagnerbianchi]

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

[DenisM]

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