1 / 41

FLogging: Metric Collection on the High Seas

FLogging: Metric Collection on the High Seas. Joe Ludwig Director of Development Flying Lab Software. What is FLogging?. Server-side Metric collection Game events with parameters Server performance events. Formal + Log = FLog. Design Goals. Designer goals

nieve
Download Presentation

FLogging: Metric Collection on the High Seas

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. FLogging: Metric Collection on the High Seas Joe Ludwig Director of Development Flying Lab Software

  2. What is FLogging? • Server-side • Metric collection • Game events with parameters • Server performance events

  3. Formal + Log = FLog

  4. Design Goals • Designer goals • Lots of different views on data • Possible to derive context from data • Programmer goals • Easy to add new flog types • No global recompiles

  5. Data Collection

  6. Adding a new flog type • Add any new field types in XML • Add the new flog type in XML • Upgrade database to generate SQL • Tables • Procedures • Views • Add call to record flog

  7. Flog types defined in XML Flogger_field_types.xml: <flsFlogFieldType> <name>level</name> <sqlType>TINYINT</sqlType> </flsFlogFieldType>

  8. Flog types defined in XML Flogger_types.xml: <flsFlogType> <name>level_gained</name> <fieldNames> <e>level</e> </fieldNames> <usesObjectId>true</usesObjectId> <hideByDefault>false</hideByDefault> </flsFlogType>

  9. Add call to record flog void flsAdvancementManager::setLevel(flsCharacter* pCharacter, igChar iNewLevel) { pCharacter->assignNewLevel(iNewLevel); if(pCharacter->isPlayer()) { flsFlogger::flogf("level_gained", pCharacter->getLocalId(), "%d", iNewLevel); } }

  10. Database Format - Tables • Flog table – one entry per flog • Value tables – One entry per flog that uses that field type • Flog_type table – One entry per type

  11. flog table

  12. value table Type of value column is controlled by flogger_field_types.xml

  13. flog_type table

  14. Database Format – Stored Procedures • Flog type procedures • Flog type expiration procedures • Flog_heartbeat procedure

  15. Flog type procedures CREATE PROCEDURE Flog_level_gained @iObjectId INT, @level_param TINYINT AS BEGIN DECLARE @pid bigint, @cid int; SELECT @pid = puid, @cid = clusterid FROM #flog_vars; INSERT INTO flog (puid, cluster_id, flog_type, object_id) VALUES (@pid, @cid, 3, @iObjectId); DECLARE @lastid BIGINT; SET @lastid = SCOPE_IDENTITY(); INSERT INTO flog_level (id, value) VALUES (@lastid, @level_param); END

  16. Flog age procedures CREATE PROCEDURE Flog_Age_skill_used AS DELETE flog FROM flog, flog_item_id WHERE DATEDIFF(DAY, flog.flog_time, CURRENT_TIMESTAMP)>=2 AND flog.id=flog_item_id.id

  17. Flog Heartbeat procedure CREATE PROCEDURE Flog_Maintenance AS BEGIN EXEC flog_Age_maneuver_skill_used; EXEC flog_Age_skill_used; EXEC flog_Age_volley_fired; END

  18. Database Format – Type View CREATE VIEW Flog_Data_level_gained AS SELECT flog.id, flog_level.value AS [level] FROM flog INNER JOIN flog_level ON flog.id=flog_level.id WHERE flog.flog_type=3

  19. What we flog • Login/Logout events • Zoning • Economic events • Advancement events • Missions • Conquest events • Chat • GM commands

  20. How much data? • 4.5 million new flog records per week • Top 5 flog types account for 50%

  21. Example flog output

  22. Money Gained

  23. Money Lost

  24. Exploit Detection

  25. Reports using context • Context is available in the data • Extracting it can be slow • Example: Deaths per level

  26. Current level function CREATE FUNCTION level_at_flog (@iObject INT, @iCluster INT, @iFlogId BIGINT) RETURNS INT AS BEGIN DECLARE @iLevel INT SELECT TOP 1 @iLevel = flog_level.value FROM flog INNER JOIN flog_level ON flog.id=flog_level.id WHERE (flog.id < @iFlogID) AND flog.object_id=@iObject AND flog.cluster_id = @iCluster AND flog.flog_type=3 ORDER BY flog.id DESC IF @iLevel IS NULL SET @iLevel=1 RETURN @iLevel END

  27. Death by level table CREATE TABLE death_by_level (id BIGINT, level INT, object_id INT, cluster_id INT)

  28. Death by level table INSERT INTO death_by_level (id, level, object_id, cluster_id) SELECT flog.id, dbo.level_at_flog(flog.object_id, flog.cluster_id, flog.id) AS [Level], flog.object_id, flog.cluster_id FROM flog WHERE flog.flog_type=65

  29. Death by level table

  30. Death by level report SELECT level, COUNT(id) AS [Deaths] FROM death_by_level GROUP BY level ORDER BY level

  31. More data from context • Death by zone • Death by mission • XP by zone • Skills purchased by level • Play time per level

  32. Room to improve • More and better reports • Automatic data rollups • Parameterized queries • Multiple associated fields in one table • Performance

  33. Questions? Slides available at http://programmerjoe.com Joe Ludwig joe@flyinglab.com

More Related