New Server 32 CPUs 32Gb RAM vs MySQL has gone away

[razuk]

I have a new server with 32 cpus and 32gb ram and we are having mysql errors once in a while, mostly on a Magento website.

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

I have alterady tweaked mysql, but the error remains.

I also installed mysql tuner > /http://108.168.204.170/mysqltunerReport.txt

and mysqlmymom > /http://108.168.204.170/mysqlmymonlite_stats.txt

Here is my my.cnf:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-locking
##skip-innodb
query_cache_limit=32M
query_cache_size=32M
query_cache_type=1
max_user_connections=100
max_connections=1000
interactive_timeout=300
wait_timeout=100
connect_timeout=999
thread_cache_size=128
key_buffer=16M
join_buffer=1M
join_buffer_size=1M
max_allowed_packet=512M
table_cache=1024
#record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
server-id=1
federated

innodb_buffer_pool_size=1G


#[mysql.server]
#user=mysql
#basedir=/var/lib 

open_files_limit=50000
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid

#[mysqldump]
#quick
#max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M 

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

 

[cPanelMichael]

Hello

Are there any additional error messages in your MySQL error log when this behavior occurs?

Thank you.

 

[razuk]

There has been an error processing your request

SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query

Trace:
#0 /home/madepars/public_html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/madepars/public_html/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/madepars/public_html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 /home/madepars/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `core_tr...', Array)
#4 /home/madepars/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `core_tr...', Array)
#5 /home/madepars/public_html/lib/Zend/Db/Adapter/Abstract.php(808): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource/Translate.php(79): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select), Array)
#7 /home/madepars/public_html/app/code/core/Mage/Core/Model/Translate.php(284): Mage_Core_Model_Resource_Translate->getTranslationArray(NULL, Object(Zend_Locale))
#8 /home/madepars/public_html/app/code/core/Mage/Core/Model/Translate.php(137): Mage_Core_Model_Translate->_loadDbTranslation(true)
#9 /home/madepars/public_html/app/code/core/Mage/Core/Model/Locale.php(721): Mage_Core_Model_Translate->init('adminhtml', true)
#10 /home/madepars/public_html/app/code/core/Mage/Tax/Helper/Data.php(345): Mage_Core_Model_Locale->revert()
#11 /home/madepars/public_html/app/design/adminhtml/default/default/template/catalog/product/js.phtml(44): Mage_Tax_Helper_Data->getPriceFormat(Object(Mage_Core_Model_Store))
#12 /home/madepars/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/madepars/...')
#13 /home/madepars/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#14 /home/madepars/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#15 /home/madepars/public_html/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#16 /home/madepars/public_html/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Template->_toHtml()
#17 /home/madepars/public_html/app/code/core/Mage/Core/Block/Text/List.php(43): Mage_Core_Block_Abstract->toHtml()
#18 /home/madepars/public_html/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
#19 /home/madepars/public_html/app/code/core/Mage/Core/Block/Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#20 /home/madepars/public_html/app/code/core/Mage/Core/Block/Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('js', true)
#21 /home/madepars/public_html/app/design/adminhtml/default/default/template/page.phtml(82): Mage_Core_Block_Abstract->getChildHtml('js')
#22 /home/madepars/public_html/app/code/core/Mage/Core/Block/Template.php(241): include('/home/madepars/...')
#23 /home/madepars/public_html/app/code/core/Mage/Core/Block/Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml/defau...')
#24 /home/madepars/public_html/app/code/core/Mage/Core/Block/Template.php(286): Mage_Core_Block_Template->renderView()
#25 /home/madepars/public_html/app/code/core/Mage/Adminhtml/Block/Template.php(81): Mage_Core_Block_Template->_toHtml()
#26 /home/madepars/public_html/app/code/core/Mage/Core/Block/Abstract.php(863): Mage_Adminhtml_Block_Template->_toHtml()
#27 /home/madepars/public_html/app/code/core/Mage/Core/Model/Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#28 /home/madepars/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(390): Mage_Core_Model_Layout->getOutput()
#29 /home/madepars/public_html/app/code/core/Mage/Adminhtml/controllers/Catalog/ProductController.php(269): Mage_Core_Controller_Varien_Action->renderLayout()
#30 /home/madepars/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Adminhtml_Catalog_ProductController->editAction()
#31 /home/madepars/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('edit')
#32 /home/madepars/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#33 /home/madepars/public_html/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#34 /home/madepars/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#35 /home/madepars/public_html/index.php(96): Mage::run('', 'store')
#36 {main}

Hello

Are there any additional error messages in your MySQL error log when this behavior occurs?

Thank you.

 

[thinkbot]

adjust

innodb_buffer_pool_size = 2500M
query_cache_limit=2M
query_cache_size=100M

max_user_connections=100
max_connections=300
key_buffer_size=750M
table_cache_size=6000

sort_buffer_size=256K
read_buffer_size=128K


slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.2


remove those below:
key_buffer (the same as key_buffer_size)
table_cache )the same as table_cache_size)
max_connect_errors=10
thread_concurrency=8


then restart

 

[razuk]

We have made changes in mysql configuration now except variable table_cache_size as it is not recognized in mysql configuration. We have set table_cache=6000 instead.

adjust

innodb_buffer_pool_size = 2500M
query_cache_limit=2M
query_cache_size=100M

max_user_connections=100
max_connections=300
key_buffer_size=750M
table_cache_size=6000

sort_buffer_size=256K
read_buffer_size=128K


slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.2


remove those below:
key_buffer (the same as key_buffer_size)
table_cache )the same as table_cache_size)
max_connect_errors=10
thread_concurrency=8


then restart

 

[thinkbot]

ahh yes, it's table_open_cache, not table_cache_size
it's the same as table_cache
except table_cache is deprecated in next mysql version

anyways, after several hours, 24h best please post mysqltuner.pl results again

 

[razuk]

Ok, i'll do it. Thank you.

ahh yes, it's table_open_cache, not table_cache_size
it's the same as table_cache
except table_cache is deprecated in next mysql version

anyways, after several hours, 24h best please post mysqltuner.pl results again

 

[razuk]

We got a new error on Magento:

SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Trace:
#0 /home/madepars/public_html/lib/Zend/Db/Adapter/Pdo/Mysql.php(96): Zend_Db_Adapter_Pdo_Abstract->_connect()
#1 /home/madepars/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(313): Zend_Db_Adapter_Pdo_Mysql->_connect()
#2 /home/madepars/public_html/lib/Zend/Db/Adapter/Abstract.php(459): Varien_Db_Adapter_Pdo_Mysql->_connect()
#3 /home/madepars/public_html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SET NAMES utf8', Array)
#4 /home/madepars/public_html/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SET NAMES utf8', Array)
#5 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource.php(169): Varien_Db_Adapter_Pdo_Mysql->query('SET NAMES utf8')
#6 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource.php(110): Mage_Core_Model_Resource->_newConnection('pdo_mysql', Object(Mage_Core_Model_Config_Element))
#7 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(320): Mage_Core_Model_Resource->getConnection('core_write')
#8 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(350): Mage_Core_Model_Resource_Db_Abstract->_getConnection('write')
#9 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(335): Mage_Core_Model_Resource_Db_Abstract->_getWriteAdapter()
#10 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(360): Mage_Core_Model_Resource_Db_Abstract->_getReadAdapter()
#11 /home/madepars/public_html/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(134): Mage_Core_Model_Resource_Db_Abstract->getReadConnection()
#12 /home/madepars/public_html/app/code/core/Mage/Core/Model/Config.php(1348): Mage_Core_Model_Resource_Db_Collection_Abstract->__construct(Object(Mage_Core_Model_Resource_Website))
#13 /home/madepars/public_html/app/code/core/Mage/Core/Model/Config.php(1380): Mage_Core_Model_Config->getModelInstance('core_resource/w...', Object(Mage_Core_Model_Resource_Website))
#14 /home/madepars/public_html/app/Mage.php(490): Mage_Core_Model_Config->getResourceModelInstance('core/website_co...', Object(Mage_Core_Model_Resource_Website))
#15 /home/madepars/public_html/app/code/core/Mage/Core/Model/Abstract.php(208): Mage::getResourceModel('core/website_co...', Object(Mage_Core_Model_Resource_Website))
#16 /home/madepars/public_html/app/code/core/Mage/Core/Model/Abstract.php(213): Mage_Core_Model_Abstract->getResourceCollection()
#17 /home/madepars/public_html/app/code/core/Mage/Core/Model/App.php(608): Mage_Core_Model_Abstract->getCollection()
#18 /home/madepars/public_html/app/code/core/Mage/Core/Model/App.php(466): Mage_Core_Model_App->_initStores()
#19 /home/madepars/public_html/app/code/core/Mage/Core/Model/App.php(349): Mage_Core_Model_App->_initCurrentStore('', 'store')
#20 /home/madepars/public_html/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#21 /home/madepars/public_html/index.php(96): Mage::run('', 'store')
#22 {main}

Ok, i'll do it. Thank you.

 

[thinkbot]

It means that Magento can't connect to MySQL server, this can happen when you turn off or restart MySQL

btw. please post mysqltuner.pl results

btw2. for server load monitoring it would be good if you install WHM munin plugin

 

[razuk]

Hello,

The munin plugin has been installed and i am monitoring it. After the mysql changes the magento errors are not occuring anymore, or at least we did not get it for a while. On the other hand there are a lot of people complaining about the slowness of the webmail (roundcube) and it started after the mysql new configuration. Thanks for the help.

Here is the report from Mysql tuner

-------- Performance Metrics --------------------- 

[--] Up for: 1d 1h 9m 18s (465M q [5K qps], 435K conn, TX: 436B, RX: 372B) 
[--] Reads / Writes: 96% / 4% 
[--] Total buffers: 3.3G global + 1.9M per thread (300 max threads) 
[OK] Maximum possible memory usage: 3.9G (12% of installed RAM) 
[OK] Slow queries: 0% (52K/465M) 
[OK] Highest usage of available connections: 44% (133/300) 
[OK] Key buffer size / total MyISAM indexes: 750.0M/105.5M 
[OK] Key buffer hit rate: 100.0% (3B cached / 1M reads) 
[OK] Query cache efficiency: 96.3% (445M cached / 462M selects) 
[!!] Query cache prunes per day: 1583676 
[OK] Sorts requiring temporary tables: 2% (56K temp sorts / 2M sorts) 
[!!] Joins performed without indexes: 234198 
[OK] Temporary tables created on disk: 12% (300K on disk / 2M total) 
[OK] Thread cache hit rate: 99% (134 created / 435K connections) 
[!!] Table cache hit rate: 0% (6K open / 7M opened) 
[OK] Open file limit used: 20% (10K/50K) 
[OK] Table locks acquired immediately: 99% (21M immediate / 21M locks) 
[OK] InnoDB data size / buffer pool: 2.0G/2.4G 

-------- Recommendations ------------------------- 
General recommendations: 
Run OPTIMIZE TABLE to defragment tables for better performance 
Adjust your join queries to always utilize indexes 
Increase table_cache gradually to avoid file descriptor limits 
Variables to adjust: 
query_cache_size (> 100M) 
join_buffer_size (> 1.0M, or always use indexes with joins) 
table_cache (> 6000)

It means that Magento can't connect to MySQL server, this can happen when you turn off or restart MySQL

btw. please post mysqltuner.pl results

btw2. for server load monitoring it would be good if you install WHM munin plugin

 

[bej]

Magento runs with mySQL (PDO)

Did you enable PDO_mysql extention?
You can use the tool WHM --> Software --> Easy Apache Update --> 5. Exhaustive Options List to enable it.

 

[razuk]

Yes, it is enable. Magento is running fine.

 

[razuk]

Should I follow these mysql tuner recommendations?

Yes, it is enable. Magento is running fine.

 

[rocksolid2]

adjust your my.cnf with the following

Change this line

#max_allowed_packet=16M

change to

max_allowed_packet=64M

 

[razuk]

It is already 512M.