# 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 the same configuration to multiple databases
- **Cross-Version**: Custom parameter groups stay compatible across engine versions
- **Modify Anytime**: Change parameters without recreating the group
- **Revert**: Restore to default settings if needed

When you attach a group, the dropdown automatically filters to compatible groups: default (system) groups are **version-scoped** and match your instance's engine version, while **custom** groups remain usable across versions.

## 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

> Parameter groups can also be created, read, updated, and deleted programmatically through the [public API](https://docs.danubedata.ro/api-overview).

## 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

```sql
-- 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

```sql
-- 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)

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

```ini
# 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)

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

```ini
# 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

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

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

### Write-Heavy Workload

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

```ini
# 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

- [PostgreSQL](https://docs.danubedata.ro/databases-postgresql)
- [MySQL](https://docs.danubedata.ro/databases-mysql)
- [MariaDB](https://docs.danubedata.ro/databases-mariadb)
- [Database Overview](https://docs.danubedata.ro/databases-overview)
- [Monitoring Overview](https://docs.danubedata.ro/monitoring-overview)
- [Performance Optimization Tips](https://docs.danubedata.ro/databases-overview#performance)