Nginx Reverse proxy in front of WordPress and MySQL slowness

Question

I have the following setup:

1 server with 4 cores / 8 HT (Intel Xeon E3-1231 v3) with 32GB RAM and 2TB HDD I've installed Proxmox on it. Then I've created a few KVMs:

1. Nginx Reverse proxy - Public IP 192.168.0.10, Internal IP 10.10.10.10

   • The IPs are set for example purposes only, the real IPs are different

2. Nginx which servers only static content website - Only Internal IP 10.10.10.20

3. Nginx + PHP-FPM which servers WordPress - Only internal IP 10.10.10.30

4. MySQL - Only internal IP 10.10.10.40

All servers communicates trough the internal network. The "lag" is 0.200ms - 0.350ms

I also have 2 websites:

1. example.com - pointed via A record to Nginx Reverse Proxy's Public IP 192.168.0.10

2. wp.example.com - pointed via A record to Nginx Reverse Proxy's Public IP 192.168.0.10

Nginx Reverse Proxy is configured to proxy_pass the requests based on the website entered in the browser.

When you try to load example.com NGINX reverse proxy pass the connection to internal IP 10.10.10.20 where another nginx serves the html website - example.com

So far so good. Everything works good.

However, the issue occur when someone try to enter wp.example.com, NGINX reverse proxy pass the connection to IP 10.10.10.30 where another nginx + php-fpm serves the php files.

So far so good, however I've installed WordPress and use MySQL server which is setup on another KVM with IP 10.10.10.40. Ok. Everything works, however the connection to and from MySQL server seems to be slow, as the entire wordpress website is loading too slow. If only php files are served (without any mysql queries) the website is loading super fast, however when WordPress is installed it makes connections to the databases which is on another server and the things start to work slow...

None of the machines are loading high at all. They have a lot of free CPU, disk space and RAM. However for some reason MySQL (MariaDB 10.2.6) is the bottleneck here... but I do not know why...

Here is the conf file from my NGINX reverse proxy:

server  {
    listen  80;
    server_name example.com www.example.com;
    location / {
        proxy_pass http://10.10.10.20:80/;
    }
}
server {
    listen 80;
    server_name wp.example.com www.wp.example.com;
    location / {
        proxy_pass http://10.10.10.30:80/;
    }
    proxy_set_header X-Forwarded-Host $host;
    proxy_set_header X-Forwarded-Server $host;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header Host $host;
}

Here is the nginx conf file from my KVM server who serves PHP website - WordPress:

server {
    listen       10.10.10.30:80;
    server_name  wp.example.com www.wp.example.com;
    root   /var/www;
    index index.php index.html index.htm;
    location / {
         try_files $uri $uri/ =404;
    }
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    location = /50x.html {
         root /usr/share/nginx/html;
    }
    location ~ \.php$ {
         try_files $uri =404;
         fastcgi_pass unix:/run/php-fpm/php-fpm.sock; 
         fastcgi_index index.php;
         fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
         include fastcgi_params;
    }
}

How I can troubleshot why WordPress is loading too slow? There aren't packet drops between servers, also the connections are good I think... only 0.200 - 0.350 ms between servers...

I think the slowness occur because WordPress tries to make connections to external addresses but as the server where wordpress "lives" doesn't have public internet connection, this slows the things... I am not sure if this is the reason... but it could be?

Answer 1

Here are the answers to your questions:

1. I am connecting to the MySQL via IP in wp-config.php
2. IP.
3. I am connecting directly to the MySQL, no proxy is used here.
4. No, I am not using SSL as the MySQL server "lives" in isolated KVM with only private network
5. No I am not... It is possible to refer me to a guide?
6. MySQL use HDD, however it has only 1 default WordPress database in it with only a few connections, as I am the only one opening my website...
7. I am using the default values in my.cnf for now
8. The MySQL server and the WordPress server is on the same Proxmox node, they are KVM machines inside same Proxmox Node

I've performed some tests, and it seems ONLY a few parts from WP are slow: navigating to plugins menu from the admin area navigating to theme menu from the admin area

Also when I login to the admin area.. its slow...

I think this is some how related to the lack of Internet connection... only private network...

Answer 2

A few things pop into my mind:

1. How are you connecting from wordpress to the mysql server (the wp-config.php file)?
2. Are you using the IP address or a DNS?
3. Do you connect directly to the mysql server or do you have a proxysql in the middle?
4. Are you using ssl to connect to mysql?
5. Have you done a benchmark on the mysql server, and determine how many queries it can take from the remote wp server?
6. Is the mysql server using an SSD? HDD will be slow...
7. Have you tweaked the my.cnf file, or are you using the default settings?
8. Are the mysql and wp servers on the same zone, within the same region?

For mysql, I would suggest removing mariadb and installing Percona. You can generate a new my.cnf from there too: https://tools.percona.com/

I also recently installed MariaDB 10.2 and was wondering why it was much slower than 10.1... and percona solved it for me.