Admin: Automated server configuration article

2025-04-15T16:59:52Z

Automated server configuration article

New page

# 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.

== 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.

{| class="wikitable"
! 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 ===

{| class="wikitable"
! 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 ==

* **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]].

== Troubleshooting ==

* **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.

== Further Reading ==

* [[MySQL Documentation]]
* [[MediaWiki Installation]]
* [[Server Security]]

[[Category:Server Hardware]]

== Intel-Based Server Configurations ==
{| class="wikitable"
! 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 ==
{| class="wikitable"
! 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 ==
[https://powervps.net/?from=32 Configure and order] your ideal server configuration

=== Need Assistance? ===
* Telegram: [https://t.me/powervps @powervps Servers at a discounted price]

⚠️ *Note: All benchmark scores are approximate and may vary based on configuration. Server availability subject to stock.* ⚠️

{{Exchange Box}}

MySQL - Revision history

Admin: Automated server configuration article