Transcript GDC 2005 - Programmer Joe

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
Lots of different views on data
 Possible to derive context from data


Programmer goals
Easy to add new flog types
 No global recompiles

Data Collection
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
Flog types defined in XML
Flogger_field_types.xml:
<flsFlogFieldType>
<name>level</name>
<sqlType>TINYINT</sqlType>
</flsFlogFieldType>
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>
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);
}
}
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

flog table
Field
Type
Id
BIGINT IDENTITY
Flog_type
INT
Flog_time
DATETIME
Cluster_id
INT
Character_id
INT
Puid
BIGINT
value table
Field
Type
Id
BIGINT
Value
TINYINT
Type of value column is controlled by
flogger_field_types.xml
flog_type table
Field
Type
Flog_type
INT
Name
CHAR(32)
hideByDefault
TINYINT
Database Format –
Stored Procedures
Flog type procedures
 Flog type expiration procedures
 Flog_heartbeat procedure

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
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
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
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
What we flog








Login/Logout events
Zoning
Economic events
Advancement events
Missions
Conquest events
Chat
GM commands
How much data?
4.5 million new flog records per week
 Top 5 flog types account for 50%

room_entered
xp_kill
exit_used
money_gained_kill
mission_stage_advanced
money_lost_recipe
18.95%
11.78%
7.66%
6.59%
5.76%
4.20%
Example flog output
Time
Type
7/10/2007
2:59:17
PM
chat_society
message: ahoy Joany
View
Log
7/10/2007
2:31:38
PM
room_entered
room: Main
zone: P_Br_WstEnd
zone_context:
View
Log
7/10/2007
2:31:35
PM
rpc_priv
account: 1942
name: ZoneToPlayer
View
Log
7/10/2007
2:31:27
PM
rpc_priv
account: 1942
name: SendSystemChat
View
Log
7/10/2007
2:31:27
PM
chat_system
message: Ahoy! I'm your duty GM. If you are
experiencing problems, feel free to send me a tell
(/tell GM Rhaegar, message). If you need to report
a bug, please do so via
http://support.flyinglab.com
View
Log
7/10/2007
2:27:47
PM
rpc_priv
account: 1942
name: RequestCharacterInfo
View
Log
7/10/2007
2:26:40
PM
chat_direct
partner: 1042
message: Hi there - the best place to find info like
that is Nation chat
View
Log
7/10/2007
2:16:55
PM
rpc_priv
account: 1942
name: ZoneToPlayer
View
Log
7/10/2007
2:15:11
PM
rpc_priv
account: 1942
name: ZoneToPlayer
View
Log
7/10/2007
2:14:48
PM
rpc_priv
account: 1942
name: SystemWho
View
Log
Money Gained
Reason
money_gained_mission
money_gained_trade
money_gained_kill
money_gained_auction
money_gained_shop
money_gained_create_society
money_gained_respec
money_gained_mail
Amount
31,310,500
11,038,331
8,029,993
6,826,344
4,123,899
13,000
1,263
350
Money Lost
Reason
Amount
money_lost_trade
11,038,331
money_lost_recipe
10,980,920
money_lost_auction
9,956,249
money_lost_shop
7,587,867
money_lost_ActivateStructureDeed
4,760,800
money_lost_admin
3,338,500
money_lost_recipe_tax
633,113
money_lost_PayStructureUpkeep
510,225
money_lost_auction_listing_fee
159,867
money_lost_create_society
89,000
money_lost_mail
14,550
money_lost_AuctionRelist
1,566
Exploit Detection
Character XP Velocity Date
10589
10,713 8/20/2007
10589
10,098 8/19/2007
11268
9,097 8/17/2007
515
8,370 8/18/2007
11077
8,316 8/19/2007
7237
8,226 8/18/2007
3344
7,522 8/20/2007
6801
7,275 8/17/2007
310
7,063 8/21/2007
Reports using context
Context is available in the data
 Extracting it can be slow
 Example: Deaths per level

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
Death by level table
CREATE TABLE death_by_level
(id BIGINT,
level INT,
object_id INT,
cluster_id INT)
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
Death by level table
id
130804
199407
231699
242162
348568
382145
384861
390520
402353
423383
Level
7
3
7
4
8
7
8
8
9
9
object_id
1
45
41
43
74
75
86
86
22
33
cluster_id
0
0
0
0
0
0
0
0
0
0
Death by level report
SELECT
level,
COUNT(id) AS [Deaths]
FROM death_by_level
GROUP BY level
ORDER BY level
More data from context
Death by zone
 Death by mission
 XP by zone
 Skills purchased by level
 Play time per level

Room to improve
More and better reports
 Automatic data rollups
 Parameterized queries
 Multiple associated fields in one table
 Performance

Questions?
Slides available at http://programmerjoe.com
Joe Ludwig
[email protected]