Won’t fix bug #5892: add index on OXSEO table to avoid random deadlocks

A couple of days ago we were working on Bug #5892. The reporter complaint about random deadlocks with increasing traffic in OXSEO table where InnoDB is used. After debugging and analysing the InnoDB status message, he sorted out the culprit:

This is because there is no index is set on oxobjectid, the whole table instead of just one row was blocked for the transaction.

As a solution @matene, the reporter, suggested to set the index via

However, we couldn’t reproduce this issue. Furthermore, we found out that adding an index results in a major impact on our performance measures. The response time of our performance test setup increased by more than 100%.

It seems that adding an index can help fixing this particular issue, as long as product data etc. don’t change a lot. However, if you have more frequent changes in your basic data, then this could lead to the performance drop we encountered.

Therefore we cannot add this change into the default product setup. If you consider to add this change in your project, make sure to double check how often your data is changed, otherwise you might encounter performance issues.

Thanks @matene for this valuable input!

 

0.00 avg. rating (0% score) - 0 votes
2 replies
  1. Reinhard Vogl says:

    Whenever we use the oxid SOAP Connector in our projects, we have to add the index. Else a lot of customers will end in a mysql deadlock. If you update like 10 or more articles with variants and multiple categories the setting of oxexpired will take some seconds. Every call to get a seourl will end on this deadlock. I think its pretty easy to reproduce with a script, that will use erpconnector and update 1000 existing articles in 50 article steps.

    Reply
    • Florian Auer
      Florian Auer says:

      Hi Reinhard, thanks for your feedback. We will use your hints and try to reproduce the issue again. However, adding an index still blows up our performance measures, so we still recommend to use the additional index with care.

      Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *