Database keep crushing after few hours.

roki4ka

roki4ka

Senior Member
Joined
Jun 20, 2016
Messages
954
Reaction score
743
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?
 
Advertise on BHW
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.
 
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.
 
If you could paste the .err MySQL log file in here, I could check what the issue could be and give you hints.
 
Check your log file.

Copy/paste the 100 last lines on pastebin and give us.
 
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
 
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.
 
pavan said:
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
Click to expand...

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
 
loedown said:
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.
Click to expand...

Yes the old database is on the shared hosting, still

But I have deleted and imported the database several times.
 
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
 
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.
 
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
 
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 user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    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.
 
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.
 
I havent touched much settings, nor have created swap file, it hasn't crashed for 8 hours now, but need to wait more time.
 
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.
 
Advertise on BHW
You must log in or register to reply here.
Sign up now!

Main Menu

Home Main Forum List Black Hat SEO White Hat SEO BHW Newbie Guide Blogging Black Hat Tools Social Media Downloads

Marketplace

Account Selling Content / Copywriting EBooks / Methods Hosting Misc Proxies For Sale SEO - Link building SEO - Packages Social Media Social Media - Panels Web Design More

Making Money

Affiliate Programs Hire a Freelancer IM Journeys Making Money Pay Per Click Site Flipping Want to Buy

BlackHat World

Dispute Resolution Forum Suggestions & Feedback Introductions Lounge
Back
Top