MySQL / MariaDB administration

MySQL and MariaDB administration – database optimization, backup, and replication

The database is the heart of every WordPress site and WooCommerce store – and simultaneously the most common performance bottleneck. The default MySQL configuration is not tailored to WordPress's requirements: an InnoDB buffer that is too small, a lack of query optimization, and bloated wp_options and wp_postmeta tables containing thousands of unnecessary transients and revisions.

That is why at WebOptimo we treat MySQL and MariaDB as components requiring continuous attention – we tune the configuration to the actual workload profile, eliminate slow queries before they become a problem, and ensure that a backup isn't just a cron entry, but a tested restoration procedure.

Tuning

MySQL / MariaDB configuration optimization

The default MySQL configuration is a starting point, not the goal. We adjust key server parameters: innodb_buffer_pool_size (tailored to database size and available RAM), innodb_log_file_size, tmp_table_size, max_connections, thread_cache_size, join_buffer_size. We choose each parameter based on a workload analysis – we do not copy configurations from the internet. We detail memory calculations for MariaDB within the context of the entire stack in our publication Efficient LEMP stack architecture.

Queries

Analysis and optimization of slow queries

We enable and analyze the slow query log, identify queries burdening the database, add missing indexes, and rewrite inefficient queries. In WordPress and WooCommerce, typical issues include: full table scans on wp_postmeta, slow queries on wp_options with autoload, and missing indexes on order and product tables. We use EXPLAIN and profiling tools to identify bottlenecks.

WordPress

WordPress and WooCommerce database optimization

We clean the database of transients, post revisions, orphaned metadata, logs, and spam comments (see the full guide: WordPress database optimization). We optimize wp_options (limiting autoload), wp_postmeta (indexing), and WooCommerce tables (orders, sessions, logs). We convert MyISAM tables to InnoDB, verify character encoding (utf8mb4), and repair corrupted tables.

Backup

Backups and disaster recovery

We perform database backups using mysqldump, mysqlpump, or Percona XtraBackup. For large WooCommerce databases, we use incremental hot backups via Percona XtraBackup – without table locking and zero downtime. We store copies locally and remotely (offsite) with AES-256 encryption and retention rotation. We regularly test data recovery procedures. In ZFS environments, we use atomic snapshots and optimizations described in our publication ZFS optimization for PostgreSQL and MySQL.

Replication

MySQL / MariaDB replication

We configure database replication: master-slave for distributing read loads and disaster recovery, master-master for high availability. Replication is especially crucial for large WooCommerce stores with heavy traffic – reads from the replica relieve the main database server. We monitor replication lag and data integrity.

Monitoring

Database performance monitoring

We monitor key MySQL/MariaDB metrics 24/7: queries per second, InnoDB buffer pool usage, slow queries, active connections, table locks, table and index size, and disk usage. Alerts are triggered when thresholds are exceeded – we react before database problems impact site performance.

Most WordPress and WooCommerce performance issues stem from the database – not the server, PHP, or cache. A bloated wp_options table with thousands of autoloaded transients, missing indexes on wp_postmeta, and a default MySQL configuration with an InnoDB buffer set to 128 MB for a 2 GB database. Database optimization is not a one-time effort – it is regular work that we perform as part of every WordPress care plan. For larger database migrations, it's also worth checking our guide on migrating WordPress to a new server.

Process

How we work

MySQL and MariaDB database administration at WebOptimo is a repeatable, structured process – not a one-time intervention.

Audit Analysis of the MySQL server configuration (my.cnf), slow query log, InnoDB variable status, table sizes, indexes, bloat, and backup status. The result is a report with a prioritized list of issues and recommendations.
Plan Prioritizing changes based on their impact on performance and deployment risks. Quick wins first; larger changes (indexes, partitioning, replication) with a schedule and rollback scenario.
Implementation Tuning server parameters, adding missing indexes, cleaning the database, and configuring replication or backups. Changes are made gradually, with metrics measured before and after – every optimization has a measured effect.
Monitoring Continuous 24/7 monitoring of key metrics: queries per second, InnoDB buffer pool, slow queries, active connections, replication lag. Alerts and responses before a problem impacts the application.
Reporting Periodic reports with performance metrics, a list of completed actions, and recommendations for the next period. The client always knows the state of the database and what we plan next.
Scenarios

When do you need MySQL / MariaDB administration?

Not every company has a database administrator on their team – but every company with a production database sooner or later faces one of these scenarios.

Performance

The site runs slowly despite a powerful server

Adding RAM and cores won't help when the InnoDB buffer is set to 128 MB and the heaviest queries scan entire tables without indexes. A database audit identifies the actual bottlenecks – often, a few configuration changes and adding missing indexes yield a greater effect than a server twice as expensive.

Backup

The backup exists, but no one has tested it

A cron job with mysqldump that saves a file on the same disk as the database is not a backup strategy – it's a false sense of security. We verify whether the copies are complete, encrypted, stored offsite, and whether the restoration procedure actually works under time pressure.

Migration

You are moving to a new server

Database migration is not just export and import. You need to verify character encoding (utf8mb4), the table engine (InnoDB vs MyISAM), the MySQL/MariaDB version on the new server, and then tune the configuration for the new hardware resources. We carry out migrations with minimal downtime and full verification of data integrity.

Scale

The store is growing – the database can't keep up

A growing product catalog, order history, and concurrent customer traffic put strain on WooCommerce databases in ways the default MySQL configuration doesn't anticipate. We deploy read replication, connection pooling, and table partitioning before performance issues become business problems.

Support

No DBA on the team

A developer who "also handles the database" is not a database administrator. There's no time for monitoring, slow query log analysis is postponed, and MySQL updates are put off for months. We take over MySQL/MariaDB administration as an external DBA – with regular monitoring, tuning, and reporting.

Security

The database is accessible from the outside

Port 3306 open to the world, root with the password "admin123", no SSL on connections – these are not hypothetical scenarios. Such mistakes still happen on self-configured VPS and dedicated servers. We secure access to MySQL: restricting the bind-address, deploying roles and permissions according to the principle of least privilege, and forcing encrypted connections.

Frequently asked questions

Questions and answers

WordPress officially supports both MySQL and MariaDB. MariaDB is a fork of MySQL with better performance in many scenarios, a transparent licensing model, and active development. In most cases, we recommend MariaDB – it is fully compatible with WordPress and WooCommerce, while offering InnoDB (Aria) engine optimizations, a better query optimizer, and faster operations on large tables.

Optimization includes tuning MySQL server configuration (innodb_buffer_pool_size, innodb_log_file_size, query_cache, tmp_table_size), analyzing and optimizing slow queries (slow query log), adding missing indexes, cleaning tables of transients and revisions, optimizing wp_options and wp_postmeta tables, converting MyISAM to InnoDB, and configuring connection limits.

We perform MySQL database backups using mysqldump, mysqlpump, or Percona XtraBackup (hot backups without table locking). We store copies locally and remotely (offsite), with encryption and retention rotation. For large WooCommerce databases, we use incremental Percona XtraBackup backups, minimizing server load and execution time.

Yes. We configure MySQL/MariaDB replication – master-slave for reading from the replica and disaster recovery, master-master for high availability. Replication allows distributing the read load across multiple servers, which is particularly important for large WooCommerce stores with heavy traffic.

The most common cause of a slow WordPress site on a powerful server is a misconfigured or unoptimized database. The default MySQL configuration is not tailored for WordPress – the InnoDB buffer is too small, queries aren't optimized, wp_options and wp_postmeta tables are bloated, and indexes are missing. Database audits and optimization often yield a bigger performance improvement than adding server resources.

Yes. We monitor key MySQL/MariaDB metrics: queries per second, buffer pool usage, slow queries, active connections, table locks, and the size of tables and indexes. Alerts are sent immediately when thresholds are exceeded. We regularly analyze the slow query log and propose optimizations.

Yes. We perform MySQL/MariaDB database migrations between servers – export, transfer, import with data integrity verification. We migrate WordPress and WooCommerce databases considering URL changes (search-replace), character encoding, and the configuration of the new MySQL server. Migration takes place without data loss and with minimal downtime.

Contact

Let's talk about optimizing your database

We will present a MySQL/MariaDB database administration and optimization offer tailored to your needs. No obligations, no marketing jargon – a concrete proposal after a short conversation or database analysis.

Phone

+48 608 271 665

Mon–Fri, 8:00–16:00 CET

Email

contact@weboptimo.pl

We respond within 24h

Company

WebOptimo

VAT ID: PL6391758393

Marcin Szewczyk-Wilgan - m4c.pl  Powered by FreeBSD  Edited in Vim