Replay System

[Ze_PilOt]

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.

I just did a SELECT query? Most likely everything is cached as that table is interrogated all the time. Disabling caching never really work in my tests.

Tell me how I should do it to run it as you do?

But really, searching anything in that table is blazing fast. The only problem is how much memory is needed.

[Sheeo]

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.

I just did a SELECT query?

Tell me how I should do it to run it as you do?

But really, searching anything in that table is blazing fast. The only problem is how much memory is needed.

Yeah sorry, I don't know how you're getting the times without data sending directly?

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?

[Ze_PilOt]

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?

[/quote]

I've only timed the queries each time, not displaying the data.

Yes, data are removed if the replay is older than 3 months, because the lobby is unable to display that many replays either way.

If the lobby was able to do some complex searches and displays several pages, and with a new server having more memory, that limitation would go away (or will be less limiting).

[nine2]

I totally agree that there needs to be a leader/leaders that decides what functions go in, what priority they are, and how they get done. Ip6 sounds very important as well.

If you had said at the start of this 'i dont have time', i would not have responded at all, but instead we went back and forth with a bunch of misdirection stuff.

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.

I can appreciate that but my time is limited (I have a young baby) and I'm not going to learn new languages for this, so no lobby ui work for me. However here are new functions that I want:

- replay search gets wildcard mapname search and wildcard playername search - two things that annoy me very much.
- replay search gets more results, fast - i want to see further back
- new statistics page where you can compare two players and see stats on how much they beat each other, divided by faction, map, etc.

Although I wouldn't be doing any UI work for them, perhaps I could do the db work locally and prove on my own system that I can get these features working. Then I mockup a screenshot of what it could look like, then come on the forums and ask someone to do the UI work for me.

Some of these features may not be possible, but I would like to discover that via attempting to do it. The truth is we can't even see what is possible without the schema.

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.

Fair point, but you could come up with a mechanism for it. Eg: I prove my idea works via examples. I make change scripts and so on, server guy (you) reviews it, makes a backup, runs scripts, do some test, etc. Ideally though you would see the forum post I mentioned earlier (with mockup, looking for a UI guy) and say "yeah that's awesome I'm going to shove that right in". You know, community collaboration and all that.

It's even more true for something done in the free time of everyone involved.

You not having time to do things is a valid reason and I will not complain. But this thread has NOT been about your time.

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.

The community is willing to spend time on enhancements that they are passionate about. If you had more time it would be wonderful if we could perfect and implement the community's ideas. Telling me to do some other work instead is ... not really what it's about. You can't take someone's passion and try and put it over there instead.

TLDR:

All I asked for was data and a schema. That's not a huge ask of anyone. You could have said you were busy but instead ... drama.

[Sheeo]

Yes, data are removed if the replay is older than 3 months, because the lobby is unable to display that many replays either way.

If the lobby was able to do some complex searches and displays several pages, and with a new server having more memory, that limitation would go away (or will be less limiting).

So we have 560k replays within the last 3 months? Doesn't sum up for me, considering 54k games were played the last 30 days.

The actual data send times should be included in profiles -- if anything it reminds one what is going on.

I'm not entirely convinced that a denormalized copy of all these stats is worth the overhead; my version of the query wouldn't be run directly, since we're only ever interested in a small subset of replays (Which is what the indexes help with).

[Ze_PilOt]

So we have 560k replays within the last 3 months?

No, if you check the dump, there is a row for every player (so several rows for the same game).

That allows filtering the games by rating or player name (and if the lobby was able to, by faction).

I'm not entirely convinced that a denormalized copy of all these stats is worth the overhead; my version of the query wouldn't be run directly, since we're only ever interested in a small subset of replays (Which is what the indexes help with).

Yes and no.

ie. if you say "let take only the 3 last months", and search for rating 2000+ replays on theta, it's unlikely you will fill 100 replays (what is currently returned to the lobby).

So to allow to see more, you would need a "see more.." option in the lobby, and pagination support.

I hate to repeat myself, but it's really the core of the problem : the lobby needs works for display/query.

[rootbeer23]

how do you benchmark the select without row output?

[Sheeo]

All right, thanks for the clarification.

Yes and no.

ie. if you say "let take only the 3 last months", and search for rating 2000+ replays on theta, it's unlikely you will fill 100 replays (what is currently returned to the lobby).

So to allow to see more, you would need a "see more.." option in the lobby, and pagination support.

I hate to repeat myself, but it's really the core of the problem : the lobby needs works for display/query.

Certainly the lobby should support better querying. I'm sure thygrrr is willing to help out here, or someone else if he's too busy

I do think it's relevant to see if we can actually answer these queries in reasonable time without the denormalized setup, however. It also allows us to look into replays which are older than 3 months, and perhaps there are more low-hanging optimizations we can do (I only spent little time so far looking at the query).

[Ze_PilOt]

It also allows us to look into replays which are older than 3 months, and perhaps there are more low-hanging optimizations we can do (I only spent little time so far looking at the query).

Having a replay_vault table that does not delete older replay would allow it too.

I agree that optimizing the queries would be better, but it's always a question of time and priorities..

[RoLa]

Let's dont waste the precious time of Zep. With the migration to the new Server the dumps need to be done anyway. Directly after transition there might be also the time to make a dump of all relevant tables for us. I will work in the meantime on the python version of FAF-Chart so we can integrate that in the lobby for local replay files and evt. i can write a routine to export the data from replay files to a database of your choice. That will lack some data but hey at least it would be something.