View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005892 | OXID eShop (all versions) | 4.01. Database handling | public | 2014-09-25 16:23 | 2018-01-05 08:39 |
| Reporter | matene | Assigned To | |||
| Priority | urgent | Severity | crash | Reproducibility | random |
| Status | resolved | Resolution | fixed | ||
| Product Version | 4.8.7 / 5.1.7 | ||||
| Fixed in Version | 4.10.0 / 5.3.0 | ||||
| Summary | 0005892: Add index on OXSEO table to avoid random deadlocks | ||||
| Description | With increasing traffic we've gotten more and more deadlocks in the database. They might not be completely avoided in InnoDB, but they can be decreased! In this instance we got the following error about 5 timer per hour: oxConnectionException-oxException (time: 2014-09-22 16:05:47): [1213]: mysql:EXECUTE error: [1213: Beim Versuch, eine Sperre anzufordern, ist ein Deadlock aufgetreten. Versuchen Sie, die Transaktion neu zu starten] in EXECUTE with parameters replace oxseohistory ( oxobjectid, oxident, oxshopid, oxlang, oxinsert ) select oxobjectid, MD5( LOWER( oxseourl ) ), oxshopid, oxlang, now() from oxseo where oxtype ='oxarticle' and oxobjectid = '8065189220abcbf85.94218099' and oxshopid = '6' and oxlang = 0 and oxexpired = '1', for user *** After some lengthy debugging and anlysing the InnoDB status message, we figured out the culprit: update oxseo set oxexpired = '1' where oxobjectid = '***' Why? Because there is no index on oxobjectid alone - thus it blocks the whole table and not just one row. Solution: ALTER TABLE oxseo ADD INDEX `OXOBJECTIDONLY` ( `OXOBJECTID` ); It takes a few minutes on large (=normal) oxseo tables and increases the index size by 10-15%. | ||||
| Tags | Search | ||||
| Theme | All | ||||
| Browser | All | ||||
| PHP Version | 5.3 | ||||
| Database Version | 5.1 | ||||
| related to | 0006762 | resolved | vasyl.liulka | Deadlock in oxseo when deleting categories in backend |
|
|
Hi all, thanks for reporting this to us. However, we were not able to reproduce this issue. Furthermore, we found out that adding an index has a major impact on our performance measures. The response time of our performance test setup increased by over 100 %. It seems that adding an index can help fixing this particular issue, as long as product data etc. doesn'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 are planning to add this change in your project, make sure to double check how often your data is changed, otherwise you might encounter performance issues. |
|
|
This conspicuity occurs if a process stresses the oxseo table, like a synchronisation of the whole article table with an ERP. If a change causes a new SEO url the shop has to lock the table, therefore it can change the value of the field oxexpired. The "deadlock danger" becomes critical when a customer calls the article in the frontend. The shop tries to make a new SEO URL, because the old one is expired. But the lock detains the change and so the way to a deadlock is made. |
|
|
As this problem typically occurs in high load scenarios combined with an extensive use of the erp soap api we must, at that point, execute the unsupported sql statement of the bug reporter in every new project just to make sure oxid won't fail in production. |
|
|
We've just hit this with a high traffic shop using soap erp A LOT. What exactly made the performance drop by 100%? Maybe it's worth investigating or at least rewriting this core query, so it can utilize the existing KEY `OXOBJECTID` (`OXLANG`,`OXOBJECTID`,`OXSHOPID`) |
|
|
This bug has been confirmed by popular vote. |
|
|
This bug has been fixed in \oxSeoEncoder::markAsExpired() in the following versions of OXID eShop: - v5.0.14 - v5.1.8 - v5.2.1 - v5.3.0 - v6.0.0 where the following piece of code was replaced: - $sWhere .= $iLang ? ( $sWhere ? " and oxlang = '{$iLang}'" : "where oxlang = '{$iLang}'" ) : ''; + $sWhere .= !is_null($iLang) ? ($sWhere ? " and oxlang = '{$iLang}'" : "where oxlang = '{$iLang}'") : ''; Since these versions the resulting query is able to use the existing index oxseo.OXOBJECTID, even if $iLang is 0 For lower shop versions a workaround could be to re-order the columns of the existing index oxseo.OXOBJECTID the following way: ALTER TABLE `oxseo` DROP INDEX `OXOBJECTID`, ADD INDEX `OXOBJECTID` (`OXOBJECTID`, `OXLANG`, `OXSHOPID`) ; The reordering of the columns in the index should have no negative performance impact, as an EXISTING index is used and no index is added. As there is a primary index on OXLANG, this index could be used by queries, which do provide only OXLANG (without OXOBJECTID) Keep in mind that dropping the index and recreating it could take a long time on systems with lots of entries in oxseo. You should double check with you DB admin for the best strategy and the expected duration of the maintenance window. Also one should keep in mind, that under very high load there could still occur some deadlocks, but it should be really few. In order to monitor the deadlocks, the MySQL variable innodb_print_all_deadlocks, which was introduced in MySQL 5.5.30 could be set to 'on'. See https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks for details. |
