Database Management Systems
# Database Management Systems
This article provides a detailed overview of Database Management Systems (DBMS) used with MediaWiki, focusing on configuration and technical details relevant for server administrators. Understanding your DBMS is crucial for optimal MediaWiki performance and data integrity. We will cover commonly used databases: MySQL/MariaDB, PostgreSQL, and SQLite.
Introduction to DBMS and MediaWiki
MediaWiki relies on a robust DBMS to store all its data: page content, user information, revision history, and configuration settings. The choice of DBMS can significantly impact performance, scalability, and ease of maintenance. While MediaWiki supports several options, MySQL/MariaDB and PostgreSQL are the most commonly deployed in production environments. SQLite is suitable for smaller, less demanding installations, such as testing or personal wikis.
The `$wgDBtype` variable in `LocalSettings.php` defines the database type used by your MediaWiki installation. Correctly configuring this variable, along with the associated database credentials and settings, is the first step in setting up your MediaWiki environment. Refer to the Configuration settings page for details on `LocalSettings.php`.
MySQL / MariaDB
MySQL and MariaDB are popular open-source relational database management systems. MariaDB is a fork of MySQL, aiming to remain open-source while providing enhanced features and performance. They are widely used with MediaWiki due to their maturity and extensive documentation.
Technical Specifications
| Parameter | Value | ||||||
|---|---|---|---|---|---|---|---|
| Database Engine | InnoDB (recommended) | Version (Minimum) | MySQL 5.7 or MariaDB 10.2 | Character Set | utf8mb4 (recommended) | Collation | utf8mb4_unicode_ci (recommended) |
| Max Allowed Packet | 64M (adjustable based on needs) | Wait Timeout | 28800 (seconds - adjustable) |
Configuration Considerations
- **InnoDB:** Using the InnoDB storage engine is *strongly* recommended for MediaWiki. It provides transaction support, row-level locking, and better crash recovery compared to MyISAM.
- **Character Set & Collation:** `utf8mb4` and `utf8mb4_unicode_ci` are essential for supporting a wide range of characters, including emojis and complex scripts. Incorrect character set configuration can lead to data corruption. See Internationalization and localization for more details.
- **`max_allowed_packet`:** This variable controls the maximum size of a single packet that can be sent to or received from the MySQL server. Increase it if you encounter errors related to large data transfers, such as importing large XML dumps.
- **Caching:** Leverage MySQL/MariaDB's query cache and other caching mechanisms to improve performance. Consider using a dedicated caching layer like Memcached or Redis in conjunction with the database for optimal results.
- **Connection Pooling:** PostgreSQL can be resource-intensive when handling a large number of concurrent connections. Using a connection pooler like PgBouncer is *highly* recommended to reduce overhead and improve performance. See Database connection pooling for information.
- **`shared_buffers`:** This setting controls the amount of memory allocated to the PostgreSQL buffer cache. Adjust it based on your server's RAM capacity and workload.
- **Write-Ahead Logging (WAL):** Configure WAL appropriately for your replication strategy. `replica` is recommended for replication scenarios.
- **Indexing:** Properly indexing database tables is crucial for query performance. Analyze your query patterns and create indexes accordingly. See Database optimization for guidance.
- **Concurrency:** SQLite is not designed for high-concurrency environments. Multiple writers can cause contention and performance issues.
- **File System:** SQLite operates on the local file system. Network file systems can introduce performance overhead.
- **Backup:** Regularly back up the SQLite database file to prevent data loss.
- **Performance:** For larger wikis, SQLite's performance will degrade significantly compared to MySQL/MariaDB or PostgreSQL. Consider migrating to a more robust DBMS if you experience performance issues. Review Performance tuning before considering a migration.
- Create, read, update, and delete tables.
- Create indexes.
- Select, insert, update, and delete data.
- MediaWiki database schema
- Database administration
- Configuration settings
- Performance tuning
- Security best practices
- Telegram: @powervps Servers at a discounted price
PostgreSQL
PostgreSQL is another powerful open-source relational database system known for its standards compliance, reliability, and advanced features. It is an excellent alternative to MySQL/MariaDB, offering robust support for complex queries and data types.
Technical Specifications
| Parameter | Value | ||||||
|---|---|---|---|---|---|---|---|
| Version (Minimum) | PostgreSQL 9.6 | Character Encoding | UTF8 (recommended) | Collation | en_US.UTF-8 (adjust based on locale) | Connection Pooling | PgBouncer (recommended) |
| WAL Level | replica (recommended for replication) | Shared Buffers | 25% of RAM (adjustable based on needs) |
Configuration Considerations
SQLite
SQLite is a self-contained, serverless, zero-configuration database engine. It stores the entire database in a single file. It is suitable for small-scale deployments, such as local testing or personal wikis, where high concurrency and scalability are not critical.
Technical Specifications
| Parameter | Value | ||||||
|---|---|---|---|---|---|---|---|
| Database File | Typically `data/sqlite.db` | Concurrency | Limited (single-writer, multiple-readers) | File System | Local filesystem only | Transaction Support | Full ACID compliance |
Configuration Considerations
Database User Permissions
Regardless of the DBMS you choose, it's essential to create a dedicated database user for MediaWiki with the minimum required privileges. Granting excessive permissions can pose a security risk. The MediaWiki user should have permissions to:
Consult your DBMS documentation for specific instructions on managing user permissions. See Security best practices for overall security guidelines.
Additional Resources
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.* ⚠️