Recommended Linux Kernel and System Settings for PostgreSQL
Introduction
When it comes to running a high-performance PostgreSQL database, choosing the right Linux kernel and system settings can greatly impact its overall performance and stability. In this blog post, we will discuss the recommended Linux kernel and system settings that can optimize the performance of your PostgreSQL database.
Below, you will find the comprehensive list of recommended Linux kernel and system settings that can significantly enhance the performance of OLTP workloads on a Postgres server. These settings primarily focus on Postgres details and the crucial fs.file-max value.
Memory Management
vm.overcommit_memory = 2: Prevents the kernel from excessive memory usage, reducing the risk of OOM Killer terminating Postgres processes.vm.swappiness = 1: Minimizes swapping of Postgres processes, as swapping significantly degrades database performance.vm.min_free_kbytes = 102400: Reserves a certain amount of memory to handle unexpected peak demands, ensuring available memory for Postgres when needed.Transparent Huge Pages (THP): Configure transparent_hugepage/enabled and transparent_hugepage/defrag to never disable THP, avoiding potential latency issues for Postgres.
Input/Output Management
vm.dirty_expire_centisecs = 1000: Determines when dirty pages are flushed to disk, affecting how Postgres writes to WAL and other files.vm.dirty_background_bytes = 67108864: Sets the threshold for the kernel to start writing dirty data to disk in the background.vm.dirty_bytes = 536870912: Specifies the maximum amount of dirty memory before Postgres processes immediately write out data.
File System and File Handling:
fs.file-max = [appropriate value]: Sets the limit on the number of open file descriptors system-wide, which should be sufficiently high to avoid limiting Postgres.
NUMA Configuration
vm.zone_reclaim_mode = 0: Disables NUMA memory reclaim, which may negatively impact Postgres performance on NUMA hardware.kernel.numa_balancing = 0: Turns off automatic NUMA balancing, reducing unnecessary data movement across NUMA nodes for Postgres.
Process and Connection Scheduling:
kernel.sched_autogroup_enabled = 0: Improves Postgres scheduling by not grouping client connections, which can delay process wake-ups.
Network Configuration
net.ipv4.ip_nonlocal_bind = 1: Facilitates high availability setups for Postgres, where the service may need to bind to non-local IP addresses.net.ipv4.ip_forward = 1: Enables IP forwarding if Postgres nodes require routing capabilities.net.ipv4.ip_local_port_range = 10000 65535: Increases the port range for outgoing connections to accommodate more client connections for Postgres.net.core.netdev_max_backlog = 10000: Determines the maximum number of packets, increasing backlog to handle peak network traffic without dropping packets.net.ipv4.tcp_max_syn_backlog = 8192: Increases the queue length for incoming connections, preventing Postgres from dropping during a burst of new connections.net.core.somaxconn = 65535: Sets the maximum queuing socket connection count for Postgres, allowing for more concurrent connections.net.ipv4.tcp_tw_reuse = 1: Enables Postgres to quickly recycle sockets in TIME_WAIT state, beneficial for connection-heavy workloads.
Each of these settings is carefully chosen to maximize the performance and reliability of a Postgres server when handling OLTP workloads, particularly for mobile and web applications. It is crucial to thoroughly evaluate and verify these settings in a testing environment that closely mirrors the production setup before implementing any modifications to a live system. This ensures that any changes made will effectively optimize the server’s performance and maintain its stability under real-world conditions.
Conclusion
Optimizing the Linux kernel and system settings for your PostgreSQL database can significantly enhance its performance and stability. By following the recommended settings discussed in this blog post, you can ensure that your PostgreSQL database is running efficiently and able to handle high workloads effectively. Remember to always monitor the performance after making any changes and tweak the settings accordingly to achieve the best results.