on

How to backup and restore a MySQL database on Windows and Linux

April 21, 2019 by Simon

Why backup and restore

This is a quick guide demonstrating how you can backup and restore a MySQL database on Windows and Linux using Adminer.

You may need to know how to backup a restore a database for a number of reasons..

e.g

Send the database to someone to debug or give feedback while learning.
Move the database from a local machine to the cloud
Move the database from cloud vendor A to cloud vendor B
etc.

Having a backup of the VM is good but having a backup of the database too is better. I use UpCloud for hosting my VM’s and setting backups is easy. But I cannot download those backups.

Murphy’s Law

“If anything can go wrong, it will”

The most important reason for taking a backup and knowing how to restore it is for disaster recovery reasons.

Backup (the easiest way) with Adminer

Adminer is a free PHP based IDE for MySQL and other databases. Simply install Adminer and save the file on your local computer or remote web server directory.

FYI: The Adminer author Jakub Vrana has a patron page, I am a patron of this awesome software.

Snip from Adminers website. “Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consist of a single file ready to deploy to the target server. Adminer is available for MySQL, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, Firebird, SimpleDB, Elasticsearch andMongoDB.”

TIP: The file would be publicly accessible to anyone so don’t save it to a common area, obfuscate the file, protect it of delete the file when you are done using it.

Once Adminer is installed load it in a web browser, login with your MySQL credentials. Once you login you will see all databases and an Import and Export menu.

Adminer main screen, all databases and import and export menu.

tbtest is a simple database with one table and 4 fields (ID, Key, Value and Modified)

.Click Export to open the export screen.

Export screen showing a list of databases and export options

Click Export, a SQL file will be generated (this is the export of the database).

Here is a save of the file:
https://fearby.com/wp-content/uploads/export.txt

Exported view of https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-management.html

Its that simple.

If I add a binary blob file to the table and upload a PNG file lets see how the export looks.

Screenshot o the new table with a blog field in Adminer UI

Let export the database again in Adminer and check out the output. I used Sublime Text editor to view the export file.

New Export shows the binary file in the Backup SQL file

Restore (the easiest way) with Adminer

OK lets delete the tbtest database and then restore it with Adminer. I used Adminer to delete (DROP) the database.

Database “dbtest” deleted.

Now lets create a blank database to restore to (same name).

Database created.

Now lets import the database backup using Adminer.

Click Import, select the backup file and un-tick Stop on errors.

Import screenshot, dxtest selectded, Restore file selected, stop on errors disabled

TIP: The 2MB next the the choose file button is defined by your web server and PHP configuration. If you are trying to import a larger database (e.g 80MB) first increase the limits in your web server and PHP (via php.ini).

The Import (restore should take seconds)

The database was imported from a backup, all tables and records imported just fine.

Bonus methods.

On Ubuntu use this guide to backup from the command line. If you use the Oracle MySQL Workbench read this.

I hope this helps someone.

How to install PHP 7.2.latest on Ubuntu

November 17, 2018 by Simon

Here is how you can update to PHP 7.2.12 on Ubuntu. PHP has a support page that declares the support date ranges and support types: http://php.net/supported-versions.php

How to install PHP 7.2.latest on Ubuntu 16.04

November 17, 2018 by Simon

How to install PHP 7.2.latest on Ubuntu 16.04/ Ubuntu 18.04/Debian etc/

I have a number of guides on moving away from CPanel, Setting up VM’s on UpCloud, AWS, Vultr or Digital Ocean along with installing and managing WordPress from the command line. PHP is my programming language of choice.

PHP has a support page that declares the support date ranges and support types: http://php.net/supported-versions.php

A version of PHP is either actively supported, security fix supported or end of life. Read this post to check WordPress for PHP compatibility.

From time to time vulnerabilities come up that require PHP updates to be applied.

Multiple flaw found in #PHP, most severe of which could allow arbitrary code execution
Affected Versions:
PHP 7.2 —prior to 7.2.5
PHP 7.1 —prior to 7.1.17
PHP 7.0 —prior to 7.0.30
PHP 5.0 —prior to 5.6.36https://t.co/TtiqXePoHu
Upgrade to the latest version of PHP immediately
— The Hacker News (@TheHackersNews) May 1, 2018

#PHP 7.2.12 has been released https://t.co/iNXGYTs0PX
— Neustradamus (@neustradamus) November 9, 2018

Source Link here

I have guides on setting up PHP 7 here on Digital Ocean, here on AWS and here on Vultr. I have tried upgrading to PHP 7.1 in the past with no luck (I forgot to change something and rolled back to 7.0).

FYI: I have a guide on setting up PHP child workers so the output from some commands below may be different than yours. Here are the steps I performed to install PHP 7.2 alongside 7.0 then switch. to 7.2.

Backup your system

Do perform a Snapshot or Backup before proceeding. Nothing beats a quick restore if things fail.

Note: Use this information at your own risk.

Updating php 7.2.12 to 7.2.12

Update your Ubuntu systems

apt-get update && apt-get upgrade

1	apt-get update && apt-get upgrade

Updating from an older php (e.g 5.x, 7.1, 7.1 to say 7.2.12)

Backup PHP

cd /etc/php
zip -r php7.0backup.zip 7.0/

1 2	cd /etc/php zip -r php7.0backup.zip 7.0/

Install Helper

This software provides an abstraction of the used apt repositories. It allows you to easily manage your distribution and independent software vendor software sources. More Info

apt-get install python-software-properties

1	apt-get install python-software-properties

Add the main PHP repo (more information)

add-apt-repository ppa:ondrej/php

1	add-apt-repository ppa:ondrej/php

Update the package lists

“In a nutshell, apt-get update doesn’t actually install new versions of the software. Instead, it updates the package lists for upgrades for packages that need upgrading, as well as new packages that have just come to the repositories.” from here

apt-get update

1	apt-get update

List Installed Packages (optional)

dpkg -l

dpkg -l

Install PHP 7.2

apt-get install php7.2

1	apt-get install php7.2

Install common PHP modules

apt-get install php-pear php7.2-curl php7.2-dev php7.2-mbstring php7.2-zip php7.2-mysql php7.2-xml

1	apt-get install php-pear php7.2-curl php7.2-dev php7.2-mbstring php7.2-zip php7.2-mysql php7.2-xml

Install PHP FPM

apt-get install php7.2-fpm

1	apt-get install php7.2-fpm

Update all packages (may be needed to update from php 7.2.4 to 7.2.5)

sudo apt-get upgrade

1	sudo apt-get upgrade

Edit your NGINX sites-available config

sudo nano /etc/nginx/sites-available/default
# I set: fastcgi_pass /run/php/php7.2-fpm.sock;

1 2	sudo nano /etc/nginx/sites-available/default # I set: fastcgi_pass /run/php/php7.2-fpm.sock;

Edit your NGINX sites-enabled config

sudo nano /etc/nginx/sites-enabled/default
# I set: fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;

1 2	sudo nano /etc/nginx/sites-enabled/default # I set: fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;

I edited these lines

location ~ \.php$ {
    ...
    fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;
    ...
}

location ~ \.php$ {

...

fastcgi_pass unix:/var/run/php/php7.2-fpm.sock;

...

}

Edit your PHP config (and make desired changes)

sudo nano /etc/php/7.2/fpm/php.ini

1	sudo nano /etc/php/7.2/fpm/php.ini

Edit your PHP pool config file (as required). See this guide here.

e.g.

> cgi.fix_pathinfo=0
> max_input_vars = 1000
> memory_limit = 1024M
> max_file_uploads = 8M
> post_max_size = 8M

sudo nano /etc/php/7.2/fpm/pool.d/www.conf

1	sudo nano /etc/php/7.2/fpm/pool.d/www.conf

Make sure you set: listen = /run/php/php7.2-fpm.sock

Set PHP 7.2 as the default PHP

update-alternatives --set php /usr/bin/php7.2

1	update-alternatives --set php /usr/bin/php7.2

Check your PHP version

php -v

php -v

Reload PHP

sudo service php7.2-fpm reload

1	sudo service php7.2-fpm reload

Reload NGINX

nginx -t
nginx -s reload
/etc/init.d/nginx restart

nginx -t

nginx -s reload

/etc/init.d/nginx restart

Check the status of your PHP (and child workers)

sudo service php7.2-fpm status
● php7.2-fpm.service - The PHP 7.2 FastCGI Process Manager
   Loaded: loaded (/lib/systemd/system/php7.2-fpm.service; enabled; vendor preset: enabled)
   Active: active (running) since Fri 2018-05-04 19:02:27 AEST;
     Docs: man:php-fpm7.2(8)
  Process: 123456 ExecReload=/bin/kill -USR2 $MAINPID (code=exited, status=0/SUCCESS)
 Main PID: 123456 (php-fpm7.2)
   Status: "Processes active: 0, idle: 10, Requests: 0, slow: 0, Traffic: 0req/sec"
    Tasks: 11
   Memory: 30.5M
      CPU: 10.678s
   CGroup: /system.slice/php7.2-fpm.service
           ├─16494 php-fpm: master process (/etc/php/7.2/fpm/php-fpm.conf)
           ├─16497 php-fpm: pool www
           ├─16498 php-fpm: pool www
           ├─16499 php-fpm: pool www
           ├─16500 php-fpm: pool www
           ├─16501 php-fpm: pool www
           ├─16502 php-fpm: pool www
           ├─16503 php-fpm: pool www
           ├─16504 php-fpm: pool www
           ├─16505 php-fpm: pool www
           └─16506 php-fpm: pool www

sudo service php7.2-fpm status

● php7.2-fpm.service - The PHP 7.2 FastCGI Process Manager

Loaded: loaded (/lib/systemd/system/php7.2-fpm.service; enabled; vendor preset: enabled)

Active: active (running) since Fri 2018-05-04 19:02:27 AEST;

Docs: man:php-fpm7.2(8)

Process: 123456 ExecReload=/bin/kill -USR2 $MAINPID (code=exited, status=0/SUCCESS)

Main PID: 123456 (php-fpm7.2)

Status: "Processes active: 0, idle: 10, Requests: 0, slow: 0, Traffic: 0req/sec"

Tasks: 11

Memory: 30.5M

CPU: 10.678s

CGroup: /system.slice/php7.2-fpm.service

├─16494 php-fpm: master process (/etc/php/7.2/fpm/php-fpm.conf)

├─16497 php-fpm: pool www

├─16498 php-fpm: pool www

├─16499 php-fpm: pool www

├─16500 php-fpm: pool www

├─16501 php-fpm: pool www

├─16502 php-fpm: pool www

├─16503 php-fpm: pool www

├─16504 php-fpm: pool www

├─16505 php-fpm: pool www

└─16506 php-fpm: pool www

Check your website.

Troubleshooting

Guides that helped me.

https://thishosting.rocks/install-php-on-ubuntu/

https://websiteforstudents.com/wordpress-supports-php-7-2-heres-how-to-install-with-nginx-and-mariadb-support/

Check your log files

tail /var/log/nginx/error.log

1	tail /var/log/nginx/error.log

Debug FPM Service

systemctl status php7.2-fpm.service
● php7.2-fpm.service - The PHP 7.2 FastCGI Process Manager
   Loaded: loaded (/lib/systemd/system/php7.2-fpm.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2018-05-06 00:18:55 AEST; 7min ago
     Docs: man:php-fpm7.2(8)
  Process: 123456 ExecReload=/bin/kill -USR2 $MAINPID (code=exited, status=0/SUCCESS)
 Main PID: 123 (php-fpm7.2)
   Status: "Processes active: 0, idle: 10, Requests: 44, slow: 0, Traffic: 0req/sec"
    Tasks: 11
   Memory: 212.6M
      CPU: 12.052s
   CGroup: /system.slice/php7.2-fpm.service
           ├─438 php-fpm: master process (/etc/php/7.2/fpm/php-fpm.conf)
           ├─441 php-fpm: pool www
           ├─442 php-fpm: pool www
           ├─443 php-fpm: pool www
           ├─444 php-fpm: pool www
           ├─445 php-fpm: pool www
           ├─446 php-fpm: pool www
           ├─447 php-fpm: pool www
           ├─449 php-fpm: pool www
           ├─450 php-fpm: pool www
           └─451 php-fpm: pool www

May 06 00:18:55 server systemd[1]: Stopped The PHP 7.2 FastCGI Process Manager.
May 06 00:18:55 server systemd[1]: Starting The PHP 7.2 FastCGI Process Manager...
May 06 00:18:55 server systemd[1]: Started The PHP 7.2 FastCGI Process Manager.

systemctl status php7.2-fpm.service

● php7.2-fpm.service - The PHP 7.2 FastCGI Process Manager

Loaded: loaded (/lib/systemd/system/php7.2-fpm.service; enabled; vendor preset: enabled)

Active: active (running) since Sun 2018-05-06 00:18:55 AEST; 7min ago

Docs: man:php-fpm7.2(8)

Process: 123456 ExecReload=/bin/kill -USR2 $MAINPID (code=exited, status=0/SUCCESS)

Main PID: 123 (php-fpm7.2)

Status: "Processes active: 0, idle: 10, Requests: 44, slow: 0, Traffic: 0req/sec"

Tasks: 11

Memory: 212.6M

CPU: 12.052s

CGroup: /system.slice/php7.2-fpm.service

├─438 php-fpm: master process (/etc/php/7.2/fpm/php-fpm.conf)

├─441 php-fpm: pool www

├─442 php-fpm: pool www

├─443 php-fpm: pool www

├─444 php-fpm: pool www

├─445 php-fpm: pool www

├─446 php-fpm: pool www

├─447 php-fpm: pool www

├─449 php-fpm: pool www

├─450 php-fpm: pool www

└─451 php-fpm: pool www

May 06 00:18:55 server systemd[1]: Stopped The PHP 7.2 FastCGI Process Manager.

May 06 00:18:55 server systemd[1]: Starting The PHP 7.2 FastCGI Process Manager...

May 06 00:18:55 server systemd[1]: Started The PHP 7.2 FastCGI Process Manager.

Remove PHP 7.0

sudo apt-get purge php7.0-common
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libaspell15 libauthen-pam-perl libc-client2007e libio-pty-perl libmcrypt4 librecode0 libtidy-0.99-0 libxmlrpc-epi0 linux-headers-4.4.0-109
  linux-headers-4.4.0-109-generic linux-headers-4.4.0-112 linux-headers-4.4.0-112-generic linux-headers-4.4.0-87
  linux-headers-4.4.0-87-generic linux-headers-4.4.0-96 linux-headers-4.4.0-96-generic linux-image-4.4.0-109-generic
  linux-image-4.4.0-112-generic linux-image-4.4.0-87-generic linux-image-4.4.0-96-generic linux-image-extra-4.4.0-109-generic
  linux-image-extra-4.4.0-112-generic linux-image-extra-4.4.0-87-generic linux-image-extra-4.4.0-96-generic mlock
Use 'sudo apt autoremove' to remove them.
The following packages will be REMOVED:
  php7.0-cli* php7.0-common* php7.0-curl* php7.0-fpm* php7.0-gd* php7.0-imap* php7.0-intl* php7.0-json* php7.0-mbstring* php7.0-mcrypt*
  php7.0-mysql* php7.0-opcache* php7.0-pspell* php7.0-readline* php7.0-recode* php7.0-sqlite3* php7.0-tidy* php7.0-xml* php7.0-xmlrpc*
  php7.0-xsl*

sudo apt-get purge php7.0-common

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following packages were automatically installed and are no longer required:

libaspell15 libauthen-pam-perl libc-client2007e libio-pty-perl libmcrypt4 librecode0 libtidy-0.99-0 libxmlrpc-epi0 linux-headers-4.4.0-109

linux-headers-4.4.0-109-generic linux-headers-4.4.0-112 linux-headers-4.4.0-112-generic linux-headers-4.4.0-87

linux-headers-4.4.0-87-generic linux-headers-4.4.0-96 linux-headers-4.4.0-96-generic linux-image-4.4.0-109-generic

linux-image-4.4.0-112-generic linux-image-4.4.0-87-generic linux-image-4.4.0-96-generic linux-image-extra-4.4.0-109-generic

linux-image-extra-4.4.0-112-generic linux-image-extra-4.4.0-87-generic linux-image-extra-4.4.0-96-generic mlock

Use 'sudo apt autoremove' to remove them.

The following packages will be REMOVED:

php7.0-cli* php7.0-common* php7.0-curl* php7.0-fpm* php7.0-gd* php7.0-imap* php7.0-intl* php7.0-json* php7.0-mbstring* php7.0-mcrypt*

php7.0-mysql* php7.0-opcache* php7.0-pspell* php7.0-readline* php7.0-recode* php7.0-sqlite3* php7.0-tidy* php7.0-xml* php7.0-xmlrpc*

php7.0-xsl*

PHP 7.0 Removed 🙂

Remove other unused packages

sudo apt autoremove

1	sudo apt autoremove

At the time of writing (November the 18th 2018) PHP 7.2.12 is the latest version of PHP and PHP 7.3 will be out at the end of the year.

Good luck and I hope this guide helps someone

Ask a question or recommend an article

Revision History

v1.4 Updated the post to mention PHP 7.0 EOL

v1.3 Updated to add PHP 7.2.12 information

v1.2 PHP 7.2.9 and PHP 7.2 updates

v1.1 Remove PHP 7.0 steps

v1.0 Initial post

Setup two factor authenticator protection at login on Ubuntu or Debian

October 14, 2018 by Simon

This is a quick post that shows how I set up two-factor authenticator protection at login on Ubuntu or Debian

Aside

If you have not read my previous posts I have now moved my blog to the awesome UpCloud host (signup using this link to get $25 free UpCloud VM credit). I compared Digital Ocean, Vultr and UpCloud Disk IO here and UpCloud came out on top by a long way (read the blog post here). Here is my blog post on moving from Vultr to UpCloud.

Buy a domain name here

Now on with the post.

Backup

I ensured I had a backup of my server. This is easy to do on UpCloud. If something goes wrong I will rollback.

Why Setup 2FA on SSH connections

1) Firewalls or whitelists may not protect you from detection.

2) SSH authorisation bypass bugs may appear.

I’ve just relased libssh 0.8.4 and 0.7.6 to address CVE-2018-10933. This is an auth bypass in the server. Please update as soon as possible! https://t.co/Qhra2TXqzm
— Andreas Schneider (@cryptomilk) October 16, 2018

2FA authorisation is another lube of defence.

Yubico Yubi Key

Read my block post here to learn how to use the Yubico YubiKey NEO hardware-based two-factor authentication device to improve authentication and logins to OSX and software

Timezone

It is important that you set the same timezone as the server you are trying to secure two 2FA. I can run this command on Linux to set the timezone.

dpkg-reconfigure tzdata

1	dpkg-reconfigure tzdata

Check the time command

sudo hwclock --show

1	sudo hwclock --show

Output

2018-10-14 20:34:02.994867+1100

Install Google Authenticator

sudo apt install libpam-google-authenticator

1	sudo apt install libpam-google-authenticator

Output

> Reading package lists…
> Building dependency tree
> Reading state information… Done
> The following additional packages will be installed:
> libqrencode3
> The following NEW packages will be installed:
> libpam-google-authenticator libqrencode3
> 0 upgraded, 2 newly installed, 0 to remove and 11 not upgraded.
> Need to get 56.8 kB of archives.
> After this operation, 183 kB of additional disk space will be used.
> Do you want to continue? [Y/n] y
> Get:1 http://us.archive.ubuntu.com/ubuntu bionic/universe amd64 libqrencode3 amd64 3.4.4-1build1 [23.9 kB]
> Get:2 http://us.archive.ubuntu.com/ubuntu bionic/universe amd64 libpam-google-authenticator amd64 20170702-1 [32.9 kB]
> Fetched 56.8 kB in 0s (426 kB/s)
> Selecting previously unselected package libqrencode3:amd64.
> (Reading database … 122554 files and directories currently installed.)
> Preparing to unpack …/libqrencode3_3.4.4-1build1_amd64.deb …
> Unpacking libqrencode3:amd64 (3.4.4-1build1) …
> Selecting previously unselected package libpam-google-authenticator.
> Preparing to unpack …/libpam-google-authenticator_20170702-1_amd64.deb …
> Unpacking libpam-google-authenticator (20170702-1) …
> Setting up libqrencode3:amd64 (3.4.4-1build1) …
> Processing triggers for libc-bin (2.27-3ubuntu1) …
> Processing triggers for man-db (2.8.3-2) …
> Setting up libpam-google-authenticator (20170702-1) …

Configure Google Authenticator

Run google-authenticator and answer the following questions

Q1) Do you want authentication tokens to be time-based (y/n): Y

You will be presented with a token you can add to the Yubico Authenticator or other authenticator apps,

TIP: Write down any recovery codes displayed

Scan the code with your 2FA Authenticator app (e.g Google Authenticator, Yubico Authenticator or freeOTP from https://freeotp.github.io)

The 2FA code is now available for use in my YubiCo Authenticator app

Q2) Do you want me to update your “/root/.google_authenticator” file? (y/n): Y

Q3) Do you want to disallow multiple uses of the same authentication
token? This restricts you to one login about every 30s, but it increases
your chances to notice or even prevent man-in-the-middle attacks (y/n): Y

Q4) By default, a new token is generated every 30 seconds by the mobile app.
In order to compensate for possible time-skew between the client and the server,
we allow an extra token before and after the current time. This allows for a
time skew of up to 30 seconds between authentication server and client. If you
experience problems with poor time synchronization, you can increase the window
from its default size of 3 permitted codes (one previous code, the current
code, the next code) to 17 permitted codes (the 8 previous codes, the current
code, and the 8 next codes). This will permit for a time skew of up to 4 minutes
between client and server.
Do you want to do so? (y/n) y: Y

Q5) If the computer that you are logging into isn’t hardened against brute-force login attempts, you can enable rate-limiting for the authentication module. By default, this limits attackers to no more than 3 login attempts every 30s.
Do you want to enable rate-limiting? (y/n): Y

Review Google Authenticator Config

sudo nano ~/.google_authenticator

1	sudo nano ~/.google_authenticator

You can change this if need be.

Edit SSH Configuration (Authentication)

sudo nano /etc/pam.d/sshd

1	sudo nano /etc/pam.d/sshd

Add the line below the line “@include common-auth”

auth required pam_google_authenticator.so

1	auth required pam_google_authenticator.so

Comment out the following line (this is the most important step, this forces 2FA)

#@include common-auth

1	#@include common-auth

Edit SSH Configuration (Challenge Response Authentication)

Edit the ssh config file.

sudo nano /etc/ssh/sshd_config

1	sudo nano /etc/ssh/sshd_config

Search For

ChallengeResponseAuthentication

1	ChallengeResponseAuthentication

Set this to

yes

yes

Ensure the following line exists

UsePAM yes

1	UsePAM yes

Add the following line

AuthenticationMethods publickey,password publickey,keyboard-interactive

1	AuthenticationMethods publickey,password publickey,keyboard-interactive

Edit Common Auth

sudo nano /etc/pam.d/common-auth

1	sudo nano /etc/pam.d/common-auth

Add the following line before the line that says “auth [success=1 default=ignore] pam_unix.so nullok_secure”

auth required pam_google_authenticator.so

1	auth required pam_google_authenticator.so

Restart the SSH service and test the codes in a new terminal before rebooting.

TIP: Do not exit the working connected session and you may need it to fix issues.

Restart the SSH service a tets it

/etc/init.d/ssh restart
[ ok ] Restarting ssh (via systemctl): ssh.service.

1 2	/etc/init.d/ssh restart [ ok ] Restarting ssh (via systemctl): ssh.service.

If you have failed to set it up authenticator codes will fail to work.

Failed attempts

Further authentication required
Using keyboard-interactive authentication.
Verification code:
Using keyboard-interactive authentication.
Verification code:
Using keyboard-interactive authentication.
Verification code:

Further authentication required

Using keyboard-interactive authentication.

Verification code:

Using keyboard-interactive authentication.

Verification code:

Using keyboard-interactive authentication.

Verification code:

When it is configured OK (at login SSH connection) I was prompted for further information

Further Information required
Using keyboard-interactive authentication
Verification Code: ######
sshuser@server#

Further Information required

Using keyboard-interactive authentication

Verification Code: ######

sshuser@server#

I am now prompted at login to insert a 2FA token (after inserting my YubiKey)

Turn on 2FA on other sites

Check out https://www.turnon2fa.com and tutorials here.

I hope this guide helps someone.

Please consider using my referral code and get $25 UpCloud VM credit if you need to create a server online.

https://www.upcloud.com/register/?promo=D84793

Ask a question or recommend an article

Revision History

V1.3 turnon2fa.com

V1.2 ssh auth bypass

v1.1 Authenticator apps

v1.0 Initial Post

No matter what server-provider you are using I strongly recommend you have a hot spare ready on a different provider

August 5, 2018 by Simon

I recently had an issue where I set up a website for a friend. I invested 6 hours into setting up, the server and backups were automatically deleted after 7 days while I was away from my keyboard because I assumed the account was valid and had credits.

Read Full Article:

https://fearby.com/article/no-matter-what-server-provider-you-are-using-i-strongly-recommend-you-have-a-hot-spare-ready-on-a-different-provider/

Adding two sub domains (one pointing to a new UpCloud VM and the other pointing to an NGINX subsite) on Ubuntu 18.04

June 27, 2018 by Simon

Here is how I added two subdomains (one pointing to a new UpCloud VM and the other pointing to an NGINX subsite) on Ubuntu 18.04

If you have not read my previous posts I have now moved my blog to the awesome UpCloud host (signup using this link to get $25 free credit). I compared Digital Ocean, Vultr and UpCloud Disk IO here and UpCloud came out on top by a long way (read the blog post here). Here is my blog post on moving from Vultr to UpCloud.

UpCloud performance is great.

Buy a domain name from Namecheap here.

Goal(s)

Setup 2x subdomains on https://fearby.com

– Sub Domain #1: https://test.fearby.com (pointing to a dedicated UpCloud VM in Singapore for testing).

– Sub Domain #2: https://audit.fearby.com (pointing to a sub-website on the NGINX/VM that runs https://fearby.com )

Let’s set up the first Sub Domain (dedicated VM) and SSL

Backup

Do backup your server first.

I created a second server ($5 month or $0.07c hour 1,024MB Memory, 25GB Disk, 1024 GB Month Data Transfer) at UpCloud. If you don’t already have an account at UpCloud use this link to signup and get $25 free credit ( https://www.upcloud.com/register/?promo=D84793 ). Read my blog post on why UpCloud is awesome and how I moved my domain to UpCloud.

Once I spun up a second server I obtained the IPv4 and IPv6 IP addresses of the new “test” VM from the UpCloud dashboard.

IPV4 IP: 94.237.65.54
IPV6 IP: 2a04:3543:1000:2310:24b7:7cff:fe92:468c

1 2	IPV4 IP: 94.237.65.54 IPV6 IP: 2a04:3543:1000:2310:24b7:7cff:fe92:468c

DNS

These DNS records were already in place with my DNS provider (Cloudflare).

A fearby.com 209.50.48.88
AAAA fearby.com 2605:7380:1000:1310:24b7:7cff:fe92:0d64

1 2	A fearby.com 209.50.48.88 AAAA fearby.com 2605:7380:1000:1310:24b7:7cff:fe92:0d64

I added these DNS records for the subdomains.

I added a new A NAME record for the new shared NGINX subdomain (for https://audit.fearby.com), this subdomain will be a sub-website that is running off the same server as https://fearby.com

A audit 209.50.48.88
AAAA audit 2605:7380:1000:1310:24b7:7cff:fe92:0d64

1 2	A audit 209.50.48.88 AAAA audit 2605:7380:1000:1310:24b7:7cff:fe92:0d64

I added another set of records for the new dedicated VM subdomain (for https://test.fearby.com)

A test 94.237.65.54
AAAA test 2a04:3543:1000:2310:24b7:7cff:fe92:468c

1 2	A test 94.237.65.54 AAAA test 2a04:3543:1000:2310:24b7:7cff:fe92:468c

I waited for DNS to replicate around the globe by watching https://www.whatsmydns.net/

Setup a Firewall

On the new dedicated https://test.fearby.com VM, I installed the ufw firewall.

sudo apt-get install ufw

1	sudo apt-get install ufw

I configured the firewall to allow minimum ports (and added whitelisted IP for port 22 and added UpCloud DNS servers). I will lock this down some more later.

TIP: If your ISP does not offer a dedicated IP try a VPN. I use https://cyberghostvpn.com on OSX and Android.

Firewall rules.

sudo ufw status numbered

     To                         Action      From
     --                         ------      ----
[ 1] 22                         ALLOW IN    x.x.x.x
[ 2] 80                         ALLOW IN    Anywhere
[ 3] 443                        ALLOW IN    Anywhere
[ 4] 53                         ALLOW IN    93.237.127.9
[ 5] 53                         ALLOW IN    93.237.40.9
[ 6] 25                         DENY IN     Anywhere
[ 7] 80 (v6)                    ALLOW IN    Anywhere (v6)
[ 8] 443 (v6)                   ALLOW IN    Anywhere (v6)
[ 9] 53                         ALLOW IN    2a04:3540:53::1
[10] 53                         ALLOW IN    2a04:3544:53::1
[11] 22                         ALLOW IN    x.x.x.x.x.x.x.x.x
[12] 25 (v6)                    DENY IN     Anywhere (v6)

sudo ufw status numbered

To Action From

-- ------ ----

[ 1] 22 ALLOW IN x.x.x.x

[ 2] 80 ALLOW IN Anywhere

[ 3] 443 ALLOW IN Anywhere

[ 4] 53 ALLOW IN 93.237.127.9

[ 5] 53 ALLOW IN 93.237.40.9

[ 6] 25 DENY IN Anywhere

[ 7] 80 (v6) ALLOW IN Anywhere (v6)

[ 8] 443 (v6) ALLOW IN Anywhere (v6)

[ 9] 53 ALLOW IN 2a04:3540:53::1

[10] 53 ALLOW IN 2a04:3544:53::1

[11] 22 ALLOW IN x.x.x.x.x.x.x.x.x

[12] 25 (v6) DENY IN Anywhere (v6)

I enabled the firewall.

sudo ufw enable

1	sudo ufw enable

Install NGINX (on https://test.fearby.com)

On the new dedicated https://test.fearby.com VM I…

Created a new www root

mkdir /www-root

1	mkdir /www-root

Set permissions

sudo chown -R www-data:www-data /www-root

1	sudo chown -R www-data:www-data /www-root

Installed NGINX

sudo apt-get update
sudo apt-get install nginx

1 2	sudo apt-get update sudo apt-get install nginx

I created a placeholder webpage

sudo nano /www-root/index.html

1	sudo nano /www-root/index.html

Configured the root value in /etc/nginx/sites-available/default

Created a symbolic link of the nginx config

sudo ln -s /etc/nginx/sites-available/default /etc/nginx/sites-enabled/default

1	sudo ln -s /etc/nginx/sites-available/default /etc/nginx/sites-enabled/default

Lets Encrypt SSL

I have previously setup Lets encrypt on Ubuntu 16.04 but not 18.04. Certbot had info on setting up Lets Encrypt for 14.x 16.x and 17.x but not 18.x

Full credit for the SSL steps goes to @Linuxize ( tips on setting up Lets Encrypt on Ubuntu 18.04 ). Check out https://linuxize.com/

I installed Lets Encrypt certbot

sudo apt update
sudo apt install certbot

1 2	sudo apt update sudo apt install certbot

I created a new Diffie–Hellman key

mkdir -p /etc/ssl/certs/
sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 2048

1 2	mkdir -p /etc/ssl/certs/ sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 2048

Map requests to http://test.fearby.com/.well-known/acme-challenge to /var/lib/letsencrypt/.well-known ( Read the linuxize post for detailed steps ).

mkdir -p /var/lib/letsencrypt/.well-known
chgrp www-data /var/lib/letsencrypt
chmod g+s /var/lib/letsencrypt

mkdir -p /var/lib/letsencrypt/.well-known

chgrp www-data /var/lib/letsencrypt

chmod g+s /var/lib/letsencrypt

Create a /etc/nginx/snippets/letsencrypt.conf on http://test.fearby.com and enforce the redirect.

location ^~ /.well-known/acme-challenge/ {
  allow all;
  root /var/lib/letsencrypt/;
  default_type "text/plain";
  try_files $uri =404;
}

location ^~ /.well-known/acme-challenge/ {

allow all;

root /var/lib/letsencrypt/;

default_type "text/plain";

try_files $uri =404;

}

Create a /etc/nginx/snippets/ssl.conf file on http://test.fearby.com

ssl_dhparam /etc/ssl/certs/dhparam.pem;

ssl_session_timeout 1d;
ssl_session_cache shared:SSL:50m;
ssl_session_tickets off;

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
ssl_ciphers 'ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA:ECDHE-ECDSA-DES-CBC3-SHA:ECDHE-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:DES-CBC3-SHA:!DSS';
ssl_prefer_server_ciphers on;

ssl_stapling on;
ssl_stapling_verify on;
resolver 8.8.8.8 8.8.4.4 valid=300s;
resolver_timeout 30s;

add_header Strict-Transport-Security "max-age=15768000; includeSubdomains; preload";
add_header X-Frame-Options SAMEORIGIN;
add_header X-Content-Type-Options nosniff;

ssl_dhparam /etc/ssl/certs/dhparam.pem;

ssl_session_timeout 1d;

ssl_session_cache shared:SSL:50m;

ssl_session_tickets off;

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

ssl_ciphers 'ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA:ECDHE-ECDSA-DES-CBC3-SHA:ECDHE-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:DES-CBC3-SHA:!DSS';

ssl_prefer_server_ciphers on;

ssl_stapling on;

ssl_stapling_verify on;

resolver 8.8.8.8 8.8.4.4 valid=300s;

resolver_timeout 30s;

add_header Strict-Transport-Security "max-age=15768000; includeSubdomains; preload";

add_header X-Frame-Options SAMEORIGIN;

add_header X-Content-Type-Options nosniff;

Let’s get a certificate

sudo certbot certonly --agree-tos --email your@email.com --webroot -w /var/lib/letsencrypt/ -d test.fearby.com

1	sudo certbot certonly --agree-tos --email your@email.com --webroot -w /var/lib/letsencrypt/ -d test.fearby.com

Certificates have been created 🙂

ls -al /etc/letsencrypt/live/test.fearby.com/
total 12
drwxr-xr-x 2 user user 4096 Jun 26 11:30 .
drwx------ 3 user user 4096 Jun 26 11:30 ..
-rw-r--r-- 1 user user  543 Jun 26 11:30 README
lrwxrwxrwx 1 user user   39 Jun 26 11:30 cert.pem -> ../../archive/test.fearby.com/cert1.pem
lrwxrwxrwx 1 user user   40 Jun 26 11:30 chain.pem -> ../../archive/test.fearby.com/chain1.pem
lrwxrwxrwx 1 user user   44 Jun 26 11:30 fullchain.pem -> ../../archive/test.fearby.com/fullchain1.pem
lrwxrwxrwx 1 user user   42 Jun 26 11:30 privkey.pem -> ../../archive/test.fearby.com/privkey1.pem

ls -al /etc/letsencrypt/live/test.fearby.com/

total 12

drwxr-xr-x 2 user user 4096 Jun 26 11:30 .

drwx------ 3 user user 4096 Jun 26 11:30 ..

-rw-r--r-- 1 user user 543 Jun 26 11:30 README

lrwxrwxrwx 1 user user 39 Jun 26 11:30 cert.pem -> ../../archive/test.fearby.com/cert1.pem

lrwxrwxrwx 1 user user 40 Jun 26 11:30 chain.pem -> ../../archive/test.fearby.com/chain1.pem

lrwxrwxrwx 1 user user 44 Jun 26 11:30 fullchain.pem -> ../../archive/test.fearby.com/fullchain1.pem

lrwxrwxrwx 1 user user 42 Jun 26 11:30 privkey.pem -> ../../archive/test.fearby.com/privkey1.pem

Now lets edit “/etc/nginx/sites-available/default” on https://test.fearby.com VM and add the cert paths.

server {
        listen 80 default_server;
        listen [::]:80 default_server;

        listen 443 ssl http2;
        listen [::]:443 ssl http2;

        if ($scheme != "https") {
                return 301 https://$host$request_uri;
        }

        ssl_certificate /etc/letsencrypt/live/test.fearby.com/fullchain.pem;
        ssl_certificate_key /etc/letsencrypt/live/test.fearby.com/privkey.pem;
        ssl_trusted_certificate /etc/letsencrypt/live/test.fearby.com/chain.pem;

        include snippets/ssl.conf;

        #ssl_stapling on; # Requires nginx >= 1.3.7
        # add_header Strict-Transport-Security "max-age=63072000; includeSubdomains; preload";

        add_header X-Frame-Options DENY;
        add_header X-Content-Type-Options nosniff;
        add_header X-XSS-Protection "1; mode=block";

        root /www-root/;

        include snippets/letsencrypt.conf;

        index index.html;

        server_name test.fearby.com;

        location / {
                try_files $uri $uri/ =404;
        }
}

server {

listen 80 default_server;

listen [::]:80 default_server;

listen 443 ssl http2;

listen [::]:443 ssl http2;

if ($scheme != "https") {

return 301 https://$host$request_uri;

}

ssl_certificate /etc/letsencrypt/live/test.fearby.com/fullchain.pem;

ssl_certificate_key /etc/letsencrypt/live/test.fearby.com/privkey.pem;

ssl_trusted_certificate /etc/letsencrypt/live/test.fearby.com/chain.pem;

include snippets/ssl.conf;

#ssl_stapling on; # Requires nginx >= 1.3.7

# add_header Strict-Transport-Security "max-age=63072000; includeSubdomains; preload";

add_header X-Frame-Options DENY;

add_header X-Content-Type-Options nosniff;

add_header X-XSS-Protection "1; mode=block";

root /www-root/;

include snippets/letsencrypt.conf;

index index.html;

server_name test.fearby.com;

location / {

try_files $uri $uri/ =404;

}

Reload NGINX

sudo systemctl reload nginx

1	sudo systemctl reload nginx

sudo nginx -t
sudo nginx -s reload
sudo systemctl reload nginx

sudo nginx -t

sudo nginx -s reload

sudo systemctl reload nginx

Now let’s setup the second subdomain (subsite off https://fearby.com) and SSL

I already have NGINX on https://fearby.com set up a second site.

DNS

We have already setup a DNS record for https://audit.fearby.com (above)

Firewall

Already configured at https://fearby.com

SSL

Because I had an existing Comodo certificate on https://fearby.com I am going to repeat the steps above to generate a new certificate but save the NGINX config to /etc/nginx/sites-available/audit.fearby.com (this activates the second site)

TIP: Follow the Linuxize guide here (for creating ssl.conf, letsencrypt.conf etc config files), Do a backup and restore if need be.

I created a new Diffie–Hellman key

mkdir -p /etc/ssl/certs/
sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 2048

1 2	mkdir -p /etc/ssl/certs/ sudo openssl dhparam -out /etc/ssl/certs/dhparam.pem 2048

Let’s get a certificate

sudo certbot certonly --agree-tos --email your@email.com --webroot -w /var/lib/letsencrypt/ -d audit.fearby.com

1	sudo certbot certonly --agree-tos --email your@email.com --webroot -w /var/lib/letsencrypt/ -d audit.fearby.com

Configure NGINX

Map requests to http://audit.fearby.com/.well-known/acme-challenge to /var/lib/letsencrypt/.well-known ( Read the linuxize post for detailed steps ).

mkdir -p /var/lib/letsencrypt/.well-known
chgrp www-data /var/lib/letsencrypt
chmod g+s /var/lib/letsencrypt

mkdir -p /var/lib/letsencrypt/.well-known

chgrp www-data /var/lib/letsencrypt

chmod g+s /var/lib/letsencrypt

I created a new NGINX site ( /etc/nginx/sites-available/audit.fearby.com )

#proxy_cache_path /tmp/nginx-cache keys_zone=one:10m;#
server {
        root /www-audit-root;

        # Listen Ports
        listen 80;
        listen [::]:80;
        listen 443 ssl http2;
        listen [::]:443 ssl http2;

        # Default File
        index index.html index.php index.htm;

        # Server Name
        server_name audit.fearby.com;

        include snippets/letsencrypt.conf;

        location / {
                try_files $uri $uri/ =404;
        }

        ssl_certificate /etc/letsencrypt/live/audit.fearby.com/fullchain.pem;
        ssl_certificate_key /etc/letsencrypt/live/audit.fearby.com/privkey.pem;
        ssl_trusted_certificate /etc/letsencrypt/live/audit.fearby.com/chain.pem;

        ssl_dhparam /etc/ssl/certs/auditdhparam.pem;

        ssl_session_timeout 1d;
        #ssl_session_cache shared:SSL:50m;
        ssl_session_tickets off;

        ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
        ssl_ciphers 'ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA38$

        ssl_prefer_server_ciphers on;

        ssl_stapling on;
        ssl_stapling_verify on;

        #resolver 8.8.8.8 8.8.4.4 valid=300s;
        #resolver_timeout 30s;

        add_header Strict-Transport-Security "max-age=15768000; includeSubdomains; preload";
        add_header X-Frame-Options SAMEORIGIN;
        add_header X-Content-Type-Options nosniff;

        if ($scheme != "https") {
                return 301 https://$host$request_uri;
        }
}

#proxy_cache_path /tmp/nginx-cache keys_zone=one:10m;#

server {

root /www-audit-root;

# Listen Ports

listen 80;

listen [::]:80;

listen 443 ssl http2;

listen [::]:443 ssl http2;

# Default File

index index.html index.php index.htm;

# Server Name

server_name audit.fearby.com;

include snippets/letsencrypt.conf;

location / {

try_files $uri $uri/ =404;

}

ssl_certificate /etc/letsencrypt/live/audit.fearby.com/fullchain.pem;

ssl_certificate_key /etc/letsencrypt/live/audit.fearby.com/privkey.pem;

ssl_trusted_certificate /etc/letsencrypt/live/audit.fearby.com/chain.pem;

ssl_dhparam /etc/ssl/certs/auditdhparam.pem;

ssl_session_timeout 1d;

#ssl_session_cache shared:SSL:50m;

ssl_session_tickets off;

ssl_protocols TLSv1 TLSv1.1 TLSv1.2;

ssl_prefer_server_ciphers on;

ssl_stapling on;

ssl_stapling_verify on;

#resolver 8.8.8.8 8.8.4.4 valid=300s;

#resolver_timeout 30s;

add_header Strict-Transport-Security "max-age=15768000; includeSubdomains; preload";

add_header X-Frame-Options SAMEORIGIN;

add_header X-Content-Type-Options nosniff;

if ($scheme != "https") {

return 301 https://$host$request_uri;

}

I created a symbolic link of the config file

sudo ln -s /etc/nginx/sites-available/audit.fearby.com /etc/nginx/sites-enabled/audit.fearby.com

1	sudo ln -s /etc/nginx/sites-available/audit.fearby.com /etc/nginx/sites-enabled/audit.fearby.com

Reload NGINX

sudo systemctl reload nginx

1	sudo systemctl reload nginx

sudo nginx -t
sudo nginx -s reload
sudo systemctl reload nginx

sudo nginx -t

sudo nginx -s reload

sudo systemctl reload nginx

How to test the certificate renewal

sudo certbot renew --dry-run

1	sudo certbot renew --dry-run

Automate the renewal in crontab (every 12 hours)

I set this crontab entry up on https://fearby.com and https://test.fearby.com

crontab -e
0 */12 * * * root test -x /usr/bin/certbot -a \! -d /run/systemd/system && perl -e 'sleep int(rand(3600))' && certbot -q renew --renew-hook "systemctl reload nginx"

1 2	crontab -e 0 /12 * * root test -x /usr/bin/certbot -a \! -d /run/systemd/system && perl -e 'sleep int(rand(3600))' && certbot -q renew --renew-hook "systemctl reload nginx"

Conclusion

Yes, I haVe 2 subdomains (1x dedicated VM and the other is a sub-website off an existing server) with SSL certificates.

Ping Results

ping -c 4 fearby.com
PING fearby.com (209.50.48.88): 56 data bytes
64 bytes from 209.50.48.88: icmp_seq=0 ttl=44 time=220.000 ms
64 bytes from 209.50.48.88: icmp_seq=1 ttl=44 time=290.602 ms
64 bytes from 209.50.48.88: icmp_seq=2 ttl=44 time=311.938 ms
64 bytes from 209.50.48.88: icmp_seq=3 ttl=44 time=330.841 ms

--- fearby.com ping statistics ---
4 packets transmitted, 4 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 220.000/288.345/330.841/41.948 ms

ping -c 4 test.fearby.com
PING test.fearby.com (94.237.65.54): 56 data bytes
64 bytes from 94.237.65.54: icmp_seq=0 ttl=44 time=333.590 ms
64 bytes from 94.237.65.54: icmp_seq=1 ttl=44 time=252.433 ms
64 bytes from 94.237.65.54: icmp_seq=2 ttl=44 time=271.153 ms
64 bytes from 94.237.65.54: icmp_seq=3 ttl=44 time=292.685 ms

--- test.fearby.com ping statistics ---
4 packets transmitted, 4 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 252.433/287.465/333.590/30.200 ms

ping -c 4 audit.fearby.com
PING audit.fearby.com (209.50.48.88): 56 data bytes
64 bytes from 209.50.48.88: icmp_seq=0 ttl=44 time=281.662 ms
64 bytes from 209.50.48.88: icmp_seq=1 ttl=44 time=307.676 ms
64 bytes from 209.50.48.88: icmp_seq=2 ttl=44 time=227.985 ms
64 bytes from 209.50.48.88: icmp_seq=3 ttl=44 time=215.566 ms

--- audit.fearby.com ping statistics ---
4 packets transmitted, 4 packets received, 0.0% packet loss
round-trip min/avg/max/stddev = 215.566/258.222/307.676/37.845 ms

ping -c 4 fearby.com

PING fearby.com (209.50.48.88): 56 data bytes

64 bytes from 209.50.48.88: icmp_seq=0 ttl=44 time=220.000 ms

64 bytes from 209.50.48.88: icmp_seq=1 ttl=44 time=290.602 ms

64 bytes from 209.50.48.88: icmp_seq=2 ttl=44 time=311.938 ms

64 bytes from 209.50.48.88: icmp_seq=3 ttl=44 time=330.841 ms

--- fearby.com ping statistics ---

4 packets transmitted, 4 packets received, 0.0% packet loss

round-trip min/avg/max/stddev = 220.000/288.345/330.841/41.948 ms

ping -c 4 test.fearby.com

PING test.fearby.com (94.237.65.54): 56 data bytes

64 bytes from 94.237.65.54: icmp_seq=0 ttl=44 time=333.590 ms

64 bytes from 94.237.65.54: icmp_seq=1 ttl=44 time=252.433 ms

64 bytes from 94.237.65.54: icmp_seq=2 ttl=44 time=271.153 ms

64 bytes from 94.237.65.54: icmp_seq=3 ttl=44 time=292.685 ms

--- test.fearby.com ping statistics ---

4 packets transmitted, 4 packets received, 0.0% packet loss

round-trip min/avg/max/stddev = 252.433/287.465/333.590/30.200 ms

ping -c 4 audit.fearby.com

PING audit.fearby.com (209.50.48.88): 56 data bytes

64 bytes from 209.50.48.88: icmp_seq=0 ttl=44 time=281.662 ms

64 bytes from 209.50.48.88: icmp_seq=1 ttl=44 time=307.676 ms

64 bytes from 209.50.48.88: icmp_seq=2 ttl=44 time=227.985 ms

64 bytes from 209.50.48.88: icmp_seq=3 ttl=44 time=215.566 ms

--- audit.fearby.com ping statistics ---

4 packets transmitted, 4 packets received, 0.0% packet loss

round-trip min/avg/max/stddev = 215.566/258.222/307.676/37.845 ms

Webpage Results

Troubleshooting

If you are having troubles generating the initial certificate check that you have not blocked port 80 and don’t have “Strict-Transport-Security” heavers enabled.

sudo certbot certonly --agree-tos --email your@email.com --webroot -w /var/lib/letsencrypt/ -d g
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Obtaining a new certificate
Performing the following challenges:
http-01 challenge for yoursubdomain.domain.com
Using the webroot path /var/lib/letsencrypt for all unmatched domains.
Waiting for verification...
Cleaning up challenges
Failed authorization procedure. yoursubdomain.domain.com (http-01): urn:acme:error:unauthorized :: The client lacks sufficzLlmg_w6Tc: q%!(EXTRA string=<html>
<head><title>404 Not Found</title></head>
<body bgcolor="white">
<center><h1>404 Not Found</h1></center>
<hr><center>)

IMPORTANT NOTES:
 - The following errors were reported by the server:

   Domain: yoursubdomain.domain.com
   Type:   unauthorized
   Detail: Invalid response from
   http://yoursubdomain.domain.com/.well-known/acme-challenge/_QA3jblEydx5mE8I8OdRsd2EdHIj4R-przLlmg_w6Tc:
   q%!(EXTRA string=<html>
   <head><title>404 Not Found</title></head>
   <body bgcolor="white">
   <center><h1>404 Not Found</h1></center>
   <hr><center>)

   To fix these errors, please make sure that your domain name was
   entered correctly and the DNS A record(s) for that domain
   contain(s) the right IP address.

sudo certbot certonly --agree-tos --email your@email.com --webroot -w /var/lib/letsencrypt/ -d g

Saving debug log to /var/log/letsencrypt/letsencrypt.log

Obtaining a new certificate

Performing the following challenges:

http-01 challenge for yoursubdomain.domain.com

Using the webroot path /var/lib/letsencrypt for all unmatched domains.

Waiting for verification...

Cleaning up challenges

Failed authorization procedure. yoursubdomain.domain.com (http-01): urn:acme:error:unauthorized :: The client lacks sufficzLlmg_w6Tc: q%!(EXTRA string=<html>

<head><title>404 Not Found</title></head>

<center><h1>404 Not Found</h1></center>

<hr><center>)

IMPORTANT NOTES:

- The following errors were reported by the server:

Domain: yoursubdomain.domain.com

Type: unauthorized

Detail: Invalid response from

http://yoursubdomain.domain.com/.well-known/acme-challenge/_QA3jblEydx5mE8I8OdRsd2EdHIj4R-przLlmg_w6Tc:

q%!(EXTRA string=<html>

<head><title>404 Not Found</title></head>

<center><h1>404 Not Found</h1></center>

<hr><center>)

To fix these errors, please make sure that your domain name was

entered correctly and the DNS A record(s) for that domain

contain(s) the right IP address.

I re-ran the certbot command but pointed to the real /www-root (not/var/lib/letsencrypt/)

Create a new

mkdir /www-root/.well-known/
mkdir /www-root/.well-known/acme-challenge/
sudo certbot certonly --agree-tos --email your@email.com --webroot -w /www-root -d yoursubdomain.domain.com

mkdir /www-root/.well-known/

mkdir /www-root/.well-known/acme-challenge/

sudo certbot certonly --agree-tos --email your@email.com --webroot -w /www-root -d yoursubdomain.domain.com

I hope this guide helps someone.

Please consider using my referral code and get $25 credit for free.

https://www.upcloud.com/register/?promo=D84793

Ask a question or recommend an article

Revision History

v1.1 Troubleshooting

v1.0 Initial Post

Upgrading an Ubuntu server on UpCloud to add more CPU, Memory and Disk Space

June 25, 2018 by Simon

Before UpCloud, when I had 512MB ram on Vultr I had multiple NGINX crashed a day so I used a bash script and scheduled a cron job to clear memory cache when memory fell below 100MB (view the script here). To further increase the speed of the WordPress I have configured the OS to use memory over the disk. About once a day free memory falls below 100MB (this is not a problem as my script clears cached items automatically).

Read the full article here: https://fearby.com/article/upgrading-an-ubuntu-server-on-upcloud-to-add-more-cpu-memory-and-disk-space/

Upgrading an Ubuntu server on UpCloud to add more CPU, Memory and Disk Space

June 25, 2018 by Simon

Upgrading an Ubuntu server on UpCloud to add more CPU, Memory and Disk Space

If you have not read my previous posts I have now moved my blog from Vultr to the awesome UpCloud host (signup using this link to get $25 free credit).

Recently I compared Digital Ocean, Vultr and UpCloud Disk IO here and UpCloud came out on top by a long way (read the blog post here). Here is my blog post on moving from Vultr to UpCloud.

Spoiler: UpCloud performance is great.

Why Upgrade

I have 1 CPU, 1 GB memory and 50GB storage and it is running well? I have PHP child workers set up and have set up the preferred use of memory over swap file usage.

I’d like to add more memory as I am working on some things (watch this space) and I will use the extra memory. I’d prefer the server is set up now for the expected workload.

How to Upgrade

This is how I upgraded from 1xCPU/2GB Memory/50GB Storage/2TB Transferred Data to a 2 CPU/4GB Mmeory/80GB Storage/4TB Transferred Data server.

UpCloud Pricing: https://www.upcloud.com/pricing/

Upgrade an UpCloud VM

I shut down my existing VM. Read this guide to setup a VM.

shutdown -P

1	shutdown -P

Login to the UpCloud dashboard, select your server (confirm the server has shut down) and click General Settings, choose the upgrade and click Update.

I confirmed the upgrade options (2x CPU, 4096 MB Memory).

Click Update

After 10 seconds you can start your server from the UpCloud server.

I confirmed the CPU upgrade was visible in the VM

cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 63
model name      : Intel(R) Xeon(R) CPU E5-2687W v3 @ 3.10GHz
stepping        : 2
microcode       : 0x1
cpu MHz         : 3099.978
cache size      : 16384 KB
physical id     : 0
siblings        : 2
core id         : 0
cpu cores       : 2
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon rep_good nopl xtopology cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid xsaveopt arat
bugs            : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass
bogomips        : 6199.95
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 63
model name      : Intel(R) Xeon(R) CPU E5-2687W v3 @ 3.10GHz
stepping        : 2
microcode       : 0x1
cpu MHz         : 3099.978
cache size      : 16384 KB
physical id     : 0
siblings        : 2
core id         : 1
cpu cores       : 2
apicid          : 1
initial apicid  : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon rep_good nopl xtopology cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid xsaveopt arat
bugs            : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass
bogomips        : 6199.95
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

cat /proc/cpuinfo

processor : 0

vendor_id : GenuineIntel

cpu family : 6

model : 63

model name : Intel(R) Xeon(R) CPU E5-2687W v3 @ 3.10GHz

stepping : 2

microcode : 0x1

cpu MHz : 3099.978

cache size : 16384 KB

physical id : 0

siblings : 2

core id : 0

cpu cores : 2

apicid : 0

initial apicid : 0

fpu : yes

fpu_exception : yes

cpuid level : 13

wp : yes

flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon rep_good nopl xtopology cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid xsaveopt arat

bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass

bogomips : 6199.95

clflush size : 64

cache_alignment : 64

address sizes : 40 bits physical, 48 bits virtual

power management:

processor : 1

vendor_id : GenuineIntel

cpu family : 6

model : 63

model name : Intel(R) Xeon(R) CPU E5-2687W v3 @ 3.10GHz

stepping : 2

microcode : 0x1

cpu MHz : 3099.978

cache size : 16384 KB

physical id : 0

siblings : 2

core id : 1

cpu cores : 2

apicid : 1

initial apicid : 1

fpu : yes

fpu_exception : yes

cpuid level : 13

wp : yes

bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass

bogomips : 6199.95

clflush size : 64

cache_alignment : 64

address sizes : 40 bits physical, 48 bits virtual

power management:

Software Tweaks Post Upgrade.

I added these settings to the top of /etc/nginx/nginx.conf to ensure the extra CPU was used.

worker_processes auto;
worker_cpu_affinity auto;

1 2	worker_processes auto; worker_cpu_affinity auto;

I increased PHP FPM ( /etc/php/7.2/fpm/php.ini ) to increase the memory usage and child workers. I doubled child workers and max memeory limit.

memory_limit = 3072M
pm.max_children = 80
pm.start_servers = 30
pm.min_spare_servers = 10
pm.max_spare_servers = 30

memory_limit = 3072M

pm.max_children = 80

pm.start_servers = 30

pm.min_spare_servers = 10

pm.max_spare_servers = 30

I restarted NGINX and PHP

nginx -t
nginx -s reload
/etc/init.d/nginx restart
service php7.2-fpm restart

nginx -t

nginx -s reload

/etc/init.d/nginx restart

service php7.2-fpm restart

I tweaked WordPress max memory limits

define( 'WP_MEMORY_LIMIT','3072M');
define( 'WP_MAX_MEMORY_LIMIT','3072M');

1 2	define( 'WP_MEMORY_LIMIT','3072M'); define( 'WP_MAX_MEMORY_LIMIT','3072M');

MySQL Tweaks: I logged into MySQL

mysql -u root -p

1	mysql -u root -p

I ran “SHOW GLOBAL STATUS” to view stats

mysql> SHOW GLOBAL STATUS;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name                                 | Value                                            |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients                               | 0                                                |
| Aborted_connects                              | 0                                                |
| Binlog_cache_disk_use                         | 0                                                |
| Binlog_cache_use                              | 0                                                |
| Binlog_stmt_cache_disk_use                    | 0                                                |
| Binlog_stmt_cache_use                         | 0                                                |
| Bytes_received                                | 3179986                                          |
| Bytes_sent                                    | 223872114                                        |
| Com_admin_commands                            | 0                                                |
| Com_assign_to_keycache                        | 0                                                |
| Com_alter_db                                  | 0                                                |
| Com_alter_db_upgrade                          | 0                                                |
| Com_alter_event                               | 0                                                |
| Com_alter_function                            | 0                                                |
| Com_alter_instance                            | 0                                                |
| Com_alter_procedure                           | 0                                                |
| Com_alter_server                              | 0                                                |
| Com_alter_table                               | 0                                                |
| Com_alter_tablespace                          | 0                                                |
| Com_alter_user                                | 0                                                |
| Com_analyze                                   | 0                                                |
| Com_begin                                     | 0                                                |
| Com_binlog                                    | 0                                                |
| Com_call_procedure                            | 0                                                |
| Com_change_db                                 | 284                                              |
| Com_change_master                             | 0                                                |
| Com_change_repl_filter                        | 0                                                |
| Com_check                                     | 0                                                |
| Com_checksum                                  | 0                                                |
| Com_commit                                    | 0                                                |
| Com_create_db                                 | 0                                                |
| Com_create_event                              | 0                                                |
| Com_create_function                           | 0                                                |
| Com_create_index                              | 0                                                |
| Com_create_procedure                          | 0                                                |
| Com_create_server                             | 0                                                |
| Com_create_table                              | 0                                                |
| Com_create_trigger                            | 0                                                |
| Com_create_udf                                | 0                                                |
| Com_create_user                               | 0                                                |
| Com_create_view                               | 0                                                |
| Com_dealloc_sql                               | 0                                                |
| Com_delete                                    | 18                                               |
| Com_delete_multi                              | 0                                                |
| Com_do                                        | 0                                                |
| Com_drop_db                                   | 0                                                |
| Com_drop_event                                | 0                                                |
| Com_drop_function                             | 0                                                |
| Com_drop_index                                | 0                                                |
| Com_drop_procedure                            | 0                                                |
| Com_drop_server                               | 0                                                |
| Com_drop_table                                | 0                                                |
| Com_drop_trigger                              | 0                                                |
| Com_drop_user                                 | 0                                                |
| Com_drop_view                                 | 0                                                |
| Com_empty_query                               | 0                                                |
| Com_execute_sql                               | 0                                                |
| Com_explain_other                             | 0                                                |
| Com_flush                                     | 0                                                |
| Com_get_diagnostics                           | 0                                                |
| Com_grant                                     | 0                                                |
| Com_ha_close                                  | 0                                                |
| Com_ha_open                                   | 0                                                |
| Com_ha_read                                   | 0                                                |
| Com_help                                      | 0                                                |
| Com_insert                                    | 342                                              |
| Com_insert_select                             | 0                                                |
| Com_install_plugin                            | 0                                                |
| Com_kill                                      | 0                                                |
| Com_load                                      | 0                                                |
| Com_lock_tables                               | 0                                                |
| Com_optimize                                  | 0                                                |
| Com_preload_keys                              | 0                                                |
| Com_prepare_sql                               | 0                                                |
| Com_purge                                     | 0                                                |
| Com_purge_before_date                         | 0                                                |
| Com_release_savepoint                         | 0                                                |
| Com_rename_table                              | 0                                                |
| Com_rename_user                               | 0                                                |
| Com_repair                                    | 0                                                |
| Com_replace                                   | 0                                                |
| Com_replace_select                            | 0                                                |
| Com_reset                                     | 0                                                |
| Com_resignal                                  | 0                                                |
| Com_revoke                                    | 0                                                |
| Com_revoke_all                                | 0                                                |
| Com_rollback                                  | 0                                                |
| Com_rollback_to_savepoint                     | 0                                                |
| Com_savepoint                                 | 0                                                |
| Com_select                                    | 16358                                            |
| Com_set_option                                | 849                                              |
| Com_signal                                    | 0                                                |
| Com_show_binlog_events                        | 0                                                |
| Com_show_binlogs                              | 0                                                |
| Com_show_charsets                             | 0                                                |
| Com_show_collations                           | 0                                                |
| Com_show_create_db                            | 0                                                |
| Com_show_create_event                         | 0                                                |
| Com_show_create_func                          | 0                                                |
| Com_show_create_proc                          | 0                                                |
| Com_show_create_table                         | 0                                                |
| Com_show_create_trigger                       | 0                                                |
| Com_show_databases                            | 3                                                |
| Com_show_engine_logs                          | 0                                                |
| Com_show_engine_mutex                         | 0                                                |
| Com_show_engine_status                        | 0                                                |
| Com_show_events                               | 0                                                |
| Com_show_errors                               | 0                                                |
| Com_show_fields                               | 921                                              |
| Com_show_function_code                        | 0                                                |
| Com_show_function_status                      | 0                                                |
| Com_show_grants                               | 0                                                |
| Com_show_keys                                 | 1                                                |
| Com_show_master_status                        | 0                                                |
| Com_show_open_tables                          | 0                                                |
| Com_show_plugins                              | 0                                                |
| Com_show_privileges                           | 0                                                |
| Com_show_procedure_code                       | 0                                                |
| Com_show_procedure_status                     | 0                                                |
| Com_show_processlist                          | 0                                                |
| Com_show_profile                              | 0                                                |
| Com_show_profiles                             | 0                                                |
| Com_show_relaylog_events                      | 0                                                |
| Com_show_slave_hosts                          | 0                                                |
| Com_show_slave_status                         | 0                                                |
| Com_show_status                               | 6                                                |
| Com_show_storage_engines                      | 0                                                |
| Com_show_table_status                         | 0                                                |
| Com_show_tables                               | 2                                                |
| Com_show_triggers                             | 0                                                |
| Com_show_variables                            | 6                                                |
| Com_show_warnings                             | 1                                                |
| Com_show_create_user                          | 0                                                |
| Com_shutdown                                  | 0                                                |
| Com_slave_start                               | 0                                                |
| Com_slave_stop                                | 0                                                |
| Com_group_replication_start                   | 0                                                |
| Com_group_replication_stop                    | 0                                                |
| Com_stmt_execute                              | 4                                                |
| Com_stmt_close                                | 4                                                |
| Com_stmt_fetch                                | 0                                                |
| Com_stmt_prepare                              | 4                                                |
| Com_stmt_reset                                | 0                                                |
| Com_stmt_send_long_data                       | 4                                                |
| Com_truncate                                  | 2                                                |
| Com_uninstall_plugin                          | 0                                                |
| Com_unlock_tables                             | 0                                                |
| Com_update                                    | 70                                               |
| Com_update_multi                              | 0                                                |
| Com_xa_commit                                 | 0                                                |
| Com_xa_end                                    | 0                                                |
| Com_xa_prepare                                | 0                                                |
| Com_xa_recover                                | 0                                                |
| Com_xa_rollback                               | 0                                                |
| Com_xa_start                                  | 0                                                |
| Com_stmt_reprepare                            | 0                                                |
| Connection_errors_accept                      | 0                                                |
| Connection_errors_internal                    | 0                                                |
| Connection_errors_max_connections             | 0                                                |
| Connection_errors_peer_address                | 0                                                |
| Connection_errors_select                      | 0                                                |
| Connection_errors_tcpwrap                     | 0                                                |
| Connections                                   | 292                                              |
| Created_tmp_disk_tables                       | 1124                                             |
| Created_tmp_files                             | 30                                               |
| Created_tmp_tables                            | 1369                                             |
| Delayed_errors                                | 0                                                |
| Delayed_insert_threads                        | 0                                                |
| Delayed_writes                                | 0                                                |
| Flush_commands                                | 1                                                |
| Handler_commit                                | 6094                                             |
| Handler_delete                                | 33                                               |
| Handler_discover                              | 0                                                |
| Handler_external_lock                         | 38571                                            |
| Handler_mrr_init                              | 0                                                |
| Handler_prepare                               | 0                                                |
| Handler_read_first                            | 2299                                             |
| Handler_read_key                              | 134761                                           |
| Handler_read_last                             | 237                                              |
| Handler_read_next                             | 310119                                           |
| Handler_read_prev                             | 2733                                             |
| Handler_read_rnd                              | 222350                                           |
| Handler_read_rnd_next                         | 472820                                           |
| Handler_rollback                              | 0                                                |
| Handler_savepoint                             | 0                                                |
| Handler_savepoint_rollback                    | 0                                                |
| Handler_update                                | 15605                                            |
| Handler_write                                 | 17310                                            |
| Innodb_buffer_pool_dump_status                | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status                | Buffer pool(s) load completed at 180624 23:38:01 |
| Innodb_buffer_pool_resize_status              |                                                  |
| Innodb_buffer_pool_pages_data                 | 1035                                             |
| Innodb_buffer_pool_bytes_data                 | 16957440                                         |
| Innodb_buffer_pool_pages_dirty                | 0                                                |
| Innodb_buffer_pool_bytes_dirty                | 0                                                |
| Innodb_buffer_pool_pages_flushed              | 1936                                             |
| Innodb_buffer_pool_pages_free                 | 7144                                             |
| Innodb_buffer_pool_pages_misc                 | 13                                               |
| Innodb_buffer_pool_pages_total                | 8192                                             |
| Innodb_buffer_pool_read_ahead_rnd             | 0                                                |
| Innodb_buffer_pool_read_ahead                 | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted         | 0                                                |
| Innodb_buffer_pool_read_requests              | 306665                                           |
| Innodb_buffer_pool_reads                      | 950                                              |
| Innodb_buffer_pool_wait_free                  | 0                                                |
| Innodb_buffer_pool_write_requests             | 26509                                            |
| Innodb_data_fsyncs                            | 1229                                             |
| Innodb_data_pending_fsyncs                    | 0                                                |
| Innodb_data_pending_reads                     | 0                                                |
| Innodb_data_pending_writes                    | 0                                                |
| Innodb_data_read                              | 16273920                                         |
| Innodb_data_reads                             | 1078                                             |
| Innodb_data_writes                            | 2857                                             |
| Innodb_data_written                           | 53379584                                         |
| Innodb_dblwr_pages_written                    | 1275                                             |
| Innodb_dblwr_writes                           | 109                                              |
| Innodb_log_waits                              | 0                                                |
| Innodb_log_write_requests                     | 450                                              |
| Innodb_log_writes                             | 585                                              |
| Innodb_os_log_fsyncs                          | 793                                              |
| Innodb_os_log_pending_fsyncs                  | 0                                                |
| Innodb_os_log_pending_writes                  | 0                                                |
| Innodb_os_log_written                         | 664064                                           |
| Innodb_page_size                              | 16384                                            |
| Innodb_pages_created                          | 56                                               |
| Innodb_pages_read                             | 988                                              |
| Innodb_pages_written                          | 1936                                             |
| Innodb_row_lock_current_waits                 | 0                                                |
| Innodb_row_lock_time                          | 0                                                |
| Innodb_row_lock_time_avg                      | 0                                                |
| Innodb_row_lock_time_max                      | 0                                                |
| Innodb_row_lock_waits                         | 0                                                |
| Innodb_rows_deleted                           | 2                                                |
| Innodb_rows_inserted                          | 19219                                            |
| Innodb_rows_read                              | 249102                                           |
| Innodb_rows_updated                           | 77                                               |
| Innodb_num_open_files                         | 81                                               |
| Innodb_truncated_status_writes                | 0                                                |
| Innodb_available_undo_logs                    | 128                                              |
| Key_blocks_not_flushed                        | 0                                                |
| Key_blocks_unused                             | 12751                                            |
| Key_blocks_used                               | 645                                              |
| Key_read_requests                             | 321877                                           |
| Key_reads                                     | 648                                              |
| Key_write_requests                            | 196                                              |
| Key_writes                                    | 150                                              |
| Locked_connects                               | 0                                                |
| Max_execution_time_exceeded                   | 0                                                |
| Max_execution_time_set                        | 0                                                |
| Max_execution_time_set_failed                 | 0                                                |
| Max_used_connections                          | 3                                                |
| Max_used_connections_time                     | 2018-06-24 23:43:48                              |
| Not_flushed_delayed_rows                      | 0                                                |
| Ongoing_anonymous_transaction_count           | 0                                                |
| Open_files                                    | 229                                              |
| Open_streams                                  | 0                                                |
| Open_table_definitions                        | 206                                              |
| Open_tables                                   | 786                                              |
| Opened_files                                  | 502                                              |
| Opened_table_definitions                      | 208                                              |
| Opened_tables                                 | 817                                              |
| Performance_schema_accounts_lost              | 0                                                |
| Performance_schema_cond_classes_lost          | 0                                                |
| Performance_schema_cond_instances_lost        | 0                                                |
| Performance_schema_digest_lost                | 0                                                |
| Performance_schema_file_classes_lost          | 0                                                |
| Performance_schema_file_handles_lost          | 0                                                |
| Performance_schema_file_instances_lost        | 0                                                |
| Performance_schema_hosts_lost                 | 0                                                |
| Performance_schema_index_stat_lost            | 0                                                |
| Performance_schema_locker_lost                | 0                                                |
| Performance_schema_memory_classes_lost        | 0                                                |
| Performance_schema_metadata_lock_lost         | 0                                                |
| Performance_schema_mutex_classes_lost         | 0                                                |
| Performance_schema_mutex_instances_lost       | 0                                                |
| Performance_schema_nested_statement_lost      | 0                                                |
| Performance_schema_prepared_statements_lost   | 0                                                |
| Performance_schema_program_lost               | 0                                                |
| Performance_schema_rwlock_classes_lost        | 0                                                |
| Performance_schema_rwlock_instances_lost      | 0                                                |
| Performance_schema_session_connect_attrs_lost | 0                                                |
| Performance_schema_socket_classes_lost        | 0                                                |
| Performance_schema_socket_instances_lost      | 0                                                |
| Performance_schema_stage_classes_lost         | 0                                                |
| Performance_schema_statement_classes_lost     | 0                                                |
| Performance_schema_table_handles_lost         | 0                                                |
| Performance_schema_table_instances_lost       | 0                                                |
| Performance_schema_table_lock_stat_lost       | 0                                                |
| Performance_schema_thread_classes_lost        | 0                                                |
| Performance_schema_thread_instances_lost      | 0                                                |
| Performance_schema_users_lost                 | 0                                                |
| Prepared_stmt_count                           | 0                                                |
| Qcache_free_blocks                            | 1                                                |
| Qcache_free_memory                            | 16760152                                         |
| Qcache_hits                                   | 0                                                |
| Qcache_inserts                                | 0                                                |
| Qcache_lowmem_prunes                          | 0                                                |
| Qcache_not_cached                             | 16355                                            |
| Qcache_queries_in_cache                       | 0                                                |
| Qcache_total_blocks                           | 1                                                |
| Queries                                       | 19164                                            |
| Questions                                     | 19155                                            |
| Select_full_join                              | 0                                                |
| Select_full_range_join                        | 0                                                |
| Select_range                                  | 2677                                             |
| Select_range_check                            | 0                                                |
| Select_scan                                   | 2098                                             |
| Slave_open_temp_tables                        | 0                                                |
| Slow_launch_threads                           | 0                                                |
| Slow_queries                                  | 0                                                |
| Sort_merge_passes                             | 12                                               |
| Sort_range                                    | 4859                                             |
| Sort_rows                                     | 244452                                           |
| Sort_scan                                     | 854                                              |
| Ssl_accept_renegotiates                       | 0                                                |
| Ssl_accepts                                   | 0                                                |
| Ssl_callback_cache_hits                       | 0                                                |
| Ssl_cipher                                    |                                                  |
| Ssl_cipher_list                               |                                                  |
| Ssl_client_connects                           | 0                                                |
| Ssl_connect_renegotiates                      | 0                                                |
| Ssl_ctx_verify_depth                          | 0                                                |
| Ssl_ctx_verify_mode                           | 0                                                |
| Ssl_default_timeout                           | 0                                                |
| Ssl_finished_accepts                          | 0                                                |
| Ssl_finished_connects                         | 0                                                |
| Ssl_server_not_after                          |                                                  |
| Ssl_server_not_before                         |                                                  |
| Ssl_session_cache_hits                        | 0                                                |
| Ssl_session_cache_misses                      | 0                                                |
| Ssl_session_cache_mode                        | NONE                                             |
| Ssl_session_cache_overflows                   | 0                                                |
| Ssl_session_cache_size                        | 0                                                |
| Ssl_session_cache_timeouts                    | 0                                                |
| Ssl_sessions_reused                           | 0                                                |
| Ssl_used_session_cache_entries                | 0                                                |
| Ssl_verify_depth                              | 0                                                |
| Ssl_verify_mode                               | 0                                                |
| Ssl_version                                   |                                                  |
| Table_locks_immediate                         | 11962                                            |
| Table_locks_waited                            | 0                                                |
| Table_open_cache_hits                         | 19395                                            |
| Table_open_cache_misses                       | 817                                              |
| Table_open_cache_overflows                    | 12                                               |
| Tc_log_max_pages_used                         | 0                                                |
| Tc_log_page_size                              | 0                                                |
| Tc_log_page_waits                             | 0                                                |
| Threads_cached                                | 2                                                |
| Threads_connected                             | 1                                                |
| Threads_created                               | 3                                                |
| Threads_running                               | 1                                                |
| Uptime                                        | 2944                                             |
| Uptime_since_flush_status                     | 2944                                             |
+-----------------------------------------------+--------------------------------------------------+
353 rows in set (0.00 sec)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

mysql> SHOW GLOBAL STATUS;

+-----------------------------------------------+--------------------------------------------------+

| Variable_name | Value |

+-----------------------------------------------+--------------------------------------------------+

| Aborted_clients | 0 |

| Aborted_connects | 0 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 0 |

| Bytes_received | 3179986 |

| Bytes_sent | 223872114 |

| Com_admin_commands | 0 |

| Com_assign_to_keycache | 0 |

| Com_alter_db | 0 |

| Com_alter_db_upgrade | 0 |

| Com_alter_event | 0 |

| Com_alter_function | 0 |

| Com_alter_instance | 0 |

| Com_alter_procedure | 0 |

| Com_alter_server | 0 |

| Com_alter_table | 0 |

| Com_alter_tablespace | 0 |

| Com_alter_user | 0 |

| Com_analyze | 0 |

| Com_begin | 0 |

| Com_binlog | 0 |

| Com_call_procedure | 0 |

| Com_change_db | 284 |

| Com_change_master | 0 |

| Com_change_repl_filter | 0 |

| Com_check | 0 |

| Com_checksum | 0 |

| Com_commit | 0 |

| Com_create_db | 0 |

| Com_create_event | 0 |

| Com_create_function | 0 |

| Com_create_index | 0 |

| Com_create_procedure | 0 |

| Com_create_server | 0 |

| Com_create_table | 0 |

| Com_create_trigger | 0 |

| Com_create_udf | 0 |

| Com_create_user | 0 |

| Com_create_view | 0 |

| Com_dealloc_sql | 0 |

| Com_delete | 18 |

| Com_delete_multi | 0 |

| Com_do | 0 |

| Com_drop_db | 0 |

| Com_drop_event | 0 |

| Com_drop_function | 0 |

| Com_drop_index | 0 |

| Com_drop_procedure | 0 |

| Com_drop_server | 0 |

| Com_drop_table | 0 |

| Com_drop_trigger | 0 |

| Com_drop_user | 0 |

| Com_drop_view | 0 |

| Com_empty_query | 0 |

| Com_execute_sql | 0 |

| Com_explain_other | 0 |

| Com_flush | 0 |

| Com_get_diagnostics | 0 |

| Com_grant | 0 |

| Com_ha_close | 0 |

| Com_ha_open | 0 |

| Com_ha_read | 0 |

| Com_help | 0 |

| Com_insert | 342 |

| Com_insert_select | 0 |

| Com_install_plugin | 0 |

| Com_kill | 0 |

| Com_load | 0 |

| Com_lock_tables | 0 |

| Com_optimize | 0 |

| Com_preload_keys | 0 |

| Com_prepare_sql | 0 |

| Com_purge | 0 |

| Com_purge_before_date | 0 |

| Com_release_savepoint | 0 |

| Com_rename_table | 0 |

| Com_rename_user | 0 |

| Com_repair | 0 |

| Com_replace | 0 |

| Com_replace_select | 0 |

| Com_reset | 0 |

| Com_resignal | 0 |

| Com_revoke | 0 |

| Com_revoke_all | 0 |

| Com_rollback | 0 |

| Com_rollback_to_savepoint | 0 |

| Com_savepoint | 0 |

| Com_select | 16358 |

| Com_set_option | 849 |

| Com_signal | 0 |

| Com_show_binlog_events | 0 |

| Com_show_binlogs | 0 |

| Com_show_charsets | 0 |

| Com_show_collations | 0 |

| Com_show_create_db | 0 |

| Com_show_create_event | 0 |

| Com_show_create_func | 0 |

| Com_show_create_proc | 0 |

| Com_show_create_table | 0 |

| Com_show_create_trigger | 0 |

| Com_show_databases | 3 |

| Com_show_engine_logs | 0 |

| Com_show_engine_mutex | 0 |

| Com_show_engine_status | 0 |

| Com_show_events | 0 |

| Com_show_errors | 0 |

| Com_show_fields | 921 |

| Com_show_function_code | 0 |

| Com_show_function_status | 0 |

| Com_show_grants | 0 |

| Com_show_keys | 1 |

| Com_show_master_status | 0 |

| Com_show_open_tables | 0 |

| Com_show_plugins | 0 |

| Com_show_privileges | 0 |

| Com_show_procedure_code | 0 |

| Com_show_procedure_status | 0 |

| Com_show_processlist | 0 |

| Com_show_profile | 0 |

| Com_show_profiles | 0 |

| Com_show_relaylog_events | 0 |

| Com_show_slave_hosts | 0 |

| Com_show_slave_status | 0 |

| Com_show_status | 6 |

| Com_show_storage_engines | 0 |

| Com_show_table_status | 0 |

| Com_show_tables | 2 |

| Com_show_triggers | 0 |

| Com_show_variables | 6 |

| Com_show_warnings | 1 |

| Com_show_create_user | 0 |

| Com_shutdown | 0 |

| Com_slave_start | 0 |

| Com_slave_stop | 0 |

| Com_group_replication_start | 0 |

| Com_group_replication_stop | 0 |

| Com_stmt_execute | 4 |

| Com_stmt_close | 4 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 4 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 4 |

| Com_truncate | 2 |

| Com_uninstall_plugin | 0 |

| Com_unlock_tables | 0 |

| Com_update | 70 |

| Com_update_multi | 0 |

| Com_xa_commit | 0 |

| Com_xa_end | 0 |

| Com_xa_prepare | 0 |

| Com_xa_recover | 0 |

| Com_xa_rollback | 0 |

| Com_xa_start | 0 |

| Com_stmt_reprepare | 0 |

| Connection_errors_accept | 0 |

| Connection_errors_internal | 0 |

| Connection_errors_max_connections | 0 |

| Connection_errors_peer_address | 0 |

| Connection_errors_select | 0 |

| Connection_errors_tcpwrap | 0 |

| Connections | 292 |

| Created_tmp_disk_tables | 1124 |

| Created_tmp_files | 30 |

| Created_tmp_tables | 1369 |

| Delayed_errors | 0 |

| Delayed_insert_threads | 0 |

| Delayed_writes | 0 |

| Flush_commands | 1 |

| Handler_commit | 6094 |

| Handler_delete | 33 |

| Handler_discover | 0 |

| Handler_external_lock | 38571 |

| Handler_mrr_init | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 2299 |

| Handler_read_key | 134761 |

| Handler_read_last | 237 |

| Handler_read_next | 310119 |

| Handler_read_prev | 2733 |

| Handler_read_rnd | 222350 |

| Handler_read_rnd_next | 472820 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 15605 |

| Handler_write | 17310 |

| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 180624 23:38:01 |

| Innodb_buffer_pool_resize_status | |

| Innodb_buffer_pool_pages_data | 1035 |

| Innodb_buffer_pool_bytes_data | 16957440 |

| Innodb_buffer_pool_pages_dirty | 0 |

| Innodb_buffer_pool_bytes_dirty | 0 |

| Innodb_buffer_pool_pages_flushed | 1936 |

| Innodb_buffer_pool_pages_free | 7144 |

| Innodb_buffer_pool_pages_misc | 13 |

| Innodb_buffer_pool_pages_total | 8192 |

| Innodb_buffer_pool_read_ahead_rnd | 0 |

| Innodb_buffer_pool_read_ahead | 0 |

| Innodb_buffer_pool_read_ahead_evicted | 0 |

| Innodb_buffer_pool_read_requests | 306665 |

| Innodb_buffer_pool_reads | 950 |

| Innodb_buffer_pool_wait_free | 0 |

| Innodb_buffer_pool_write_requests | 26509 |

| Innodb_data_fsyncs | 1229 |

| Innodb_data_pending_fsyncs | 0 |

| Innodb_data_pending_reads | 0 |

| Innodb_data_pending_writes | 0 |

| Innodb_data_read | 16273920 |

| Innodb_data_reads | 1078 |

| Innodb_data_writes | 2857 |

| Innodb_data_written | 53379584 |

| Innodb_dblwr_pages_written | 1275 |

| Innodb_dblwr_writes | 109 |

| Innodb_log_waits | 0 |

| Innodb_log_write_requests | 450 |

| Innodb_log_writes | 585 |

| Innodb_os_log_fsyncs | 793 |

| Innodb_os_log_pending_fsyncs | 0 |

| Innodb_os_log_pending_writes | 0 |

| Innodb_os_log_written | 664064 |

| Innodb_page_size | 16384 |

| Innodb_pages_created | 56 |

| Innodb_pages_read | 988 |

| Innodb_pages_written | 1936 |

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 0 |

| Innodb_row_lock_time_avg | 0 |

| Innodb_row_lock_time_max | 0 |

| Innodb_row_lock_waits | 0 |

| Innodb_rows_deleted | 2 |

| Innodb_rows_inserted | 19219 |

| Innodb_rows_read | 249102 |

| Innodb_rows_updated | 77 |

| Innodb_num_open_files | 81 |

| Innodb_truncated_status_writes | 0 |

| Innodb_available_undo_logs | 128 |

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 12751 |

| Key_blocks_used | 645 |

| Key_read_requests | 321877 |

| Key_reads | 648 |

| Key_write_requests | 196 |

| Key_writes | 150 |

| Locked_connects | 0 |

| Max_execution_time_exceeded | 0 |

| Max_execution_time_set | 0 |

| Max_execution_time_set_failed | 0 |

| Max_used_connections | 3 |

| Max_used_connections_time | 2018-06-24 23:43:48 |

| Not_flushed_delayed_rows | 0 |

| Ongoing_anonymous_transaction_count | 0 |

| Open_files | 229 |

| Open_streams | 0 |

| Open_table_definitions | 206 |

| Open_tables | 786 |

| Opened_files | 502 |

| Opened_table_definitions | 208 |

| Opened_tables | 817 |

| Performance_schema_accounts_lost | 0 |

| Performance_schema_cond_classes_lost | 0 |

| Performance_schema_cond_instances_lost | 0 |

| Performance_schema_digest_lost | 0 |

| Performance_schema_file_classes_lost | 0 |

| Performance_schema_file_handles_lost | 0 |

| Performance_schema_file_instances_lost | 0 |

| Performance_schema_hosts_lost | 0 |

| Performance_schema_index_stat_lost | 0 |

| Performance_schema_locker_lost | 0 |

| Performance_schema_memory_classes_lost | 0 |

| Performance_schema_metadata_lock_lost | 0 |

| Performance_schema_mutex_classes_lost | 0 |

| Performance_schema_mutex_instances_lost | 0 |

| Performance_schema_nested_statement_lost | 0 |

| Performance_schema_prepared_statements_lost | 0 |

| Performance_schema_program_lost | 0 |

| Performance_schema_rwlock_classes_lost | 0 |

| Performance_schema_rwlock_instances_lost | 0 |

| Performance_schema_session_connect_attrs_lost | 0 |

| Performance_schema_socket_classes_lost | 0 |

| Performance_schema_socket_instances_lost | 0 |

| Performance_schema_stage_classes_lost | 0 |

| Performance_schema_statement_classes_lost | 0 |

| Performance_schema_table_handles_lost | 0 |

| Performance_schema_table_instances_lost | 0 |

| Performance_schema_table_lock_stat_lost | 0 |

| Performance_schema_thread_classes_lost | 0 |

| Performance_schema_thread_instances_lost | 0 |

| Performance_schema_users_lost | 0 |

| Prepared_stmt_count | 0 |

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 16760152 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 16355 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

| Queries | 19164 |

| Questions | 19155 |

| Select_full_join | 0 |

| Select_full_range_join | 0 |

| Select_range | 2677 |

| Select_range_check | 0 |

| Select_scan | 2098 |

| Slave_open_temp_tables | 0 |

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

| Sort_merge_passes | 12 |

| Sort_range | 4859 |

| Sort_rows | 244452 |

| Sort_scan | 854 |

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | |

| Ssl_cipher_list | |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 0 |

| Ssl_ctx_verify_mode | 0 |

| Ssl_default_timeout | 0 |

| Ssl_finished_accepts | 0 |

| Ssl_finished_connects | 0 |

| Ssl_server_not_after | |

| Ssl_server_not_before | |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | NONE |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 0 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 0 |

| Ssl_verify_mode | 0 |

| Ssl_version | |

| Table_locks_immediate | 11962 |

| Table_locks_waited | 0 |

| Table_open_cache_hits | 19395 |

| Table_open_cache_misses | 817 |

| Table_open_cache_overflows | 12 |

| Tc_log_max_pages_used | 0 |

| Tc_log_page_size | 0 |

| Tc_log_page_waits | 0 |

| Threads_cached | 2 |

| Threads_connected | 1 |

| Threads_created | 3 |

| Threads_running | 1 |

| Uptime | 2944 |

| Uptime_since_flush_status | 2944 |

+-----------------------------------------------+--------------------------------------------------+

353 rows in set (0.00 sec)

Read more on SHOW GLOBAL STATUS here. Read more on the values here.

I can see NO major errors here (possibly due to UpClouds awesome disk IO) so I won’t be making memory tweaks in MySQL. Sign Up using this link and get $25 credit free on UpCloud and see for yourself how fast they are.

Configure Ubuntu System Memory Usage

Edit /etc/sysctl.conf

Add the following to allow things to sit in ram longer

vm.vfs_cache_pressure=50

1	vm.vfs_cache_pressure=50

Snip from: https://www.kernel.org/doc/Documentation/sysctl/vm.txt

This percentage value controls the tendency of the kernel to reclaim the memory which is used for caching of directory and inode objects.

At the default value of vfs_cache_pressure=100 the kernel will attempt to reclaim dentries and inodes at a "fair" rate with respect to pagecache and swapcache reclaim.  Decreasing vfs_cache_pressure causes the kernel to prefer to retain dentry and inode caches. When vfs_cache_pressure=0, the kernel will never reclaim dentries and inodes due to memory pressure and this can easily lead to out-of-memory conditions. Increasing vfs_cache_pressure beyond 100 causes the kernel to prefer to reclaim dentries and inodes.

Increasing vfs_cache_pressure significantly beyond 100 may have negative performance impact. Reclaim code needs to take various locks to find freeable directory and inode objects. With vfs_cache_pressure=1000, it will look for ten times more freeable objects than there are."

This percentage value controls the tendency of the kernel to reclaim the memory which is used for caching of directory and inode objects.

At the default value of vfs_cache_pressure=100 the kernel will attempt to reclaim dentries and inodes at a "fair" rate with respect to pagecache and swapcache reclaim. Decreasing vfs_cache_pressure causes the kernel to prefer to retain dentry and inode caches. When vfs_cache_pressure=0, the kernel will never reclaim dentries and inodes due to memory pressure and this can easily lead to out-of-memory conditions. Increasing vfs_cache_pressure beyond 100 causes the kernel to prefer to reclaim dentries and inodes.

Increasing vfs_cache_pressure significantly beyond 100 may have negative performance impact. Reclaim code needs to take various locks to find freeable directory and inode objects. With vfs_cache_pressure=1000, it will look for ten times more freeable objects than there are."

Read these pages here and here regarding setting MySQL memory.

Reboot

shutdown -r now

1	shutdown -r now

Resize the disk

The 2x CPU, 4GB memory plan comes with 80GB storage allowance. My disk at present is 50GB and I will update the size soon following this guide soon.

Quick Benchmark

I used loader.io to load 500 users to access my site in 1 minute.

The benchmark worked with no errors.

This benchmark was performed with no Cloudflare caching. I should get Cloudflare caching working again to lower the average response time. I loaded my website manually in Google Chrome while loader.io threw 500 users at my site and it loaded very fast.

Conclusion

After a few days, I checked my memory logs and there were no low memory triggers (just normal internal memory management triggers). Ubuntu was happier.

This graph was taken before I set “vm.vfs_cache_pressure” so I will update this graph in a few days.

I hope this guide helps someone.

Please consider using my referral code and get $25 credit for free.

https://www.upcloud.com/register/?promo=D84793

Ask a question or recommend an article

Revision History

1.0 Initial Post

Measuring VM performance (CPU, Disk, Latency, Concurrent Users etc) on Ubuntu and comparing Vultr, Digital Ocean and UpCloud – Part 4 of 4

June 5, 2018 by Simon

How can you measure VM performance (CPU, Disk, Latency, Concurrent Users etc) on Ubuntu and comparing Vultr, Digital Ocean and UpCloud – Part 4 of 4

Read Part 1, Part 2, Part 3 or Part 4

I ran the MySQL benchmark preparation command again (no problem this time).

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=###################### prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=###################### prepare

sysbench 0.4.12: multi-threaded system evaluation benchmark

Creating table 'sbtest'...

Creating 1000000 records in table 'sbtest'...

Test table and records created

Now I can benchmark MySQL on my main server.

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=################################# --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

1	sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=################################# --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

RAW Output

sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            336210
        write:                           0
        other:                           48030
        total:                           384240
    transactions:                        24015  (400.09 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 336210 (5601.24 per sec.)
    other operations:                    48030  (800.18 per sec.)

Test execution summary:
    total time:                          60.0242s
    total number of events:              24015
    total time taken by event execution: 480.0242
    per-request statistics:
         min:                                  1.79ms
         avg:                                 19.99ms
         max:                                141.00ms
         approx.  95 percentile:              37.49ms

Threads fairness:
    events (avg/stddev):           3001.8750/27.36
    execution time (avg/stddev):   60.0030/0.01

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 8

Doing OLTP test.

Running mixed OLTP test

Doing read-only test

Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Threads started!

Time limit exceeded, exiting...

(last message repeated 7 times)

Done.

OLTP test statistics:

queries performed:

write: 0

other: 48030

total: 384240

transactions: 24015 (400.09 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 336210 (5601.24 per sec.)

other operations: 48030 (800.18 per sec.)

Test execution summary:

total time: 60.0242s

total number of events: 24015

total time taken by event execution: 480.0242

per-request statistics:

min: 1.79ms

avg: 19.99ms

max: 141.00ms

approx. 95 percentile: 37.49ms

Threads fairness:

events (avg/stddev): 3001.8750/27.36

execution time (avg/stddev): 60.0030/0.01

Results

queries performed (in 60 seconds):

read: 336210
other: 48030
total: 384240

I decided to add an index to see if I can speed this query up (read the MySQL index page here). I added an index (in Adminer) on the columns “Id” and “pad” for the sbtest table in the test database

I restarted the MySQL process

mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.

1 2	mysql restart [ ok ] Restarting mysql (via systemctl): mysql.service.

I ran the same benchmark again.

Raw Output

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=########################## --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            426538
        write:                           0
        other:                           60934
        total:                           487472
    transactions:                        30467  (507.69 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 426538 (7107.67 per sec.)
    other operations:                    60934  (1015.38 per sec.)

Test execution summary:
    total time:                          60.0110s
    total number of events:              30467
    total time taken by event execution: 479.9124
    per-request statistics:
         min:                                  5.75ms
         avg:                                 15.75ms
         max:                                138.57ms
         approx.  95 percentile:              25.10ms

Threads fairness:
    events (avg/stddev):           3808.3750/8.70
    execution time (avg/stddev):   59.9891/0.00

sysbench --test=oltp --oltp-table-size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=root --mysql-password=########################## --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:

Number of threads: 8

Doing OLTP test.

Running mixed OLTP test

Doing read-only test

Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Threads started!

Time limit exceeded, exiting...

(last message repeated 7 times)

Done.

OLTP test statistics:

queries performed:

write: 0

other: 60934

total: 487472

transactions: 30467 (507.69 per sec.)

deadlocks: 0 (0.00 per sec.)

read/write requests: 426538 (7107.67 per sec.)

other operations: 60934 (1015.38 per sec.)

Test execution summary:

total time: 60.0110s

total number of events: 30467

total time taken by event execution: 479.9124

per-request statistics:

min: 5.75ms

avg: 15.75ms

max: 138.57ms

approx. 95 percentile: 25.10ms

Threads fairness:

events (avg/stddev): 3808.3750/8.70

execution time (avg/stddev): 59.9891/0.00

Results

The quick index added 20% extra throughput on queries 🙂

Don’t forget to delete your test database

DROP DATABASE `test`;

1	DROP DATABASE `test`;

Viewing MySQL Index Usage (on the “test” database)

Query to show Index stats for a table ‘test’

SELECT
 OBJECT_SCHEMA as 'Database', OBJECT_NAME as 'Table', 
 INDEX_NAME as 'Index', 
 COUNT_STAR, 
 SUM_TIMER_WAIT,  MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT, 
 COUNT_READ, 
 SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ,  
 COUNT_FETCH, SUM_TIMER_FETCH, MIN_TIMER_FETCH, AVG_TIMER_FETCH, MAX_TIMER_FETCH
FROM 
 performance_schema.table_io_waits_summary_by_index_usage
WHERE 
 object_schema = 'test'

SELECT

OBJECT_SCHEMA as 'Database', OBJECT_NAME as 'Table',

INDEX_NAME as 'Index',

COUNT_STAR,

SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT,

COUNT_READ,

SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ,

COUNT_FETCH, SUM_TIMER_FETCH, MIN_TIMER_FETCH, AVG_TIMER_FETCH, MAX_TIMER_FETCH

FROM

performance_schema.table_io_waits_summary_by_index_usage

WHERE

object_schema = 'test'

I can see the MySQL PRIMARY index is getting used 🙂

Read more in viewable query stats (columns) here.

Other System Information Tools

Show processor information

cat /proc/cpuinfo

1	cat /proc/cpuinfo

Output

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 61
model name      : Virtual CPU a7769a6388d5
stepping        : 2
microcode       : 0x1
cpu MHz         : 2394.454
cache size      : 16384 KB
physical id     : 0
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level

Personal Development Blog...

Coding for fun since 1996, Learn by doing and sharing.

Buy a domain name, then create your own server (get $25 free credit)

"If you're not still learning, you're already dying."
- Ryan Holiday - Ego is the Enemy

Follow me on Twitter: @FearbySoftware

View All Posts.

on

How to backup and restore a MySQL database on Windows and Linux

Why backup and restore

Murphy’s Law

Backup (the easiest way) with Adminer

Restore (the easiest way) with Adminer

Bonus methods.

How to install PHP 7.2.latest on Ubuntu

How to install PHP 7.2.latest on Ubuntu 16.04

Adding two sub domains (one pointing to a new UpCloud VM and the other pointing to an NGINX subsite) on Ubuntu 18.04

Upgrading an Ubuntu server on UpCloud to add more CPU, Memory and Disk Space

Upgrading an Ubuntu server on UpCloud to add more CPU, Memory and Disk Space

Measuring VM performance (CPU, Disk, Latency, Concurrent Users etc) on Ubuntu and comparing Vultr, Digital Ocean and UpCloud – Part 4 of 4