SQL Server and Optimization

[Mystykall]

So I've been noticing "lag" and holdups coming from my SQL database. The database is hosted on the local machine and connecting via localhost and a custom user (not root). 

 

What ends up happening eventually is the server is unable to pull information from the SQL server in a decent amount of time and times out. Safes unlock slow, character information retrieval is very slow and often times unsuccessful. 

 

The SQL server is set up as a service that starts with the computer, and continues to run. Epoch Server, and BEC are both run as services through the machine as well, BEC and Epoch tasks are both killed and restarted every 3 hours. 

 

I've been trying to find "Good" settings for the my.ini, but it is not a well documented part of Epoch. I have attached my.ini and basic.cfg below.

[client]

port=XXXX

[mysql]

default-character-set=UTF8


[mysqld]

event_scheduler=on

# The TCP/IP Port the MySQL Server will listen on
port=XXXX


#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=UTF8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=100
query_cache_size=0

# section [mysqld_safe]
table_cache=256
tmp_table_size=35M
thread_cache_size=8

#*** MyISAM Specific options

myisam_max_sort_file_size=100G
myisam_sort_buffer_size=128M
key_buffer_size=64M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
max_allowed_packet = 4M
net_buffer_length = 8K

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=2048M
innodb_log_file_size = 512M
innodb_thread_concurrency=18
innodb_fast_shutdown = 1
innodb_lock_wait_timeout = 50

MinBandwidth=1030798;
MaxBandwidth=1073741824;
MaxMsgSend=192;
MaxSizeGuaranteed=512;
MaxSizeNonguaranteed=156;
MinErrorToSendNear=0.04999999;
MinErrorToSend=0.0039999988;
MaxCustomFileSize=0;
Windowed=0;
adapter=-1;
3D_Performance=1;
Resolution_W=800;
Resolution_H=600;
Resolution_Bpp=32;
serverLongitude=-79;
serverLatitude=43;
serverLongitudeAuto=-79;
serverLatitudeAuto=43;

[Neversober]

I have also been dealing tihe "lag" and "holdups" that I am sure are being caused by DB queries.

I would also love to have an answer to this.

[Defent]

I decided to tweak some of the settings aswell so here is what I did:

I changed so that the servers all run on independant mysql accounts and I doubled some of the buffer values and packets being allowed to my server.

As seen here:

# The MySQL server
[mysqld]
port= porthere
socket = "C:/Server/xamp/mysql/mysql.sock"
basedir = "C:/Server/xamp/mysql" 
tmpdir = "C:/Server/xamp/tmp" 
datadir = "C:/Server/xamp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 4M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 234M
log_error = "mysql_error.log"
event_scheduler=on

# Change here for bind listening
# bind-address="127.0.0.1" 
# bind-address = ::1          # for ipv6

# Where do all the plugins live
plugin_dir = "C:/Server/xamp/mysql/lib/plugin/" 

#skip-networking
skip-federated

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = "C:/Server/xamp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/Server/xamp/mysql/data"
#innodb_log_arch_dir = "C:/Server/xamp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

## UTF 8 Settings
#init-connect=\'SET NAMES utf8\'
#collation_server=utf8_unicode_ci
#character_set_server=utf8
#skip-character-set-client-handshake
#character_sets-dir="C:/Server/xamp/mysql/share/charsets"

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

I dont use Innodb so I only tweaked the mysql variables. I believe I have recived some performance increase.