0

We have two schemas, both from the same template and with near identical record count (less than 100). They are both running in the same MariaDB 5.5.68. We also have existing schemas that run this exact query over thousands of records without issue. They are also on this same DB instance.

Our issue is that sites newly created from our template, have started hanging when a specific query (details below) is executed. When the query hangs, it effectively locks the server until it is killed.

We have manual verified that the broken schema is configured the same with regards to collation, engine, indexing on the tables. All confirmed the same.

I was able to get the raw query from the processlist. Running this query directly against the DB works for older copies of the schema -- some with thousands of records. However, on newly created shcemas this behavior occurs even with less than 10 records.

The data being stored in these sites is extremely similar. The tables are simple. There are no florigen keys, no triggers or stored procedures are involved. The execution plan seems to indicate indexes are setup as needed.

We see the same behavior when we EXPLAIN the query. With the one that runs correctly, I get an execution plan.

enter image description here

If I try to explain the query on a schema where it fails, it just hangs as does the actual query.

The query is dynamically generated by the CMS. It is quite lengthy, but I've trimmed it to the relevant information.

SELECT `lnt_record_title`.`entry_id`, 
`lnt_record_title`.`title`, 
`lnt_record_title`.`url`,
 `lnt_record_title`.`active`, 
 `lnt_record_title`.`entry_date`, 
  [snip]
FROM (`lnt_record_title`)

RIGHT JOIN `lnt_field_data_43` ON `lnt_field_data_43`.`entry_id` = `lnt_record_title`.`entry_id`
RIGHT JOIN `lnt_field_data_44` ON `lnt_field_data_44`.`entry_id` = `lnt_record_title`.`entry_id`
[snip]
WHERE `lnt_record_title`.`host_id` =  '9921'
AND `lnt_record_title`.`site_id` =  '1'
AND `active` =  '1' 

I've snipped the full field and join list, but the snipped lines are identical to what is shown. At this point, I'm not sure what I even need to look at. UPDATE: I have also run mysqlcheck on the entire database, it reports all tables ok.

1
  • One possibility is you're running into a cardinality estimation issue of sorts, which is resulting in different query plans between the two cases you're facing. This is a common issue in general, when everything else is equal, except the data itself. I'm not well versed enough in MariaDB to advise you on how to specifically diagnose your issue unfortunately, but hopefully that at least gives you something to research that could be one possibility of your issue. The EXPLAIN ANALYZE of both plans would be helpful, but I understand you're unable to get it for the problematic case.
    – J.D.
    Nov 23, 2021 at 18:17

1 Answer 1

1

I would expect those to need LEFT JOIN, not RIGHT JOIN. (But I don't understand the intent.)

Although lnt_record_title seems to be quite small, it might be better if it had

INDEX(host_id, site_id, active, entry_id)

5.5.68 is quite old; suggest you upgrade if possible. A lot of improvements were made in the Optimizer after that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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