Chapter 12. Adjusting kernel parameters for database servers To ensure efficient operation of database servers and databases, you must configure the required sets of kernel parameters. 12.1. Introduction to database servers A database server is a service that provides features of a database management system (DBMS). DBMS provides utilities for database administration and interacts with end users, applications, and databases. Red Hat Enterprise Linux 8 provides the following database management systems: MariaDB 10.3 MariaDB 10.5 - available since RHEL 8.4 MariaDB 10.11 - available since RHEL 8.10 MySQL 8.0 PostgreSQL 10 PostgreSQL 9.6 PostgreSQL 12 - available since RHEL 8.1.1 PostgreSQL 13 - available since RHEL 8.4 PostgreSQL 15 - available since RHEL 8.8 PostgreSQL 16 - available since RHEL 8.10 12.2. Parameters affecting performance of database applications The following kernel parameters affect performance of database applications. fs.aio-max-nr Defines the maximum number of asynchronous I/O operations the system can handle on the server. Note Raising the fs.aio-max-nr parameter produces no additional changes beyond increasing the aio limit. fs.file-max Defines the maximum number of file handles (temporary file names or IDs assigned to open files) the system supports at any instance. The kernel dynamically allocates file handles whenever a file handle is requested by an application. However, the kernel does not free these file handles when they are released by the application. It recycles these file handles instead. The total number of allocated file handles will increase over time even though the number of currently used file handles might be low. kernel.shmall Defines the total number of shared memory pages that can be used system-wide. To use the entire main memory, the value of the kernel.shmall parameter should be <= total main memory size. kernel.shmmax Defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space. kernel.shmmni Defines the maximum number of shared memory segments the database server is able to handle. net.ipv4.ip_local_port_range The system uses this port range for programs that connect to a database server without specifying a port number. net.core.rmem_default Defines the default receive socket memory through Transmission Control Protocol (TCP). net.core.rmem_max Defines the maximum receive socket memory through Transmission Control Protocol (TCP). net.core.wmem_default Defines the default send socket memory through Transmission Control Protocol (TCP). net.core.wmem_max Defines the maximum send socket memory through Transmission Control Protocol (TCP). vm.dirty_bytes / vm.dirty_ratio Defines a threshold in bytes / in percentage of dirty-able memory at which a process generating dirty data is started in the write() function. Note Either vm.dirty_bytes or vm.dirty_ratio can be specified at a time. vm.dirty_background_bytes / vm.dirty_background_ratio Defines a threshold in bytes / in percentage of dirty-able memory at which the kernel tries to actively write dirty data to hard-disk. Note Either vm.dirty_background_bytes or vm.dirty_background_ratio can be specified at a time. vm.dirty_writeback_centisecs Defines a time interval between periodic wake-ups of the kernel threads responsible for writing dirty data to hard-disk. This kernel parameters measures in 100th's of a second. vm.dirty_expire_centisecs Defines the time of dirty data that becomes old to be written to hard-disk. This kernel parameters measures in 100th's of a second. Additional resources Dirty pagecache writeback and vm.dirty parameters

Commands: