Tuning database for a single WordPress website on VPS

Nasser · March 11, 2023, 4:36pm

Hello,

I have just installed HestiaCP on VPS with Debian 11.

Then I changed the PHP parameters to the following

I have also changed mysql default values in /etc/mysql/my.cnf to the following

key_buffer_size = 2048M
max_allowed_packet = 1024M
table_open_cache = 2048
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 32M
thread_cache_size = 16
query_cache_size= 32M

Finally, I have added the following to WordPress wp-config.php

/** Memory Limit */
define(‘WP_MEMORY_LIMIT’, ‘2048M’);
define( ‘WP_MAX_MEMORY_LIMIT’, ‘2048M’ );

I did multiple benchmarking and the performance of the database was below expectation

Can someone help achieve better performance with my database?

Thank you for your support.

eris · March 11, 2023, 5:25pm

There should be a topic about it… But can’t find it anywhere

Nasser · March 11, 2023, 5:45pm

Hello Eris,

Thank you for your response.

I have searched before posting this but I didn’t find anything about database tuning.

Nasser · March 12, 2023, 2:30pm

Hello,

This is the best I could manage to achieve with this VPS

My final edit to /etc/mysql/my.cnf

thread_cache_size = 32
table_open_cache = 2048
sort_buffer_size = 8M
innodb = force
innodb_buffer_pool_size = 512M
innodb_log_file_size = 1GB
innodb_stats_on_metadata = OFF
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 10M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 6
join_buffer_size = 8M
tmp_table_size = 128M
key_buffer_size = 128M
max_allowed_packet = 64M
max_heap_table_size = 128M
read_rnd_buffer_size = 16M
read_buffer_size = 2M
bulk_insert_buffer_size = 64M
max_connections = 512
myisam_sort_buffer_size = 128M
explicit_defaults_for_timestamp = 1
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
log_bin_trust_function_creators = 1
disable_log_bin
max_user_connections=50
wait_timeout=10
interactive_timeout=50
long_query_time=5

Please share any idea that can help improve the database performance other than Object Caching.

desp · March 12, 2023, 6:05pm

Hi. First of all, I’m NOT EXPERT. But i have some experience with tuning and performance issues & wordpress.

Lets divide problem to small parts, for easier understanding what is going on.

To understand, that your DB is slow, you must to have some tool to trace this “slowness”.
Have you did that? How you know that your db slow, but nothing else? Have you traced that? Have you used Debugging in WordPress – Documentation

or Query Monitor – WordPress plugin | WordPress.org

Have you trace “big queries”? Or complex queries? Have you

got explanation of what query do for you?

Are you sure that your db has all indexes for fast access data?

Have you analyzed your db tables at all? Or queries?
Have you checked that your db has all indexes? Maybe there are some stupid plugins who do extremely big & complex queries in infinite loop and lock your db? Who knows?

For example I know that plugin called Ultimate Membership - bugged for many many years, provoke dramatical load on db, and nobody care to add proper indexes to dbs, or fixes for that.

While simple plugin like that: Index WP MySQL For Speed – WordPress plugin | WordPress.org
can solve the issue (backups must have before doing such operation)

Do you see how many things i just started to point before ever touching your config files?
That the key.

In short, you need not touching default config in hestiaCP which is great.
But instead to install trace/debug software to understand what is going on.
And measure the time for queries, for load, what is going on, response times, and so on.
All tools for that above mentioned.

Also, are you sure that your db not broken? Repaired? Or optimized?

This is just example.

Done all of that, and nothing helped?

Who said that your server is good?
What about testing your hardware first?

How to understand that your server is okay?

Single Core performance must be > 600, ideally more than 1k
IOPS > 1k, ideally more - better for small chunks of data
Nothing else does not really matter.

Does not help?

What version of software do you use?
Do you know that perfromance difference between php versions between 8.0 and 8.1 and 8.2 different?

Are you using latest version? Opcache enabled?

How about caching? Lets talk about caching thing for your queries.
If your mysql answers is slow, and you done all things above.

What about adding a Redis to your server & redis plugin for your WP?

Redis will be like a layer between your app & mysql and will cache popular queries and give them instantly pre-made answers from memory. Which dramatically boosts performance.

github.com

rhubarbgroup/redis-cache/blob/develop/INSTALL.md

# Installing Redis Object Cache

If you manage plugins using Composer, follow [these instructions](#composer-instructions).

## 1. Installing Redis server

This plugin requires Redis as the persistent object cache backend, so first, make sure [Redis Server](https://redis.io) is installed and running. You can [install it yourself](http://redis.io/topics/quickstart), or ask your hosting company for assistance.

## 2. Installing the plugin

Next, install the `Redis Object Cache` plugin via the WordPress Dashboard, or using Composer. For detailed installation instructions, please read the [standard installation procedure for WordPress plugins](https://wordpress.org/documentation/article/manage-plugins/#finding-and-installing-plugins-1).

## 3. Configuring the plugin

After installing and activating the plugin, go to `WordPress -> Settings -> Redis` or `Network Admin -> Settings -> Redis` on Multisite networks. There, enable the cache and check if the plugin can connect automatically.

If not, you must edit the `wp-config.php` file in your `/wp-content` directory. By default the object cache will connect to Redis Server over TCP at `127.0.0.1:6379` and use database `0`,
if you see `Status: Not connected` either ask your hosting provider for assistance, or [configure the connection yourself](https://github.com/rhubarbgroup/redis-cache/#configuration).

A good starting configuration is:

This file has been truncated. show original

Have you tried all of that?

Your changes in config file above - is VERY bad.

These changes will increase loading, and will add more problems to your VPS & projects. Do not do that.

The time when you need to tune default settings in mysql - it’s when you tried all above, and you know for sure that after changes and your analysis - your server got faster.

You increased caches, but you do not understand how it works. In result when there will be a lot of cache - your db will dramatically slow, because will read data from swap. because of luck of memory & big loops of searching operations due to big cache sizes.

DO NOT DO THAT.

HestiaCP default configs = great.
Try to first understand what is the reason that slow down your website.

Nasser · March 17, 2023, 10:45am

Hello desp,

I appreciate you taking the time to help me out.

I have checked every step and what help me out the most is benchmarking the server

This is the result before

This is the result after

The support team of my server was kind enough and give me a free upgrade to a better VPS.

Thank very much.

Best regards,
Nasser

desp · March 17, 2023, 11:50am

This is okay results for 10+ yearso ld hardware to have 600 bench for CPU.
But modern CPU can in single thread do 1500 benches.
At least something.

About 300 points in previous bench - this is not really bad, this is ALARM, ALERT to run away from such provider. This is big sign of extremal level of oversharing CPU among clients on the same node near to you.

Also, optionally it can be because someone on the your node abused CPU too much, and support not really care about that until you report to them.

Your current 600 points ± okay for that CPU. So probably it’s ok for now.
But if after some time you will notice performance degradation - just run away from the provider anywhere else. To any other provider, not a big one, but some small/medium sized provider.

Ideally on ryzen-like CPU, or modern intel cpus.
And you will notice very big performance jump.

NVMe for sure.

Nasser · March 23, 2023, 12:57pm

desp

Thank you for following up with me.

After monitoring for a few days I have noticed degradation and this is the result.
The Single-Core Score was 625 and today is 146
The Multi-Core Score was 1516 and today is 178

My hosting provider is RackNerd. What are the small/medium sized providers you recommend ?

Best regards,
Nasser

eris · March 23, 2023, 1:21pm

Hetzner
https://hetzner.cloud/?ref=yVXZRaYoKYdW

themew · March 23, 2023, 1:23pm

One of the VPS providers we’ve used for years is Racknerd and have never had an issue with them.

They have dozens of locations so you may want to reach out to their support team and notify them of the issue you’re having and/or request they move you to another machine or another location that may not experience the issues you’re describing.

Nasser · March 23, 2023, 2:13pm

eris,

I already have an account with Hetzner.
Single-Core Score: 1108
Multi-Core Score: 2654

This is Today result for the server

But it’s expensive in comparison to RackNerd. That’s why I migrated to RackNerd. However, the performance became an issue right now and I am considering another hosting provider.

Best regards,
Nasser

Nasser · March 23, 2023, 2:17pm

Hello themew,

Thank you for your reply.

This is the support reply:

Hello Nasser,

Please note that geekbench values varies on a KVM VPS since it runs over a hypervisor and as a guest machine. If you want the exact geekbench values, it should be a dedicated server with dedicated hardware resources.

Thank You,

Sreejit Rajan
Senior Systems Administrator
E@ [email protected]

I am not sure how can I prove to them the issue after this response?

Best regards,
Nasser

jlguerrero · March 23, 2023, 4:30pm

That’s the thing with vps. I changed to physical machines on hetzner and so you start (ovh) and I have seen a great improvement in the performance.

Raphael · March 23, 2023, 4:49pm

this!

VPS is mostly bit stable, get a hetzner ax server, install it using proxmox and create your own nodes - that will be the way to get max performance.

desp · March 23, 2023, 5:37pm

just move away from this shit provider. Really.
Do not try to argue. They’re cheap because they’re oversell.
Imagine VirtualBox.
Imagine you have real hardware PC for example 8 cores and 32GB ram.
Now imagine that you make 8 VPS, and allocate each of your CPU core to each VPS.
You got total 8 VPS.
And performance awesome or best possible.

How usually VPS providers do:
They allocate to 1 CPU core around 4-6 clients.

Because 4-6 clients at the same time usually most of the time not running CPU intensive tasks. But if all clients at the same time will do that, it will affect everyone. But chance that this will happen if allocated for example 5 clients per CPU core = pretty low.

But imagine that you allocate not 5, but for example 15 clients on the same CPU core with fixed 3.2Ghz for example?

What about 30 clients?

How about 50?

Or even 70?

How fast will apps running on the same real PC insiode 560 virtualboxes running at the same time on 8 cpu cores only? I think you got idea.

They doing the same.
Just avoid them.
Purchase adequate VPS in adequate price range like 5-7-10 usd / mo and pay for that.
When you paying like annually payment below 20 usd for 1GB / year = something for sure not okay with the VPS.

Because IP price ~15 usd / year sometimes for some providers.

5 usd for resources left that need to rent/puchase/delvier allocate to you from your $20 per year? Really?

nah. Something for sure wrong

Guys above already mentioned good providers like hetzner, ovh.
There are also linode, vultr, extravm, lunanode, alphaVPS

You’re forced to pay minimum 5-10 usd / mo for VPS with decent performance.
Or you will face trash that racknerd offer to you for cheaper price.

Its up to you.

141 bench - awful. This is not shit, this is below shit.
If i was on your position - i will for sure say some kind words to this provider, and do refund (depends on price for vps).

Because this is not a performance at all. This is totally crap shit.
Sorry for cursing.

Nasser · March 23, 2023, 8:15pm

Hi jlguerrero,

Thank you for your response.

I am trying to balance between the cost and the performance because I am not running a business or making a profit from my website. However, I have found very good deals on hetzner for a huge boost in performance.

Thank you.

Nasser · March 23, 2023, 9:08pm

ScIT

I am not that expert, but I learned many things about server management, thanks to you guys and I will start experimenting with dedicated servers with proxmox very soon.

Thank you for your support and for this great control panel.

jlguerrero · March 23, 2023, 9:32pm

Search for hetzner auction

Nasser · March 23, 2023, 9:38pm

Hello desp,

I argued with them and they asked for the SSH details to look as a “courtesy”. Then after few minutes I received an email from them “Emergency Power Maintenance”.

Additional Information: We have identified a failing PDU, which can cause power stability issues in production. Therefore, on-site technicians will be replacing the rackmountable PDU that these 3 servers are connected to.

Due to the nature of the issue causing symptoms that could be negatively impactful to production, we will be proceeding with this emergency maintenance as soon as possible (at any point within the next two hours of this e-mail).

When I asked the support if this is related to my issue they replied

Hello,

That may have been related since the servers were earlier receiving an unstable power connection. The maintenance has now been completed. How do things look now on your end?

Thank You,

After the maintenance the performance have improved but it is less than what I had on 17.03.2023

I will keep eye on hetzner auction for a good deal

I appreciate your help, Thank you very much.

Nasser · March 23, 2023, 9:41pm

I am keep an eye on the auction for a good deal.

Thank you.