Database Optimization
# Database Optimization
This article details techniques for optimizing the database server used by your MediaWiki 1.40 installation. A well-tuned database is crucial for maintaining site performance, particularly as the wiki grows in size and traffic. This guide is intended for system administrators and those with a basic understanding of database administration. We will focus on MySQL/MariaDB, the most commonly used database backend for MediaWiki.
Understanding Database Bottlenecks
Before diving into specific optimizations, it's important to understand common database bottlenecks that affect MediaWiki. These include:
- **Slow Queries:** Complex or poorly indexed queries can take a long time to execute, slowing down page loads and edit operations.
- **Table Locks:** Concurrent access can lead to table locks, preventing users from reading or writing data.
- **Insufficient Memory:** Insufficient RAM can force the database to use disk for caching, significantly impacting performance.
- **Disk I/O:** Slow disk access times can be a major bottleneck, especially for large wikis.
- **Connection Limits:** The database server may be configured with too few maximum connections, denying access to users.
- `mysqladmin`: A command-line tool for monitoring MySQL server status.
- `SHOW GLOBAL STATUS`: A MySQL command to display various server status variables.
- `SHOW PROCESSLIST`: A MySQL command to display currently running queries and their status.
- **phpMyAdmin:** A web-based interface for managing MySQL databases. (See Manual:phpMyAdmin for installation instructions)
- **Percona Monitoring and Management (PMM):** A free and open-source platform for managing and monitoring MySQL performance.
- **Analyze Slow Queries:** Use the `SLOW_QUERY_LOG` to identify queries that are taking a long time to execute. (See Manual:Slow Query Log for setup.)
- **Add Indexes to Frequently Queried Columns:** Columns used in `WHERE`, `JOIN`, and `ORDER BY` clauses should be indexed.
- **Avoid Over-Indexing:** Too many indexes can slow down write operations.
- **Use Composite Indexes:** For queries that filter on multiple columns, consider creating a composite index.
- **Optimize Tables:** Use `OPTIMIZE TABLE` to reclaim unused space and improve performance.
- **Analyze Tables:** Use `ANALYZE TABLE` to update table statistics, helping the query optimizer make better decisions.
- **Backup Regularly:** Regularly back up your database to protect against data loss. See Manual:Backups for backup strategies.
- **Purge Old Revisions:** Use the `maintenance/purge_old_revisions.php` script to remove old revisions that are no longer needed. (See Manual:Purge Old Revisions for instructions).
- Telegram: @powervps Servers at a discounted price
Monitoring Database Performance
Regularly monitoring database performance is essential for identifying bottlenecks. Several tools can be used:
Configuration Variables
Tweaking MySQL/MariaDB configuration variables is a key aspect of database optimization. The following variables are particularly important for MediaWiki:
| Variable | Description | Suggested Value (Example) |
|---|---|---|
| `innodb_buffer_pool_size` | The size of the buffer pool used by InnoDB to cache data and indexes. | 50-80% of available RAM (e.g., 4G) |
| `key_buffer_size` | The size of the buffer used for MyISAM indexes. (Less relevant if using InnoDB) | 64M - 256M |
| `query_cache_size` | The size of the query cache. (Deprecated in recent MySQL versions, consider disabling) | 0 or a small value (e.g., 32M) |
| `max_connections` | The maximum number of concurrent connections to the database server. | 150 - 500 (adjust based on traffic) |
| `innodb_log_file_size` | The size of each InnoDB log file. | 256M - 1G |
These values are examples and should be adjusted based on your server's resources and workload. Always test changes in a staging environment before applying them to production. See Manual:Configuration for more details on configuration.
Indexing Strategies
Proper indexing is crucial for fast query performance. MediaWiki relies heavily on indexes for searching and retrieving data.
Here's a table illustrating common MediaWiki tables and suggested indexes:
| Table | Column(s) | Index Type |
|---|---|---|
| `page` | `page_title`, `page_namespace` | UNIQUE |
| `page` | `page_id` | PRIMARY |
| `revision` | `rev_page` | INDEX |
| `revision` | `rev_timestamp` | INDEX |
| `categorylink` | `cl_from` | INDEX |
| `categorylink` | `cl_to` | INDEX |
Remember to use `ANALYZE TABLE` after adding indexes to update statistics. See Manual:Database Maintenance for more maintenance tips.
Database Engine Choice: InnoDB vs. MyISAM
MediaWiki officially supports both InnoDB and MyISAM, but **InnoDB is strongly recommended**.
| Feature | InnoDB | MyISAM |
|---|---|---|
| Transaction Support | Yes | No |
| Row-Level Locking | Yes | Table-Level Locking |
| Data Integrity | High | Lower |
| Performance (Reads) | Generally good, especially with proper configuration. | Faster for read-only workloads. |
| Performance (Writes) | Can be slower than MyISAM for simple writes, but better concurrency. | Faster for simple writes. |
InnoDB provides better data integrity, concurrency, and transaction support, which are essential for a collaborative wiki environment. While MyISAM might offer slightly faster read performance in some cases, the benefits of InnoDB generally outweigh the drawbacks. Converting tables from MyISAM to InnoDB is covered in Manual:Converting Tables.
Regular Maintenance
Regular database maintenance is crucial for maintaining optimal performance:
Conclusion
Optimizing your MediaWiki database is an ongoing process. By monitoring performance, adjusting configuration variables, implementing proper indexing strategies, choosing the right database engine, and performing regular maintenance, you can ensure that your wiki remains responsive and reliable. Always test any changes in a staging environment before applying them to production. For further assistance, consult the MediaWiki documentation and the MySQL/MariaDB documentation.
Manual:Configuration Manual:Database Maintenance Manual:Converting Tables Manual:Backups Manual:Purge Old Revisions Manual:phpMyAdmin Extension:PerformanceMonitor Help:Contents Manual:Upgrading Manual:Installation Manual:Command-line tools Manual:Maintenance scripts Help:Database Help:Performance Help:Site configuration Manual:Caching
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.* ⚠️