MariaDB has several per-session buffers that speed up some queries. If these values are too small, some complex queries may start to take too much time. However, if they are too big and many concurrent connections are established, a big amount of memory could be wasted. The DBA should know the main per-session buffers and keep them reasonably small (but still big enough for the workload).
When an application opens a connection, it should approximately know which kinds of statements it will issue. It is possible that the application establishes different connections for different tasks; for example, this is what web applications usually do. So, it is not uncommon that most connections execute simple queries, while others perform more complex tasks, and thus they need more resources.
Per-session buffers can be configured on a per-session basis: a connection can change the size of some buffers without affecting other connections. This allows the allocation of bigger buffers for some sessions and small buffers for connections that will execute simple queries.
To configure a buffer on a per-session basis, connections need to issue a statement similar to the following:
MariaDB [(none)]> SET @@session.join_buffer_size = 64000; Query OK, 0 rows affected (0.00 sec)
To set a default value for all sessions, run the following query:
MariaDB [(none)]> SET @@global.join_buffer_size = 160000; Query OK, 0 rows affected (0.00 sec)
But, even with well-configured session variables, memory will be wasted if too many connections are not properly closed when they are not required. Application developers should make sure that connections are closed as soon as possible. Also, connections should have a reasonable timeout. When a connection is idle, it is closed after a certain number of seconds. This number is defined by the wait_timeout server variable. We can also force the server to reject connections if they exceed a certain number. This number is defined by max_connections. Setting a proper value for this variable can save a server from a
Denial-Of-Service attack. But, before doing this, we must be sure that we are not underestimating our workload, or some users will not be able to connect to the database.
The following server variables determine the main per-session buffers lengths:
sort_buffer_size: This is a buffer used to speed up theORDER BYoperations. We know it is too small if aSHOW SESSION STATUSstatement shows a high value forSort_merge_passes.read_buffer_size: This optimizes the sequential scans of MyISAM tables.read_rnd_buffer_size: This speeds up the queries executed with a multirange optimization strategy and all random reads from MyISAM tables.join_buffer_size: This optimizes the joins that use a batched key access strategy.bulk_insert_buffer_size: This is used to speed up the multirow inserts (including theLOAD DATA INFILEstatement) into a MyISAM table.