My website is experiencing issues at checkout. I’m using Magento Enterprise 1.8 and my checkout module is Idev’s Onestepcheckout.
The issue we are seeing is that the eav_entity_store table is taking an exceedingly long time (up to 51 seconds) to return an order number to Mage_Eav_Model_Entity_Type.
What I do know is that the query run to get this is a transaction run as ‘FOR UPDATE’ so the row being accessed is locked until the transaction completes. I’ve looked at other parts of the code as well as the PHP code throughout the transaction where the row is locked (we’re using InnoDB so the lock should be getting released once the transaction is committed) and I’m just not seeing anything there (or in the slow query logs) that should be causing a lock wait anywhere near 51 seconds.
I have considered that requests may be getting stacked up and slowly creeping up in time as they wait, but I’m seeing the query time go from 6ms to 20k ms to 50k ms 1,2,3. It isn’t an issue of 100-200 requests stacked up, as there are only a few dozen of these a day.
I’m aware that MySql uses parent locking, but there are no FK’s related to this table whatsoever. There are two BTREE indexes that at one point were FK’s but have since been Altered (that happened years ago). Keep in mind that the eav_entity_store table has less than 50 rows and is only 5 columns wide (4 smallint and a varchar). I seriously doubt tablesize or improper indexing is the culprit. In the spirit of TLDR, however, I will say that the two BTREE indexes are the two columns by which we select from this table.
One possibility is that I may need to replace the two indexes with a compound index, as the ONLY reads to this table are coming from a query that reads (FROM [Column with Index A] AND [Column with Index B]). I simply don’t know if row-level locking would prevent this query from accessing another row in the table with the indexes currently on the table.
At this point, I’ve become convinced that the underlying issue is strictly DB related, but any Magento or MySql advice regarding this would be greatly appreciated. Anybody still actually reading this can hopefully appreciate that I have exhausted a number of options already and am seriously stumped here. Any info that you think may help is welcome. Thanks.