cMaNGOS InnoDB Tables & my.cnf Settings

In a default cManGOS installation, most of the database tables are configured as MyISAM. This setting allows for maximum compatibility on servers with low memory limits. We can gain row-level over table-level locking in the database and improve on overall performance, reliability and server startup times by converting all MyISAM tables to InnoDB format, which requires higher memory usage on the server. We should also configure the my.cnf file to create larger InnoDB mempools to be used with the new tables and for other general performance optimization settings.

Convert Mysql tables to InnoDB Format

Create a shell script and copy/paste the following code to convert the tables from MyISAM to InnoDB for each of the databases included in a cManGOS-TBC installation. Replace the values for the database names, mysql root username and password at the beginning of the file.

#!/bin/bash

// Fill in database names and mysql root user/pass here:
dbmangos=<mangos_dbname>
dbrealmd=<realmd_dbname>
dbcharac=<charac_dbname>
u=<username>
p=<password>

TABLES=$(mysql -p$p -u$u --skip-column-names -B -D $dbmangos -e 'show tables')
for T in $TABLES
do
    mysql -p$p -u$u -D $dbmangos -e "ALTER TABLE $T ENGINE=InnoDB"
done
TABLES=$(mysql -p$p -u$u --skip-column-names -B -D $dbrealmd -e 'show tables')
for T in $TABLES
do
    mysql -p$p -u$u -D $dbrealmd -e "ALTER TABLE $T ENGINE=InnoDB"
done
TABLES=$(mysql -p$p -u$u --skip-column-names -B -D $dbcharac -e 'show tables')
for T in $TABLES
do
    mysql -p$p -u$u -D $dbcharac -e "ALTER TABLE $T ENGINE=InnoDB"
done

Make sure to remove the shell script containing your mysql root password after completing the configuration.

Configure Optimal my.cnf Settings

Edit the my.cnf file and copy/paste the following settings. This will create a 2gb (2x1gb) max-sized memory pool for InnoDB to use, it will also require 512mb hard drive space for the log file. On systems with more memory available, a larger total pool size and # of instances should be specified. If you have other applications already running on the same mysql server, you may have conflicting configurations.

On Debian systems my.cnf is located at:
/etc/mysql/my.cnf

On Red Hat systems and others your my.cnf may also be located at:
/etc/my.cnf

[client]
default-character-set = utf8mb4

[mysqld]
# Set Innodb_buffer_pool_size = 80% of total memory - 2gb (mmaps reserved)
# Set innodb_buffer_pool_instances = 1gb x buffer_pool_size per pool_instance (2048M = 2)
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 2
# Max_connections not to be set above ~1000
max_connections = 300
# Allocate startup memory per connection (300 x 2M)
join_buffer_size = 2M
# Allow input from large worlddb .sql files
max_allowed_packet = 512M

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# Set skip_log_bin=0 if database replication is required, lowers performance
skip_log_bin=1
sync_binlog = 1
binlog_format = MIXED
innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=1
transaction-isolation = READ-COMMITTED
# Flush log after each transaction, performance cost over trx_commit = 2 but protects db in case of mysql crash
innodb_flush_log_at_trx_commit = 1

# Obtain optimal settings from mysql tuning script and further experimenting
innodb_log_buffer_size = 64M
innodb_max_dirty_pages_pct = 95
query_cache_type = 1
query_cache_limit = 8M
query_cache_min_res_unit = 4k
query_cache_size = 64M
tmp_table_size= 64M
max_heap_table_size= 64M
key_buffer_size = 128M
thread_cache_size = 20

innodb_log_file_size = 512M
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
long_query_time = 1
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

The Mysql service should be restarted after running the script to activate the new settings.

These values are primarily obtained from running optimizations on a Nextcloud instance which requires high-speed read/write syncs to the database from many users, and also the MySQLTuner and Tuning Primer scripts. Many of the settings are recommended and provided with further details in the following TecMint guide. More ideal values may be obtained from running these scripts on the database for a particular cMaNGOS server.

Avatar

Related

Previous