DMIT Database Optimisation: MySQL/PostgreSQL Tuning

Why the database is often the bottleneck

On a DMIT cloud server, most performance problems are not CPU or bandwidth — they are the database: slow queries, missing indexes, uncontrolled connections and an undersized buffer pool can drag down the whole site. This guide uses MySQL / MariaDB and PostgreSQL as examples, with actionable tuning for DMIT servers.

Establish a baseline first

Quantify before tuning — don't guess. Enable the slow query log to find the most expensive statements:

-- MySQL: enable slow query log (>1s)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
-- summarize with mysqldumpslow
mysqldumpslow -s t /var/log/mysql/slow.log | head -20

On PostgreSQL use the pg_stat_statements extension to rank slow queries. Fix the Top 5 first — that usually yields immediate gains.

Index optimisation

Index columns in WHERE, JOIN, ORDER BY
Composite indexes follow the leftmost-prefix rule
Avoid functions or type casts on indexed columns (breaks the index)
Run ANALYZE regularly to refresh statistics

-- MySQL: find unused indexes
SELECT * FROM sys.schema_unused_indexes;
-- PostgreSQL: tables with many seq scans
SELECT * FROM pg_stat_user_tables WHERE seq_scan > 1000;

Connection pooling and max connections

Opening a new connection per request exhausts connections fast. Use a pool (HikariCP, PgBouncer):

# PostgreSQL + PgBouncer (pgbouncer.ini)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

Keep app-side timeouts short (e.g. 5s) to avoid slow-query pile-ups. For system-level tuning see Performance Tuning.

Buffer pool and memory

InnoDB's innodb_buffer_pool_size is the key knob. Rule of thumb: on a dedicated DB server set it to 60-75% of RAM, leaving headroom for the OS and other processes.

# /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 4
max_connections = 200

After changes watch mysqladmin status; target buffer pool hit rate > 99%. If memory-constrained, see Cost Optimisation on scaling up.

Read/write split and caching

For read-heavy workloads add Redis for hot data, or deploy a read replica on DMIT to offload reads:

# install Redis
apt install redis-server -y
# app layer: check Redis first, fall back to DB
GET user:123
# miss -> SELECT ... -> SETEX user:123 300 "..."

Routine maintenance

Weekly OPTIMIZE TABLE / VACUUM ANALYZE to reclaim fragmentation
Configure automated backups (see Monitoring & Backup)
Monitor slow-query trends to prevent regressions

Performance Tuning, Troubleshooting Guide, Docker Deployment Guide.

Data checked: 2026-08-18. Specific parameters follow official docs and your instance specs.