Database management
# Database Management
This article details the database management aspects of our MediaWiki 1.40 server configuration. Proper database management is crucial for performance, stability, and data integrity. This guide is aimed at newcomers responsible for server maintenance.
Database System Choice
We utilize MariaDB as our primary database system. While MySQL is also compatible, MariaDB offers performance enhancements and open-source commitment aligning with our server philosophy. The database stores all wiki content, user information, revision history, and configuration settings. Understanding its configuration is paramount. Alternatives like PostgreSQL are possible but require significant configuration changes to MediaWiki. For detailed information on supported databases, refer to the Configuration/Databases page.
Database Server Specifications
The following table outlines the specifications of our database server:
| Specification | Value |
|---|---|
| Server Operating System | Ubuntu Server 22.04 LTS |
| CPU | Intel Xeon Gold 6248R (24 cores) |
| RAM | 128 GB DDR4 ECC |
| Storage | 2 x 2 TB NVMe SSD (RAID 1) |
| Network Interface | 10 Gbps Ethernet |
This configuration supports high read/write operations and ensures data redundancy through RAID. Regular monitoring of Server statistics is crucial to identify potential bottlenecks.
MariaDB Configuration
The `my.cnf` file (typically located at `/etc/mysql/my.cnf` or `/etc/my.cnf`) governs the MariaDB server's behavior. Key configuration parameters are detailed below. Adjustments should be made cautiously and after thorough testing, referencing the MariaDB documentation.
| Parameter | Value | Description |
|---|---|---|
| `innodb_buffer_pool_size` | 64G | Specifies the size of the buffer pool used by the InnoDB storage engine. A larger pool generally improves performance. |
| `innodb_log_file_size` | 512M | Size of each InnoDB log file. Larger values reduce checkpointing frequency, but increase recovery time. |
| `innodb_flush_log_at_trx_commit` | 1 | Controls the balance between data safety and performance. `1` (default) provides full ACID compliance, while `0` or `2` can improve performance at the risk of data loss. |
| `max_connections` | 500 | Maximum number of concurrent client connections allowed. |
| `query_cache_size` | 0 | Query cache size. Disabled due to performance issues in modern workloads. |
These settings are optimized for our MediaWiki installation. Monitoring the Performance logs will help identify the need for adjustments.
Database Maintenance Tasks
Regular database maintenance is essential for optimal performance. Key tasks include:
- Backups: Daily full backups and incremental backups every hour are performed using mysqldump. Backups are stored offsite for disaster recovery. See Backup procedures for details.
- Optimization: Weekly table optimization using `OPTIMIZE TABLE` reduces fragmentation and improves query performance.
- Repair: If data corruption is detected (rare, but possible), `REPAIR TABLE` can be used to attempt to fix the issues.
- Log Rotation: MariaDB logs are rotated regularly to prevent disk space exhaustion. See Log file management.
- User Management: Strict access control is enforced. Only authorized personnel have access to the database server, and all access is logged. Refer to User account management.
- Database Monitoring: Constant monitoring of database performance metrics (query times, connection counts, disk I/O) using tools like Nagios and Grafana.
- Firewall: A firewall restricts access to the database server to only authorized IP addresses.
- Strong Passwords: Strong, unique passwords are used for all database users.
- Regular Security Audits: Regular security audits are performed to identify and address potential vulnerabilities.
- Encryption: Data is encrypted both in transit and at rest.
- Principle of Least Privilege: Database users are granted only the necessary permissions. See Security policies for details.
- Telegram: @powervps Servers at a discounted price
MediaWiki Database Tables
MediaWiki utilizes a number of tables to store its data. Here’s a summary of some critical tables:
| Table Name | Description |
|---|---|
| `mw_user` | Stores user account information. |
| `mw_page` | Stores information about wiki pages (IDs, titles, namespace). |
| `mw_revision` | Stores the content and revision history of each page. |
| `mw_category` | Stores information about wiki categories. |
| `mw_categorylink` | Links pages to categories. |
| `mw_watchlist` | Stores the list of pages watched by each user. |
Understanding the relationships between these tables is crucial for advanced troubleshooting and database query optimization. Detailed table schemas can be found on the Database schema page.
Security Considerations
Database security is paramount. We implement the following measures:
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.* ⚠️