MySQL causing lags

Nasdero · November 24, 2013

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

Razorman · December 17, 2013

Is this being addressed/recognised by the devs? My server has started suffering from massive desyncs since 1.0.3 & now the same with 1.0.3.1, 15k objects in DB, a lot granted but the same as before these 2 versions?

MGT · December 17, 2013

I've found its better to keep under 8-9k items on the db for the best balance between server fps, desync and player experience, I do this with various time based sql clearup/unlock procs.

Axe Cop · December 18, 2013

You are right but i just dont understand why the server is suffering so much from a high object count? Why has the server to check all the object every second and not only the new and changed ones..stupid arma :p

Nasdero · December 18, 2013

I really don't understand what is going on, two days ago, I have had ~45FPS with a couple of players, we made no change, Yesterday 1 player ~16FPS from serverstart, after every regularly restart the same. Today 1 player, ~45FPS ~4000objects, again we did not change anything!

I guess we will go back to 1.0.3 this week and like I wrote yesterday, we will do a wipe on friday, I started yesterday do build some castle for our clans, but if nothing happens from the devs side, I will quit playing Epoch in near future!

@MGT: I wrote you a message in opendayz.net because, here you can not receive privat messages, could you please read it there?

Razorman · December 18, 2013

Care to share? those .sql's MGT?

MGT · December 18, 2013

As long as you have the "lastactive" column installed, this coupled with a 14 day maintenance cycle will keep objects below 8000 on a populated server

These are all procedures called via pMain in my restarter batch file like this

.\SQL\bin\mysql.exe --user=**** --password=**** --host=127.0.0.1 --port=**** --database=dayz_epoch --execute="call pMain()"

Create a procedure called pMain like this

BEGIN
	CALL Cleanup;
	CALL Refill;
        CALL damage;
        CALL veh;
        CALL veh2;
        CALL veh3;               
	CALL unlockvaults;
	CALL deletestorage;
	CALL removedead;
        CALL unlockvehicles;
        CALL emptystorage;	
END

Cleanup

DELETE
    FROM object_data
    WHERE Damage = '1'

Refill

UPDATE `traders_data` SET qty=100 WHERE  qty<10

Damage

UPDATE `object_data` SET `Damage`=0.2 WHERE `ObjectUID` <> 0 AND `CharacterID` <> 0 AND `lastactive` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY) AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') )

veh

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE object_data.lastactive < NOW() - INTERVAL 1 DAY
AND Hitpoints LIKE '%wheel_2_1_steering",1%'
AND Hitpoints LIKE '%wheel_2_2_steering",1%'
OR Hitpoints LIKE '%motor",1%'
OR Hitpoints LIKE '%karoserie",1%'
OR Hitpoints LIKE '%Pravy zadni tlumic",1%'
OR Hitpoints LIKE '%engine",1%';
END

veh2

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE object_data.lastactive < NOW() - INTERVAL 1 DAY
AND Hitpoints LIKE '%wheel_1_1_steering",1%'
AND Hitpoints LIKE '%wheel_1_2_steering",1%';
END

veh3

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE object_data.lastactive < NOW() - INTERVAL 1 DAY
AND Hitpoints LIKE '%glass1",1%'
AND Hitpoints LIKE '%glass2",1%'
AND Hitpoints LIKE '%glass3",1%'
AND Hitpoints LIKE '%glass4",1%';
END

unlockvaults

UPDATE `object_data` SET `CharacterID` = 0 WHERE `lastactive` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 10 DAY) AND `CharacterID` > 0 AND `Classname` NOT IN ('VaultStorage')  AND `Inventory` <> '[]' AND `Inventory` IS NOT NULL

deletestorage

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE Classname LIKE '%Storage",1%'
OR Classname LIKE '%House",1%'
OR Classname LIKE '%Shed",1%'
OR Classname LIKE '%Shack",1%'
AND object_data.lastactive < NOW() - INTERVAL 14 DAY;

END

removedead

delete FROM `character_data` USING character_data, character_data AS tmpcharacter_data WHERE NOT character_data.CharacterID=tmpcharacter_data.CharacterID AND character_data.CharacterID<tmpcharacter_data.CharacterID AND (character_data.PlayerUID=tmpcharacter_data.PlayerUID)

unlockvehicles

UPDATE `object_data` SET `CharacterID` = 0 WHERE `lastactive` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY) AND `CharacterID` > 0 AND `Classname` NOT IN ('VaultStorage','LockboxStorage','VaultStorageLocked','LockboxStorageLocked')  AND `Inventory` <> '[]' AND `Inventory` IS NOT NULL

emptystorage

BEGIN
DELETE FROM dayz_epoch.object_data
WHERE `lastactive` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) 
AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) 
AND ( (`Inventory` IS NULL) 
AND Classname LIKE '%TentStorage",1%'
AND (`Inventory` = '[]') 
OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') );

END

Radiix · December 18, 2013

Thank you very much MGT. Awesome!

Nasdero · December 18, 2013

Yeap thank you for sharing, I do not really need one of this, but it is nice that ppl share their knowledge.

Razorman · December 18, 2013

Gracias Amigo!

Nasdero · December 24, 2013

So what I found out, one main problem do we get cause of the Bell Chopper, one player and the copper spawned we have had 15-16FPS, no chopper spawned 48FPS, since we removed this Bells, we have ~48 until we get more then ~16 players, then the FPS slowly decreasing. We wiped last friday and restricted modular buildings to 100 and the server is running well, I placed ~11 castle and ~13 towers on the map, which our player can use for theire base.

kengglol · January 6, 2014

So what I found out, one main problem do we get cause of the Bell Chopper, one player and the copper spawned we have had 15-16FPS, no chopper spawned 48FPS, since we removed this Bells, we have ~48 until we get more then ~16 players, then the FPS slowly decreasing. We wiped last friday and restricted modular buildings to 100 and the server is running well, I placed ~11 castle and ~13 towers on the map, which our player can use for theire base.

Same problem with us! Server FPS at 1-15 players = 12-15 FPS

After delete the UH-13 amphibious from database immediately 47+ FPS

Thanks to Nasdero for the tipp

January 9, 2014

MGT this is exactly what I needed! I'm on DayZ.st and ran:

delete FROM `Character_DATA` USING Character_DATA, Character_DATA AS tmpcharacter_data WHERE NOT Character_DATA.CharacterID=tmpcharacter_data.CharacterID AND Character_DATA.CharacterID<tmpcharacter_data.CharacterID AND (Character_DATA.PlayerUID=tmpcharacter_data.PlayerUID)

it deleted 1960 entries! (ugh)

I love the idea of unlocking cars and safes after a certain time period. But how does this work? Would it run every restart? Also, like many people renting servers, I don't think I have access to run batch files, is there a way to run these every restart without that? Dayz.st uses phpMyAdmin.

Thanks!

Adminlaststand · January 13, 2014

So what I found out, one main problem do we get cause of the Bell Chopper, one player and the copper spawned we have had 15-16FPS, no chopper spawned 48FPS, since we removed this Bells, we have ~48 until we get more then ~16 players, then the FPS slowly decreasing. We wiped last friday and restricted modular buildings to 100 and the server is running well, I placed ~11 castle and ~13 towers on the map, which our player can use for theire base.

Same problem with us! Server FPS at 1-15 players = 12-15 FPS

After delete the UH-13 amphibious from database immediately 47+ FPS

Thanks to Nasdero for the tipp

OMG GUYS u nailed it!!

I've just removed the bell from 2 servers in both cases the fps went back up from 8-15 to 45+

I suggest other people give this a go

Cheers

MySQL causing lags

Question

Link to comment

Share on other sites

38 answers to this question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Please sign in to comment