   Auditing all actions in a MMO
  In the MMO I'm working on, I ideally want to record every notable event each player performs. For instance every fight, every item use, every teleport, etc. This allows us to not only investigate accusations of cheating or game errors, but to develop reports on how the game is used - that sounds a bit vague because I don't yet know what we want to look at.I'd MUCH prefer if a traditional DB can be used to store all this data, rather than a mass of file data. Of course it could be a huge amount of data but I'm not thinking it would go in the normal game DB. Instead it seems the ideal use for a data warehouse, which could be on a separate server entirely.However, how feasible is this? It's potentially a lot of DB work... more than is involved actually running the game proper. Does anyone know if other MMOs tend to do this kind of thing?   
  Every MMO i have worked on pretty much does this, a SQL database that stores all critical actions and parameters, e.g time/date, gold involved, unique item sequence numbers, players invovled in trade etcBe warned though, the size of these databases outweighs the game data by several times, and only so many weeks work of data can be kept 'online' The implementation needs to be solid so as not to cause any lag on the server whilst waiting for these events to be logged, ideally the events should be queued and batched for database inserts   ;  I don't think many log data on quite that fine-grained a level, though probably some do. It seems odd that you say "which could be on a separate server entirely" though, as I think most MMOs will already have the DB on a separate server to the game server(s) anyway. It just makes good sense.If you did want to go down this route, you might choose something like MySQL which is pretty quick anyway, and use the "INSERT DELAYED" statement so you're not consuming much client time.   ;  We log data at that level. In our system, the servers generate specific "events" intended for the data warehouse. Those are temporarily logged to flat files, and are periodically shunted through a traditional ETL process into a data warehouse.We also have traditional server logs, for diagnostics and post-mortem, which are even more detailed; we save them for some time, but eventually purge them on a rotating basis.;Quote:Original post by hplus0603We log data at that level. In our system, the servers generate specific "events" intended for the data warehouse. Those are temporarily logged to flat files, and are periodically shunted through a traditional ETL process into a data warehouse.Yeah, a proper Data warehouse using ETL and STAR is what I am imagining.;Quote:Original post by MarvinBe warned though, the size of these databases outweighs the game data by several times, and only so many weeks work of data can be kept 'online' I don't think you understand what a data warehouse is... sites like Amazon  would use a similar system to store details of all transaction history. It is NOT a standard database.;Quote:Original post by KylotanI don't think many log data on quite that fine-grained a level, though probably some do. It seems odd that you say "which could be on a separate server entirely" though, as I think most MMOs will already have the DB on a separate server to the game server(s) anyway. It just makes good sense.Sorry I mean a separate DBQuote:Original post by KylotanIf you did want to go down this route, you might choose something like MySQL which is pretty quick anyway, and use the "INSERT DELAYED" statement so you're not consuming much client time.We're using SQL Server, I don't know if it has something similar?;Quote:Original post by d000hgWe're using SQL Server, I don't know if it has something similar?As far as I know there is not.   ;  Oh well, performance shouldn't be issue with SQLServer anyway.   ;Quote:Original post by d000hgOh well, performance shouldn't be issue with SQLServer anyway.Don't bet on it.You're right about another db on another server BTW. Queries on data warehouse DBs usually involve looking at many many more rows than you would on your live DB (in any 'running total' queries for instance). You can refactor the DB to lessen this problem, but it'll make it harder to reuse existing SQL (though there are ways around this using views).Problem is, the types of queries people typically want to do on historical data are usually much more complex than what is used day-to-day on the live DB. It usually seems to be cheaper to buy an extra machine(s) specifically for data warehousing than risk slowing down your production DB servers and make your coding harder. The main saving comes from not having to care so much about the performance of SQL queries on the warehouse, making them faster to write.Running the datawarehouse DB and live DB on the same machine means that the performance of one affects the other, meaning you have to treat data warehouse queries as (potentially, relatively) performace critical.FWIW my experience of data warehousing is for websites rather than MMOs.   