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:

[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

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/

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

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