Step 1: Locate the settings window
To edit the MariaDB settings, go to the Server you want to edit the database settings of, go to the Services tab, locate MariaDB on the list, and choose MariaDB settings from the three-dots menu on the right:
Step 2: Configure the settings as you want
Here you have several settings that we deem the most important to tweak in a MariaDB installation for optimum performance.
- InnoDB buffer pool size: This is by far the most important setting. It represents the amount of memory allocated to the InnoDB buffer pool, which is used to cache frequently accessed data and index pages from the InnoDB storage engine. The purpose of the buffer pool is to reduce disk I/O and improve performance by keeping commonly used data in memory, so that it can be accessed faster. This is not per PHP worker, but from total server memory, and this value is the total shared across all your WordPress websites in the server. A good rule of thumb is to allocate as much memory as possible to the buffer pool, without starving the operating system or other applications for memory. Our recommendation is up to 50% of the available system RAM, but you can go higher for websites with more activity, especially WooCommerce websites.
- Maximum number of connections: This is the maximum amount of concurrent connections that the server can handle at any given time. If you come across an error message telling you that the maximum number of connections is exceeded, this is the value you have to edit if your server has enough resources to handle more connections. We’ve increased this value by default, so most of the times you don’t have to worry about it.
- Memory that can be spent for caching SQL queries: The amount of RAM in MB that can be spent to cache some SQL queries, which is a mechanism used to improve performance by caching the results of frequently executed queries in memory. When a query is executed, the database checks if the query and its results are already in the cache, and if so, it returns the results from the cache instead of executing the query again. We recommend a starting point of 64MB, but feel free to increase it if your RAM budget isn’t tight.
- Memory that can be spent for caching per SQL query: Very similar to the above, but determines the amount of RAM that can be spent for storing a single query’s result. This way, you can limit that the query cache doesn’t get bloated with big but infrequent queries, like search queries.
- Maximum allowed packet: It determines the maximum size of a single packet or query that the server will accept from a client. This is a value that helps in the prevention of excessively large queries from consuming excessive server resources, and to provide a measure of protection against certain types of denial-of-service attacks. However, if any of your WordPress installations need to send or receive very large amounts of data, you may need to increase this value to avoid errors.
We recommend you to focus your attention on just editing the InnoDB buffer pool size value according to your RAM budget, which is the most important one of these options. If you ever hit one of these weird database errors, then you can start editing these other options to remediate the situation.