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:
On Red Hat systems and others your my.cnf may also be located at:
[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.