Documentation

Database Parameter Groups

Database parameter groups allow you to customize database configuration settings for your managed PostgreSQL, MySQL, and MariaDB instances. This guide covers parameter management, common configurations, and best practices.

Overview

Parameter groups contain database engine configuration values that control:

  • Performance: Buffer sizes, cache settings, query optimization
  • Connection Handling: Connection limits, timeouts
  • Logging: Query logging, error reporting
  • Replication: Replication settings, binary log configuration
  • Security: Authentication methods, SSL/TLS requirements

How Parameter Groups Work

Default Parameter Groups

Each database engine version has a default parameter group:

  • Optimized Defaults: Pre-configured for general use
  • Cannot Modify: Default groups are read-only
  • Starting Point: Use as template for custom groups

Custom Parameter Groups

Create custom parameter groups for specific workloads:

  • Multiple Databases: Apply same configuration to multiple databases
  • Version-Specific: Each database version needs its own parameter group
  • Modify Anytime: Change parameters without recreating group
  • Revert: Restore to default settings if needed

Creating a Parameter Group

Via Dashboard

  1. Navigate to Databases > Parameter Groups
  2. Click Create Parameter Group
  3. Configure:
    • Name: Descriptive name (e.g., "production-mysql-8")
    • Engine: PostgreSQL, MySQL, or MariaDB
    • Version: Specific engine version
    • Base Group: Default or existing custom group
  4. Click Create

Copying an Existing Group

Clone an existing parameter group:

  1. Navigate to Parameter Groups
  2. Select group to copy
  3. Click Actions > Copy
  4. Provide new name
  5. Modify parameters as needed

Applying Parameter Groups

To New Database

During database creation:

  1. Navigate to Create Database
  2. Under Advanced Options
  3. Select Custom Parameter Group
  4. Choose your parameter group
  5. Complete database creation

To Existing Database

Apply to an existing database:

  1. Navigate to your database
  2. Click Settings > Parameter Group
  3. Select parameter group
  4. Click Apply
  5. Restart database if required

Note: Some parameters require database restart to take effect.

Common PostgreSQL Parameters

Performance Parameters

shared_buffers

Controls amount of memory for caching data:

shared_buffers = 8GB

Recommendations:

  • Small instances (< 8 GB RAM): 25% of RAM
  • Medium instances (8-32 GB RAM): 25-40% of RAM
  • Large instances (> 32 GB RAM): 8-16 GB

work_mem

Memory for sort operations and hash tables:

work_mem = 64MB

Recommendations:

  • OLTP workloads: 32-64 MB
  • Analytics workloads: 128-256 MB
  • Complex queries: Up to 1 GB

effective_cache_size

Estimate of OS cache available:

effective_cache_size = 24GB

Recommendation: 50-75% of total RAM

max_connections

Maximum concurrent connections:

max_connections = 200

Recommendations:

  • Small instances: 100-200
  • Medium instances: 200-500
  • Large instances: 500-1000
  • Use connection pooling to reduce required connections

Query Optimization Parameters

random_page_cost

Cost of random disk I/O:

random_page_cost = 1.1

Recommendation: 1.1 for SSD storage (default is 4.0 for spinning disks)

effective_io_concurrency

Concurrent I/O operations:

effective_io_concurrency = 200

Recommendation: 200 for NVMe SSDs

Logging Parameters

log_statement

Log SQL statements:

log_statement = 'none'  # none, ddl, mod, all

Options:

  • none: Don't log statements (production default)
  • ddl: Log CREATE, ALTER, DROP statements
  • mod: Log DDL + INSERT, UPDATE, DELETE
  • all: Log all statements (development only)

log_min_duration_statement

Log slow queries:

log_min_duration_statement = 1000  # milliseconds

Recommendation: 1000-5000ms for production

log_line_prefix

Format for log lines:

log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Replication Parameters

wal_level

Write-ahead log detail level:

wal_level = replica

Options:

  • minimal: Minimal logging
  • replica: Enable replication (default)
  • logical: Enable logical replication

max_wal_senders

Maximum concurrent replication connections:

max_wal_senders = 10

Recommendation: Number of replicas + 2

Common MySQL/MariaDB Parameters

Performance Parameters

innodb_buffer_pool_size

InnoDB buffer pool size:

innodb_buffer_pool_size = 8589934592  # 8 GB in bytes

Recommendations:

  • Dedicated database: 70-80% of RAM
  • Shared server: 50-60% of RAM

max_connections

Maximum concurrent connections:

max_connections = 250

Recommendations:

  • Small instances: 100-250
  • Medium instances: 250-500
  • Large instances: 500-1000

query_cache_size

Query cache size (MySQL 5.7, MariaDB):

query_cache_size = 268435456  # 256 MB

Note: Query cache is removed in MySQL 8.0+

tmp_table_size

Maximum size of internal in-memory tables:

tmp_table_size = 67108864  # 64 MB

max_heap_table_size

Maximum size for MEMORY tables:

max_heap_table_size = 67108864  # 64 MB

Recommendation: Set equal to tmp_table_size

InnoDB Parameters

innodb_log_file_size

InnoDB redo log file size:

innodb_log_file_size = 512M

Recommendations:

  • Light writes: 256-512 MB
  • Heavy writes: 1-2 GB
  • Very heavy writes: 4 GB

innodb_flush_log_at_trx_commit

InnoDB log flush behavior:

innodb_flush_log_at_trx_commit = 1

Options:

  • 0: Write and flush once per second (fastest, least safe)
  • 1: Write and flush at each commit (safest, default)
  • 2: Write at commit, flush once per second (compromise)

innodb_file_per_table

Separate file for each table:

innodb_file_per_table = ON

Recommendation: Always ON (default in modern versions)

Binary Log Parameters

log_bin

Enable binary logging:

log_bin = ON

Note: Required for replication and point-in-time recovery

binlog_format

Binary log format:

binlog_format = ROW

Options:

  • ROW: Most reliable for replication (recommended)
  • STATEMENT: Smaller logs but less reliable
  • MIXED: Automatic selection

expire_logs_days

Binary log retention:

expire_logs_days = 7

Recommendation: 7-14 days

Replication Parameters

server_id

Unique server identifier:

server_id = 1

Note: Automatically configured for managed databases

read_only

Make replica read-only:

read_only = ON

Note: Automatically set on replicas

Parameter Validation

Static vs Dynamic Parameters

Dynamic Parameters:

  • Applied immediately without restart
  • Examples: max_connections, work_mem, log_statement

Static Parameters:

  • Require database restart
  • Examples: shared_buffers, wal_level, innodb_buffer_pool_size
  • Dashboard indicates restart requirement

Parameter Limits

Some parameters have limits based on instance size:

  • max_connections: Limited by available RAM
  • shared_buffers: Cannot exceed 75% of RAM
  • innodb_buffer_pool_size: Cannot exceed 80% of RAM

The dashboard validates parameters and shows warnings for invalid values.

Monitoring Parameter Impact

Performance Metrics

After changing parameters, monitor:

  • Query Performance: Check slow query log
  • CPU Usage: Watch for increased CPU usage
  • Memory Usage: Ensure no OOM issues
  • Connection Count: Verify connection limits are adequate
  • Cache Hit Rate: Monitor buffer cache effectiveness

PostgreSQL Monitoring

-- Buffer cache hit rate (should be > 99%)
SELECT 
    sum(blks_hit)::float / (sum(blks_hit) + sum(blks_read)) as cache_hit_ratio
FROM pg_stat_database;

-- Current connections
SELECT count(*) FROM pg_stat_activity;

-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query 
FROM pg_stat_activity 
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

MySQL/MariaDB Monitoring

-- Buffer pool hit rate (should be > 99%)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- Current connections
SHOW STATUS LIKE 'Threads_connected';

-- Max connections reached
SHOW STATUS LIKE 'Max_used_connections';

-- Temporary tables created on disk
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Use Case Examples

OLTP Workload (High Concurrency)

# PostgreSQL
shared_buffers = 8GB
work_mem = 32MB
max_connections = 500
effective_cache_size = 24GB
random_page_cost = 1.1

# MySQL/MariaDB
innodb_buffer_pool_size = 8G
max_connections = 500
innodb_flush_log_at_trx_commit = 1
tmp_table_size = 64M
max_heap_table_size = 64M

Analytics Workload (Complex Queries)

# PostgreSQL
shared_buffers = 16GB
work_mem = 256MB
max_connections = 100
effective_cache_size = 48GB
random_page_cost = 1.1

# MySQL/MariaDB
innodb_buffer_pool_size = 32G
max_connections = 100
tmp_table_size = 512M
max_heap_table_size = 512M
sort_buffer_size = 8M
read_rnd_buffer_size = 8M

Development/Testing

# PostgreSQL
log_statement = 'all'
log_min_duration_statement = 0
log_connections = on
log_disconnections = on

# MySQL/MariaDB
general_log = ON
slow_query_log = ON
long_query_time = 0
log_queries_not_using_indexes = ON

Write-Heavy Workload

# PostgreSQL
shared_buffers = 12GB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

# MySQL/MariaDB
innodb_buffer_pool_size = 16G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Best Practices

Parameter Tuning

  1. Start with Defaults: Default parameters work well for most workloads
  2. Change One at a Time: Modify one parameter, test, measure impact
  3. Monitor After Changes: Watch performance metrics for 24-48 hours
  4. Document Changes: Keep track of what and why you changed
  5. Test in Staging: Try parameter changes in non-production first
  6. Avoid Extremes: Don't max out all parameters
  7. Consider Hardware: Parameters should match instance resources

Common Mistakes to Avoid

  • Over-allocating Memory: Don't allocate 100% of RAM to database
  • Too Many Connections: Use connection pooling instead of increasing limits
  • Disabling Logging: Keep essential logging for troubleshooting
  • Unsafe Settings: Don't sacrifice durability for marginal performance gains
  • Ignoring Defaults: Defaults are tuned; only change when necessary

Performance Optimization Process

  1. Identify Bottleneck: Use monitoring to find actual bottleneck
  2. Research Parameter: Understand what parameter controls bottleneck
  3. Calculate Value: Determine appropriate value based on workload
  4. Test Change: Apply in staging environment
  5. Measure Impact: Compare before/after metrics
  6. Deploy to Production: Apply if improvement is significant
  7. Monitor Continuously: Watch for unexpected side effects

Troubleshooting

Database Won't Start After Parameter Change

Cause: Invalid parameter value

Solution:

  1. Revert to previous parameter group
  2. Review parameter value
  3. Check parameter limits for your instance size
  4. Apply corrected parameter group

Performance Degraded After Change

Cause: Inappropriate parameter value

Solution:

  1. Revert to previous parameter group
  2. Review workload characteristics
  3. Research appropriate values
  4. Test with smaller incremental changes

Parameter Change Not Taking Effect

Cause: Parameter requires restart

Solution:

  1. Check if parameter is static (requires restart)
  2. Restart database from dashboard
  3. Verify parameter value after restart

Related Documentation