Mysql 8 performance schema I am using WordPress with mysql8 and the memory usage of the mysql process was above 60% causing server issues. Updating the my.cnf file to include. MySQL 8.x actually IS supported, but requires a slightly different command when creating the user and password, as version 8 expects passwords to be SHA256 encoded. When creating the database user, with the MySQL prompt, use the following: ALTER USER 'username'@'localhost' IDENTIFIED WITH mysqlnativepassword BY 'password'. WordPress is the popular and widely used blogging and content management system in the world. PHP processing and MySQL/MariaDB database are used as a backend in WordPress. It is simplest way to create a Blog, Portfolio Website, webshop etc. In this tutorial, we will show you how to install WordPress with Apache on CentOS 8. How to Upgrade WordPress to MySQL 8 2018-10-27 2 comments. So today I’ve been upgrading a bunch of my servers to install latest releases of software, which is why I needed to upgrade wordpress to MySQL 8. Which is a thing that everyone who is responsible for managing number of servers is supposed to do once in awhile. WordPress blogging platform is a PHP based web application and uses a MySQL database as a backend database. In this article we will explain in easy to follow step by step format how to manually create a MySQL database for your WordPress blog or website.
WordPress requires that you use a MySQL compatible database for its database backend. It used to be that you could confidently choose MySQL and go on with life, but in 2019 the choice isn’t quite that simple. With the MySQL, MariaDB, and Percona as the attractive options, how do you know which to choose?
Choosing a database isn’t always about performance, but for the sake of this article it will be 🙂
For this series of tests we tested database performance out-of-the-box with no special tweaks. It is quite possible that a professional database administrator could make each database run more performant, but most people hosting WordPress aren’t DBAs. With regards to caching, none was enabled. We wanted to test database performance, not cache performance.
What was tested?
The WordPress host machine was a DigitalOcean CPU Optimized droplet with 16 vCPUs (dedicated hyper-threads) and 32 GB of RAM. This monster of a machine was chosen so that we could be certain that Nginx + PHP-FPM weren’t the cause of any bottlenecks. A minor tweak to the PHP-FPM config allowed for full use of all 16 vCPUs.
The database host machines were DigitalOcean CPU Optimized droplets with 4 vCPUs (dedicated hyper-threads) and 8GB of RAM. CPU Optimized droplets were chosen because we didn’t want our tests to be at the mercy of shared CPU resources.
Each deployment was in DigitalOcean’s SFO2 region with the WordPress server communicating with the database over the internal private network. The traffic producing nodes were deployed in DigitalOcean’s NYC3 data center and communicated via the public internet.
The software versions are as follows:
- Percona Server for MySQL 8.0.16-7
- MariaDB 10.4.8-GA
- MySQL 8.0.18
What tests were run?
Want to test your site’s performance? Sign up for Kernl WordPress Load Testing.For each database that was tested, we ran a load test with the following parameters:
- 500 concurrent users
- 2 req/s ramp up
- 30 minute duration
The goal here wasn’t to bring the database to it’s knees but instead see how it performed under sustained heavy load, but not so heavy that it falls over.
MariaDB WordPress Performance
During the MySQL acquisition of Oracle in 2009 there was a lot of concern amongst the core developers that Oracle would eventually close off MySQL to the world (similar to Oracle’s business model). Before that could take place, a GPL fork of MySQL was created named MariaDB.
MariaDB is open source and in active development. But how does it stand up to our WordPress load tests? Let’s find out.
First we’ll take a look at the requests and failures per second.
As you can see from the results above the performance scales up very well over time eventually peaking at ~379 req/s. We see periodic database-related errors (the vCPUs on the database were almost completely saturated) but nothing too crazy. Next, let’s see how the response times look.
The median response time of WordPress when backed by MariaDB under heavy load stays remarkably consistent. You can see that the average is slowly creeping up by the end of the test, but nothing that would be noticeable by customers yet.
The response time distribution is a little more interesting than the median response time. 90% of all requests finish in under 300ms and 99% of all requests finish in less than 500ms. Overall, the performance of MariaDB out of the box with no configuration is quite good.
Percona WordPress Performance
Another open-source fork of MySQL, Percona was started in 2006 and has been steadily delivering value-added features and enterprise support on top of MySQL for more than 12 years. With all that accumulated experience, how does Percona measure up?
Overall the Percona WordPress performance was pretty good. Not quite a good as MariaDB but nothing to scoff at. The one interesting thing was that the error rate was consistent across most of the test once the request volume passed ~200 requests/s. Lets see if the response time graph adds to the story.
The data here is actually pretty interesting. The response time for Percona was comparable to MariaDB right up until the time it started getting consistent errors. After that, it more than doubled and stayed that way for the rest of the test.
The response time distribution tells similar story to the response time chart. The difference between the 50th percentile and the 99th percentile shows that performance was very consistent across the entire test, it just wasn’t as good as MariaDB.
MySQL WordPress Performance
Last but not least (well….) in our test is MySQL. It’s been around since 1994 and is now owned by Oracle. In it’s latest releases there has been lot of great features such as window functions and more JSON features to compete with PostgreSQL. So let’s see how it did.
MySQL had a similar trajectory to Percona: It did pretty good across the board with a small but consistent series of errors after it started to go past 200 req/s. Also note that it never achieved higher than 300 req/s, which both MariaDB and Percona did.
The shape of the MySQL response time graph looks nearly identical to Percona, just about 50ms slower once the vCPUs started to get saturated. The detailed look in the response time distribution tells a similar story.
The response time distribution is where you can see Percona and MySQL diverge a bit. At the 99th percentile MySQL is returning at 875ms, while Percona was more like 550ms. In general, the distribution matches what one would expect given how the response time graph looks
Conclusions
The out of the box numbers for MariaDB make it look like the clear winner here. And compared to both Percona and MySQL it is in the raw performance department. This isn’t to say that you couldn’t tune Percona or MySQL to out-perform MariaDB, but only that you get more performance with zero configuration changes.
Wordpress backed by Scaleway Database for MySQL 8 - Overview
In this tutorial you will learn how to setup your own Wordpress blog on a Virtual Compute Instance and backed by Scaleway Database for MySQL 8 to improve your blogs speed, reliability and overall performance. Scaleway Database for MySQL 8 provides up to 48 cores and 256 GB of RAM.
WordPress is a widely used, free, and open-source content management system (CMS) written in PHP. It uses the MySQL relational database system to store its contents. The look and feel of WordPress is highly customizable using different themes and plugins.
Requirements
- You have an account and are logged into console.scaleway.com
- You have configured your SSH Key
- You have a Scaleway Elements Instance running Ubuntu Bionic Beaver (18.04 LTS)
- You have a Managed Database for MySQL v8
Preparing the managed database
1 . Go to the Database section of your Scaleway console and choose your Managed Database instance from the list:
2 . Click on the Databases tab. A list of your existing databases exist. Click + Create Database to create a new database:
3 . Enter a name for your new database, for example wordpress and confirm by clicking on Create Database:
Your database is being created now.
Optionally: You can restrict access to your database instance to your compute instance for increased security.
Important: This is an advanced feature and may cause unavaibility of your database. Be careful when using it.
1 . Click on the Allowed IPs tab to see the list of allowed IPs:
2 . Click on the delete button to delete the default rule (0.0.0.0/0 Allow All).
3 . Click Add IP and enter the IP of your compute instance in CIDR notation (for example: 51.15.123.234/32)
4 . Confirm by clicking the Add IP button
Preparing the compute instance
To host Wordpress on our Virtual Instance, we need to install the required software on it. In this tutorial we will use the Apache Webserver with PHP 7.4 to host our blog.
1 . Connect yourself to your compute instance using SSH.
2 . Update the apt package manager and upgrade the software already installed on the machine to make sure you have installed the latest upgrades and security patches:
2 . Install the Apache web server and PHP:
Downloading and installing Wordpress
1 . Download the latest Wordpress relase using wget:
2 . Unpack the downloaded file using the following tar command:
3 . Move the content of the wordpress folder in a directory under the document root for Apache by creating a new folder for your blog (i.e. blog.example.com if you want to host your blog at https://blog.example.com/).
4 . Configure the ownership and file permissons on the blog directory. To make sure your blog works as expected, the directory should be owned by the Apache2 user and group called www-data.
5 . Enter the blog directory and create a wp-config.php file by copying the example file shipped with the application:
6 . Open the configuration file in a text editor, for example nano and edit the MySQL server configuration as follows:
Edit the hostname for your Managed database in the form: database_ip_address:database_port for example: 51.15.151.53:6241
Save the file and exit nano.
Wordpress Mysql 8.0
7 . Create a Apache configuration file for your new blog by opening it in a text editor, we use nano again:
Copy and paste the following content into the file, then save it and quit the text editor:
8 . Test the Apache configuration file for syntax errors:
Wordpress Can't Connect To Mysql 8
9 . If no errors appear, enable the new site and reload Apache2 to activate the configuration:
Securing the connection using TLS
By default HTTP is a plain text protocol and does not use any encryption. To secure the connection between the client and the server, we are going to use the TLS protocol using a certificate issued by the Let’s Encrpt certificate authority. We are going to use a tool, called certbot which manages the the configuration of the Apache2 web server, the certificate creation and renewal of it. Certbot is developed and maintained by the Electronic Frontier Foundation (EFF].
1 . Prepare the system by installing the required dependencies:
2 . Install certbot using the apt package manager:
3 . Run certbot to configure the Apache2 web server and to request the certificate for your domain name:
During the certificate generationn, you will be prompted the following questions:
You have now successfully requested a TLS certificate for your blog. The connection between your vistors and the server is encrypted and secured.
Configuring Wordpress
Now, as Wordpress is successfully installed and connections between your users and the server are using a secure HTTPS connection, it is time to finally configure your blog.
1 . Open your web browser and type your blogs URL in the address bar of your web browser (for example: https://blog.example.com).
2 . The Wordpress installation wizard displays. Choose the desired language for your blog from the list and click Continue:
3 . Enter the details of your new blog, such as the title, choose a name for the admin user and set a secret password. Then click Install Wordpress to complete the installation of your blog:
Wordpress Mysql 8
4 . The installation of your blog has been successfully. You can log in now and write your first articles or configure the look and feel of Wordpress by installing additonal themes and modules.
Wordpress Mysql 8 Command
You have now installed and configured your blog successfully completed using a powerful Scaleway Database for MySQL 8. All content and information of your blog is being stored in this database. For more information how to customize your wordpress installation to change its look and feel, refer to the official Wordpress developer documentation.