How to store attack log?

[FrankBro]

Let's say i have a rpg game where you can attack other players or monsters. I was wondering how to efficiently store attack log in the database. My idea was a attack table with a attack id, player id, enemy id .. but then what ?

I can't really force a number of attack by making let's say 10 columns for attack, since 2 players with a lot of defense and hp but no attack could fight together and the fight would go on for a while.

So, how should i store those fights ?

[Nox]

Yes, than make columns just attributes of 1 attack (or just a combat action) and make 1 row = 1 combat action, that way you can store infinite number,
the same procedure as in other cases of variable count of entries

Say... make 1 table for basic combat info, like time started, attackers id, defenders id, place, weather, whatever...
then 1 table for combat actions like combat's id, actors id, action, additonal info
other things depending on your combat system...

More on logging here: http://community.bbgamezone.net/index.php/topic,1852.msg11919.html ...and maybe in some others

[JGadrow]

A log is simply a tool. I would place a sprintf formatted string into an "effect" column. So, en example entry might look like this:

Code: (text) [Select]

+-------------+-------------+-----------+------------------------------------------+
| attacker_id | defender_id | attack_id | result                                   |
+-------------+-------------+-----------+------------------------------------------+
| 12          | 117         | 26        | %s attacks %s with %s dealing 50 damage. |
+-------------+-------------+-----------+------------------------------------------+

The reason for referencing attacker, defender, and attack in this manner would be that if you change the names of the players or the attack, the result log would display the new name rather than the old. Since the damage was already applied to the defender (and has almost CERTAINLY changed already) there is no need to make it a variable, simply hard-code it into the result string.

*edit - corrected a formatting error.

[Chris]

An effective way to store such type of attack log does not exist. Maybe for paid members only, or if the game is small, but not for everyone.

If you have 1000 players and each can attack 100 times a day and each attack consists of 30 rounds it would mean 1000*100*30=3 million rows (and these number are not high yet and you would want to store them a bit longer than 24 hours).

I can't recall even one game that would have such log system for all members.

[FrankBro]

the thing is, attacks are not just a simple " you attacked for x dmg and received x dmg ".
It's more about logging every hit. Ex:
Player 1 atk player 2 for 12 dmg and 1 fire dmg
Player 2 atk player 1 for 8 dmg and 1 water dmg
Player 1 atk player 2 for 23 dmg and 2 fire dmg CRITICAL HIT
Player 2 atk player 1 for 0 dmg and 1 water dmg BLOCKED

So i can make animations in these attacks. I've seen a lot of sites doing that (outwar) and they don't have immense servers, i guess there is a smart way to save them.
I know it will take a lot of space if 1000 players atk about 20 times a day.

[JGadrow]

Ah, I see. You're going to need quite a massive amount of data storage with this method. However, that doesn't invalidate my format. If you look... what you typed in almost exactly matches what I had.

Animations have absolutely nothing to do with logging. The animation has everything to do with the attack that is being made. My guess is that in order to minimize amount of necessary data, they probably store the results of each FULL combat as a serialized array or something to that effect that only stores damage numbers. For instance:

Code: (php) [Select]

$combatId = 1;
$attackerId = 1;
$defenderId = 2;
$result = array
(
    array
(
'attackId' => 1,
'params' => array
(
12,
1,
),
),
array
(
'attackId' => 2,
'params' => array
(
8,
1,
),
),
array
(
'attackId' => 1,
'params' => array
(
23,
2,
),
'special' => 'critical',
),
array
(
'attackId' => 2,
'params' => array
(
0,
1,
),
'special' => 'block',
),
);

Would be the PHP code returned once a row is processed. Using the data in $result, you could call each attack to output it's specific format string describing the result. Pass in the player ids and then tack on a little special message to each if the 'special' attribute is set.

This structure was built from the example that you provided. In this manner, only a single row is utilized per combat. The only part that feels a little awful about it is the hard-coding of attack id because you might want to build a report of the usage of certain attacks to see how they compare to another attack or something. However, I think there may be a work-around for that. Another problem is if you decide to completely remove an attack. That will render the line of the report using that attack worthless.

[FrankBro]

Yea i know about the animation, the animation is built using the result.
This is EXACTLY how i though about saving it, however, my concern is space used on server/database, and how big should i make the column where i save all those arrays? a varchar(10000) ? lol

[JGadrow]

This is the smallest method that I can think of to store all attack data. Maybe someone has a better solution.

However, in order to store this (because it's a serialized object) you will need to store it as a text or binary field and not varchar. This column will be quite lengthy and full of data so it should, of course, never be considered for indexing.

[Chris]

I've seen a lot of sites doing that (outwar) and they don't have immense servers, i guess there is a smart way to save them.
I know it will take a lot of space if 1000 players atk about 20 times a day.

Take a look at Outwar then. How many attacks per player daily they store? For how long? How much data they store (number of rounds per fight)? Or any tricks they use?

Can you drop the examples of other sites that do it? I would be interesting in checking them as well.

It might be also that they do not do it in efficient way and they suffer the penalty. They could just decided that the penalty of this was justified (like if they have a very good backup system when they do not care if the data have several GB, or if they have a very good income so additional hardware is not a problem). Also, from a quick glance, they have several small worlds instead of one big, this makes it easier to do then.


Hmmm, if you want only 20 attacks daily you could get away with it (like 600 rows per player then, not nice, but can live with it ).

This is EXACTLY how i though about saving it, however, my concern is space used on server/database, and how big should i make the column where i save all those arrays? a varchar(10000) ?

I use separate int fields in the log table, and ther parse before displaying (but my combat consist of one row, not exchange of blows, so there are several digits less involved...), strings are not the way to go in a long run (but tempting, since very easy to code...)

[raestlyn]

Can you drop the examples of other sites that do it? I would be interesting in checking them as well.

Estiah.

[FrankBro]

http://fabar.outwar.com/attack/379852138
I just spoke with one of the coders, aparently its not saved really efficiently,but it's still done this way, for the sake of having it.
As you can see, even skills that were active are saved.

[codestryke]

When it comes to logs, IMHO, the more the better, the more you log the better you will be able to make decisions for the game and it's players. It's easier down the line to remove logging or remove unneeded information once you have a better idea of what you need. You, however, cannot go back and get the information you need if you didn't log it As a quick guess I would say 80% of the data in our games is nothing but log tables and still once in a great while we find that we didn't log something that we should have. On average a round of Cypher which goes from 4-6 weeks generates a backup that is just over 2 gigs of data uncompressed.

At times we've had others admin our games, within the admin panel every, page request and the get / post data that might be associated with it is logged along with the authentication name used and the ip associated with it. Again on an active round with one of the games these logs get very large fast, but they are also very rarely read so the load on those tables is mostly writes.

Large size tables are not that bad, what makes the difference is:
a. Proper table design
b. Proper engine type (myISAM / INNODB)
c. Proper indexes

On a lot of the games the logging is actually a two table design, one holds the summary data and another table with a 1 to 1 relationship that holds the details. For a battle log the skills each player has etc is in the summary table and this is what is shown to the player when they read there logs. The detail table holds the per round results for the battle. Depending on the game sometimes this data is pure text, on others it's array data that is been serialized. This is done because of how mySQL allocates storage for TEXT fields. When a player wants the details of the battle they have to click on a link in the battle log or hover over an icon with the mouse and the data is retrieved from the detail table.

There are a lot of things you have to skimp on with your first game, logging should not be one of them

[Qwerty]

You don't need to store every single attack for for every player, only say the 5 most recent ones, which would then put the maximum storage down to number of players X 5.

Edit: My idea is only effective though if you only had logs that players would want to look back on, not for statistics.

[Chris]

When it comes to logs, IMHO, the more the better, the more you log the better you will be able to make decisions for the game and it's players. It's easier down the line to remove logging or remove unneeded information once you have a better idea of what you need. You, however, cannot go back and get the information you need if you didn't log it As a quick guess I would say 80% of the data in our games is nothing but log tables and still once in a great while we find that we didn't log something that we should have. On average a round of Cypher which goes from 4-6 weeks generates a backup that is just over 2 gigs of data uncompressed.

Whoa!!! My biggest game during a course of 3 month round gets below 20MB compressed data. It seems my coding style is extremely anorecting, maybe I should add some fat...
How do you handle backups then? How often do you do it and what do you use to do them?
My connection is too poor to even consider downloading each day such monster to my PC...

[famulus]

I have a question along the same line as Chris:

At times we've had others admin our games, within the admin panel every, page request and the get / post data that might be associated with it is logged along with the authentication name used and the ip associated with it. Again on an active round with one of the games these logs get very large fast, but they are also very rarely read so the load on those tables is mostly writes.

I also save every page request along with get/post data, ip, user id, timestamp, etc.  I'm using a myISAM table to hold the data (I'm also using count and such on the table) and I find that database backups lag the game for several seconds due to table locking.  How often are you doing backups and do they cause the game any problems?



to the op:
How much player information is used during the attacks?  (stats, items, etc)  Depending on the attack code, you could save the players vital information, the mob or attacked players information, the version of your attack code, and the random number generators seed.  You could then reproduce the full attack at any time.

[JGadrow]

@famulus you may want to consider InnoDB as the engine for your log table. Row-level locking instead of table-level will help alleviate the lag.

Of course, I'm assuming most of the transactions with this table are write operations and not read operations. Even if they are read operations, proper indexing usually very quickly makes up any speed difference between MyISAM and InnoDB.

[codestryke]

Most of our tables in the database are INNODB so backups don't cause much of a lag when they are performed. I use my own custom PERL script which backs up all the databases for every game and then rotates the logs out depending on the settings I have in the script, which is 12 copies of each database. The backups are done twice daily so each game has 6 days worth of backups on the sever.

Our server has two physical drives so the backups are done on the drive that does not contain the SQL data, this way we don't need to download those backups locally. The chances of losing both drives at the same time is pretty low and if we did we would have a lot more to worry about then just backups Hardware as well as software goes a long way it what you are trying to achieve.  Our last server used a single IDE drive and doing backups, even on INNODB tables, still produced lag (especially during times of increased activity). With our current setup of dual SCSI drives the lag is ALMOST non-existent, again it depends on the activity at the time.

For me, and this is me personally, I always disliked games that only stored the last X amount of attacks. Being a developer I know that disk storage is pretty cheap so then it makes me wonder what else is cheap on the game. Depending on the style of game logging everything and letting the players see there logs goes a long way with retention. They are able to see how they progress though each round and can make an informed decision about what they are going to do next round. Nothing worse then logging in, seeing a large portion of your X stolen from attacks and you only get to see the last 10 attacks, which doesn't show all the people that attacked and stole your X.

@Chris,
Anorexic coding LOL. I consider the stuff I have in our games to be meat rather then fat All games with the execption of CyberStryke has our drop in group portal system. Group can be called Faction, Alliance, Gang or whatever depending on the theme of the game. This group system has full management of it's members, a news section, full forum with all the trimmings, broadcast messaging, template messages for join, promote, demote and kicking, ability to create a custom graphical tag for said group, logging (of course) and finally a way for members to donate X to the group and for the leadership of said group to divy out to it's members. This is all drop in with no custom programming for the game, each game then has custom sections of the portal to support the theme of the game. IE in Cypher each faction has it's own file server they can upload/download programs to share with other members. This is basically a mini Slashdot or Drupal for each and every group in the game and it does produce a lot of database entries and hits to the server. Honestly when it comes to group dynamic I've never seen a system of this depth in any pbbg game I've ever played and our players love it

[JGadrow]

For me, and this is me personally, I always disliked games that only stored the last X amount of attacks. Being a developer I know that disk storage is pretty cheap so then it makes me wonder what else is cheap on the game.

Ditto. I have a 1TB external hard disk that I keep media files (and a couple of personal project files) on. And another 1TB drive in my personal desktop. Granted, these are standard SATA drives and not exactly the optimal devices for web servers... But they only cost me around $100 each. So, storage is extremely cheap.

Heck, I have a 32GB flash drive that I picked up for about $60 that even has a lifetime warranty! Anytime it dies, I just go to the store and they give me a new drive... no paperwork necessary! lol

[Chris]

I consider onsite backups useless to me. I also keep 100% independence form the hardware. If the server provider was nuked it would mean to me only a short and unfortunate downtime and half week lost of gameplay. Simultaneous harddrives failure on the whole city where the server is located would also mean not much to me. Yes, it is paranoia, I know But there were companies going bankrupt with may site being online on them. Try getting back your backups when the provider has financial problems
I still get shrugs when I recall it, one month lost of gameplay. Strangely, players forgave me and I only lost tons of money, I think I haven't lost even one donator back then. But I don't think they would forgive me the second time Well, maybe they forgot about it by now, so who knows. Anyway, I'm not gonna check it. And I won't part with my precious small offsite backups stored on my machine, many, many miles from the server.

But if I hadn't had this kind of bad experiences I would probably went for onsite backups... There are plenty of reputable providers who do good job. Well, maybe. I think. Naver can be sure

[famulus]

I've been looking at amazon's storage service http://aws.amazon.com/s3/ for offsite backups.  You only pay for the space and transfer you use.  At $0.15 per GB, that's around $150 for a TB.  Currently, I'm backing up once per hour and doing a manual offsite backup once per day.

[Chris]

Currently, I'm backing up once per hour and doing a manual offsite backup once per day.

Glad to see someone even more paranoid than me. Makes one feel better

[Harkins]

I'm a big fan of S3 for backups. I was all set to write my own nifty backup code when I found JungleDisk, a really nice bit of backup software that stores everything on S3 (with optional heavy encryption). I've armtwisted most of my family and friends to use it for their files, too.

[Chris]

The thing is, it stores everything. If something goes bad you don't need everything, you just need a small SQL file backup so you can upload it quickly on some emergency server.

There was some SQL backup service a time ago, but I'm not sure if it even launched... Anyone know about it?