Database Management
# Database Management
This article details the database management configuration for our MediaWiki 1.40 installation. Proper database configuration is crucial for performance, stability, and scalability. This guide is aimed at system administrators and those responsible for maintaining the wiki server.
Database Selection
MediaWiki supports several database management systems (DBMS). The most commonly used and recommended are:
- MySQL/MariaDB - A popular open-source relational database. Generally the easiest to set up and maintain.
- PostgreSQL - Another powerful open-source relational database, known for its standards compliance and advanced features.
- SQLite - A file-based database, suitable for small, low-traffic wikis or development environments. *Not recommended for production.*
- MySQL/MariaDB: Run `OPTIMIZE TABLE` periodically to reclaim space and improve performance. Also, regularly check and repair tables using `CHECK TABLE` and `REPAIR TABLE`.
- PostgreSQL: Use `VACUUM` to reclaim space occupied by deleted or updated rows. `ANALYZE` updates statistics used by the query planner. Regularly running these is vital. See Database Vacuuming for more information.
- Full Backups: Create a complete copy of the database.
- Incremental Backups: Back up only the changes made since the last full or incremental backup.
- Differential Backups: Back up only the changes made since the last full backup.
- MediaWiki Installation
- Database Schema
- Performance Tuning
- Server Security
- Troubleshooting Database Issues
- Telegram: @powervps Servers at a discounted price
This guide will focus primarily on configurations using MySQL/MariaDB and PostgreSQL, as those are the standards for production environments.
MySQL/MariaDB Configuration
The following table outlines recommended MySQL/MariaDB configuration settings. These settings may need adjustment based on your server's resources and traffic levels. These settings are placed in the `my.cnf` or `my.ini` file, depending on your operating system.
| Parameter | Description | Recommended Value |
|---|---|---|
| `innodb_buffer_pool_size` | The size of the buffer pool used by InnoDB to cache data and indexes. | 50-80% of available RAM |
| `innodb_log_file_size` | The size of each InnoDB log file. Larger values improve write performance but increase recovery time. | 256M - 1G |
| `innodb_flush_log_at_trx_commit` | Controls how often InnoDB flushes log data to disk. `1` is the safest (default), `0` is fastest but risks data loss. | 1 (for production), 0 (for testing) |
| `key_buffer_size` | The size of the buffer used for MyISAM key caches. Less important if using InnoDB. | 32M - 64M |
| `max_connections` | The maximum number of concurrent connections to the database. | 150 - 500 (depending on traffic) |
Remember to restart the MySQL/MariaDB service after making changes to the configuration file. Also, ensure the `mysql` user has the necessary permissions on the MediaWiki database. See Database Permissions for details.
PostgreSQL Configuration
PostgreSQL offers a different set of configuration parameters. The `postgresql.conf` file controls these settings.
| Parameter | Description | Recommended Value |
|---|---|---|
| `shared_buffers` | The amount of memory dedicated to shared memory buffers. | 25% of available RAM |
| `work_mem` | The amount of memory used by internal sort operations and hash tables. | 64MB - 256MB |
| `maintenance_work_mem` | The amount of memory used for maintenance operations like `VACUUM` and `CREATE INDEX`. | 64MB - 512MB |
| `effective_cache_size` | An estimate of how much memory is available to the operating system for disk caching. | 50% of available RAM |
| `max_connections` | The maximum number of concurrent connections to the database. | 100 - 300 (depending on traffic) |
Like MySQL/MariaDB, restart the PostgreSQL service after modifying the `postgresql.conf` file. Security is paramount; review PostgreSQL Security for best practices.
Database Maintenance
Regular database maintenance is essential for optimal performance.
The following table shows a recommended maintenance schedule:
| Task | Frequency | Description |
|---|---|---|
| `OPTIMIZE TABLE` (MySQL/MariaDB) | Weekly | Reclaims space and improves performance. |
| `VACUUM` (PostgreSQL) | Daily | Reclaims space occupied by deleted or updated rows. |
| `ANALYZE` (PostgreSQL) | Daily | Updates statistics used by the query planner. |
| Database Backup | Daily/Weekly | Creates a copy of the database for disaster recovery. See Database Backups. |
Database Backups
Implementing a robust backup strategy is critical to protect your wiki data. Consider the following:
Tools like `mysqldump` (for MySQL/MariaDB) and `pg_dump` (for PostgreSQL) can be used to create database backups. Store backups offsite for disaster recovery purposes. See Backup Procedures for detailed instructions.
Further Reading
Intel-Based Server Configurations
| Configuration | Specifications | Benchmark |
|---|---|---|
| Core i7-6700K/7700 Server | 64 GB DDR4, NVMe SSD 2 x 512 GB | CPU Benchmark: 8046 |
| Core i7-8700 Server | 64 GB DDR4, NVMe SSD 2x1 TB | CPU Benchmark: 13124 |
| Core i9-9900K Server | 128 GB DDR4, NVMe SSD 2 x 1 TB | CPU Benchmark: 49969 |
| Core i9-13900 Server (64GB) | 64 GB RAM, 2x2 TB NVMe SSD | |
| Core i9-13900 Server (128GB) | 128 GB RAM, 2x2 TB NVMe SSD | |
| Core i5-13500 Server (64GB) | 64 GB RAM, 2x500 GB NVMe SSD | |
| Core i5-13500 Server (128GB) | 128 GB RAM, 2x500 GB NVMe SSD | |
| Core i5-13500 Workstation | 64 GB DDR5 RAM, 2 NVMe SSD, NVIDIA RTX 4000 |
AMD-Based Server Configurations
| Configuration | Specifications | Benchmark |
|---|---|---|
| Ryzen 5 3600 Server | 64 GB RAM, 2x480 GB NVMe | CPU Benchmark: 17849 |
| Ryzen 7 7700 Server | 64 GB DDR5 RAM, 2x1 TB NVMe | CPU Benchmark: 35224 |
| Ryzen 9 5950X Server | 128 GB RAM, 2x4 TB NVMe | CPU Benchmark: 46045 |
| Ryzen 9 7950X Server | 128 GB DDR5 ECC, 2x2 TB NVMe | CPU Benchmark: 63561 |
| EPYC 7502P Server (128GB/1TB) | 128 GB RAM, 1 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (128GB/2TB) | 128 GB RAM, 2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (128GB/4TB) | 128 GB RAM, 2x2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (256GB/1TB) | 256 GB RAM, 1 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (256GB/4TB) | 256 GB RAM, 2x2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 9454P Server | 256 GB RAM, 2x2 TB NVMe |
Order Your Dedicated Server
Configure and order your ideal server configurationNeed Assistance?
⚠️ *Note: All benchmark scores are approximate and may vary based on configuration. Server availability subject to stock.* ⚠️