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

razuk

February 18, 2014 08:11

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

Comments

14 comments

cPanelMichael

February 18, 2014 20:05
Hello :) Are there any additional error messages in your MySQL error log when this behavior occurs? Thank you.
0
razuk

February 18, 2014 21:41
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}
[quote="cPanelMichael, post: 1575962">Hello :) Are there any additional error messages in your MySQL error log when this behavior occurs? Thank you.
0
thinkbot

February 19, 2014 13:02
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
0
razuk

February 19, 2014 19:13
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. [quote="thinkbot, post: 1576272">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
0
thinkbot

February 19, 2014 20:54
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
0
razuk

February 20, 2014 11:29
Ok, i'll do it. Thank you. [quote="thinkbot, post: 1576912">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
0
razuk

February 21, 2014 13:24
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}
[quote="razuk, post: 1577131">Ok, i'll do it. Thank you.
0
thinkbot

February 21, 2014 14:11
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
0
razuk

February 27, 2014 17:16
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)
[quote="thinkbot, post: 1578182">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
0
bej

February 27, 2014 19:14
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.
0
razuk

February 27, 2014 19:31
Yes, it is enable. Magento is running fine.
0
razuk

February 28, 2014 12:12
Should I follow these mysql tuner recommendations? [quote="razuk, post: 1583922">Yes, it is enable. Magento is running fine.
0
rocksolid2

March 11, 2014 03:58
adjust your my.cnf with the following Change this line #max_allowed_packet=16M
change to max_allowed_packet=64M
0
razuk

March 11, 2014 19:33
It is already 512M.
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?