MySQL
# MySQL Server Configuration for MediaWiki 1.40
This article details the necessary MySQL server configuration for optimal performance of a MediaWiki 1.40 installation. Proper configuration is crucial for handling the read and write load generated by a wiki, especially as it grows in size and user base. This guide assumes you have a working MySQL server installation.
Prerequisites
Before beginning, ensure you have:
- A running MySQL server (version 5.7 or 8.0 is recommended). See MySQL Website for installation instructions.
- Root access to the MySQL server, or a user with sufficient privileges to create databases and grant permissions.
- Familiarity with basic SQL commands. Refer to SQL Tutorial for an introduction.
- A basic understanding of Server Administration concepts.
- **Large Tables:** As your wiki grows, tables like `recentchanges` and `page` can become very large. Consider using partitioning or archiving strategies to manage these tables. Refer to MediaWiki Performance for more advanced techniques.
- **Caching:** Utilize MediaWiki's built-in caching mechanisms (e.g., Memcached, APCu) to reduce database load. See MediaWiki Caching.
- **Regular Maintenance:** Perform regular database maintenance tasks such as `OPTIMIZE TABLE` and `ANALYZE TABLE` to improve performance. See Database Maintenance.
- **Slow Queries:** Use the MySQL slow query log to identify and optimize poorly performing queries. See MySQL Slow Query Log.
- **Connection Issues:** Check the MySQL error log for connection errors.
- **Resource Constraints:** Monitor server resources (CPU, memory, disk I/O) to identify bottlenecks.
- MySQL Documentation
- MediaWiki Installation
- Server Security
- Telegram: @powervps Servers at a discounted price
Database Creation
First, create a dedicated database for your MediaWiki installation. This isolates MediaWiki’s data from other applications on the server.
```sql CREATE DATABASE IF NOT EXISTS wikidb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ```
Next, create a dedicated MySQL user for MediaWiki with appropriate permissions. *Never* use the root user for MediaWiki.
```sql CREATE USER 'wikiuser'@'localhost' IDENTIFIED BY 'your_strong_password'; GRANT ALL PRIVILEGES ON wikidb.* TO 'wikiuser'@'localhost'; FLUSH PRIVILEGES; ```
Replace `'your_strong_password'` with a secure password. Limit the user to `localhost` access if MediaWiki and MySQL are on the same server for security. For remote access, change `'localhost'` to the appropriate IP address or `%` for any host (not recommended). Refer to MySQL Security for further security best practices.
Configuration Settings
The following MySQL settings significantly impact MediaWiki performance. These should be adjusted in your `my.cnf` (or `my.ini` on Windows) file. The location of this file varies depending on your operating system and MySQL installation. Common locations include `/etc/mysql/my.cnf`, `/etc/my.cnf`, or `/usr/local/mysql/etc/my.cnf`. Always back up your configuration file before making changes.
Key Configuration Parameters
Here's a table outlining recommended settings. Adjust these based on your server's resources and anticipated load.
| Parameter | Description | Recommended Value | Notes |
|---|---|---|---|
| `innodb_buffer_pool_size` | The size of the buffer pool used by the InnoDB storage engine. This is the most important setting for InnoDB performance. | 50-80% of available RAM | Increase this value as much as possible without causing swapping. |
| `innodb_log_file_size` | The size of each InnoDB log file. Larger log files reduce the frequency of checkpoints. | 256M - 1G | Adjust based on write activity; larger values require longer recovery times. |
| `innodb_flush_log_at_trx_commit` | Controls how often InnoDB flushes the log buffer to disk. | 1 | 0 is fastest, but least durable. 2 is a compromise. 1 is slowest, but most durable. |
| `key_buffer_size` | The size of the buffer used for MyISAM key blocks. (Less relevant if primarily using InnoDB) | 16M - 64M | If using MyISAM tables, increase this. |
| `max_connections` | The maximum number of simultaneous client connections allowed. | 150 - 500 | Increase if you expect a high number of concurrent users. |
| `query_cache_size` | The size of the query cache. (Deprecated in MySQL 8.0) | 0 (or disabled in 8.0) | The query cache can be detrimental to performance; disable it if using MySQL 8.0 or if contention is high. |
Character Set and Collation
It's essential to use `utf8mb4` as the character set and `utf8mb4_unicode_ci` as the collation for your database, tables, and connection. This supports a wider range of characters, including emojis. Ensure this is set during database creation (as shown above) and also configured in your MediaWiki `LocalSettings.php` file. Refer to Character Sets for more details.
Table Character Set Verification
You can verify the character set and collation of your tables using the following SQL query:
```sql SHOW CREATE TABLE mytable; ```
Replace `mytable` with the name of your table. If the character set or collation is incorrect, you can alter the table:
```sql ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ```
InnoDB Tuning
InnoDB is the recommended storage engine for MediaWiki. Properly tuning InnoDB is critical for performance.
InnoDB Status Monitoring
Use the following command to view InnoDB status information:
```sql SHOW ENGINE INNODB STATUS; ```
This provides valuable insights into buffer pool usage, log activity, and other important metrics. Pay attention to the "Buffer pool hit rate" – a high hit rate (above 99%) indicates efficient buffer pool usage. See InnoDB Monitoring for more detailed analysis.
InnoDB Configuration Detail
| Parameter | Description | Recommended Value | Notes |
|---|---|---|---|
| `innodb_file_per_table` | Enables storing each InnoDB table in a separate file. | ON | Recommended for easier maintenance and backup. |
| `innodb_io_capacity` | The number of I/O operations InnoDB can perform per second. | 200-2000 | Adjust based on your disk's capabilities. SSDs support higher values. |
| `innodb_flush_method` | Controls how InnoDB flushes data to disk. | O_DIRECT | Can improve performance, especially with SSDs. |
MediaWiki Specific Considerations
Troubleshooting
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.* ⚠️