semipro
By:
semipro

Error Establishing a database connection issues

June 16, 2017 770 views
Apache Ubuntu 16.04

I'm relatively new to using digital ocean and VPS hosting in general. I recently used the "how to debug Wordpress" tutorial and based on the results, I'm running out of memory.

I don't see how that's possible. It's a single page website with no traffic. The server has 1g of memory and 30gb of disk space. Furthermore, I have multiple websites running on the 5$ version of DigitalOcean that don't run into this issue even though they have many pages and receive some traffic.

Is there any way for me to see what's eating up all my memory?

Also, I followed the instruction on how to turn off XML-RPC so that won't be the cause.

6 Answers

Hi @semipro

Try installing htop which gives you detailed information about all processes real-time:

sudo apt install htop

Is the site on the $10 using the same theme and plugins as the $5 sites? A single heavy or badly coded theme/plugin can even make a $80 droplet go to a crawl.

  • I'm using a premium theme. It's one of the most widely used premium themes for wordpress. I only have a few plugins and they are all popular and widely used such as Yoast.

    I'm looking at htop now and it doesn't seem like I'm anywhere near maxing out on my memory. The highest I've seen memory usage go is 500m.

    The only real difference between this site and the others is I'm using cloudflare and https.

    Also, since my first post I've run a repair on the database which had no effect.

    • @semipro
      So you're currently at 500MB RAM usage with everything running?
      Are you using Let's Encrypt or just the CloudFlare HTTPS?
      You can post the last 40 lines of the MySQL error log, which should help us:

      tail -40 /var/log/mysql/error.log
      
      • Yes, it seems to sit slightly under 500M on average.

        I'm just using cloudflare and their free SSL.

        For some reason I can't submit the post with the full results of the command

        • 2017-06-16T23:51:57.714406Z 0 [Note] Shutting down plugin 'binlog'
          2017-06-16T23:51:57.715326Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

          2017-06-16T23:52:15.703712Z 0 [Warning] Changed limits: maxopenfiles: 1024 (requested 5000)
          2017-06-16T23:52:15.703802Z 0 [Warning] Changed limits: tableopencache: 431 (requested 2000)
          2017-06-16T23:52:15.917384Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicitdefaultsfortimestamp server option (see documentation for more details).
          2017-06-16T23:52:15.920400Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-0ubuntu0.16.04.1) starting as process 15437 ...
          2017-06-16T23:52:15.928930Z 0 [Note] InnoDB: PUNCH HOLE support available
          2017-06-16T23:52:15.929031Z 0 [Note] InnoDB: Mutexes and rw
          locks use GCC atomic builtins
          2017-06-16T23:52:15.929041Z 0 [Note] InnoDB: Uses event mutexes
          2017-06-16T23:52:15.929049Z 0 [Note] InnoDB: GCC builtin _atomicthread_fence() is used for memory barrier
          2017-06-16T23:52:15.929060Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
          2017-06-16T23:52:15.929073Z 0 [Note] InnoDB: Using Linux native AIO
          2017-06-16T23:52:15.929771Z 0 [Note] InnoDB: Number of pools: 1
          2017-06-16T23:52:15.930044Z 0 [Note] InnoDB: Using CPU crc32 instructions
          2017-06-16T23:52:15.933879Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
          2017-06-16T23:52:15.949695Z 0 [Note] InnoDB: Completed initialization of buffer pool

          • 2017-06-16T23:52:15.954142Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
            2017-06-16T23:52:15.971243Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
            2017-06-16T23:52:16.000591Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
            2017-06-16T23:52:16.000712Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
            2017-06-16T23:52:16.047715Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
            2017-06-16T23:52:16.048794Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
            2017-06-16T23:52:16.048815Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
            2017-06-16T23:52:16.050974Z 0 [Note] InnoDB: Waiting for purge to start
            2017-06-16T23:52:16.101364Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 91615013
            2017-06-16T23:52:16.105239Z 0 [Note] Plugin 'FEDERATED' is disabled.
            2017-06-16T23:52:16.107033Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ibbufferpool
            2017-06-16T23:52:16.120995Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170616 23:52:16
            2017-06-16T23:52:16.125127Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
            2017-06-16T23:52:16.125160Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
            2017-06-16T23:52:16.125201Z 0 [Note] - '127.0.0.1' resolves to '127.0.0.1';
            2017-06-16T23:52:16.125259Z 0 [Note] Server socket created on IP: '127.0.0.1'.
            2017-06-16T23:52:16.138347Z 0 [Note] Event Scheduler: Loaded 0 events
            2017-06-16T23:52:16.138596Z 0 [Note] /usr/sbin/mysqld: ready for connections.
            Version: '5.7.18-0ubuntu0.16.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

Watching htop has been helpful. Occasionally I will see spikes in memory and CPU usage to 100%. Unfortunately, I have no idea what I'm looking at or should be looking for.

  • @semipro Great, we're getting somewhere.
    The green row at the top of the list, starting with PID, is actually columns, which can be clicked to sort (by default it's sorted by CPU usage).
    The list will show all processes sorted by whatever column you've selected. So you have to look at the list, when it spikes, to see which process is peaking.

And finally the last few lines of the command are below. Sorry I would not allow me to place it all in one post.

2017-06-16T23:52:16.138611Z 0 [Note] Executing 'SELECT * FROM
2017-06-16T23:52:16.138616Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-06-16T23:52:16.172504Z 0 [Note] End of list of non-natively partitioned tables
2017-06-16T23:52:16.707901Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)

  • @semipro
    Okay, so MySQL hasn't crashed recently - or it's outside the log. You could have everything within one comment if you used the code button </> in the toolbar.

I've been monitoring htop for 15 minutes now and haven't noticed any more spikes. If I go to my homepage the CPU quickly jumps to 20% - 40% before going back to 1%. The memory goes up to just under 600M.

Currently everything is running fine with no database errors but I'm certain it will occur again.

What's using the most memory is /usr/sbin/mysqld at about a constant 17.5%.

/usr/sbin/apache2 -k start at 10%

and then /usr/lib/snapd/snapd and /usr/bin/python3 /usr/bin/fail2ban-server -s /var/run/fail2ban/fail2ban.sock -p /var/run/fail2ban/fail2ban.pid -x -b are both using 1-2%

  • @semipro
    Okay, that's weird. It's normal that the CPU spikes, when you walk around the website, since WordPress is a pretty big framework and loads on each page view.
    The most interesting thing would be to track whatever that suddenly eats all the RAM, since that's definitely not normal.
    From the log you posted before, it seems like MySQL's database should keep below 128MB RAM (plus a little overhead cache/etc, so something like 150-200MB) if the tables loaded in WordPress are InnoDB.

    • Okay, I'll leave htop open and if/when my website has the database error again I will report my findings back here. It seems to go through cycles. I first noticed this issue 2 days ago. Thought I fixed it and then it began happening again yesterday. Rinse and repeat until I made this post.

      Also, I have another site hosted on digital ocean running the exact same setup except it's not with CloudFlare. The memory usage is nearly 25% lower on average. It has the same theme and mostly the same plugins.

      Thanks for all the help and patience.

      • @semipro
        Any specific time? It could be a task in the crontab - or if your website is being indexes by bad spiders (where they hit every page on your site at the same time).

        • I'll make a note of the times but I've noticed it in the evening and in the morning.

          I don't understand why this site is running 150-250 higher on memory than the other.

          I'm going to begin disabling plugins and continue to monitor.

@semipro
Install htop on the other server as well. It doesn't cost any RAM or CPU, when it's not running. Then look at the differences in like Apache, MySQL, etc.

Are you running the same version of Ubuntu, Apache, PHP and MySQL on both servers?

When MySQL crashes, then before starting it again, run tail -50 /var/log/mysql/error.log

Hey, everyone, I'm back. So basically I'm still ocasionally running into the same problem. I have five different digital ocean servers. Three of them run perfectly fine on $5 servers with more traffic and web pages. There's absolutely no reason this $10 server should be running out of memory.

I've been routinely monitoring htop and at no point does my memory reach the upper limits. However, for some reason, my error log is showing my server as running out of memory.

I even went so far as to make a snapshot of my data and create a new droplet, put that snapshot on a new server. This hasn't made things any better.

It has to be one of my plugins or somehow my database is messed up. I'm considering making a new droplet and recreating my website from the ground up. It's only a few pages so it won't take too long. However, I don't want to have to resort to this.

Have another answer? Share your knowledge.