Pre-requisites: Postal MariaDB 12.3+ Database Configuration

[c41ms0n]

Postal MariaDB 12.3+ Configuration

Purpose

This configuration is focused on Postal's transient mail-queue workload:

• Frequent INSERT/UPDATE/DELETE activity
• Retention-based cleanup
• Expiration of raw-message tables
• Filesystem space reclamation
• InnoDB data scrubbing where supported
• Automatic reclamation of InnoDB system, undo, and temporary tablespaces
• Thread pooling for connection handling
• Buffer-pool state persistence across clean restarts

Hardware-specific tuning is intentionally excluded from the base configuration.

Docker Run

docker run -d \
  --name postal-mariadb \
  -p 127.0.0.1:3306:3306 \
  --restart always \
  -v /opt/postal/storage/mysql:/var/lib/mysql:rw \
  -e MARIADB_DATABASE=postal \
  -e MARIADB_ROOT_PASSWORD=postal \
  docker.io/library/mariadb \
  mariadbd \
    --innodb-data-file-path=ibdata1:12M:autoextend:autoshrink \
    --innodb-temp-data-file-path=ibtmp1:12M:autoextend:autoshrink \
    --innodb-undo-log-truncate=ON \
    --innodb-max-undo-log-size=1G \
    --innodb-immediate-scrub-data-uncompressed=ON \
    --innodb-flush-log-at-trx-commit=2 \
    --innodb-buffer-pool-dump-at-shutdown=ON \
    --innodb-buffer-pool-load-at-startup=ON \
    --thread-handling=pool-of-threads \
    --skip-log-bin

Thread Handling

--thread-handling=pool-of-threads

Enable MariaDB's thread pool instead of creating a dedicated server thread for every connection.

This is part of the base Postal configuration.

thread_pool_size is intentionally not specified. MariaDB determines the default thread-pool size from the available CPU resources, so the configuration automatically adapts to different server sizes.

thread_cache_size is intentionally not specified because it is not used when the thread pool is enabled.

Buffer Pool Persistence

--innodb-buffer-pool-dump-at-shutdown=ON
--innodb-buffer-pool-load-at-startup=ON

Persist the current buffer-pool state during a clean shutdown and restore it during startup.

This allows MariaDB to restore frequently used pages after a restart instead of rebuilding the working set entirely from disk.

innodb-buffer-pool-dump-pct is intentionally not specified. The amount of buffer-pool state to persist should remain a deployment-specific tuning decision.

Storage Reclamation

InnoDB System Tablespace

--innodb-data-file-path=ibdata1:12M:autoextend:autoshrink

Allow the InnoDB system tablespace to grow when required and automatically shrink when reclaimable space is available.

This prevents ibdata1 from permanently retaining historical growth.

Temporary Tablespace

--innodb-temp-data-file-path=ibtmp1:12M:autoextend:autoshrink

Allow the InnoDB temporary tablespace to grow when required and automatically shrink after temporary data is released.

The temporary tablespace can also be explicitly truncated without restarting MariaDB:

SET GLOBAL innodb_truncate_temporary_tablespace_now=1;

Undo Tablespaces

--innodb-undo-log-truncate=ON
--innodb-max-undo-log-size=1G

Enable automatic truncation of undo tablespaces when they exceed the configured threshold.

The threshold limits long-term undo growth in a workload with continuous INSERT/UPDATE/DELETE activity.

The threshold can be adjusted for a particular deployment if monitoring shows that a different value is more appropriate.

Per-Table Space Reclamation

Modern MariaDB uses innodb_file_per_table=ON by default.

No explicit innodb_file_per_table option is required in the configuration.

Using individual tablespaces allows:

• Dropped raw-message tables to release their .ibd files
• Rebuilt tables to return unused space to the filesystem

Data Scrubbing

--innodb-immediate-scrub-data-uncompressed=ON

Enable immediate scrubbing for applicable uncompressed InnoDB data when pages are freed.

This is the current MariaDB immediate-scrub mechanism.

It is not equivalent to the historical Percona background scrubber for compressed InnoDB pages.

Transaction Durability

--innodb-flush-log-at-trx-commit=2

Use a throughput-oriented durability mode suitable for a transient mail-queue workload.

Committed redo is flushed approximately once per second instead of forcing an fsync for every transaction commit.

Use:

--innodb-flush-log-at-trx-commit=1

when maximum crash durability is required.

Binary Logging

--skip-log-bin

Disable binary logging when the Postal database is not used for replication or point-in-time recovery.

This avoids maintaining another persistent copy of transactional changes.

Postal Redo Log Requirement

Postal requires redo capacity of at least approximately 10 times the maximum configured message size.

Examples:

Maximum message size     Minimum redo capacity

15 MB                     150 MB
25 MB                     250 MB
50 MB                     500 MB
100 MB                    1 GB

innodb_log_file_size should therefore be configured according to the actual Postal maximum message size.

Do not hard-code a single redo-log size in the generic configuration.

Packet Size

max_allowed_packet should be larger than the configured Postal maximum message size.

This is deployment-specific and is intentionally not included in the base Docker command.

Purge Threads

innodb_purge_threads is intentionally not hard-coded.

MariaDB defaults should be used initially, then adjusted based on observed:

• Purge lag
• History list length
• DELETE throughput
• CPU utilization
• Storage latency

Postal's retention workload can produce substantial purge activity, but the optimal number of purge threads depends on the actual deployment.

Buffer Pool Size

innodb_buffer_pool_size is intentionally not hard-coded.

It should be sized according to:

• Available system RAM
• Postal application memory usage
• Other workloads sharing the host
• Database working-set size
• Storage performance

Table Reclamation

Do not run OPTIMIZE TABLE after every DELETE.

Use it selectively for long-lived Postal tables that have accumulated substantial DATA_FREE.

Check table fragmentation:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS size_gb,
    ROUND(DATA_FREE / 1024 / 1024 / 1024, 2) AS free_gb,
    ROUND(
        DATA_FREE / NULLIF(DATA_LENGTH + INDEX_LENGTH, 0) * 100,
        1
    ) AS free_pct
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB'
  AND TABLE_SCHEMA LIKE 'postal%'
ORDER BY DATA_FREE DESC;

Rebuild only tables that require space reclamation:

OPTIMIZE TABLE postal.messages;

For Postal raw-message data, dropping the complete expired raw-message table is preferred to deleting millions of individual rows.

Storage Reclamation Model

Postal
  |
  +-- DELETE expired rows
  |      |
  |      +-- InnoDB purge
  |      +-- immediate scrub where applicable
  |
  +-- DROP expired raw-message tables
  |      |
  |      +-- .ibd removed
  |      +-- filesystem space reclaimed
  |
  +-- Persistent tables with significant DATA_FREE
  |      |
  |      +-- OPTIMIZE TABLE
  |      +-- table rebuild
  |      +-- filesystem space reclaimed
  |
  +-- ibdata1
  |      |
  |      +-- autoshrink
  |
  +-- Undo tablespaces
  |      |
  |      +-- automatic truncation
  |
  +-- ibtmp1
         |
         +-- autoshrink
         +-- explicit truncate when required

Deployment-Specific Parameters

The following parameters should be configured separately for each deployment:

• innodb_log_file_size — based on the Postal maximum message size
• max_allowed_packet — based on the Postal maximum message size
• innodb_buffer_pool_size — based on available RAM and workload
• innodb_purge_threads — based on purge workload and CPU resources

The base configuration deliberately avoids hard-coded CPU, RAM, storage, and message-size assumptions.