The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Workarounds and Optimization' started by razuk, Feb 18, 2014.

  1. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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:

    Code:
    [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
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    There has been an error processing your request

    Code:
    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}

     
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
    #4 thinkbot, Feb 19, 2014
    Last edited: Feb 19, 2014
  5. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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.

     
  6. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
  7. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Ok, i'll do it. Thank you.

     
  8. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    We got a new error on Magento:

    Code:
    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}
    
     
  9. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
  10. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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
    Code:
    -------- 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) 


     
  11. bej

    bej Registered

    Joined:
    Feb 27, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    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.
     
  12. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Yes, it is enable. Magento is running fine.
     
  13. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Should I follow these mysql tuner recommendations?

     
  14. rocksolid2

    rocksolid2 Registered

    Joined:
    Mar 10, 2014
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    adjust your my.cnf with the following

    Change this line
    Code:
    #max_allowed_packet=16M
    
    
    change to

    Code:
    max_allowed_packet=64M
     
  15. razuk

    razuk Member

    Joined:
    Feb 18, 2014
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    It is already 512M.
     
Loading...

Share This Page