MySQL and PostgreSQL in Production: A Basic Tuning Guide

MySQL and PostgreSQL in Production: A Basic Tuning Guide

Whether you choose MySQL or PostgreSQL, the core of production deployment is the same: make the default configuration match your hardware and workload. Both ship with "compatibility-first" rather than "performance-first" defaults, and memory parameters in particular tend to be conservative, so tuning after deployment is a must.

1. Connections and Concurrency

PostgreSQL: max_connections defaults to 100. The official wiki notes that PostgreSQL on good hardware can support a few hundred connections, but for thousands you should introduce a connection pooler (such as PgBouncer) to reduce overhead.

# postgresql.conf
max_connections = 200

MySQL: max_connections defaults to 151. Each MySQL connection carries memory overhead; pair it with thread_cache_size to reuse threads:

# my.cnf
max_connections = 300
thread_cache_size = 64

Bigger is not better. Each MySQL connection occupies several MB of RAM and each PostgreSQL backend process eats memory too, so blindly raising max_connections to thousands can exhaust the machine before the workload ever does. The common pattern is for apps to connect to a pool that reuses a small number of real connections. On the PostgreSQL side the standard answer is PgBouncer; with pool_mode set to transaction, 50 to 200 backend connections can carry thousands of application connections:

# pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50

2. Memory Tuning

PostgreSQL memory parameters (classic guidance from the official wiki):

# Example for a dedicated database server with 16GB RAM
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB

work_mem is allocated per operation: 50MB x 30 concurrent users quickly consumes 1.5GB, so multiply by concurrency when sizing it.

MySQL InnoDB memory parameters:

# Example for 16GB RAM
innodb_buffer_pool_size = 8G   # ~50% to 75% of physical memory
innodb_log_file_size = 1G
innodb_buffer_pool_instances = 8

innodb_buffer_pool_size is the most critical parameter; it caches table and index data. innodb_log_file_size controls redo log size, and if too small, write-heavy workloads suffer frequent checkpoints. On MySQL 8.0.30+ you can manage redo logs centrally with innodb_redo_log_capacity.

3. WAL and Durability

PostgreSQL's synchronous_commit (default on) guarantees every commit is flushed to disk and is the foundation of ACID; do not disable it in production. wal_buffers follows shared_buffers automatically and rarely needs manual adjustment.

MySQL's innodb_flush_log_at_trx_commit:

• 1 (default): flush on every commit, safest
• 2: flush once per second, better performance but may lose up to 1 second of data on crash
• 0: determined by the system, fastest but riskiest

For almost all production scenarios, keep 1 and combine it with sync_binlog=1 for replication safety.

4. Automatic Maintenance

Never turn off autovacuum in PostgreSQL. The official wiki is explicit: "the answer to almost all vacuuming problems is to vacuum more often, not less". It removes dead rows and prevents table bloat:

autovacuum = on
autovacuum_max_workers = 3

MySQL redo log and purge threads: keeping the defaults is usually fine; the priority is monitoring status values such as Innodb_buffer_pool_wait_free to spot bottlenecks.

5. Slow Query Logs

Both databases support slow query logs, the first step in locating performance problems.

PostgreSQL:

log_min_duration_statement = 1000   # log queries taking over 1 second
log_line_prefix = '%t:%r:%u@%d:[%p]: '

MySQL:

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

The slow query log is only the first step; once you have entries, learn to read execution plans. PostgreSQL uses EXPLAIN ANALYZE; MySQL's EXPLAIN does not actually execute by default, so use EXPLAIN ANALYZE (8.0.18+) when you want real costs. When reading a plan, watch three things: full table scans (seq scan / type=ALL), extra sorting (Sort / filesort), and indexes that should be used but are not (estimated rows far above actual matches). Most slow queries disappear with one well-chosen index.

6. Deployment Shapes and Operations

• Containerized: databases can also run in Docker Compose, with the data directory persisted via named volumes; see also the Compose LAMP environment.
• Backups: essential in production; see the backup and restore runbook and cloud server backup strategy.
• Monitoring: track connections, slow queries and buffer-pool hit ratios with Prometheus and Grafana, and route alerts into monitoring and alerting.
• LNMP integration: for PHP sites, see the LNMP stack setup.

A Real Tuning Story

An article-heavy site hit 100% database CPU at the evening peak, with pages taking 3 seconds to render. The first move was enabling the slow query log; the next day it surfaced an aggregation query counting articles by month that ran for tens of seconds. Reading the plan confirmed it was skipping the published_at index; after adding one, the query dropped from 8 seconds to 0.3. Then shared_buffers went from the default 128MB to 4GB, the hit ratio climbed, and CPU usage fell by half. Three parameters changed in total, each validated with a load-test script — database tuning is often just "logs + index + memory".

16IDC Note

The biggest tuning payoff comes up front: matching memory parameters to hardware, enabling slow query logs and keeping automatic maintenance on solves most performance problems without diving into indexes and query plans. The key principle is to change one parameter at a time, validate with real load, and record every change, rather than blindly copying online configs. For small and mid-sized projects, getting these basics right matters more than chasing advanced features.

Reference: PostgreSQL tuning wiki https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; PG resource parameters https://www.postgresql.org/docs/current/runtime-config-resource.html ; MySQL InnoDB parameters https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html ; PgBouncer config https://www.pgbouncer.org/config.html

Source: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server , https://www.postgresql.org/docs/current/runtime-config-resource.html , https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html