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

Database keep crushing after few hours.

Discussion in 'Web Hosting' started by roki4ka, Jun 24, 2017.

  1. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    Hello guys,

    I have migrated another website to a VPS from shared hosting due to insufficient resources.

    I have done this tens of times and for the 1st time, I face such problem.

    I have set the server, the site, everything. Imported the database and everything works great. After 2-16 hours the database crashes. Shows the message cannot connect to database. Website, emails, phpmyadmin, ftp... everything is unaccessable when this happens.

    The site is on WordPress, the wp-config is correct, the username of the VPS is different so the whole database name is different too and with this a little difference in the definitions in wp-config but it is set properly.

    When I restore the website from server end I see few tables that can't be repaired there (on some plugins) "The storage engine for the table doesn't support repair" But they show as OK. Anyway, I have removed the plugins but still, there are crashes.

    Please, if anyone knows any useful tips?
     
  2. Asif WILSON Khan

    Asif WILSON Khan Executive VIP Jr. VIP

    Joined:
    Nov 10, 2012
    Messages:
    11,464
    Likes Received:
    32,385
    Gender:
    Male
    Occupation:
    Fun Lovin' Criminal
    Location:
    London
    Home Page:
  3. HostStage

    HostStage Jr. VIP Jr. VIP

    Joined:
    May 20, 2010
    Messages:
    1,861
    Likes Received:
    1,766
    Occupation:
    BHW - CEO of Webhosting Company
    Location:
    BWH from France
    Home Page:
    Since you are on a VPS you do have a lot of leverage to optimize your VPS For your website.
    It would be hard to tell you upfront what the issue could be as there are multiple sources of issues.

    If you are comfortable with SSH, checking the .err MySQL file would be the best starting point.

    If managed VPS, reach out to your web hosting provider for them to optimize it all.
     
    • Thanks Thanks x 1
  4. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    It is self-managed VPS. I am managing my own VPS since 2015.

    I don't have super mega knowledge but I know the basics.

    I haven't contacted them for that reason.

    Yes, atm I have 13 tabs open with different forums and "solutions" and analyzing every single one of them.
     
  5. HostStage

    HostStage Jr. VIP Jr. VIP

    Joined:
    May 20, 2010
    Messages:
    1,861
    Likes Received:
    1,766
    Occupation:
    BHW - CEO of Webhosting Company
    Location:
    BWH from France
    Home Page:
    If you could paste the .err MySQL log file in here, I could check what the issue could be and give you hints.
     
    • Thanks Thanks x 1
  6. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    Ok 1 minute
     
  7. dub28

    dub28 Regular Member

    Joined:
    Sep 23, 2012
    Messages:
    209
    Likes Received:
    44
    Check your log file.

    Copy/paste the 100 last lines on pastebin and give us.
     
    • Thanks Thanks x 1
  8. pavan

    pavan Elite Member

    Joined:
    Mar 30, 2008
    Messages:
    1,819
    Likes Received:
    456
    seems like a server issue to me
    what are the server specs and your site traffic
    which host are you using ?

    meanwhile you can install wp super cache and reduce the number of sql queries to the database server
    its not a proper solution but its a temporary work around
     
    • Thanks Thanks x 1
  9. bartosimpsonio

    bartosimpsonio Jr. VIP Jr. VIP Premium Member

    Joined:
    Mar 21, 2013
    Messages:
    12,055
    Likes Received:
    10,833
    Occupation:
    WHEREZ MA
    Location:
    BITCOINS AT?
    Home Page:
    This can be due to low traffic. Called the "morning bug".
     
    • Thanks Thanks x 1
  10. loedown

    loedown Jr. VIP Jr. VIP

    Joined:
    Jun 29, 2009
    Messages:
    1,586
    Likes Received:
    496
    Home Page:
    You're probably using a Innodb database and the repair is for MyISM. Do you have a backup of the database that is not corrupt? Contact the provider on this one.
     
    • Thanks Thanks x 1
  11. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    I have WP super cache installed

    2gb ram 2.4ghz processor 40gb ssd @ time4vps

    Traffic is ~1500 unique visits a day with a lot more sessions and average session duration 15 min.

    Somehow i can't display the error log
     
  12. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    Yes the old database is on the shared hosting, still

    But I have deleted and imported the database several times.
     
  13. almanula

    almanula Registered Member

    Joined:
    Jun 5, 2016
    Messages:
    72
    Likes Received:
    16
    Gender:
    Male
    If you are not on Digital Ocean, enable SWAP Space. Most of the problems with mysql is due to memory. Another cause might be due to disk full... Use SSH and type the following.. df -h check to see if you have enough space on the disk
     
    • Thanks Thanks x 1
  14. SharkServers

    SharkServers Jr. VIP Jr. VIP

    Joined:
    Jun 29, 2014
    Messages:
    406
    Likes Received:
    194
    Occupation:
    Web Hosting
    Location:
    DMCA? Pff! www.SuckMyBallsDM.CA
    Home Page:
    Couple of possibilities:

    1) Disk full, although unlikely if you're able to restart MySQL and make it run for a couple of hours before crashing again;
    2) Max connections reached. This can be done due to bad coding or when being attacked. Check max_connections via phpMyAdmin, and increase if necessary and if server resources allow you to (via /etc/my.cnf);
    3) Other configuration issues, which I can't advise of without seeing the log file.
     
    • Thanks Thanks x 1
  15. malayguru

    malayguru Regular Member

    Joined:
    Oct 29, 2012
    Messages:
    268
    Likes Received:
    42
    Gender:
    Male
    Occupation:
    Entrepreneur
    Location:
    Singapore
    try to build a new WordPress site and see if the database for that new site will crash
    troubleshoot all the possible factors, could be your database has a problem, else your PHPMyAdmin is the root cause
     
    • Thanks Thanks x 1
  16. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    Wow guys I didn't expect so much help

    Thank you I will try to apply all your tips.

    Few recommendations from a member that saw the VPS

    Code:
    0624 15:51:37 [ERROR] mysqld: Table './admin_wp865/wphj_options' is marked as crashed and should be repaired
    170624 15:51:37 [ERROR] mysqld: Table './admin_wp865/wphj_options' is marked as crashed and should be repaired
    170624 15:51:37 [ERROR] mysqld: Table './admin_wp865/wphj_options' is marked as crashed and should be repaired
    170624 15:51:37 [ERROR] mysqld: Table './admin_wp865/wphj_options' is marked as crashed and should be repaired
    170624 15:51:37 [ERROR] mysqld: Table './admin_wp865/wphj_options' is marked as crashed and should be repaired
    170624 15:51:37 [Warning] Checking table:   './admin_wp865/wphj_options'
    170624 15:57:09 [ERROR] mysqld: Table './admin_wp865/wphj_404_to_301' is marked as crashed and should be repaired
    170624 15:57:09 [Warning] Checking table:   './admin_wp865/wphj_404_to_301'
    170624 15:57:09 [ERROR] mysqld: Table './admin_wp865/wphj_et_social_stats' is marked as crashed and should be repaired
    170624 15:57:09 [Warning] Checking table:   './admin_wp865/wphj_et_social_stats'
    170624 15:57:09 [ERROR] mysqld: Table './admin_wp865/wphj_minification_chart' is marked as crashed and should be repaired
    170624 15:57:09 [Warning] Checking table:   './admin_wp865/wphj_minification_chart'
    I have tried to repair these dozens of times but they can't be repaired

    Code:
    170624 11:26:12 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
    170624 11:26:12 [ERROR] mysqld: Out of memory (Needed 96681984 bytes)
    170624 11:26:12 [Note] Plugin 'FEEDBACK' is disabled.
    170624 11:26:13 [ERROR] Unknown/unsupported storage engine: InnoDB
    170624 11:26:13 [ERROR] Aborting
    And then crashes... This is the last crash. Very strange as this VPS has only 1 site hosted on it and I have ~150mb available

    These are the other recommendations, which I have to try and find how to correct:

    Code:
    General recommendations:
        Control warning line(s) into /var/log/mariadb/mariadb.log file
        Control error line(s) into /var/log/mariadb/mariadb.log file
        Remove Anonymous User accounts - there are 2 anonymous accounts.
        Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Set up a Secure Password for [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
        Restrict Host for user@% to [email protected]
        22 CVE(s) found for your MySQL release. Consider upgrading your version !
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries which have no LIMIT clause
        Set thread_cache_size to 4 as a starting value
        Increase table_open_cache gradually to avoid file descriptor limits
        Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
        Beware that open_files_limit (1024) variable
        should be greater than table_open_cache (64)
        Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
        Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Variables to adjust:
        query_cache_size (=0)
        query_cache_type (=0)
        query_cache_limit (> 1M, or use smaller result sets)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_open_cache (> 64)
        performance_schema = OFF disable PFS
        innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
     
  17. Gogol

    Gogol Jr. VIP Jr. VIP

    Joined:
    Sep 10, 2010
    Messages:
    3,410
    Likes Received:
    3,057
    Gender:
    Male
    Not sure if anyone has already mentioned, you might want to create a swap file as well if you don't have too much ram. Most of the time for me, that is the root of the problems.
     
  18. headspace

    headspace Newbie

    Joined:
    Jun 20, 2017
    Messages:
    44
    Likes Received:
    5
    Gender:
    Male
    Home Page:
    Did this get resolved? Im interested to know what worked.
     
  19. roki4ka

    roki4ka Senior Member

    Joined:
    Jun 20, 2016
    Messages:
    869
    Likes Received:
    636
    I havent touched much settings, nor have created swap file, it hasn't crashed for 8 hours now, but need to wait more time.
     
  20. SharkServers

    SharkServers Jr. VIP Jr. VIP

    Joined:
    Jun 29, 2014
    Messages:
    406
    Likes Received:
    194
    Occupation:
    Web Hosting
    Location:
    DMCA? Pff! www.SuckMyBallsDM.CA
    Home Page:
    With regards to MySQL running out of memory, a couple of adjustments to my.cnf could help. Depending on how much RAM your VPS has, try replacing your /etc/my.cnf with one from /usr/share/mysql - there should be multiple template files (such as my-medium.cnf, my-huge.cnf, etc) with each pre-configured for servers with different amount of resources.