MySQL Dies on reindexer catalog_product_price - 75,000+ products

[Urbanwarfare]

Hi,

I can appreciate this is a repeat of serveral questions on here that I have read, through and actioned the suggestions which seem to resolve the problem for them, but unfortunately it hasn't worked for me so far.

I have a freshly imported (via Magmi) Magento ver. 1.9.2.4 setup with a set of 135,766 (simple) & 13,609 (configurable) products assigned. All assignments are present in the back-end but not in the front-end.

I have the flat catalogue enabled and re-indexing on those has been successful but running it for the command catalog_product_price fails with:

Product Prices index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in public_html/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT RELEASE_...', Array)
#5 public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT RELEASE_...', Array)
#6 public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(72): Varien_Db_Adapter_Pdo_Mysql->query('SELECT RELEASE_...', Array)
#7 public_html/app/code/core/Mage/Index/Model/Lock/Storage/Db.php(84): Mage_Index_Model_Resource_Helper_Mysql4->releaseLock('trade....')
#8 public_html/app/code/core/Mage/Index/Model/Lock.php(208): Mage_Index_Model_Lock_Storage_Db->releaseLock('index_process_2')
#9 public_html/app/code/core/Mage/Index/Model/Lock.php(181): Mage_Index_Model_Lock->_releaseLockDb('index_process_2')
#10 public_html/app/code/core/Mage/Index/Model/Process.php(469): Mage_Index_Model_Lock->releaseLock('index_process_2', NULL)
#11 public_html/app/code/core/Mage/Index/Model/Process.php(223): Mage_Index_Model_Process->unlock()
#12 public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#13 public_html/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#14 public_html/shell/indexer.php(216): Mage_Shell_Compiler->run()
#15 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away, query was: SELECT RELEASE_LOCK(?);' in /home
/uwfs0022/public_html/lib/Zend/Db/Statement/Pdo.php:235
Stack trace:
#0 public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT RELEASE_...', Array)
#4 public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT RELEASE_...', Array)
#5 public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(72): Varien_Db_Adapter_Pdo_Mysql->query('SELECT RELEASE_...', Array)
#6 public_html/app/code/core/Mage/Index/Model/Lock/Storage/Db.php(84): Mage_Index_Model_Resource_Helper_Mysql4->releaseLock('trade....')
#7 public_html/app/code/core/Mage/Index/Model/Lock.php(208): Mage_Index_Model_Lock_Storage_Db->releaseLock('index_process_2')
#8 public_html/app/code/core/Mage/Index/Model/Lock.php(181): Mage_Index_Model_Lock->_releaseLockDb('index_process_2')
#9 public_html/app/code/core/Mage/Index/Model/Process.php(469): Mage_Index_Model_Lock->releaseLock('index_process_2', NULL)
#10 public_html/app/code/core/Mage/Index/Model/Process.php(223): Mage_Index_Model_Process->unlock()
#11 public_html/app/code/core/Mage/Index/Model/Process.php(260): Mage_Index_Model_Process->reindexAll()
#12 public_html/shell/indexer.php(168): Mage_Index_Model_Process->reindexEverything()
#13 public_html/shell/indexer.php(216): Mage_Shell_Compiler->run()
#14 {main}
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in public_html/lib/Zen
d/Db/Statement/Pdo.php:228
Stack trace:
#0 public_html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 public_html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 public_html/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT RELEASE_...', Array)
#5 public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT RELEASE_...', Array)
#6 public_html/app/code/core/Mage/Index/Model/Resource/Helper/Mysql4.php(72): Varien_Db_Adapter_Pdo_Mysql->query('SELE in public_html/lib/
Zend/Db/Statement/Pdo.php on line 235

I am running on a dedicated server with SSD drivers and 16GB of Ram so if there are any suggestions or plugins anyone can recommend I have the access/permissions to try it.

I have flushed the database and installed a smaller portion of the data so 75,500 simple & 7,541 configurable products and it still fails at exactly the same point.

From reading various threads/blogs/posts and stack overflow I have the following settings in my mysql config any pointers are greatly appreciated:

[mysqld]
## connection settings
max_connect_errors=1000
max_connections=500
max_user_connections=30
wait_timeout=7200
connect_timeout=10
interactive_timeout=7200
lock_wait_timeout=120


## cache settings
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
table_open_cache=2048
table_definition_cache=16384
thread_cache_size=12

## buffer sizes
key_buffer=4096M
sort_buffer_size=2M
read_buffer_size=1M
join_buffer_size=1M

## tmpdir / temp table sizes
tmp_table_size=128M
max_heap_table_size=128M
tmpdir=/dev/shm

## misc. settings
# default-storage-engine = MYISAM
datadir=/var/lib/mysql
skip-external-locking

server-id = 1
open-files-limit = 132768
max_allowed_packet= 4GB

## innodb settings
innodb_data_file_path = ibdata1:10M:autoextend
innodb_thread_concurrency = 12
innodb_buffer_pool_size = 10GB
# innodb_buffer_pool_instances = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_file_format = BARRACUDA
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_max_dirty_pages_pct = 60
innodb_stats_on_metadata = off

## innodb forced recovery
#innodb_force_recovery = 1
#innodb_purge_threads = 0

## slow query logging
#log_slow_queries=/var/lib/mysql/slow-queries.log
#log-long-format
#long_query_time=5

open_files_limit=14000
[mysqld_safe]
open-files-limit = 132768

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer = 128M
sort_buffer = 96M
read_buffer = 32M
write_buffer = 32M

[mysql]
no-auto-rehash

Thanks,
Simon

[robfico]

Try increasing the "max_allowed_packet" from 4 GB to 8 GB (or more) in MySQL, and see if that fixes the error. That is often the culprit for the MySQL server has gone away error.

[Urbanwarfare]

I will try that now - fingers crossed - most articles seem to focus on the innodb pool buffer....

I went as high as 12GB but it has made no difference... it seems bizarre that the re-indexing isn't more reliable than throwing as much server resource at it as possible?

[Urbanwarfare]

It still isn't working but I might be heading in the right direction...

My hosting provider suggested increasing the 

wait_timeout / interactive_timeout to 9000 and the script failed after 5 and half minutes.

So I increased those values and the lock_wait_timeout so they are all 12000 and the script failed after 22 minutes. Its late now but I will try 18000 in the morning.

Does anyone mind sharing any catalog_product_price results that I could compare for bench-marking to try and estimate what sort of running time I could expect with my catalog - anything to help would be much appreciated.

[Urbanwarfare · Accepted solution]

FIXED IT!

Beware developing brand new sites with new plugins - add your products first and test your flow etc before you add consmetic extensions because sometimes they can bite you - as one has me.

I was using a grid plugin for configurable products which for some reason has its on pricing table which it was trying to populate during the re-indexing. I reset the process and did some bench marking with 10% of my catalog:

/* 1,4965 - Products */
Product Attributes index was rebuilt successfully in 00:00:01
Product Prices index was rebuilt successfully in 00:03:26
Catalog URL Rewrites index was rebuilt successfully in 00:00:30
Product Flat Data index was rebuilt successfully in 00:00:06
Category Flat Data index was rebuilt successfully in 00:00:01
Category Products index was rebuilt successfully in 00:00:01
Catalog Search Index index was rebuilt successfully in 00:00:03
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00

I had this MySQL to show me locked tables:

SHOW OPEN TABLES WHERE in_use <>0

And I noticed an table for the extension being locked - so I disabled it and removed it and my bench marking for the 149.000 products is now this:

/* 149,685 */
Product Attributes index was rebuilt successfully in 00:00:13
Product Prices index was rebuilt successfully in 00:01:21
Catalog URL Rewrites index was rebuilt successfully in 00:05:38
Product Flat Data index was rebuilt successfully in 00:00:53
Category Flat Data index was rebuilt successfully in 00:00:01
Category Products index was rebuilt successfully in 00:00:07
Catalog Search Index index was rebuilt successfully in 00:00:41
Stock Status index was rebuilt successfully in 00:00:00
Tag Aggregation Data index was rebuilt successfully in 00:00:00  

This explains why nothing else I was trying was working - I hope this thread can help anyone else!