How to optimise MariaDB v10.3?

Dear all MariaDB or MySQL experts,
I am running a VPS with ISPConfig v3.2, Ubuntu 20.04, with 8GB RAM + 2 CPU cores (claimed by hosting company).
I host 2 personal sites on it. They are very heavy wordpress v6.2 and Laravel framework.
Could you suggest a cnf file of MariaDB to optimise the database operation?

Thanks for your opinions.

 

Try: https://mariadb.com/kb/en/optimization-and-tuning/

 

Not every tip works for ISPConfig.
Do not add this:
skip-name-resolve = 1

This broke sending out mails for a few hours!
I have removed it at once!


These seem to work.
[mysqld]
wait_timeout=60
tmp_table_size=64M #recommend 64M per GB of memory
max_heap_table_size=64M #the same as tmp_table_size

query_cache_size=64M

 

i don't know about laravel, but wordpress seems to make every mysql query sequentially, only making a new query once it's received a response to the one it's already made, not sending off a bunch of separate queries together and waiting for the responses to each of them.
so the best database optimization would seem to be to minimize the number of queries.
so generally.. use the minimal number of well-coded plugins that you can get away with.
cache wherever possible. cache database results.. use full page caching for wordpress. if the webserver can serve up a full html page from the cache, it's the fastest possible way to serve the page, and it doesn't use a single database query.
no mysql load.. no php scripts.. minimal disk / memory / cpu usage. it's hard to optimize mysql to beat that.

 

Psychologically, I feel my quoted tips working well.

 

Did you mean page cache? Which WP 6.2 plugin do you use?

 

i don't use any particular plugin.. i don't do the web design side of things myself. i just keep everything running...
that said.. what gets used depends on the site itself and how it's hosted..
sometimes we use redis-cache, sometimes w3 total cache or wp rocket.. if someone's paying to host a single site on their own vps, we might stick varnish cache on the server and use that on port 80/443, and just pass anything not already cached through to apache on a non-standard port.
there is no fixed 'this is the best way', it all depends on the nature of the site itself, the traffic patterns, and how the host server is configured.
what is the ideal solution for one site may be the worst possible option for another site.

 

Agreed.

 

How is this related - MariaDB optimization vs upgrade? Do not hijack other people's thread. Please open a new thread for your own specific question(s).