1

I have a select query called by a script to our MySQL Cluster that has gone from running in a second or two to taking well over a minute per query (and it has to run this query 24000 times). Nothing has changed code-wise in the few months since it was last run. I'm not a proper DBA, just a long-time MySQL user, and I read on another thread that it could be an issue with the indexes outgrowing the limit of the key_cache size? But would this apply to ndbcluster table types? And if so, how would I tell?

The output of Explain for the problem query is:

EXPLAIN SELECT userid from users left outer join routes on routes.user_id = users.id left outer join aliases on aliases.route_id = routes.id where aliases.name='xxxx' and domain_id=8;

+----+-------------+---------+------+-----------------+----------+---------+------------------+--------+-----------------------------------+ 
| id | select_type | table   | type | possible_keys   | key      | key_len | ref              | rows   | Extra                            |
+----+-------------+---------+------+-----------------+----------+---------+------------------+--------+-----------------------------------+ 
|  1 | SIMPLE      | users   | ALL  | PRIMARY         | NULL     | NULL    | NULL             | 177802 |                                  | 
|  1 | SIMPLE      | routes  | ref  | PRIMARY,user_id | user_id  | 4       | vnames.users.id  |      1 | Using where                       | 
|  1 | SIMPLE      | aliases | ref  | route_id        | route_id | 4       | vnames.routes.id |      1 | Using where with pushed condition |
+----+-------------+---------+------+-----------------+----------+---------+------------------+--------+-----------------------------------+

And the output of Show Indexes for the three tables is:

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| aliases |          0 | PRIMARY  |            1 | id          | A         |      377197 |     NULL | NULL   |      | BTREE      |         |
| aliases |          1 | route_id |            1 | route_id    | A         |      377197 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| routes |          0 | PRIMARY  |            1 | id          | A         |      208442 |     NULL | NULL   |      | BTREE      |         |
| routes |          1 | user_id  |            1 | user_id     | A         |      208442 |     NULL | NULL   |      | BTREE      |         |
| routes |          1 | address  |            1 | address     | A         |      208442 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          0 | PRIMARY  |            1 | id          | A         |      177803 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | userid   |            1 | userid      | A         |      177803 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Any help/suggestions would be greatly appreciated.

1 Answer 1

0

I suspect this query has died due to the volume in the tables. Running a query that returns all userids 24000 times will eventually cause performance issues.

At some point you your tables grow to the point they don't fit in memory (very fast access) and you end up reading data from disk (very slow queries for most queries. This is a sudden switch. One query I wrote went from sub-second to five minutes when this happened. If you can compact the tables, this may resolve the condition for a while. Adding more memory may help. (Even if your database doesn't have enough buffer space, the O/S may buffer the data for you.)

This is how I formatted the query to understand it.

SELECT userid 
FROM   users 
LEFT OUTER JOIN routes 
ON     routes.user_id = users.id 
LEFT OUTER JOIN aliases 
ON     aliases.route_id = routes.id 
WHERE  aliases.name = 'xxxx' 
AND    domain_id = 8;

If you are searching for aliases that match the aliases.name, it would help to index that column.

Based on the LEFT OUTER JOINS, you should get the userid for all users or all users with domain_id = 8. As I read this query it is a long way of writing:

SELECT userid 
FROM   users;

Or

SELECT userid 
from   users
WHERE  domain_id = 8;

However I expect you want the user id when the alias and domain match.

SELECT userid 
FROM   users 
JOIN   routes 
ON     routes.user_id = users.id 
JOIN   aliases 
ON     aliases.route_id = routes.id 
WHERE  aliases.name = 'xxxx' 
AND    domain_id = 8;

If you are looking for the userid matching the aliases.name value, it would help to index aliases.name.

9
  • Hi Bill, you are correct in that aliases.name could use an index and that may solve the issue (I will certainly add it), but the query has been in use for several years and has always executed in a second or so. It is called once per name from an input file (there 24000 names in the input file). So I'm wondering why it would suddenly start executing over 100X slower when no server or code changes have been made. Could it be a key_cache issue in that the indexed fields have grown too big to be held in the allocated key cache size?
    – PMT
    Jul 8, 2016 at 14:01
  • @PMT At some point you your tables grow to the point they don't fit in memory (very fast access) and you end up reading data from disk for most queries. This is a sudden switch. One query I wrote went from sub-second to five minutes when this happened. If you can compact the tables, this may recover the condition for now. Try comparing the results and timing of the first three queries I provided.
    – BillThor
    Jul 8, 2016 at 14:05
  • Which table does domain_id belong to? Jul 8, 2016 at 15:08
  • domain_id belongs to the aliases table, so that second query actually wouldn't work without being joined to the aliases table. The first query, Select userid FROM users; took .58 seconds. The last query with the JOINs took 1'12". It sounds like adding an index would help, but I'm also wondering if I can up the memory allocated for caching tables (and if this is any different in MySQL Cluster since the tables are ndbcluster type).
    – PMT
    Jul 8, 2016 at 15:25
  • I found this interesting check/solution yesterday, but it applies to MyISAM and InnoDB engines, and I don't know enough about MySQL Cluster to know if this can be applied as a test there too: stackoverflow.com/questions/11748629/…
    – PMT
    Jul 8, 2016 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.