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?
Best Answer
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.