Best settings for MySQL.
- Thread starter neravia
- Start date
potinho
Advanced OT User
Dont know if the best option (everyone can show opinion to us), but im using:
XML:
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
innodb_buffer_pool_size = 512M
innodb_buffer_pool_instances = 4
innodb_log_file_size = 128M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
# * Fine Tuning
#
key_buffer_size = 64M
max_allowed_packet = 64M
thread_stack = 1M
thread_cache_size = 128
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
#max_connections = 300
#table_open_cache = 1000
max_heap_table_size = 64M
tmp_table_size = 64M
bulk_insert_buffer_size= 64M
Main goal for OTS MySQL database config is to store all data in RAM, calculate all queries results in RAM and never access HDD (even SSD) as it's much slower.
Every slowdown of database is lag for all players online! Save player items query takes 0.1 sec to execute? It's 0.1 sec freez on server!
@potinho config looks great for small servers with 2-4 GB RAM.
Add - im not sure, but it may be possible to do this change only before you create database (import TFS schema):
In some cases disabling query cache makes MySQL work faster:
It should work on TFS, as it very rare loads same data from database, so queries never hit cache.
For 16+ GB RAM server, you can upgrade some values from @potinho post to:
Again, modifying
Every slowdown of database is lag for all players online! Save player items query takes 0.1 sec to execute? It's 0.1 sec freez on server!
@potinho config looks great for small servers with 2-4 GB RAM.
Add - im not sure, but it may be possible to do this change only before you create database (import TFS schema):
Code:
innodb_file_per_table = 1In some cases disabling query cache makes MySQL work faster:
Code:
query_cache_limit = 0M
query_cache_size = 0MFor 16+ GB RAM server, you can upgrade some values from @potinho post to:
Code:
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
key_buffer_size = 512M
max_allowed_packet = 512M
max_heap_table_size = 1G
tmp_table_size = 1G
bulk_insert_buffer_size = 1Ginnodb_ parameters after importing TFS schema may make MySQL fail to restart. I always configure MySQL first, then install OTS and import database.
slaw
Software Developer
There is also a useful script for adjusting mysql configuration for better performance:
github.com
GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.
MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - GitHub - major/MySQLTuner-perl: MySQLTu...
github.com
Similar threads
