Database Hosting: A Practical Guide

The disk in an online store’s server in Homs failed at 6 pm. The backup existed, but it was from midnight, so 18 hours of orders were gone. The problem wasn’t performance or the choice of database; it was a question nobody had asked: how much data can the business afford to lose? Engineers call this the recovery point objective (RPO). Hosting a database means answering that question and three more: where the database runs, how much memory it needs, and who can connect to it. This guide covers MySQL, MariaDB and PostgreSQL with commands, from sizing to a restore drill.

01Where to run the database

Three options, separated by how much you control and how much you carry:

Whichever you choose, keep the database close to the application. A page that runs 80 sequential queries loses only 160 ms if the round trip between app and database is 2 ms, but nearly 5 seconds if the database sits on another continent at 60 ms. That’s why the application and its database run in the same data centre, over a private network if they’re on two servers.

02Sizing: memory first

A fast database is one that finds its active data in memory. In MySQL and MariaDB that’s governed by the InnoDB buffer pool through innodb_buffer_pool_size; in PostgreSQL by shared_buffers together with the operating system’s cache. Practical starting points:

• MySQL and MariaDB: On a server dedicated to the database, give the buffer pool 60 to 70% of memory. If the web server and PHP share the machine, start at 30 to 40%, because whatever the database takes comes out of PHP’s processes.
• PostgreSQL: shared_buffers around 25% of memory, and effective_cache_size between 50 and 75%. Don’t raise work_mem much: it’s allocated for every sort operation in every connection, so it multiplies with connections.
• Connections: Every connection reserves memory of its own, so don’t raise max_connections into the thousands. With PostgreSQL, put a connection pooler such as PgBouncer in front of the database if your app opens many short connections.

Measure your actual data size before you reserve memory. If data and indexes add up to 1.5 GB, a buffer pool of 2 to 3 GB keeps all of it in memory with room to grow, and anything beyond that sits unused until the data grows.

# Data and index size per database, in MB (MySQL and MariaDB)sudo mariadb -e "SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS size_mb FROM information_schema.tables GROUP BY table_schema;"# MariaDB settings for a dedicated database server with 8 GB of memorysudo tee /etc/mysql/mariadb.conf.d/60-tuning.cnf >/dev/null <<'EOF'[mysqld]innodb_buffer_pool_size = 5Gmax_connections = 200slow_query_log = 1slow_query_log_file = /var/log/mysql/slow.loglong_query_time = 0.5EOFsudo systemctl restart mariadb

# PostgreSQL on a dedicated server with 8 GB of memorysudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '2GB';"sudo -u postgres psql -c "ALTER SYSTEM SET effective_cache_size = '6GB';"sudo -u postgres psql -c "ALTER SYSTEM SET work_mem = '16MB';"# shared_buffers only changes after a restartsudo systemctl restart postgresql

Then storage. Every committed transaction waits until its log record is durably written to disk (fsync), so the latency of that write caps the number of transactions per second. On local NVMe it’s usually under a millisecond; on network storage it can reach several milliseconds. Measure it before you put a production database on any server:

# Durable write latency: look at the fdatasync percentiles in the output; p99 under 1 ms is good for a databasefio --name=fsync-test --rw=write --bs=4k --size=256m --fdatasync=1 --ioengine=sync --runtime=30 --time_basedrm -f fsync-test.0.0

And don’t buy speed by giving up safety: setting innodb_flush_log_at_trx_commit to 2 speeds up writes, but can lose the last second of transactions if the operating system crashes.

03Backups and point-in-time recovery

Three levels, each covering what the others don’t:

• Logical backup: An SQL file or archive from mariadb-dump, mysqldump or pg_dump. Easy to move between versions and servers, and slow to restore once the database grows to tens of gigabytes.
• Physical backup: A copy of the database’s own files, with tools such as mariadb-backup, Percona XtraBackup or pg_basebackup. Much faster to restore for large databases, but tied to the same version.
• Change log: The binary log (binlog) in MySQL and MariaDB, and the WAL in PostgreSQL. Combined with a full backup, it gives you point-in-time recovery, such as rolling the database back to the minute before a table was dropped by mistake.

A snapshot of the whole server under a running database is no substitute for these levels: at best it’s the image of a server whose power was cut, which may or may not come back cleanly, and it gives you no point in time between two snapshots. We explain the difference between snapshots and backups in a separate article on this blog.

# Run as root, then move the backups to storage off the servermkdir -p /var/backups/db && chmod 700 /var/backups/db# MariaDB: a consistent backup without locking InnoDB tables (with MySQL, use mysqldump)mariadb-dump --single-transaction --routines --triggers --events shop | gzip > /var/backups/db/shop-$(date +%F).sql.gz# PostgreSQL: compressed custom format, restorable in full or in partsudo -u postgres pg_dump -Fc shop > /var/backups/db/shop-$(date +%F).dump

Point-in-time recovery works like this: you enable the binary log in advance and record, with every full backup, its position in that log; when an incident happens, you restore the latest backup, then replay the log from that position up to the minute before the mistake. In PostgreSQL you archive WAL files off the server and set a recovery target time when you restore. Tools such as pgBackRest and WAL-G handle all of this, and they’re safer than hand-written scripts.

# MariaDB: add to the settings file above and restart; the binary log is kept for 7 days[mysqld]log_bin = mysql-binbinlog_format = ROWbinlog_expire_logs_seconds = 604800# Then add --master-data=2 to the mariadb-dump command so each backup records its starting position# At incident time: mariadb-binlog --stop-datetime replays up to the time you specify

A backup that has never been restored is just an assumption. Set aside half an hour every month to restore the latest backup into a temporary database, check the row counts in your most important tables, and write down how long the restore took: that number is what you’ll tell management on the day of the outage.

# As root. MariaDB: restore into a temporary database, timed, then a quick checkmariadb -e "CREATE DATABASE shop_restore_test"time (gunzip -c /var/backups/db/shop-2026-10-06.sql.gz | mariadb shop_restore_test)mariadb -e "SELECT COUNT(*) FROM shop_restore_test.orders"# PostgreSQLsudo -u postgres createdb shop_restore_testtime sudo -u postgres pg_restore -d shop_restore_test < /var/backups/db/shop-2026-10-06.dump# After the check, drop only the two temporary databases, and double-check the name firstmariadb -e "DROP DATABASE shop_restore_test"sudo -u postgres dropdb shop_restore_test

04Replication: what it protects you from, and what it doesn’t

Replication keeps a second server continuously applying the primary’s changes: through the binary log in MySQL and MariaDB, and through WAL streaming in PostgreSQL. It protects you from the primary failing, and takes heavy read queries such as reports off it. But it doesn’t protect you from human error: a dropped table reaches the replica within a fraction of a second. Replication complements backups; it doesn’t replace them.

Watch for two things. First, asynchronous replication can lag by seconds under load, so whatever was written to the primary just before a failure may not be on the replica. Second, automatic failover needs at least three nodes or a voting mechanism, or you risk two servers that each believe they’re the primary. Tools such as Patroni for PostgreSQL and Galera for MariaDB solve this, with operational complexity that deserves planning. For most small companies, a good balance is a primary, an asynchronous replica in another data centre, point-in-time recovery, and a manual failover documented step by step.

05Security: who connects, with which rights

• Listening: Bind the database to 127.0.0.1 if the application is on the same server, or to a private network address if it’s on another. Don’t leave it listening on 0.0.0.0 behind a password alone: ports 3306 and 5432 are scanned from the internet constantly.
• Firewall: Allow the database port for the application server’s address only, and close it to everything else.
• Least privilege: One user per application with read and write rights on its own database only, a separate user for schema migrations, and rights on all databases for the administrator alone.
• Encryption: Every connection that crosses a network goes over TLS: require_secure_transport in MySQL and MariaDB, and hostssl lines with scram-sha-256 authentication in PostgreSQL’s pg_hba.conf.
• On shared hosting: If you enable remote MySQL access in cPanel, add one specific fixed address, not the % wildcard that opens it to every address.

# Who is listening on the database ports? You want 127.0.0.1 or a private address, not 0.0.0.0sudo ss -ltnp | grep -E ':(3306|5432) '# An application user on the shop database only, from the application server’s address onlysudo mariadb -e "CREATE USER 'shop_app'@'198.51.100.12' IDENTIFIED BY 'Replace-With-A-Long-Random-Password'; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'198.51.100.12';"# Firewall: the MariaDB port for the application server onlysudo ufw allow from 198.51.100.12 to any port 3306 proto tcp

06Monitoring: the slow query before the customer complaint

Most database slowness comes from a handful of queries without suitable indexes. Don’t guess; log them. For MySQL and MariaDB we turned on the slow query log in the settings file above with a half-second threshold, and in PostgreSQL the pg_stat_statements extension collects statistics for every query:

# MariaDB: top 5 queries by total time in the slow log (or mariadb-dumpslow)sudo mysqldumpslow -s t -t 5 /var/log/mysql/slow.log# PostgreSQL: load the extension, then restart (if the value already lists other libraries, keep them)sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"sudo systemctl restart postgresqlsudo -u postgres psql -d shop -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"# Top 5 queries by total execution timesudo -u postgres psql -d shop -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time::numeric, 1) AS mean_ms, left(query, 80) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

And keep an eye on five indicators: connections in use against the maximum, the share of reads served from memory rather than disk, replica lag, free disk space, and whether the last backup succeeded. Tools such as Percona Monitoring and Management, or Prometheus with Grafana, bring them together in one dashboard with alerts.

Problem“Too many connections” errors

FixThe application opens connections and doesn’t close them, or requests are slow so their connections pile up. Review the slow queries, put a connection pooler in front of the database, then raise the limit as far as memory allows.

ProblemThe database is slow while the CPU is quiet

FixLook at disk wait and the share of reads served from memory: the buffer pool is most likely smaller than the active data.

ProblemThe disk suddenly filled up although the data didn’t grow

FixBinary log or WAL files are piling up. Check the retention period, and make sure WAL archiving or the replica hasn’t stopped: PostgreSQL keeps the files until they’re consumed.

07Before you decide

Start from the question of loss, not performance: how many minutes of data can you afford to lose, and how many hours of downtime? If the answer is “a full day”, a reliable nightly backup is enough. If it’s “minutes”, you need a change log archived off the server, which means a server whose settings you control.

At Illevante, cPanel hosting includes unlimited MySQL-compatible MariaDB databases and PostgreSQL databases, with phpMyAdmin and remote MySQL access, and databases are part of the daily JetBackup backups. That’s enough for most WordPress sites and small stores. When you need memory tuning, point-in-time recovery or a replica, a CoreVPS server gives you root access on AMD EPYC processors, DDR5 ECC memory and NVMe storage in RAID 10, with MySQL and MongoDB as ready-made apps at order time, and Percona Monitoring and Grafana for monitoring. Run the fio test on day one, and do a full restore before you move your first real data.