Improving Database performance

[fmoreira86]

This weekend I swaped from Mysql 8 to MariaDB 10.5 and instantly noticed a performance improvement.

As MariaDB still supports Query Cache I tried with the settings PS recommends: https://devdocs.prestashop-project.org/8/scale/optimizations/

In fact it improves the whole page *A LOT*, for instance I've arround 180K orders, and with query cache I can open the "orders" tab in about 1.5 seconds Vs 2.6 seconds with query cache disable VS Mysql 8 6seconds (!!!).

 

I've arround 50 to 100 users concurrent users and I'm a bit worried about keeping query cache enabled, for instance due to "Waiting for query cache lock" issue, etc. : https://mysqlquicksand.wordpress.com/2020/05/08/10-reasons-why-mysql-query-cache-should-be-switched-off/

With this beeing said, my PS runs on a 32GB RAM, 32 core CPU, with Enterprise SSDs (not NVME). I'm using the recommended settings pretty much:

 

query_cache_limit = 128K 
query_cache_size = 32M
query_cache_type = ON
table_open_cache = 4000
thread_cache_size = 80
host_cache_size=1000

read_buffer_size            = 2M 
read_rnd_buffer_size        = 1M
join_buffer_size            = 2M 
sort_buffer_size             = 2M
innodb_buffer_pool_size     = 12G

performance_schema = OFF
max_heap_table_size = 2G
tmp_table_size = 2G

 

Tipically my front office loads in 1.2 to 2.8 seconds now (depending if it's a product page, category page, search page, etc) ... but you know, we're always trying to improve things.

Any comments / suggestions?

 

[El Patron]

Thanks for posting, part of our hosting move or new client host tuning is that we first tune the mysql engine.

An easy way to review/monitor your query cache, and other parts is to use PhpMyAdmin Status tab:  (even shows values needing review in red).

I've never seen a hosting package yet that was more than default mysql settings.

TIP: set up a mysql slow log, and check on a regular basis as some tables 'will' grow rather large causing issues with ttfb.  

https://app.screencast.com/InkcOA9kGadbq

[fmoreira86]

Thanks for your reply!

There's not a great amount of "reds".

There's also some recommendations.

What would you suggest based on the config I shared?

 

Edited March 13, 2023 by fmoreira86 (see edit history)

[El Patron]

17 hours ago, fmoreira86 said:

Thanks for your reply!

There's not a great amount of "reds".

There's also some recommendations.

What would you suggest based on the config I shared?

 

 

Hi, without sitting down, hands on I cannot really just make recommendations.  You will need to adjust per reds/recommendations, test/validate. It's difficult to get it perfect, memcache is another option that will do some of these things automatically but we stick with mysql config.

 

happy prestashopping  

[Preskaribou]

so do you recommend maria ?

[fmoreira86]

On 9/22/2023 at 1:25 PM, Preskaribou said:

so do you recommend maria ?

I use maria db (with caching enabled)

[Preskaribou]

ok thanks  very appreciated !