Every single night my site goes down between 3am to 6am and has gone down now for about 20 days in a row. It didn’t go down nearly as much when it had 4x the daily traffic that it does now.

Because the droplet goes down at night and I wake up quite a few hours later, my site is down for 4-8 hours. It restarts immediately when I cue sudo service mysql restart command through the console

I want to investigate and fix why my mysql keeps going down so frequently. Can someone please explain how do i go about this in a simple way?

I already have SWAP paritition too

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
4 answers

Hello, the website can down due to many factors, server errors, worpress configuration, themes or plugins updates and changes, firstly you should know the cause of this down, contact your webhosting provider and they will check if the problem is not from server, if not, use google pagespeed inshights and see what affects your website

Hello, @SamN

Could you please confirm that the MySQL service is the reason the site is experiencing the downtime issues? This will mean that every time you experience a downtime period your site will load with the “Error establishing database connection” error or similar depending from the error handling of your code or CMS.

You can always examine the MySQL error_log in order to track down any errors. On Ubuntu systems, the default location for the MySQL is /var/log/mysql/error.log

In many cases, the error logs are most easily read with the less program, a command line utility that allows you to view files but not edit them:

sudo less /var/log/mysql/error.log

When MySQL isn’t behaving as expected, you can obtain more information about the source of the trouble by running this command and diagnosing the error based on the log’s contents.

MySQL configuration

What you can also do is to use the MySQLTuner script.

The MySQLTuner is a script written in Perl and allows you to quickly test your MySQL configuration and it gives you suggestions for adjustments to increase performance and stability.

According to the official GitHub page, it supports 300 indicators for MySQL/MariaDB/Percona Server in this last version.

To run the script you could do the following:

  • SSH to your Droplet
  • Download the script:
wget http://mysqltuner.pl/ -O mysqltuner.pl
  • Then execute it:
perl mysqltuner.pl

The script would run multiple checks against your MySQL instance, all checks done by MySQLTuner are documented here.

Also as stated in the official documentation, it is still extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust.

As a good practice make sure to always test your changes on staging environments before implementing them on your production database.

On the same note, if you want to have a worry-free MySQL hosting and focus on your application, I would recommend trying out the DigitalOcean Managed Databases:

https://www.digitalocean.com/products/managed-databases-mysql/

This was mini tutorial was posted from @bobbyiliev in this question in our community: https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

You can also check our tutorials on How To Troubleshoot Issues in MySQL:

https://www.digitalocean.com/community/tutorial_series/how-to-troubleshoot-issues-in-mysql

Hope this helps!

Regards,
Alex

  • Thank you Alex. This is the mysql error log https://pastebin.com/euv2VnKx

    But I can’t tell much from it. Can you help me out?

    • Hello, @SamN

      You can see the following line in the error log:

      InnoDB: Cannot allocate memory for the buffer pool

      Which means that the droplet is running out of memory and it can not allocate enough memory for the buffer pool. You can either decrease the buffer pool size in the /etc/my.cnf file or increase the droplet’s RAM.

      I will recommend you to check my previous reply and execute the MySQL tuner script which will help you to tweak the configuration of the my.cnf file.

      Hope that helps!

      Regards,
      Alex

      • Hello Alex. I ran mysqltuner and got the following recommendations

        
        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)
            innodb_buffer_pool_size (>= 341M) if possible.
            innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
        
        

        How do I make the required changes in the my.cnf file? Most of the stuff is commented out. Which is the line to edit to decrease buffer pool size?

        • Hello, @SamN

          You need to edit the /etc/my.cnf file with any text editor like nano, vi or vim and adjust the values and then save the file and exit.

          open the file:

          nano /etc/my.cnf

          and then enter or adjust the valies for the innodb:

          innodb_buffer_pool_size = 341M
          innodb_log_file_size = 64M
          

          Hope that helps!

          Regards,
          Alex

          • Hello Alex. I made the changes and my server has stopped going down for hours like it used to. Thank you so much

            However, since the past 3 out of 4 days the server still goes down every night around the same time usually (3am-4am) but also comes back up by itself in 5 to 15 minutes. This has happened thrice now and I can’t figure out why.

            Also when I try to

            sudo less /var/log/mysql/error.log
            
            

            the output is empty

Hello, @SamN

You can check if the MySQL server is not logging it’s errors in other file:

mysql -e "show variables like 'log_error'"

Also you can still check for any InnoDB: Cannot allocate memory for the buffer pool references in the log as this will indicate that you need to proceed with a memory upgrade for the droplet.

Hope that this helps!

Regards,
Alex

  • Got the following output

    root@wordpress-nyc-1:~# mysql -e "show variables like 'log_error'"
    +---------------+--------------------------+
    | Variable_name | Value                    |
    +---------------+--------------------------+
    | log_error     | /var/log/mysql/error.log |
    +---------------+--------------------------+
    
    

    But both

    sudo less /var/log/mysql/error.log
    
    

    and

    less error.log (while being within /var/log/mysql/)
    
    

    return empty outputs

    • Hi

      You can double check if the err_log has the correct ownership and permission. The file should be owned by mysql:mysql, e.g

      ll /var/log/mysql/error.log
      -rw-r----- 1 mysql mysql 7727 Jul 15 11:05 /var/log/mysql/error.log
      

      If you’re getting different output you can chown the file and then restart the MySQL service:

      chown mysql:mysql /var/log/mysql/error.log
      systemctl restart mysql

      Hope that helps!

      Regards,
      Alex

      • Alright. It seemed to be an issue of ownership and permission and works now after chowning it.

        Plently of ‘InnoDB: Cannot allocate memory for the buffer pool’ in the mysql error log - pastebin

        So clearly I’m running out of memory. How do I diagnose whats causing my server to run out of memory though? Whether its a faulty plugin or apache or something else causing the issue instead of just upgrading the droplet?

        Because the same website has handled 5x the daily visitors at one point without crashing. So now its weird that 1/5th the load is making it crash.

        Also the site seems to be crashing only between certain particular times. Usually between 2am to 7am IST nearly almost every single time its crashed.

        I would like to find out whats making it crash before I upgrade it. How do I go about this?

        • Hi,

          Thanks for confirming this.

          I agree that it will be the best to know from where the memory usage is coming from before you go ahead and upgrade your droplet.

          I will recommend you to monitor the droplet’s processes using top/htop and see the running processes and their memory consumption. As you’ve mentioned this can come from a faulty plugin or just from the traffic itself. We’ve mentioned that the MySQL tunner script is a useful tool to adjust the buffer_pool_size and it usually recommends to have the pool_size to 50% of the available memory. The way it calculates it’s 2 times * 25% of the acutal RAM Size. However you can set it to 128M for the moment while troubleshooting.

          You check our existing tutorial on how to monitor the droplet’s resource usage:

          https://www.digitalocean.com/community/tutorials/how-to-use-top-netstat-du-other-tools-to-monitor-server-resources

          Hope that this helps!

          Regards,
          Alex

          by Justin Ellingwood
          Monitoring a server's resources is an important task for every administrator. There are many command line tools made for this purpose. This guide will cover some of the more popular utilities for monitoring your VPS.
          • Thank you Alex. I ran all those commands but everything seems to be alright under the hood. Nothing out of the usual. Not able to diagnose anything in particular causing the issue

Hi, @SamN

Thanks for getting back to me. The issue can and might be intermittent so this can only occur at different time of the day. What you can is also check the resource usage graphs in order to track the performance.

Droplet graphs are up-to-the minute visualizations of how your server is performing over time. They let you monitor Droplet performance metrics in the control panel.

Droplets come with some graphs available by default, and there are additional graphs available when you enable the free DigitalOcean Monitoring service.

You can check out the official docs here: https://www.digitalocean.com/docs/droplets/how-to/graphs/

Regards,
Alex

Submit an Answer