Forged Alliance Forever

Re: Server Cluster

2014-02-26T22:42:15+02:00

Ze_PilOt wrote:
It has a purpose, and well, the problem is already solved, so don't bother

I cant find replays older than a week.

Statistics: Posted by RoLa — 26 Feb 2014, 22:42

Re: Server Cluster

2014-02-26T12:37:23+02:00

Ze_PilOt wrote:
It has a purpose, and well, the problem is already solved, so don't bother

Problem solved, or you found a way around it?

Dragonfire wrote:
By 8 miinsl row, the b-tree size of the index is not small ...

Yea, the retail databases I used to work with was impressive, think every item that goes through a till as one line in a table.

Statistics: Posted by Cuddles — 26 Feb 2014, 12:37

Re: Server Cluster

2014-02-26T12:08:18+02:00

By 8 miinsl row, the b-tree size of the index is not small ...

Statistics: Posted by Dragonfire — 26 Feb 2014, 12:08

Re: Server Cluster

2014-02-26T11:57:10+02:00

It has a purpose, and well, the problem is already solved, so don't bother

Statistics: Posted by Ze_PilOt — 26 Feb 2014, 11:57

Re: Server Cluster

2014-02-26T11:48:53+02:00

Just reading through those scripts:

You don't have index, create indexes.

In that view, it looks to me like it does a full table scan on game_replays FOR EVERY ROW in game_stats - create indexes.

What is this? This is generated from code? I don't understand values testing against values not fields?
Rather do the value check in code and only run those queries that needs to.

Code:: (-1 = -1 OR gamemodid = -1) AND (-1 = 60702 OR playerId = 60702) AND rating >= 0 AND (-1 = -1 OR mapId = -1) AND

(-1 = -1 OR gamemodid = -1) will alwasy be true?

(-1 = 60702 ) will always be false

(-1 = -1 OR mapId = -1) will always be true?

The SQL server should not be doing those checks.

I'll check how much I can do in between work today. I can post results of indexes versus no index. Without data it will be hard and I have to generate the data that might not reflect what you have. A CSV export from you with as much as possible rows will help.

Statistics: Posted by Cuddles — 26 Feb 2014, 11:48

Re: Server Cluster

2014-02-25T18:10:57+02:00

Ze_PilOt wrote:
Re-Create the table was too long (for 7 days back only, about 2 min), but incrementing it every 2 min is fast (a 0.001 sec).
The queries to the replay vault should be non-locking and quite faster now.

nice!

Statistics: Posted by RoLa — 25 Feb 2014, 18:10

Re: Server Cluster

2014-02-25T18:09:26+02:00

i am curious, how big are each of the tables ( dump of the tables as zip and unziped) :
game_player_stats
game_replays
game_stats
game_featuredMods
table_map

and
replay_vault

Statistics: Posted by RoLa — 25 Feb 2014, 18:09

Re: Server Cluster

2014-02-25T18:05:11+02:00

Re-Create the table was too long (for 7 days back only, about 2 min), but incrementing it every 2 min is fast (a 0.001 sec).

The queries to the replay vault should be non-locking and quite faster now.

Statistics: Posted by Ze_PilOt — 25 Feb 2014, 18:05

Re: Server Cluster

2014-02-25T17:55:45+02:00

Aurion wrote:
RoLa wrote:If the query for the creation of the replay_vault takes longer than some seconds an incremental update of the table would be perhaps faster. You can save the time stamp of the last query and select and insert only newer data rows to the replay_vault

Would always be preferable in my opinion, but for a quick performance test it doesn't matter that much. Incremental is much more durable in the long run though.

with the data redundancy you gain a lot of speed ...
That was also my first idea ...
and then maybe cache the last x days on the client side ...

Another idea, if you have not enough memory for the materialized view:
Split the database ...
Normally users search for a replay:

in the last days
a very popular one

Copy the whole schema, called it archive ...
Move all "old marked" in this archive ...
Each time your have less results then query limit you ask the archive ...

or ...

use MangoDB ...
has also a better scalability for the future ...

Dragonfire wrote:
@all:
If you would implement a client side cache for player stats and more "static" data ...
Which persistence method would you recommend?
SqlLite? CVS? own folder system?

We cannot install MongoDB on each client

Statistics: Posted by Dragonfire — 25 Feb 2014, 17:55

Re: Server Cluster

2014-02-25T16:13:41+02:00

RoLa wrote:
If the query for the creation of the replay_vault takes longer than some seconds an incremental update of the table would be perhaps faster. You can save the time stamp of the last query and select and insert only newer data rows to the replay_vault

Would always be preferable in my opinion, but for a quick performance test it doesn't matter that much. Incremental is much more durable in the long run though.

Statistics: Posted by Aurion — 25 Feb 2014, 16:13

Re: Server Cluster

2014-02-25T16:13:48+02:00

If the query for the creation of the replay_vault takes longer than some seconds an incremental update of the table would be perhaps faster. You can save the time stamp of the last query and select and insert only newer data rows to the replay_vault table. Eventually this table could be also distributed as offline version so that a client can get a local copy that could be distributed over some other servers and which could be updated once a day. this would mean the replay_vault would only be queried for entries of the current day while older replays can be downloaded from other servers mirrors.

Statistics: Posted by RoLa — 25 Feb 2014, 15:49

Re: Server Cluster

2014-02-25T15:24:23+02:00

RoLa wrote:
Aurion wrote:A materialized view is just putting the whole view in a table, causing it to be MUCH faster for views with lots of joins. MySQL doesn't have built-in functionality for this so you have to write your own stuff to renew it. The question isn't wether the data changes frequently, but if you need to see everything in real-time. If the latter is not the case (I don't think every 3 minutes makes it worth your trouble) it can be viable.

I didnt test it, but it's somethin like this:
Code:
CREATE TABLE `replay_vault_temp` 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`))) where ((`game_stats`.`startTime` is not null) and (`game_stats`.`EndTime` is not null) and ((select count(0) from `game_replays` where ((`game_replays`.`UID` = `game_stats`.`id`) and (`game_replays`.`file` is not null))) <> 0)); DROP TABLE IF EXISTS `replay_vault`; RENAME TABLE `replay_vault_temp` TO `replay_vault`

you could try this every 5 minutes in the background perhaps with ENGINE = MEMORY or ENGINE = MYISAM; parameter.

Yes, something like that could be a nice performance increase.

Statistics: Posted by Aurion — 25 Feb 2014, 15:24

Re: Server Cluster

2014-02-25T15:24:42+02:00

Aurion wrote:
A materialized view is just putting the whole view in a table, causing it to be MUCH faster for views with lots of joins. MySQL doesn't have built-in functionality for this so you have to write your own stuff to renew it. The question isn't wether the data changes frequently, but if you need to see everything in real-time. If the latter is not the case (I don't think every 3 minutes makes it worth your trouble) it can be viable.

I didnt test it, but it's somethin like this:

Code:: CREATE TABLE `replay_vault_temp` 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`))) where ((`game_stats`.`startTime` is not null) and (`game_stats`.`EndTime` is not null) and ((select count(0) from `game_replays` where ((`game_replays`.`UID` = `game_stats`.`id`) and (`game_replays`.`file` is not null))) <> 0)); DROP TABLE IF EXISTS `replay_vault`; RENAME TABLE `replay_vault_temp` TO `replay_vault`

you could try this every 5 minutes in the background perhaps with ENGINE = MEMORY or ENGINE = MYISAM; parameter. You have to add some indizes also to make it faster.

Statistics: Posted by RoLa — 25 Feb 2014, 15:20

Re: Server Cluster

2014-02-25T15:15:00+02:00

rootbeer23 wrote:
where ((`game_stats`.`startTime` is not null) and (`game_stats`.`EndTime` is not null) and ((select count(0) from `game_replays` where ((`game_replays`.`UID` = `game_stats`.`id`) and (`game_replays`.`file` is not null))) <> 0))

here you execute a subselect for every candidate row. better join table game_replays.
additionally a date of 1.1.1970 is just as good as NULL in case you need to get rid of the NULLs.
or add a boolean column to indicate incomplete rows.

True. I will try to implement that when I have some time. Probably tonight because I'm intrigued by how much these null are impacting the performance.

Statistics: Posted by Ze_PilOt — 25 Feb 2014, 15:15

Re: Server Cluster

2014-02-25T15:14:10+02:00

Aurion wrote:
A materialized view is just putting the whole view in a table, causing it to be MUCH faster for views with lots of joins. MySQL doesn't have built-in functionality for this so you have to write your own stuff to renew it. The question isn't wether the data changes frequently, but if you need to see everything in real-time. If the latter is not the case (I don't think every 3 minutes makes it worth your trouble) it can be viable.

I will remove the view, make it a table that update every 10 min, to see if there is any performance increase.

Statistics: Posted by Ze_PilOt — 25 Feb 2014, 15:14