Issues with to many vehicles on map

[Link]

So i take a look at my map and see way too many unlocked vehicles on the map, i'm pretty sure this is causing huge FPS drop in my server and clearly I need to implement an event in place to clear some of these after a certain amount of days. Does anybody have any recommended database events that I could use to reduce server FPS for dealing with vehicles and objects? Thanks

[Ped]

I use this:

DELETE FROM `object_data` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 DAY) AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )

It deletes practically anything which hasn't been touched for the specific interval and has no inventory. You can change the interval days as well as remove the empty inventory requirement if you wish.

[Defent]

I run this every restart, deletes cars that have no keys. People tend to buy ATVs and then die and leave it there. Deletes the vehicles if key is not stored 

BEGIN
	DELETE FROM
		`Object_DATA`
	WHERE
		`Object_DATA`.`CharacterID` <> 0
		AND `Object_DATA`.`CharacterID` <= 12500
		AND `Object_DATA`.`Classname` NOT LIKE 'Tent%'
		AND `Object_DATA`.`Classname` NOT LIKE '%Locked'
		AND `Object_DATA`.`Classname` NOT LIKE 'Land%'
		AND `Object_DATA`.`Classname` NOT LIKE 'Cinder%'
		AND `Object_DATA`.`Classname` NOT LIKE 'Wood%'
		AND `Object_DATA`.`Classname` NOT LIKE 'Metal%'
		AND `Object_DATA`.`Classname` NOT LIKE '%Storage%'
		AND `Object_DATA`.`Classname` NOT IN ('OutHouse_DZ', 'GunRack_DZ', 'WorkBench_DZ', 'Sandbag1_DZ', 'FireBarrel_DZ', 'DesertCamoNet_DZ', 'StickFence_DZ', 'LightPole_DZ', 'DeerStand_DZ', 'ForestLargeCamoNet_DZ', 'Plastic_Pole_EP1_DZ', 'Hedgehog_DZ', 'FuelPump_DZ', 'Fort_RazorWire', 'SandNest_DZ', 'ForestCamoNet_DZ', 'Fence_corrugated_DZ', 'CanvasHut_DZ', 'Generator_DZ')
		AND FindVehicleKeysCount(Object_DATA.CharacterID) = 0;RETURN ROW_COUNT();END

[Link]

Thanks guys for the responses, i'll take a look at these and try them out, thanks.

[iBlLaclK]

If you have still to mutch Vehicles set your MaxVehicleLimit Amount to a lower amount. Im running Napf and my MaxVehicleLimit is on 150.

[Link]

Okay so this is what I have setup inside of my dayz_overpoch database, i'm not sure if what I have setup in these routines/events are correct so if somebody could check them over for me that would be a huge help;

 

DeleteNonKeyVehicles (Routine)

BEGIN
	DELETE FROM
		`Object_DATA`
	WHERE
		`Object_DATA`.`CharacterID` <> 0
		AND `Object_DATA`.`CharacterID` <= 12500
		AND `Object_DATA`.`Classname` NOT LIKE 'Tent%'
		AND `Object_DATA`.`Classname` NOT LIKE '%Locked'
		AND `Object_DATA`.`Classname` NOT LIKE 'Land%'
		AND `Object_DATA`.`Classname` NOT LIKE 'Cinder%'
		AND `Object_DATA`.`Classname` NOT LIKE 'Wood%'
		AND `Object_DATA`.`Classname` NOT LIKE 'Metal%'
		AND `Object_DATA`.`Classname` NOT LIKE '%Storage%'
		AND `Object_DATA`.`Classname` NOT IN ('OutHouse_DZ', 'GunRack_DZ', 'WorkBench_DZ', 'Sandbag1_DZ', 'FireBarrel_DZ', 'DesertCamoNet_DZ', 'StickFence_DZ', 'LightPole_DZ', 'DeerStand_DZ', 'ForestLargeCamoNet_DZ', 'Plastic_Pole_EP1_DZ', 'Hedgehog_DZ', 'FuelPump_DZ', 'Fort_RazorWire', 'SandNest_DZ', 'ForestCamoNet_DZ', 'Fence_corrugated_DZ', 'CanvasHut_DZ', 'Generator_DZ')
		AND FindVehicleKeysCount(Object_DATA.CharacterID) = 0;RETURN ROW_COUNT();END

FindVehicleKeysCount (Routine)

BEGIN
    DECLARE totalKeys INT DEFAULT 0;
    DECLARE keyName VARCHAR(32) DEFAULT "";
    DECLARE keysInChar INT DEFAULT 0;
    DECLARE keysInObj INT DEFAULT 0;

    SET keyName = (CASE
        WHEN `keyId` < 2501 THEN CONCAT('ItemKeyGreen', `keyId`)
        WHEN `keyId` < 5001 THEN CONCAT('ItemKeyRed', `keyId` - 2500)
        WHEN `keyId` < 7501 THEN CONCAT('ItemKeyBlue', `keyId` - 5000)
        WHEN `keyId` < 10001 THEN CONCAT('ItemKeyYellow', `keyId` - 7500)
        WHEN `keyId` < 12501 THEN CONCAT('ItemKeyBlack', `keyId` - 10000)
        ELSE 'ERROR'
    END);

    SET keysInChar = (SELECT COUNT(*) FROM `Character_DATA` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', keyName, '%') OR `Backpack` LIKE CONCAT('%', keyName, '%')));
    SET keysInObj = (SELECT COUNT(*) FROM `Object_DATA` WHERE `Inventory` LIKE CONCAT('%', keyName, '%'));

    RETURN (keysInChar + keysInObj);
END

removeDamagedVehicles (Event - Every 2 hours)

DELETE FROM `Object_DATA` WHERE Damage = 1

removeObjectEmpty (Event - Every 1 day)

DELETE FROM `Object_DATA` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 14 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 DAY) AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') OR (`Inventory` = '[[[],[]],[[],[]],[[],[]]]') )

removeObjectOld (Event - Every 1 day)

DELETE FROM `Object_DATA` WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 DAY) AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 42 DAY)

setDamageOnAge (Event - Every 1 day)

UPDATE `Object_DATA` SET `Damage`=0.1 WHERE `ObjectUID` <> 0 AND `CharacterID` <> 0 AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') )

UnlockNonKeyVehicles (Event - Every 1 day)

DROP EVENT IF EXISTS `UnlockNonKeyVehicles`;

updateStockDaily (Event - every 2 hours)

UPDATE `Traders_DATA` SET qty=500 WHERE qty=0 AND afile<>'trade_any_vehicle' AND afile<>'trade_any_boat'

Basically I don't want the server to suffer from fps drop because of high amounts of vehicles left on the map, but at the same time I only want locked vehicles to be removed if they havent been accessed/used for 7 days. I have also lowered my vehicle limit from 500 to 150. Does this SQL for these events/routines look right?

[Zombified]

Shouldn't the FindVehicleKeysCount include searches in the inventory of lockboxes and safes? 

 

Please let me know the success you're having with this. I'd love to use this. 

[tonk]

Code:
  DELETE FROM Object_DATA
WHERE Object_DATA.LastUpdated < NOW() - INTERVAL 7 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%';

Is your host GTX? SEEMS THE EVENTS DATABASE, doesn't work with gtx they are working on a solution. harkness a staff member posted this on their forums

[Link]

Code:
  DELETE FROM Object_DATA
WHERE Object_DATA.LastUpdated < NOW() - INTERVAL 7 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%';

Is your host GTX? SEEMS THE EVENTS DATABASE, doesn't work with gtx they are working on a solution. harkness a staff member posted this on their forums

 

Im with Vilayer

[syco]

You do know with vilayer you can run the Add recommended events in the overpoch manger. It will add all of these events to your database for you and you can alter them as you want. This way you know they are setup correct.

Add Recommended Events:  This adds common scheduled events directly to your database. These include:
•FindVehicleKeysCount
•DeleteNonKeyVehicles
•removeDamagedVehicles
•removeObjectEmpty
•removeObjectOld
•setDamageOnAge
•updateStockDaily
•UnlockNonKeyVehicles

[Zombified]

DUH! After reading that a little closer, you cover the lockboxes and safes in the "Object_DATA.Inventory".

 

Question about the Character_DATA.Alive=1...

 

If this is executed between server reboot and the player is running back to their dead body to collect keys. After server reboot their vehicle will be gone, even tho they have the key, no?  It's a timing issue, right?

 

Is there away to tie the procedure to server reboot?

 

Also, I'm running on DayZ.St and I'm rebooting every 3hrs (performance issues with all the DZMS/DZAI). I'm running "events" for different cleanups and I'm looking to run this:

 

 

delimiter |
CREATE EVENT vehicleCleanup
    ON SCHEDULE
      EVERY 3 HOUR
 BEGIN
  DELETE FROM
   `Object_DATA`
  WHERE
   `Object_DATA`.`CharacterID` <> 0
   AND `Object_DATA`.`CharacterID` <= 12500
   AND `Object_DATA`.`Classname` NOT LIKE 'Tent%'
   AND `Object_DATA`.`Classname` NOT LIKE '%Locked'
   AND `Object_DATA`.`Classname` NOT LIKE 'Land%'
   AND `Object_DATA`.`Classname` NOT LIKE 'Cinder%'
   AND `Object_DATA`.`Classname` NOT LIKE 'Wood%'
   AND `Object_DATA`.`Classname` NOT LIKE 'Metal%'
   AND `Object_DATA`.`Classname` NOT LIKE '%Storage%'
   AND `Object_DATA`.`Classname` NOT IN ('OutHouse_DZ', 'GunRack_DZ', 'WorkBench_DZ', 'Sandbag1_DZ', 'FireBarrel_DZ', 'DesertCamoNet_DZ', 'StickFence_DZ', 'LightPole_DZ', 'DeerStand_DZ', 'ForestLargeCamoNet_DZ', 'Plastic_Pole_EP1_DZ', 'Hedgehog_DZ', 'FuelPump_DZ', 'Fort_RazorWire', 'SandNest_DZ', 'ForestCamoNet_DZ', 'Fence_corrugated_DZ', 'CanvasHut_DZ', 'Generator_DZ')
   AND FindVehicleKeysCount(Object_DATA.CharacterID) = 0;RETURN ROW_COUNT();
 END|
delimiter ;

 

CREATE FUNCTION FindVehicleKeysCount
BEGIN
    DECLARE totalKeys INT DEFAULT 0;
    DECLARE keyName VARCHAR(32) DEFAULT "";
    DECLARE keysInChar INT DEFAULT 0;
    DECLARE keysInObj INT DEFAULT 0;

    SET keyName = (CASE
        WHEN `keyId` < 2501 THEN CONCAT('ItemKeyGreen', `keyId`)
        WHEN `keyId` < 5001 THEN CONCAT('ItemKeyRed', `keyId` - 2500)
        WHEN `keyId` < 7501 THEN CONCAT('ItemKeyBlue', `keyId` - 5000)
        WHEN `keyId` < 10001 THEN CONCAT('ItemKeyYellow', `keyId` - 7500)
        WHEN `keyId` < 12501 THEN CONCAT('ItemKeyBlack', `keyId` - 10000)
        ELSE 'ERROR'
    END);

    SET keysInChar = (SELECT COUNT(*) FROM `Character_DATA` WHERE `Alive` = '1' AND (`Inventory` LIKE CONCAT('%', keyName, '%') OR `Backpack` LIKE CONCAT('%', keyName, '%')));
    SET keysInObj = (SELECT COUNT(*) FROM `Object_DATA` WHERE `Inventory` LIKE CONCAT('%', keyName, '%'));

    RETURN (keysInChar + keysInObj);
END

 

Will this work?

 

Sorry for not knowing how to format the post as cleanly as everyone else seems to.

 

Thanks

[Zombified]

Seriously?

 

Nothing? 

 

So sad...  :(

[aVa Savagerx]

 

Is there away to tie the procedure to server reboot?

 

 To the best of my knowledge if you use static restart times, such as set times that your server restarts, you can set a trigger in your database to run those events at the same time. The problem is most people use dynamic restarting which would throw this method off somewhat.

 

For dynamic restarting, my understanding is that they are both separate applications so you would need to create a batch file which runs both the SQL script as well as restarts the server for you. You should be able to do this through BEC so that when it shuts the server down it executes that batch file which then brings everything back up.

 

I hope this helps. I have just woken up so may not be as coherent as possible, if you need me to explain further feel free to ask.

[Zombified]

I'm on DayZ.St server and they restrict routines... so this solution is not going to work. 

 

BUT (hehe) I figured out a backdoor process that will complete the same thing. 

 

Really appreciate you posting the sql... it helped me not having to build it from scratch.