mardi 3 février 2015

Large query with subquery runs indefinitely after mysql update


We recently did a big server update, in the process migrating from mysql-5.0 to 5.5.40 Several of the queries, which had been running well on the old server, now grind everything to a screeching halt. After some investigation, I figured out the following minimal example of a query that triggers the problem:



SELECT
(SELECT date FROM results
WHERE person_id = people.id
ORDER BY results.date DESC limit 1
) AS date
FROM people
WHERE people.boss_id = 123;


Some base info:



  • Both people and results are MyISAM tables

  • There are primary key ids on both tables and no other indexes

  • results is one of our larger tables, currently clocking in at about 4 million rows

  • people.boss_id = 123 filters down to ~3000 rows

  • The above the query runs in about 4 seconds on the older mysql-5.0 server

  • On the newer, mysql-5.5 server the same type of query on the same dataset has run for up to three hours before I manually killed the process, during which it reported as "sending data" My research suggests that that means that the database is filtering the data, but I'm not 100% sure on that.

  • Running the above query on the newer server produces the same results: stuck query "sending data" until I manually kill the process.

  • Both tables work fine on their own

  • I can actually run a single isolated version of the subquery (ie where person_id = 123 or whatever). It takes a few microseconds on the newer server.


Here's what an EXPLAIN EXTENDED looks like:



select_type table type possible_keys key key_len ref rows filtered Extra
PRIMARY people ref boss_id boss_id 4 const 4217 100.00 ----
DEPENDENT SUBQUERY results index boss_id date 32 NULL 1 1700.00 Using where


So, any ideas on what's causing this? I'm more than a little stuck. Have I run into some sort of bug? Could upgrading to mysql-5.6 possibly fix this (it's got a new query planner, after all)? Might changing one or both of the tables to InnoDB help? Would repairing one table or the other help?


Incidentally, one of the annoying side effects of this problem is that it locks up phpmyadmin. It's a little unnerving, actually. The commandline works fine and well, but phpmyadmin will just sit there "[loading]" until I kill the rogue process via command line. Any ideas on why a stuck query might do that?





Aucun commentaire:

Enregistrer un commentaire