MySQL causing lags

[Nasdero]

Hello Folks,

 

I need really your help, we tried a lot of things, it is not like that, that I installed our server and ask directly for help, but now I need you help.

Server is running well when we got up to ~14 players, when we got 15+ player, the FPS starting to decrease constantly, also when the number of players decrease. What I saw is, every x minutes, the load on the 2 core where Dayz is running are droping to nerly zero and the load on the other cores are getting higher, at the same time I can see in the task manager, that mysql getting load from about nothing to a high load, at the same time we have only ~3FPS and massive desync.

I did search weeks now to solve the problem, it was not so acute because the server was not so popular, but now we are getting mor player every day, we started the whitelist to slow down the increasing of player number :(.

Here is one of our basic.cfg , we tryed really a lot of different setting:

 

our basic.cfg:

 

language="English";
MinBandwidth=104857600;
MaxBandwidth=1073741824;
MaxMsgSend=256;
MaxSizeNonguaranteed=256;
MinErrorToSendNear=0.029999999;
MinErrorToSend=0.003;
MaxCustomFileSize=0;
Windowed=0;
adapter=-1;
3D_Performance=1;
Resolution_Bpp=32;
class sockets
{
    maxPacketSize=1400;
};
serverLongitude=9;
serverLatitude=51;
serverLongitudeAuto=9;
serverLatitudeAuto=51;

 

First we tried xamp, 2 days ago we installed mysql by hand, here the last my.ini:

 

 

[client]
no-beep
port=3306
default-character-set=utf8
[mysqld]
port=3306
datadir="C:/MySQL/MySQL Server 5.7/data\"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="PRO2153.log"
slow-query-log=0
slow_query_log_file="PRO2153-slow.log"
long_query_time=10
log-error="PRO2153.err"
max_connections=100
query_cache_size=0
table_open_cache=2000
tmp_table_size=60M
thread_cache_size=9
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=119M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=9M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=5M
innodb_buffer_pool_size=369M
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64M
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=70
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4110
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000

 

Our Hardware:

 

 

• HP ProLiant DL 320e
• Intel® Xeon® E3-1230 v2, Quad-Core (4x 3,3 GHz)
• 16 GB DDR3-RAM
• 2x 2.000 GB SATA II-HDD; 7,2k

   

The Network is 100Mbit, we got with 20-25 ~5Mbit on it.
 

Our start parameter

 

-cpuCount=2 -maxmem=2047 -exThreads=1 -noCB -port=2315 "-config=instance_13_Tavi\config.cfg" "-cfg=instance_13_Tavi\basic.cfg" "-profiles=instance_13_Tavi" -name=instance_13_Tavi "-mod=@tavi_DayZ_Epoch;@DayZ_Epoch;@DayZ_Epoch_Server;"

 

We are running Tavian 2.0, the most folder "ArmA 2 OA" are on a ramdrive, so that the HD can not be an issue on that side. I can see that the HD where mysql is running, is on a little load and the disk queue is ~0.00-0.05 , that is not really much.  Right now I could see it again, we have now 4 player, Arma is running on CPU0 and CPU2, you can see the load on the picture, at the red line mysql getting load and Arma going down.

I need really your help, that is driving me crasy!

Kind regads,

Nasdero

[BB-MaTriX]

Please help!

its unplayable!

[Torndeco]

I dont get people putting servers on a ramdrive...

Most / all the server once loaded will be in ram anyways or as files cached in ram not beening used by the OS

 

Anyway i would assume its your basic.cfg settings.

Extra player load & ai on server = to much info for server to send that is allowed, resulting in desync.

Also don't forget 1 player with bad bandwidth like sub 70 can cause desyncs aswell

 

Whats your rpt logs look like when it happens ?

messages not send ?

 

Your basic.cfg is setup slighty wrong imo

U told your server that

min bandwidth = 100mb

max bandwidth = 1gb

I would alter them to be correct when u get a chance

 

Also i would try increase

MaxMsgSend=1024;
MaxSizeGuaranteed=1024;

Is what i am using but got a larger net connection, so it will prob require some trial and error to get right.

 

There is a post somewhere to work out the maths for a rough idea of bandwidth usage, but getting late atm and got work in the morning

[Nasdero]

Yo thx for your answer, yeap ur right, the values for MinBandwidth=104857600; & MaxBandwidth=1073741824; are to high, I guit trying end of last week and Matrix tried it. The ramdrive was just another testing, to be sure it is not the HD, I moved it back allready this morning to the HD before I did read this here.

 

In the logs are messages like "message pending", not sure if it is on the same time, but i do think so.

I hope we can get some more help. What you guys tweak in your my.ini ?

[BB-MaTriX]

We turned off all ai! Still desync!

Yesterday i installed the mySQL workbench.

Everytime "queries per second" is >0" we have desync!

[Nasdero]

language="English";

MinBandwidth=10240000;

MaxBandwidth=20480000;

MaxMsgSend=1024;

MaxSizeGuranteed=1024;

MaxSizeNonguaranteed=512;

MinErrorToSendNear=0.029999999;

MinErrorToSend=0.003;

MaxCustomFileSize=0;

Windowed=0;

adapter=-1;

3D_Performance=1;

Resolution_Bpp=32;

class sockets

{

    maxPacketSize=1400;

};

serverLongitude=9;

serverLatitude=51;

serverLongitudeAuto=9;

serverLatitudeAuto=51;

 

I tried this one today, 8 Players ~40FPS, ~25second same like in screenshoot above, 2 cores with Arma going down, MySql taking load :(. Bandwidth caclulated for 40 players. I tried a lot of different values.

Message pending: Not found right now, but only 10 players right now, will keep an eye on this.

Edited November 25, 2013 by Nasdero

[Axe Cop]

You should try it with a vanilla Epoch server and some players if that is possible, maybe there is major bug in one of your mods/scripts?

Also I'am using most default settings and it's running fine, MySQL everthing default (MySQL Server 5.6 x64 on Windows Server 2008 R2) and my basic.cfg just looks like this:

language="English";
adapter=-1;
3D_Performance=48387;
Resolution_Bpp=32;
serverLongitude=8;
serverLatitude=52;
serverLongitudeAuto=8;
serverLatitudeAuto=52;
Windowed=0;

My start.bat:

start "arma2" /min "Expansion\beta\arma2oaserver.exe" -port=2302 "-config=instance_11_Chernarus\config.cfg" "-cfg=instance_11_Chernarus\basic.cfg" "-profiles=instance_11_Chernarus" -name=dayz_epoch "-mod=@DayZ_Epoch;@DayZ_Epoch_Server;" -cpucount=4 -exthreads=4

maybe try setting more cores or just use the default values and don't set anything what is not needed! most of the time that is better than setting "worng" values..

[Nasdero]

Thx, I tried -cpucount=4 also -cpucount=2 , but -exthreads=4 can't be right "Change to a number 0,1,3,5,7. This will override auto detection (which use 3 for dualcore and 7 for quadcore)" that ist from Bohemia, for server it should be 1.
I tried also without all addons with ~10 Player, but our prob starts at ~15 Player , also without the other startup parameters, but I'll try again, we need this server running well. I've had also the same basig.cfg like you, that is what's driving me crazy.

I can see that there is something going on with da database, I increased now the innodb_buffer_pool_size from 16M to 512M, we will see what will happen.

[Axe Cop]

Thx, I tried -cpucount=4 also -cpucount=2 , but -exthreads=4 can't be right "Change to a number 0,1,3,5,7. This will override auto detection (which use 3 for dualcore and 7 for quadcore)" that ist from Bohemia, for server it should be 1.

Ok weird, I copied that settings form my first hosted server (HFB) and using it since lol

I checked the cpu performance of my MySQL server and it's not doing much as expected, average 0.01% CPU (with 37 threads) over ~10 min (with 25 players currently active)

arma2aoserver is at 30% most of the time with my old AMD phenom II X4 965.. mmmh

 

Do you have anything else on the MySQL server or just one Epoch instance? I just have my whitelist on there but that's not doing much ^^

 

Edit: just checked my MySQL server with 45+ active players, average CPU utilization is around 0.1 % (avg. 16 InnoDB writes per second with 7% buffer usage with 604MB as the buffer size, there is not much reading only if a player logs in or access a trader or something) ...

Edited November 25, 2013 by Axe Cop

[BB-MaTriX]

Hi Axe,

 

we use MySQL only for Epoch!

 

Kind Regards

[BB-MaTriX]

hi again,

 

We reseted the database, after 1,5 hour we have: 20-25 players ~23 FPS and no FPS drops ATM!

 

Kind Regards

[Axe Cop]

How big was your database or was there a bug in the data maybe? If you know that :D

[Nasdero]

only bit more then 2MB, can it be that there was a bug causing by hackers?

[Radiix]

The mentioned FPS drop on the clients is caused by too many objects in the database.

 

We had like 8000 objects in "object_data" and i had 20fps max in primetime on the server. Make sure you have mysql cleanup events running!

[Nasdero]

We do run cleanups every server restart.

[Axe Cop]

We do run cleanups every server restart.

Great our server just reached 15k objects, with cleanups haha, damn base building should be restricted... lol

[Nasdero]

I checked the old logfile: 12:02:25 "HIVE: found 2904 objects"

 

Not so many objects...

 

What I saw 2 days ago:

12:02:25 "HIVE: Commence Object Streaming..."
12:02:25 Error in expression <1,1,7,1,4,13,3,1,4,2,2]],[[],[]]],[],0.0>
12:02:25   Error position: <>
12:02:25   Error Missing ]
12:02:25 Error in expression <1,1,7,1,4,13,3,1,4,2,2]],[[],[]]],[],0.0>
12:02:25   Error position: <>
12:02:25   Error Missing ]
12:02:25 Error in expression <ray = call compile format ["%1",_data];
_resultArray
};

server_characterSync = >
12:02:25   Error position: <_resultArray
};

server_characterSync = >
12:02:25   Error Undefined variable in expression: _resultarray
12:02:25 File z\addons\dayz_server\init\server_functions.sqf, line 131
12:02:25 Error in expression <iveReadWrite;
_objectArray set [_i - 1, _hiveResponse];

};
diag_log ("HIVE: got>
12:02:25   Error position: <_hiveResponse];

};
diag_log ("HIVE: got>
12:02:25   Error Undefined variable in expression: _hiveresponse
12:02:25 File z\addons\dayz_server\system\server_monitor.sqf, line 122
12:02:25 "DEBUG FPS  : 7.31596"
12:02:26 "HIVE: got 2904 objects"
12:02:28 Error in expression <
}
};   
if (!_wsDone) then {
if (count _worldspace >= 1) then { _dir = _worldsp>
12:02:28   Error position: <_worldspace >= 1) then { _dir = _worldsp>
12:02:28   Error Undefined variable in expression: _worldspace
12:02:28 File z\addons\dayz_server\system\server_monitor.sqf, line 157
12:02:28 Error in expression <0;
{

_countr = _countr + 1;

_idKey =  _x select 1;
_type =  _x select 2;
_owne>
12:02:28   Error position: <_x select 1;
_type =  _x select 2;
_owne>
12:02:28   Error Undefined variable in expression: _x
12:02:28 File z\addons\dayz_server\system\server_monitor.sqf, line 135

 

We did not change any file, one log before no Error, but we have had the lags/desync before this Error started, so there was for sure something wrong in our database.
Yesterday with 30 players (we limited it to 30) after ~4h 12-15FPS, no lags or desync, I would expect more FPS, but now we can start to optimize.

How many FPS do you have round about Axe? We do not have any additional startup parameter, no "exthreads" no "cpucount", I will add them today, step by step.

 

Thx for helping and sharing your knowledge,

Nasdero

[Axe Cop]

see here http://dayzepoch.com/forum/index.php?/topic/1992-mission-system-sarge-ai-wip/?p=25895 :D

[Nasdero]

We have had after about 2h and 30 players ~17FPS, I increase the max player to 35 and we will see what will happen. I don't need 50+ player on Tavian.

 

We got all scripts on, except the missions, one mission took about 3fps, another 10fps, but with a lot of errors, we will rework them if we got time.

[BB-MaTriX]

Now, massiv desync and lags are back in business :wacko:  round about 4500 objects

 

 

unplayable :angry:

[Guest]

i have all my dayz databases use MyISAM instead of InnoDB an charset utf8, because InnoDB uses more processing perfomance ...

ALTER TABLE `Character_DATA` ENGINE=MyISAM;
ALTER TABLE `Object_DATA` ENGINE=MyISAM;
ALTER TABLE `Player_DATA` ENGINE=MyISAM;
ALTER TABLE `Player_LOGIN` ENGINE=MyISAM;
ALTER TABLE `server_traders` ENGINE=MyISAM;
ALTER TABLE `trader_items` ENGINE=MyISAM;
ALTER TABLE `trader_tids` ENGINE=MyISAM;
ALTER TABLE `Traders_DATA` ENGINE=MyISAM;

ALTER TABLE `Character_DATA` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `Object_DATA` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `Player_DATA` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `Player_LOGIN` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `server_traders` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `trader_items` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `trader_tids` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;
ALTER TABLE `Traders_DATA` COLLATE='utf8_general_ci', CONVERT TO CHARSET utf8;

Please backup your databse first, i dont wanna be responsible for anything that could probably not work (allthough i dont have any problems with it)

[Radiix]

"DEBUG FPS : 11.23732 OBJECTS: 5064 : PLAYERS: 50"

[Axe Cop]

i have all my dayz databases use MyISAM instead of InnoDB an charset utf8, because InnoDB uses more processing perfomance ...

you need to be careful when changing the database engine, since DayZ might execute many database queries at the same time MyISAm doen't support multiple access as far as i know it will lock the table for each single query, so you can't run 2 queries at the same time on the same table (like writing 2 character updates at the same time, the database will then wait for the first query to complete, with InnoDB the queries run in parallel).

just some background info, since the queries itself should be very fast it might not matter that much ^^

[Sukkaed]

"DEBUG FPS : 11.23732 OBJECTS: 5064 : PLAYERS: 50"

Well, heres my awesome FPS. I guess that I have to start removing some objects. This is unbelievable shit.

"DEBUG FPS : 0.900445 OBJECTS: 11504 : PLAYERS: 50"

[Nasdero]

We will wipe this friday and we will massive restrict the building ( DZE_BuildingLimit = 100), we will remove the plotpole from the trader, we will distribute them buy hand, we added the Adminbase Script and the player will get a Clanbase, so they do not need that mach buildings to secure their base, our players will get their safe incl. the content back and a start up package.
We limited our Server to max 30 players, we got now round about 3500 objects in the DB, when the game is running for a while, we got twice or more objects, the FPS are not that bad, bud every x minutes we got again massive desyncs. Right now I got 19FPS on 1 player, yesterday I have had ~47FPS with a couple player and ~10 with 25 player, very strange things happening.

 

If this will not help, I will delete epoch from my server for sure!

i don't think that is a problem with the speed of mysql, I tried the DB in a ramdrive, that was damn fast and have had the same problems.

 

EDIT: 9:25:33 "DEBUG FPS : 18.8014 OBJECTS: 4186 : PLAYERS: 1"

[Axe Cop]

I've tested it on my local test server and get about 50 FPS with a clean database (0 objects) and only me as a player..

after importing my original database i get about 20 FPS with 15000 objects.. but it decreases quickly with more player so how do you guys run your server or do you just need a massive CPU for this thing to run somehow? lol