Mal wieder ein Performance problem!

Guten Morgen,


vorweg - ich hab bereits das Anfänger Tutorial gelesen und die buffer (shared,effectiv…) sind bereits bei 1GB bzw. 2GB.
Dennoch erhalte ich eine inakzeptable query-performance (bis zu 10sec pro Query).
Nachdem die Anfrage zwar gestartet wurde hab ich diese nun im Cach und diese funktioniert dann entsprechend schnell aber nur für einen bestimmten namen.

Gibt es nicht eine Möglichkeit die Datenbank zu pre-Analysieren? für eine bestimmte Anfrage? (analyse table hab ich bereits drüber laufen lassen)

Tabelle: game_players
Größe der Tabelle: ca. 11GB
Index größe: 6,7 GB

Meine Anfrage:

SELECT
count(gp.game_id),
sum(gp.field1),
sum(gp.field2),
sum(gp.field3)
FROM 	game_players gp,
game g
WHERE g.game_id=gp.game_id AND
gp.player_id=(SELECT player_id FROM players WHERE name='bestimmter-name')

field1-3 sind smallint werte (0 oder 1).


Analyse für folgenden Query:

EXPLAIN ANALYSE
SELECT
count(gp.game_id)
FROM 	game_players gp,
game g
WHERE g.game_id=gp.game_id AND
gp.player_id=(SELECT player_id FROM players WHERE screen_name='test-name')

Antwort:

“Aggregate (cost=219035.37…219035.38 rows=1 width=4) (actual time=6678.428…6678.430 rows=1 loops=1)”
" InitPlan"
" → Index Scan using player_idx_01 on players (cost=0.00…8.29 rows=1 width=4) (actual time=11.186…11.189 rows=1 loops=1)"
" Index Cond: (screen_name = ‘test-name’::text)"
" → Nested Loop (cost=401.54…218982.37 rows=17883 width=4) (actual time=587.764…6640.991 rows=18024 loops=1)"
" → Bitmap Heap Scan on game_players gp (cost=401.54…65656.03 rows=17883 width=4) (actual time=575.612…3079.928 rows=18024 loops=1)"
" Recheck Cond: (player_id = $0)"
" → Bitmap Index Scan on game_players_idx_05 (cost=0.00…397.07 rows=17883 width=0) (actual time=563.715…563.715 rows=18024 loops=1)"
" Index Cond: (player_id = $0)"
" → Index Scan using game_idx_01 on game g (cost=0.00…8.56 rows=1 width=4) (actual time=0.188…0.190 rows=1 loops=18024)"
" Index Cond: (g.game_id = gp.game_id)"
“Total runtime: 6678.545 ms”

Betriebsystem: Ubuntu 64 Bit. 4GB RAM / AMD 6000+ Dual-Core.

Was mich wundert, meine CPU Auslastung ist nur bei 5-10%? :confused:

Hm würde das nicht funktionieren…, wenn ich einfach für 100.000 verschiedene Namen einen Query starte (einfach ein programm schreiben das genau das macht), um die Querys in den Cach zu laden? Die Daten verändern sich für ca. 1 woche nicht, und genau das 1x pro Woche machen?


Dann hätte ich eine weitere Frage, wie man es vergünstigt, dass der Cach genau für solche Anfragen voll bleibt - bzw. gibt es einen Zeitraum, wann sich der Cach leert (sollte es eig. nicht geben!).

Wenn Du das für alle IDs brauchst, und das für eine Woche gültig ist, warum erstellst Du dann nicht eine table als Resultat eines Selects?

create table cache as select game_id, count(1), sum(), sum(), … from … group by game_id;
[Syntax jetzt nicht getestet, nur grob skiziert]

Auf die Spalte game_id dann einen Index und fertisch.

Andreas

Hi hab ich mir auch überlegt, ob das funktioniert? Hm okay ich werd das heute abend ausprobieren. Erstmal danke für deine Antwort!

Okay scheint zu funktionieren, jedoch hab ich folgendes Problem:

Wie folgt generiere ich die Tabelle: (funktioniert perfekt).

create table cache as 
select 
game_id,
count(gp.game_id) as count, 
sum(gp.field1) as field1
sum(gp.field2) as field2
sum(gp.field3) as field3
FROM game_players gp
group by game_id;

Jetzt hab ich den Query für die Abfrage:

SELECT
count(gp.game_id),
sum(gp.field1),
sum(gp.field2),
sum(gp.field3)
FROM 	cache gp,
game g
WHERE g.game_id=gp.game_id AND
gp.player_id=(SELECT player_id FROM players WHERE name='bestimmter-name')

Problem ist natürlich jetzt, dass die informationen für count komplett flöten gehen …
PS: Die Anfrage kann ich leider nicht abändern, da ich da nicht ran komme. Gibt es da irgendwelche tricks?

Hä?

PS: Die Anfrage kann ich leider nicht abändern, da ich da nicht ran komme. Gibt es da irgendwelche tricks?

Bitte?

Andreas

ich bearbeite eine Datenbank, die von einem externen Programm benutzt wird.

Okay, das werde ich vermutlich mit einem View lösen können.

Aber bitte nochmal kur zurück zu meinem Problem: Die Tabelle hab ich mit

group by player_id,game_id erstellt. Ein index mit:

CREATE INDEX cache_index01_playerid_gameid
ON “cache”
USING btree
(player_id, game_id);


Jedoch erhalte ich keine deutlichen performance vorteile.