1

I am getting deadlocks on this table catalogsearch_query. The table has approx 500k rows.

The site is averaging about 2000 concurrent users.

Edit: To clarify concurrent users I mean the following:

Go to Google Analytics. Click on Real Time, Click on Overview view number in "Right Now"

Magento 1.13.1

The table is 129mb. (catalogsearch_query 129.34)

New Relic is giving me a really slow response time on this query

SQL: INSERT INTO `catalogsearch_result` SELECT ? AS `query_id`, `s`.`product_id`, MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = ?) AND (MATCH (s.data_index) AGAINST (:query IN BOOLEAN MODE) OR (`s`.`data_index` LIKE ? OR `s`.`data_index` LIKE ? OR `s`.`data_index` LIKE ?)) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`) 

They are averaging 3500 orders per day with a peak of 8000 orders (42000 items) on Friday Nov 28th. I am not sure if that is busy or not but they are expecting a bigger day on Mon Dec 1.

First Question: It looks like the deadlocks are happening on the inserts. What are the ramifications if I reduce the size of this table or simply truncate it?

Second Question: If I can't truncate or remove data what are my other options?

1

The search tables are the only MyISAM tables IIRC. And the problem with these is that the relevance inserts and updates are blocking by nature.

If you truncate the tables, you'll lose search altogether.

Break the relationships between the relevance and query tables and again you'll lose search.

If you do proceed and truncate them, you'll be forced to do a full reindex of search to restore it, and not only will that lock the table for the entirety of the process (effectively taking your site offline), and again, you'll lose search.

You can't fix this by merely deleting data.


FYI. A figure for concurrency means nothing without a measurement window. Is it 2000 per minute, per hour etc.

  • "You can't fix this by merely deleting data." .... Is the situation hopeless? – brentwpeterson Nov 30 '14 at 14:05
  • Here is how I come up with the number 2000: Go to Google Analytics. Click on Real Time, Click on Overview view number in "Right Now". Whatever that relates to other numbers that is the number I used for concurrent users. – brentwpeterson Nov 30 '14 at 14:06
  • Gotcha. GA use a 60 second measurement window. Is the situation hopeless? - not at all, 2,000 concurrent sessions isn't particularly large so you shouldn't really be having any trouble provided your infrastructure is sized/configured properly. If you are using EE, then you should also be using SOLR - which lessens the impact on the fulltext table, but the relevance tables will still be used. You could rewrite the core to not bother doing an update/insert - the only thing you would lose is search metrics (ie. most popular search terms). – Ben Lessani - Sonassi Nov 30 '14 at 16:34
  • Thanks, the only issue we are having is with the Search Inserts now but we have been error free since 8am EST :) – brentwpeterson Nov 30 '14 at 22:06
1

Answered with the help of Rackspace DBA:

Based on the last deadlock this seems to be a gap lock contention:

------------------------
LATEST DETECTED DEADLOCK
------------------------
141201  9:01:08
*** (1) TRANSACTION:
TRANSACTION 22EB54179, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 58 lock struct(s), heap size 14776, 14765 row lock(s)
MySQL thread id 26916513, OS thread handle 0x7f4aba9f2700, query id 2477664514 updating
DELETE FROM `catalogsearch_result`
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13147 page no 27731 n bits 488 index `PRIMARY` of table `catalogsearch_result` trx id 22EB54179 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 22EB54129, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2.....
 trx id 22EB54129 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

This gap locking is generally required for consistency where using the default binlog-format = STATEMENT replication.

A common workaround here is to use binlog-format = ROW and adjust the transaction-isolation = READ COMMITTED rather than using the default repeatable read isolation. This relaxes the locking required by InnoDB. I am not aware of any dependency of Magento on REPEATABLE READ isolation (which is the default on other supported platforms such as Oracle), and I have seen other environments make this change with good results.

This change would only help for gap locking (and likely the query pattern above), but there can be other sort of deadlock issues that may still be an issue. In any transactional database there is often some level of deadlocking, however I do see this particular pattern with the catalogsearch_result table is happening with high frequency.

The isolation level can be changed dynamically via:

mysql> SET GLOBAL tx_isolation = 'READ COMMITTED';

Or in the my.cnf via: [mysqld] ...

transaction-isolation = READ COMMITTED
  • 1
    This does not sound like your narrative voice, Mr. Peterson. – benmarks Dec 5 '14 at 15:51
  • Yes I added the comment that Rackspace helped me with the issue. I figured it was important to get the answer – brentwpeterson Dec 5 '14 at 15:53
  • 1
    The constistency is higher with REPEATABLE READ (otherwise a NON-REPEATABLE READ might happen soon). I do not see a problem regarding this in combination with search, more in relation to stock, catalog, and sales (order increment id!). Would be interested in a long term evaluation on this. – Anna Völkl Dec 5 '14 at 17:17

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.