indexing product prices error

[LarkoJr]

Hi,

I'm runing 1.9.1 version on Centos. 

Our catalog_product_index_price in database is quite big i think. Each of our products have like 20 prices for each group. Maybe that is related to the error. 

If i normaly start reindex process in admin, it never stops. It runs for days on Catalog Product prices index.

If i connect to shell and run 

php indexer.php --reindex catalog_product_price

I get this error:

Product Prices index process unknown error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /var/www/html/magento/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
#0 /var/www/html/magento/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/html/magento/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/html/magento/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#4 /var/www/html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `index_p...', Array)
#5 /var/www/html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `index_p...', Array)
#6 /var/www/html/magento/lib/Zend/Db/Adapter/Abstract.php(635): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `index_p...', Array)
#7 /var/www/html/magento/app/code/core/Mage/Index/Model/Resource/Process.php(137): Zend_Db_Adapter_Abstract->update('index_process', Array, Array)
#8 /var/www/html/magento/app/code/core/Mage/Index/Model/Resource/Process.php(109): Mage_Index_Model_Resource_Process->_updateProcessData('2', Array)
#9 /var/www/html/magento/app/code/core/Mage/Index/Model/Process.php(222): Mage_Index_Model_Resource_Process->failProcess(Object(Mage_Index_Model_Process))
#10 /var/www/html/magento/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#11 /var/www/html/magento/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#12 /var/www/html/magento/shell/indexer.php(212): Mage_Shell_Compiler->run()
#13 {main}

Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /var/www/html/magento/lib/Zend/Db/Statement/Pdo.php:234
Stack trace:
#0 /var/www/html/magento/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/html/magento/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /var/www/html/magento/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/html/magento/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('UPDATE `index_p...', Array)
#4 /var/www/html/magento/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `index_p...', Array)
#5 /var/www/html/magento/lib/Zend/Db/Adapter/Abstract.php(635): Varien_Db_Adapter_Pdo_Mysql->query('UPDATE `index_p...', Array)
#6 /var/www/html/magento/app/code/core/Mage/Index/Model/Resource/Process.php(137): Zend_Db_Adapter_Abstract->update('index_process', Array, Array)
#7 /var/www/html/magento/app/code/core/Mage/Index/Model/Resource/Process.php(109): Mage_Index_Model_Resource_Process->_updateProcessData('2', Array)
#8 /var/www/html/magento/app/code/core/Mage/Index/Model/Process.php(222): Mage_Index_Model_Resource_Process->failProcess(Object(Mage_Index_Model_Process))
#9 /var/www/html/magento/app/code/core/Mage/Index/Model/Process.php(258): Mage_Index_Model_Process->reindexAll()
#10 /var/www/html/magento/shell/indexer.php(166): Mage_Index_Model_Process->reindexEverything()
#11 /var/www/html/magento/shell/indexer.php(212): Mage_Shell_Compiler->run()
#12 {main}

After execution, page is still there. If i want to reindex all, then server stops responding and i need to restart it. mysql stop responding. 

Any help is appreciated.

Best regards.

[iweb_p3mbo · Accepted solution]

Do you have access to change your MySQL settings at all?

You will more than likely need to fine tune things such as you innodb buffer, query cache size etc.. in order for this problem to go away.

[iweb_insom]

Hi,

Pembo's on to something. Ideally you were tune your settings to your environment, but the single biggest change we make to improve imports and reindexing is to add

innodb_buffer_pool_size=256M

to /etc/mysql/my.cnf's [mysqld] section and restart MySQL.

You can view the current buffer pool size with this SQL:

mysql> show variables like '%innodb_buffer_pool_s%';

I hope that helps,

Aaron

[LarkoJr]

Hi,

thank you for your quick reply. In the mean time i went trough hell and back. Was reading some tutorials and i wasn't paying enough attention and i deleted ibdata1 ib_logfile* I had few days old backup, but still. 

Here is my innodb_buffer_pool. I guess that is 8GB. I'm on a server with 16 GB of RAM and we still don't have much visitors. 

+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+

I'm reindexing at the moment and i'll let you know, if it works.

 -------------------------------------------------------------------------------------------------

 Still the same. 

Reindexing and site is not responsive and more. In the mysqld log i found this:

150815 23:53:27InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (417 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 7695357 OS file reads, 1636186 OS file writes, 125619 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.

[iweb_insom]

Hi,

No, that's not 8Gb - your buffer pool is currently set to 8Mb - that's far too small and the error message below agrees! If you have so much RAM you should have no problem making the pool much larger (that will require a MySQL restart).

Regards,

Aaron

[LarkoJr]

Hi,

the main issue was that i was putting 6GB in my.cnf. In most forum post, they just write X GB, but we need to put value in bytes, in my case 6.000.000.000 and that did the trick.

Thank you all for quick replies.

Best regards