Okay first of all I know you can't 'configure' your way out of performance issues. And second this properly a rookie question with regards to databases and mariadb but we are starting our self-hosting journey just now.
I have a rackserver with 16gb of ram. Which is used mainly for storing databases in MariaDB and in addition host BudiBase in a docker.
Everything worked fine (queries etc) with a couple of million rows but we are now generating more data and the question is what can I allocate to MariaDB or what would be wise to allocate to MariaDB. I have read a lot on the internet but it still leaves some open questions.
Their is a my.cnf in the users "MariaDB folder" (the one I shouldn't edit) but no my.cnf in the MariaDB10/etc/ folder.
This properly means that the orginal parameters in the system file are set? So for example:: key_buffer_size: 16k, max_allowed_packet: 1m, read_buffer_size = 256k etc.
The use of the server: many small select queries per minute, a little less but still many small insert queries a minute, 12 large select queries a day and one large delete statement a day.
What would be the parameters (in my.cnf) to tune to optimize for this use case? And what would be the max to which these parameters can be set?
Kind regards.
You should be using
ENGINE=InnoDBfor tables.You should have
innodb_buffer_pool_sizeset to about 70% of available memory (after allowing for BudiBase).Other than that, we need to look at the slow queries. Since it sounds like the 'worst' query is that daily
DELETE. My advice is summarized here: https://mysql.rjweb.org/doc.php/deletebig