-1

I am trying to investigate the cause of a slow query in MySQL; I posted about the query itself previously:How to investigate and optimise a slow MySQL query?

However, I'm asking this question separately because it potentially relates to a different problem that may underlie the first, and I'd be grateful for your insights, please.

I sent the problem referred to above to a colleague who runs a major University's website that is also based on Drupal, which I'm using. He executed the query on their server using a snapshot of our database and, compared with the tens of seconds it took my system, they were getting results in fractions of a second. For the record, our server is well-powered; we're not running a site on a Raspberry Pi or something!

He then did an explain on the query on his server and found that their set up is handling the query quite differently to ours.

enter image description here This led him to question the integrity of the index in my table, because:

On his server:

mysql> alter table taxonomy_term_data ENGINE=InnoDB; Query OK, 11589 rows affected (0.26 sec) Records: 11589 Duplicates: 0 Warnings: 0

On ours:

mysql> alter table taxonomy_term_data ENGINE=InnoDB; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0

The above was entered to see if we could regenerate the index, on the off-chance that it is corrupt.

I've since tried dumping the table and then re-importing it, as per his suggestion, but that has not made any difference.

I must admit that I am completely out of my depth, and my colleague is unsure why the ALTER table operation, on our production server, is returning 0 records compared with the correct 11,000+ he sees on their test server, despite importing a dump of our db.

Any advice that anyone can offer me to investigate this would be truly helpful.

Many thanks.

hssin
  • 55
  • 4
Chris
  • 23
  • 7
  • 3
    Are you both using the exact same version of MySQL? Another reason why the execution plan might differ is if statistics are outdated; cost-based optimizers rely on stats to choose execution plan. I've seen cases (in Oracle however) where performance would improve/degrade just because stats were re-collected. – Simon Berthiaume Feb 17 '18 at 15:38
  • I must admit that I don't know and this is something I have written back to ask. However, the difference in the execution time is so gross (orders of magnitude) for the query that something must be wrong with my config... – Chris Feb 17 '18 at 15:40
  • Have you tried to run `OPTIMIZE TABLE taxonomy_term_data;` on your table and see if it changes anything (should re-analyze everything and rebuild index s it might take time)? – Simon Berthiaume Feb 17 '18 at 16:12
  • Yes - I did that and nothing changed. It was very fast though - could that be a bad sign? The table is quite simple though - just a list of node ids and the accompanying associated taxonomy terms. – Chris Feb 17 '18 at 16:30
  • By looking at the `EXPLAIN` statement in the image, it seems the query that is running in the code on your server is different than that on your colleague's server. `` is missing in your code. `echo` the query or compare the files on both the servers. – Samir Selia Feb 17 '18 at 16:35
  • I think that's a temporary table; why my server is not generating it though, I don't know. The query that produced that explain result is the same one listed in this question: https://stackoverflow.com/questions/48452168/how-to-investigate-and-optimise-a-slow-mysql-query?noredirect=1#comment84690809_48452168 – Chris Feb 17 '18 at 16:39
  • 1
    Please make your question self-contained, including a [mcve]. Eg neither this question nor the link contain DDL, which is critical. Eg we don't really know what code you ran. – philipxy Feb 18 '18 at 07:36
  • You'll get "0 rows affected" if the engine does not change (just run it again and he will get 0 rows too). So you ran the test on different engines. – Solarflare Feb 18 '18 at 09:00

1 Answers1

0

I have solved this problem and here is the (very simple) solution, just in case anyone else is struggling with it.

I also gratefully acknowledge the help of Andy Batey at Cambridge University who spotted the issue and helped me.

The clue was that they are running MySQL 5.5; we are on 5.7. On 5.5 the query is fast; on 5.7 it's dreadfully slow.

This led to this thread: query extremely slow after migration to mysql 5.7

For me, the same query executed on the two different platforms was taking a fraction of a second (v5.5) vs 12s (v5.7).

Adding: 

"optimizer_switch='derived_merge=off'"

...to my.cnf resulted in the same query now taking 0.05s on v5.7.

I hope this helps anyone else who runs into this upgrade issue. I had spent days trawling fora and groups asking for help and also trawling through the query itself to try to optimise it....

Chris
  • 23
  • 7