- Más nuevo
- Más votos
- Más comentarios
Is there any formula to set the max_connection value in a PostgreSQL 12 EC2 instance with 64 CPUs, 500 GB RAM, and 150k IOPS?
There isn't a universal formula for determining the optimal max_connections
value because the ideal number depends on several factors specific to your environment and application.
For a PostgreSQL instance on an EC2 instance with 64 CPUs, 500 GB RAM, and 150k IOPS, you might start with a conservative estimate and then adjust based on your application's specific needs and performance testing results. A common rule of thumb is to allocate about 2-3% of the system's RAM for each connection. In your case, with 500 GB of RAM, this would suggest:
max_connections = (500 GB * 1024 MB/GB) * 0.02 to 0.03 / (memory per connection)
The memory per connection can vary, but a typical value might be around 10 MB. So, you might start with:
max_connections = (500 * 1024) * 0.02 to 0.03 / 10 = 1024 to 1536
Note: This is a rough estimate, and you should adjust it based on your application's actual memory usage and connection patterns.
Additionally, what other parameters need to be changed for a mixed workload database?
- work_mem: Memory for sort operations and hash tables. Increase for better performance in complex queries, but use cautiously as it's allocated per operation.
- maintenance_work_mem: Maximum memory for maintenance tasks like vacuuming and indexing. Increase to speed up these operations on large databases.
- shared_buffers: Memory for shared buffers, typically set to 25%-40% of total system memory.
- effective_cache_size: Helps estimate the disk cache size available to the database, usually set to 50%-75% of total system memory.
- checkpoint_segments and checkpoint_completion_target: Control the frequency and spread of WAL checkpoints. Adjust to balance I/O load.
- random_page_cost: Cost of non-sequential disk page fetches. Adjust to influence query planner's choice of index vs. sequential scans.
Contenido relevante
- OFICIAL DE AWSActualizada hace 4 meses
- OFICIAL DE AWSActualizada hace 3 años
- OFICIAL DE AWSActualizada hace 3 años
but my target is to set max_connection to 2000 , shared_buffer is 60gb, effective_cache=128gb, work_mem=128mb so now what i have to do