Catalogue Price Indexer failing - 150,000 products / 1GB DB

[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

From reading various threads/blogs/posts and stack overflow I have the following settings in my mysql config file:

wait_timeout=7200
max_allowed_packet= 873741824
innodb_buffer_pool_size = 10000000000
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M

But it is still not succeeding with the re-index. Have I got too many products?
Is there a recommended limit to the number or products Magento can handle?

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.

Thanks,
Simon

[Urbanwarfare]

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.

Here is my entire my.cnf any pointers 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