Replay System

[Ze_PilOt]

I want:

Your mom never told you to say "I would like" and not "I want"?

Being rude doesn't help when requesting something that can take time for something else (or even if it takes no time really).

1. full SCHEMA for db, not including data, not email addresses - so I can look at making everything awesomer. apparently this is impossible because you think I want email - which i dont.

http://pastebin.com/f6qxWfdE

I hope it will be awesome really soon! Keep me posted!

2. 10 rows of realistic (or real) sample data for the two extra tables I mentioned in the format I mentioned. apparently this is impossible right now just because.

You asked for a full dump, that is not possible.
10 rows is possible, but I really don't get why you need that. Whatever, I'm stop trying to understand what is in your head.

Code: Select all

CREATE TABLE IF NOT EXISTS `game_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `startTime` timestamp NULL DEFAULT NULL,
  `EndTime` timestamp NULL DEFAULT NULL,
  `gameType` enum('0','1','2','3') DEFAULT '0',
  `gameMod` tinyint(3) unsigned DEFAULT NULL,
  `host` mediumint(8) unsigned DEFAULT NULL,
  `mapId` mediumint(8) unsigned DEFAULT NULL COMMENT 'map id',
  `gameName` tinytext,
  PRIMARY KEY (`id`),
  KEY `startTime` (`startTime`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2654536 ;

--
-- Dumping data for table `game_stats`
--

INSERT INTO `game_stats` (`id`, `startTime`, `EndTime`, `gameType`, `gameMod`, `host`, `mapId`, `gameName`) VALUES
(1996396, NULL, NULL, '0', NULL, 93258, NULL, NULL),
(1996397, NULL, NULL, '0', NULL, 90766, NULL, NULL),
(1996398, '2014-03-22 12:59:25', '2014-03-22 13:32:06', '0', 6, 43433, 14, 'Anosognosia Vs MIHAN'),
(1996399, NULL, NULL, '0', NULL, 93258, NULL, NULL),
(1996400, '2014-03-22 13:00:20', '2014-03-22 13:53:19', '3', 25, 19497, 4326, 'o0firecat0o'),
(1996401, '2014-03-22 13:00:07', '2014-03-22 13:05:52', '0', 0, 49941, 560, 'Go play +1200'),
(1996402, '2014-03-22 13:01:06', '2014-03-22 14:10:12', '3', 25, 26637, 4271, '2v2'),
(1996403, '2014-03-22 13:06:49', '2014-03-22 13:08:11', '0', 0, 20161, 246, 'Rohan + Free Pizza 800+'),
(1996404, '2014-03-22 13:01:17', '2014-03-22 13:12:08', '0', 0, 80117, 564, 'M'),
(1996405, NULL, NULL, '0', NULL, 90766, NULL, NULL);


Code: Select all

CREATE TABLE IF NOT EXISTS `game_player_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gameId` bigint(20) unsigned NOT NULL,
  `playerId` mediumint(8) unsigned NOT NULL,
  `AI` tinyint(1) NOT NULL,
  `faction` tinyint(3) unsigned NOT NULL,
  `color` tinyint(4) NOT NULL,
  `team` tinyint(3) NOT NULL,
  `place` tinyint(3) unsigned NOT NULL,
  `mean` float unsigned NOT NULL,
  `deviation` float unsigned NOT NULL,
  `after_mean` float DEFAULT NULL,
  `after_deviation` float DEFAULT NULL,
  `score` tinyint(3) NOT NULL,
  `scoreTime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `playerId` (`playerId`),
  KEY `gameIdIdx` (`gameId`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4752574 ;

--
-- Dumping data for table `game_player_stats`
--

INSERT INTO `game_player_stats` (`id`, `gameId`, `playerId`, `AI`, `faction`, `color`, `team`, `place`, `mean`, `deviation`, `after_mean`, `after_deviation`, `score`, `scoreTime`) VALUES
(3461180, 1996404, 80117, 0, 1, 4, 0, 1, 1500, 500, NULL, NULL, 6, '2014-03-22 13:11:50'),
(3461181, 1996404, 82443, 0, 4, 1, 0, 2, 1500, 500, NULL, NULL, -1, '2014-03-22 13:11:44'),
(3461182, 1996359, 14079, 0, 3, 6, 1, 5, 1437.25, 120.127, NULL, NULL, 5, '2014-03-22 13:08:47'),
(3461183, 1996359, 20251, 0, 4, 17, 1, 3, 1618.14, 464.972, NULL, NULL, 5, '2014-03-22 13:08:50'),
(3461184, 1996359, 87978, 0, 4, 1, 2, 6, 1024.54, 95.7398, NULL, NULL, -1, '2014-03-22 13:08:28'),
(3461185, 1996359, 6490, 0, 1, 2, 2, 2, 1325.61, 154.512, NULL, NULL, -1, '2014-03-22 13:08:30'),
(3461186, 1996359, 85827, 0, 1, 10, 2, 4, 777.789, 137.733, NULL, NULL, -1, '2014-03-22 13:08:13'),
(3461187, 1996359, 77558, 0, 1, 7, 1, 1, 762.626, 70.8507, NULL, NULL, 5, '2014-03-22 13:08:47'),
(3461188, 1996359, 76843, 0, 3, 3, 2, 8, 1217.7, 110.425, NULL, NULL, -1, '2014-03-22 13:08:17'),
(3461189, 1996359, 74615, 0, 2, 4, 1, 7, 672.01, 83.1637, NULL, NULL, -1, '2014-03-22 13:08:36');

I do not want:

Wish.

To give you very specific request so that you can find a loophole to not provide what I want, for example saying 'thats not the problem anyway' or 'its more important to look here' or 'the challenge is over there' or 'its a RAM issue' or some other misdirection.

Maybe not being hostile would help making FAF better?

If you can't understand that I can't spend 20 min of my time every times someone wants something "Just because", and that trying to solve a problem that is non-existent is a waste of effort that could be used for something useful, you probably shouldn't participate in a community project anyway.

The time I've spend dumping these, I could have spend it cleaning the replay vault server. But you decide it wasn't a priority, who am I to say otherwise... I'm only doing that for 3 years right?

[Ze_PilOt]

A query where the needed info is already on game_stats and we can do further optimization on the other joins should be pretty fast.

Sure. I went the "brute-force" way because it needed to work ASAP, as the old system was locking the server badly.

The replay_vault table comes with the overhead of running that other query at the interval you described -- would you mind timing that one, too?

Yes, there is an overhead. But it could be decreased by implementing it in the live replay server (the one that should fill the "replay_there" column with your idea). But ...

Code: Select all

17 rows inserted. ( Query took 0.0087 sec )

When I saw that, I've estimated that the overhead didn't worth a single minute of effort to make it better.
What could be made better is the search request in that table and, mostly, lobby work to have more search options.

Nothing is limiting the server to send more than 100 results as it does now.
The limiting factor is the way the lobby is handling the results (no pagination,...)

Thanks for your help here

Again, as long as the requests are realistic and the goal is clear, I never refused any access to anything related to FAF, server code included.

[nine2]

Your mom never told you to say "I would like" and not "I want"?

Zep, I'm sorry I got frustrated and was a bit rude, but that was to a small extent due to the lack of cooperation and being called obscure.

You asked me what I 'want' so I told you want I 'want' and now I get in trouble for using the word 'like'. I do not 'like' this.

Although I can't help but point out that it took 15 pages of forum posts, I really am grateful for the information you have provided. Thanks.

You asked for a full dump, that is not possible.

Incorrect.

10 rows is possible, but I really don't get why you need that. Whatever, I'm stop trying to understand what is in your head.

And that my friend, is cooperation.

[Sheeo]

Using replay_vault table:

538,528 total, Query took 0.0002 sec

Can I see the query you used here?

[nine2]

The time I've spend dumping these, I could have spend it cleaning the replay vault server. But you decide it wasn't a priority, who am I to say otherwise... I'm only doing that for 3 years right?

You're totally right I'm sorry.

[Ze_PilOt]

Using replay_vault table:

538,528 total, Query took 0.0002 sec

Can I see the query you used here?

Ah, for that one, a simple select, as it's basically the same result than the complex query you've optimized.

[Sheeo]

Using replay_vault table:

538,528 total, Query took 0.0002 sec

Can I see the query you used here?

That's the one I've pasted earlier :

Code: Select all

INSERT IGNORE INTO replay_vault (SELECT `game_stats`.`id` AS `id`,`game_stats`.`gameName` AS `gameName`,`table_map`.`filename` AS `filename`,`game_stats`.`startTime` AS `startTime`,`game_stats`.`EndTime` AS `EndTime`,`game_featuredMods`.`gamemod` AS `gamemod`,`game_player_stats`.`playerId` AS `playerId`,`game_stats`.`mapId` AS `mapId`,(`game_player_stats`.`mean` - (3 * `game_player_stats`.`deviation`)) AS `rating`,`game_stats`.`gameMod` AS `gamemodid` from (((`game_stats` left join `game_featuredMods` on((`game_featuredMods`.`id` = `game_stats`.`gameMod`))) left join `table_map` on((`table_map`.`id` = `game_stats`.`mapId`))) join `game_player_stats` on((`game_player_stats`.`gameId` = `game_stats`.`id`)) join `game_replays` on((`game_replays`.`UID` = `game_stats`.`id`))) where (`game_stats`.`startTime` > (select date_sub(now(),interval 5 hour))and (`game_replays`.`file` is not null)))

I meant the one to select all replays from the replay_vault table. Is it just

Code: Select all

select * from replay_vault

?

[Ze_PilOt]

I meant the one to select all replays from the replay_vault table. Is it just

Code: Select all

select * from replay_vault

?

Yes sorry I've missread. Yes, just that, as it give the same result than the (overly) complex one.

[Ze_PilOt]

Your mom never told you to say "I would like" and not "I want"?

Zep, I'm sorry I got frustrated and was a bit rude, but that was to a small extent due to the lack of cooperation and being called obscure.

You asked me what I 'want' so I told you want I 'want' and now I get in trouble for using the word 'like'. I do not 'like' this.

Although I can't help but point out that it took 15 pages of forum posts, I really am grateful for the information you have provided. Thanks.

You asked for a full dump, that is not possible.

Incorrect.

10 rows is possible, but I really don't get why you need that. Whatever, I'm stop trying to understand what is in your head.

And that my friend, is cooperation.

A project the size of FAF can't run with coders running like headless chicken.
It's impossible to maintain a correct codebase if every body is doing what he want.
Imagine if you start modifying the whole DB while someone is implementing something based on it?

More importantly, some work are crucial (like the support of ipv6), some are nice but really not necessary. So efforts must be spent depending of that list of priorities. On top of that, low priority efforts can lead to problem in a higher level.

Ie. when adding themes (don't get me wrong, it is a nice feature) brought problems when we changed the lobby layout.
So we had to implement a compatibility check, being able to revert easily..... So from a simple, easy thing, it became a complex function that took a lot more time to complete.

About priorities,I can tell you that FAF will stop working properly in about 6 months/a year because of FAF lacking the support of ipv6.

That's why it needs leaders (at least one for every crucial component), that give these directions.
And like or not, I'm still one of them (I'm not the one for the FA code since a long time) for at least 2 more months.

As I've said earlier, optimizing the replay table is nice. Any optimization is always nice. But from an user point of view, it's useless as long as the lobby is not able to take advantage of these optimizations.

So if you want to give some time to the project, I would like you to work on the lobby interface for the replay vault, so it can handle complex query and pagination.

Also, you can spend time working on the DB, but it's very unlikely that your work will make it to the production server if it's unsupervised. It's not a trivial work.

"Don't change what is not broken". It's even more true for something done in the free time of everyone involved.

FAF dev was always very pragmatic, and changing that in the future will probably lead to big problems.

TL;DR version:
If you refuse to work on what is necessary (determined by the project leader(s), don't except to have any kind of support from them.

[Sheeo]

Using replay_vault table:

538,528 total, Query took 0.0002 sec

Can I see the query you used here?

Ah, for that one, a simple select, as it's basically the same result than the complex query you've optimized.

Okay, I have trouble reproducing that. How did you test?

On my local database, selecting 540k rows is a slow process, much slower than 200 microseconds. Infact it is so on my fast server, as well.

EDIT: Discounting data send time, I get roughly the same.

Since we won't be sending all the data though, I think it's more relevant to time actual queries being executed. I thought data was being deleted from the replay_vault table too?