1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Optimize my server

Discussion in 'Server Configuration and Hosting' started by FabioCesar, Feb 24, 2012.

  1. FabioCesar

    FabioCesar Member

    Please, I'm having problems with the database after the migration from VB to XF

    Running Cpanel/Whm
    Apache 2.0
    PHP 5.2.17
    Intel Xeon-Lynnfield 3460-Quadcore [2.8GHz]
    12gb DDR3
    Western Digital WD Caviar RE 16 WD2500YS [250GB] - SYSTEM
    Western Digital WD Caviar RE 16 WD2500YS [250GB] - MYSQL
    Western Digital WD Caviar RE 16 WD2500YS [250GB] - BACKUP
  2. Ghan_04

    Ghan_04 Active Member

    That does not sound good at all and is not something that I've ever seen before. Have you been running MySQL on this server without issue up to this point or is this a new install? It doesn't sound like this is a Xenforo issue. I'd be interested to see if it's possible to manually compile a MySQL build and see if it exhibits the same issues.
  3. FabioCesar

    FabioCesar Member

    This version of MySQL was already running at 4 months with no problems, I'm using MariaDB for better performance
  4. Adam Howard

    Adam Howard Well-Known Member

    Signal 11 typically means your database is corrupt and you should recover from a backup, if you do not know how to repair it. Or just as the error message suggest, some kind of hardware failure, in which case you should contact your host and assume corruption has taken place (as it's more than likely).

    I'd look into corruption 1st. Check your vBulletin database before you import. vBulletin depends heavily on Myisam, which is prone to easy corruption (debatable). Normally vBulletin or PhpMyAdmin can correct these errors. If they find any, chances are they got ported over as such.

    You could also do the same for XenForo, assuming it too is using Myisam via the import.

    I would suggest using Innodb. You can convert over. But do some reading on this first as I recall 4 tables in XenForo need to stay as Myisam (technically, they don't need to stay, but its recommended for speed).

    Assuming your database checks out. Hardware would be my next best guess.
  5. FabioCesar

    FabioCesar Member

    I think the problem is not the hardware, after 6 months using the same equipment in vbulletin, never had problems after 4 days of migration to the XF the database started to show errors

    According to the logs and even the XF is having memory overflow, which leads me to believe that the problem is setting.
  6. Brogan

    Brogan XenForo Moderator Staff Member

    What is the stack trace if you click on one of those errors?

    Essentially though, your server is out of physical memory and has no swap space left.
  7. FabioCesar

    FabioCesar Member

    Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Out of memory (Needed 8 bytes) - library/Zend/Db/Statement/Mysqli.php:214
    Gerado Por: Conta Desconhecida, Hoje às 14:41
    Rastreamento de Pilha
    #0 /home/xxx/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
    #1 /home/xxx/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
    #2 /home/xxx/public_html/library/Zend/Db/Adapter/Abstract.php(825): Zend_Db_Adapter_Abstract->query('????SELECT cach...', 1)
    #3 /home/xxx/public_html/library/XenForo/Model/User.php(810): Zend_Db_Adapter_Abstract->fetchOne('????SELECT cach...', 1)
    #4 /home/xxx/public_html/library/XenForo/Model/User.php(785): XenForo_Model_User->setPermissionsOnVisitorArray(Array)
    #5 /home/xxx/public_html/library/XenForo/Visitor.php(395): XenForo_Model_User->getVisitingGuestUser()
    #6 /home/xxx/public_html/library/XenForo/Session.php(204): XenForo_Visitor::setup(false, Array)
    #7 /home/xxx/public_html/library/XenForo/Controller.php(286): XenForo_Session::startPublicSession(Object(Zend_Controller_Request_Http))
    #8 /home/xxx/public_html/library/XenForo/Controller.php(298): XenForo_Controller->_setupSession('Trophies')
    #9 /home/xxx/public_html/library/XenForo/FrontController.php(309): XenForo_Controller->preDispatch('Trophies')
    #10 /home/xxx/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #11 /home/xxx/public_html/index.php(13): XenForo_FrontController->run()
    #12 {main}
    Solicitar Estado
    array(3) {
    ["url"] => string(56) "http://www.plusgsm.com.br/members/daviddiniz.45/trophies"
    ["_GET"] => array(0) {
    ["_POST"] => array(0) {
  8. EasyTarget

    EasyTarget Well-Known Member

    The max each query could use is 2.770GB

    Does the machine really have 12gb DDR3 available?

    There are a bunch of things in your configuration that have been tuned for MyISAM, I would go back and get the default mysql configurations, watch it for a few days and see how it does.
  9. FabioCesar

    FabioCesar Member

    My webserver is Dedicated Server by SoftLayer

    In any case, removed the Maridb and Mysql installed, let's see how it will behave
  10. FabioCesar

    FabioCesar Member

  11. EasyTarget

    EasyTarget Well-Known Member

    Three things which version of mysql are you running?
    And looking at your my.cnf file I don't see any of the InnoDB specific settings, are they set up?
    Are the Xenforo tables InnoDB or did you create them as MyISAM ?
  12. FabioCesar

    FabioCesar Member

    mysql version 5.1.61
    all tables are using MyISAM tables except the Memory

    To what extent recommend using innodb? I read several articles recommend using MyISAM for better performance.

    Currently my largest tables are:
    xf_user_field_value - 3,799,157 rows - 140,2mb
    archived_import_log - 1,880,307 rows - 60,6mb
    xf_ip - 1,566,589 rows - 125,9mb
    xf_post - 1,090,209 rows - 492,4mb
    xf_user - 475,075 rows - 77,9mb

    For the system to search the forum I'm using elasticsearch provided by Xenforo.com
  13. EasyTarget

    EasyTarget Well-Known Member

  14. FabioCesar

    FabioCesar Member

    what is making me think about moving to Innodb in this recommendation is tuningprimer
    Any recommendations on which table should be used to Innodb?
  15. Brogan

    Brogan XenForo Moderator Staff Member

  16. FabioCesar

    FabioCesar Member

    mysqltuner.pl 1.2.0 result
  17. EasyTarget

    EasyTarget Well-Known Member

    No. You have a bunch of tables
    [--] Data in InnoDB tables: 1G (Tables: 137)

    Unless that is some other database?

    However in your my.conf you have

    My guess is that is why you are getting,
    [!!] Key buffer hit rate: 94.6% (61K cached / 3K reads)
    [!!] Temporary tables created on disk: 36% (746 on disk / 2K total)

    You kind of need to go back to basics, configure for InnoDB and it will get better.

    Walter likes this.
  18. FabioCesar

    FabioCesar Member

    I forgot to tell that before posting the result of mysqltunner, was auditioning with innodb.

Share This Page