Tips for WordPress performance: Page 2 - Enhance Control Panel

Tips for WordPress performance

xyzulu

DracoBlue I don't think 128Mb is the default on Enhance.

DracoBlue

xyzulu The initial value for MariaDB in the Enhance panel was set to 128MB, but due to the recent updates, it has been changed to 512MB.

DracoBlue

xyzulu
I provided the values that worked in my case, improving the database performance. I also mentioned earlier that the author of this topic must set the values according to their server. So, I don't understand your insinuations. The results speak for themselves.

Oldschool

DracoBlue
Regardless of what plesk recommends, it's not ACID compliant and it risks a few seconds of potential data loss.

dg230124

Wow, a lot happened here while I was sleeping.

Very interesting. Thank you very much at this point.

It's a WordPress installation with database on the CloudServer CPX11 from Hetzner.

2 AMD vCPU
2 GB RAM
40 GB NVMe SSD

Under the following link there are also tips for database optimization:

https://wpbenchmark.io/improve-wordpress-speed

However, I have never dealt with database optimization before.

What settings would you recommend for the server?

DracoBlue

dg230124 Also, what do you use: MySQL or MariaDB?

dg230124

Ah, I forgot.

I use MariaDB.

DracoBlue

dg230124 Check this

[mysqld]

innodb_flush_log_at_trx_commit = 2
innodb_flush_neighbors = 0
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_io_capacity = 200
innodb_random_read_ahead = OFF
innodb_buffer_pool_size = 1G
table_open_cache = 1024
innodb_log_file_size = 32M

Let me know!

AdamM

DracoBlue innodb_buffer_pool_size = 1G for server with 2gb ram? You're giving bad advice...

DracoBlue

AdamM Typically, it's recommended to allocate around 50-70% of the available RAM. However, in the case of a server with only 2GB of RAM, you are correct. In this scenario, I would recommend adjusting it to around 512MB.

dg230124

Here are the results with innodb_buffer_pool_size = 512M:

WordPress Hosting Benchmark Tool

WPPerformanceTester

I think it won't get any better than this.

Thanks for the help.

DracoBlue

dg230124 This is a good result.

josedieguez

dg230124

Your result is not because you configured the innodb pool size.

It's because you used this:
innodb_flush_log_at_trx_commit = 2

(i'm assuming you used it).

Innodb pool size, should be configured using something like mysqltuner, not from a forum recomendation, since depends on your RAM size and your mysql innodb data size too.

dg230124

josedieguez

Thanks for the tip.

I installed MySQLTuner on my server and let it run. Before that, I removed all optimizations in my.cnf.

Here are the InnoDB metrics:

[--] InnoDB is activated.
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool size ( 512.0M ) under limit for 64 bit architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 512.0M / 5.3M
[OK] Ratio InnoDB log file size / InnoDB buffer pool size: 128.0M * 1/512.0M should be equal to 25%
[Number of InnoDB buffer pool chunks: 64 for 1 buffer pool instance(s)
[OK] Innodb_buffer_pool_size matched with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!!] InnoDB read buffer efficiency: 69.93% (642 hits / 918 total)
[!!] InnoDB write log efficiency: 0% (0 hits / 0 total)
[OK] InnoDB log wait times: 0% (0 wait times / 0 writes)

What should this information tell me now?

[OK] InnoDB Buffer Pool size ( 512.0M ) under limit for 64 bits architecture: (17179869184.0G ) - Does this entry mean that 512.0M is already preconfigured for innodb_buffer_pool_size and this value is correct?

Then I am advised to change the following variable:

Variables to customize:
innodb_log_buffer_size (> 16M)

But if I enter e.g. "innodb_log_buffer_size=32M" in my.cnf, the next check with MySQLTuner shows "Variables to adjust: innodb_log_buffer_size (> 32M)" as a recommendation. If I set the value even higher, the same thing happens. Why?

JohnB

dg230124

Imagine this:

You walk into a computer store
You ask them "whats the best computer for me? Mine is slow"
A guy just hands you an Alienware laptop and says "this one"
He doesn't ask what type of programs or games you use, if you travel, need reliablity for business, etc.

Thats basically what happened here.

Safe and effective server optimization requires knowing specific usecases and experimenting on your specific hardware. But most of all don't blindly use configs from any forum unless you understand each line (by reading the documentation).

Even chatgpt knows the basics, for example:

innodb_flush_log_at_trx_commit=2: This setting can improve performance by flushing the log to disk less frequently, but it may increase the risk of data loss in the event of a crash. It's generally considered safe for many workloads but may not be suitable if data integrity is a top priority.

Correct. Static page for a restuarant with backups? Doesn't matter because if you get data corruption you just restore a backup. Can't do that on a member/woocommerce website and then explain why someone lost critical work. Or worse you don't notice the data corruption until much later.

innodb_flush_method=O_DIRECT_NO_FSYNC: This setting bypasses the operating system's disk cache and disables fsync, potentially improving performance but increasing the risk of data loss. It's generally not recommended for production environments where data integrity is crucial.

Corrrect, it makes innodb assume that the data was written successfully without explicitly confirming it. Its also faster to just run across a road without checking for traffic...

As xyzulu said... this server is underpowered anyway. If you were not running any panel you could host a few low traffic sites with some tweaking. But enhance has to have memory and CPU to run too. Double the memory and use a dedicated 2 cores or 4 shared and then you can host multiple.

xyzulu

innodb_log_buffer_size should be 25% of innodb_buffer_pool_size

Personally, my opinion is that your server is far to small to optimise any more than the Enhance defaults. In fact, I can't see you being able to use this server for more than a single website anyway.

JohnB

JohnB Also I had chatgpt do a sassy summary because it amuses me:

So, setting innodb_flush_log_at_trx_commit to 2, huh? Congratulations, you've just earned yourself a spot in the "Risk-Taker Hall of Shame." Because why bother with pesky things like data integrity when you can squeeze out a few milliseconds of performance, right? I mean, who needs stable data anyway? Let's just roll the dice and hope for the best when the system inevitably crashes.

And as for innodb_flush_method=O_DIRECT_NO_FSYNC, well, isn't that just the cherry on top of this disaster sundae? Who needs fsync or disk cache when you've got nerves of steel, right? I can see the logic now: "Hey, let's disable essential safeguards and see how long it takes for everything to go up in flames!" Bravo, my friend, bravo.

innodb_flush_log_at_trx_commit=2 might sound like a shortcut to speed, but it's more like a shortcut to data oblivion.

Ah, so it's just a restaurant website. Well, forgive me for not shedding a tear over the potential loss of your menu and opening hours.

twest

Some good points in this thread, but also a lot of pedantic hyperbole.

In 99.99% of sites losing the last 2 SECONDS of writes to the database isn't going to cause anyone to lose any sleep. That's 2 seconds of data lost only under a crash scenario as well, which happens how so often?

The huge gain, often a 10X improvement in writes, is well worth the "risk". The only time that might not be the case is when you're running some highly specialized site where data integrity is a requirement for insurance or liability reasons - maybe a hospital or bank or some similar institution (all of which would be highly inappropriate to be hosting from a mass shared hosting provider anyways).

« Previous Page