Hallo!
Danke für die vielen INfos!
Yo, sorry, bin ich euch paar Infos schuldig geblieben.
Version
Version string PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
Version number 9.1
Grundkonfiguration postgresql
Ich hab keine Änderungen der Standard-Konfiguration vorgenommen.
Execution Plan
Also mit Zeiten kann ich keinen liefern, weils zu lange dauert. Und nur das Explain ist nicht aussagekräftig, weil ok, der Select wird kurz erklärt, aber das aufwändig dabei ist ja der Insert, der dort nur als eine einzelne Blackbox drin vorkommt.
Warum ich die ganze Tabelle weghaue
Das würde eigentlich gar nicht machen wollen, aber es kommt ein neues Feld im fütternden Select dazu, weshalb ich alles updaten muss. Ansonsten habe ich ursprünglich eh einen Trigger auf der Tabelle drauf gehabt, wobei sich das auch als doof herausgestellt hat, weil das auch bei jeder Preisänderung Alarm schlägt. Um den Trigger würde ich mich in weiterer Folge kümmern, weil sonst hab ich bei jedem Preisupdate wieder das selbe Problem, obwohl sich in der fulltext nicht mal was ändert.
"da die Tabelle berechenbar ist, könntest Du diese, wenn Du das schon so machst, als ‘unlogged table’ anlegen, falls Du 9.1 hast"
Danke! Interessante Info! Auf die Idee bin ich noch nicht gekommen. Werde ich zusätzlich noch zu dem unten stehenden ausprobieren.
was für eine Shop-Software verwendet PostgreSQL?
Zu 100% customized selbst entwickelt.
warum hostest Du sowas nicht bei uns?
Ihr wollt unbedingt, dass euch euren Server lahmlege?
…coalesce(var1, var2, var3) …und "array_to_string(array_agg(t),’ verwenden
Danke für die Hinweise, werde ich mir auch alles anschauen. Ich hab sonst eher mit mySQL zu tun gehabt und da gibts das IFNULL und das GROUP_CONCAT - da hab ich die passenden Pendants für die PostgreSQL gesucht und die oben stehenden Lösung gefunden. Aber ich lern gern dazu, danke!
"Aber mit passenden Parametern in der postgresql.conf"
Gibts ein paar Schlagworte für mich, was ich genauer anschauen soll? Die Doku kann ich dann eh selbst lesen, ein paar Stichworte wären nett. Bin wie gesagt jetzt nicht so auf postgres spezialisiert.
[HR][/HR]
Übrigens hab ich diverse andere Dinge inzw. probiert. Und zwar auf den Verdacht hin, dass es das ganze Transaktionslog sein könnte, hab ich mittels einer stored procedure versucht, Zeilenweise zu inserten. Hatte genau den selben Effekt. Dann bin ich so weit gegangen, dass ich mittels einem pg_sleep() das ganze verlangsamt habe. Ich bin dabei auf bis 0,5 sec pro Insert raufgegangen, was natürlich bei 1,3 Mio Datensätzen absolut unbrauchbar wär:
create or replace function updatefulltext() returns integer as $$
DECLARE myrecord RECORD;
begin
for myrecord in (
select
a.id,
to_tsvector('simple',
array_to_string(array[
CASE WHEN v.titel IS NOT NULL THEN v.titel
WHEN g.titel IS NOT NULL THEN g.titel
ELSE a.titel
END ,
a.komponist,
a.interpret,
v.darsteller,
a.verlag
],' ')
|| ' ' ||
array_to_string(array (SELECT autor FROM autoren WHERE recref=a.recref),' ')
) as fulltext
from articles a
left join video v on v.ean=a.ean
left join games g on g.ean=a.ean
) LOOP
begin
insert into fulltext (id,fulltext) values (myrecord.id,myrecord.fulltext);
execute pg_sleep(0.5);
exception when division_by_zero then return 0;
end;
END LOOP;
return 1;
end;
$$ language plpgsql;
Und wisst ihr was? Trotz 0,5 sec war der shop wieder nicht benützbar. Ein simples select count(*) from anderetabelle hat viele, viele Sekunden gebraucht. Irgendwie zweifle ich an der Postgres. Weil DAS darf ja nicht sein. So falsch konfiguriert kann auch die Standard-Installation der postgres nicht sein. Ausserdem ist sie ja sonst beim Lesen superschnell.
ABER: Ich hab inzw. weiter probiert und bin auf diese Lösung gestossen:
Red Hat Magazine | Tips and tricks: Memory storage on*PostgreSQL
Es macht mich traurig, dass die postgres keinen engine=memory-Flag kennt. Auch hier ist die scheinbare “Spielzeug-Datenbank” mySQL um Welten voraus. Egal, das ist jetzt nicht das Thema. Jedenfalls hab ich die RAM-Disk angelegt. Und hab hier Schritt für Schritt ausgeführt:
-- CREATE TABLESPACE temptablespace LOCATION '/mnt/ramdisk/pgdata';
-- GRANT CREATE ON TABLESPACE temptablespace TO postgres;
/*
CREATE TABLE fulltexttemp
(
id integer NOT NULL,
fulltext tsvector,
CONSTRAINT fulltexttemp_pkc PRIMARY KEY (id )
)
WITH ( OIDS=FALSE )
TABLESPACE temptablespace;
*/
-- ALTER TABLE fulltexttemp OWNER TO postgres;
/*
insert into fulltexttemp (id,fulltext) (
select
a.id,
to_tsvector('simple',
array_to_string(array[
CASE WHEN v.titel IS NOT NULL THEN v.titel
WHEN g.titel IS NOT NULL THEN g.titel
ELSE a.titel
END ,
a.komponist,
a.interpret,
v.darsteller,
a.verlag
],' ')
|| ' ' ||
array_to_string(array (SELECT autor FROM autoren WHERE recref=a.recref),' ')
) as fulltextdata
from articles a
left join video v on v.ean=a.ean
left join games g on g.ean=a.ean
)
*/
CREATE INDEX fulltexttemp_gin_index
ON fulltexttemp
USING gin
(fulltext )
TABLESPACE temptablespace;
-- alter table fulltexttemp set tablespace pg_default;
-- alter index fulltexttemp_gin_index set tablespace pg_default;[color=#333333]
-- offen: tablespace wieder löschen von ramdisk; automaisches mounten bei reboot[/color]
Die Load saust zwar auch wieder wieder rauf, aber es gibt einen entscheidenden Unterschied: Der Shop wird zwar langsamer, aber er ist weiterhin bedienbar und läuft nicht in die timeouts rein.
Ich werde mich wohl mit dieser Lösung anfreunden. Und einen Schalter in den _Shop einbauen, ob ich jetzt die Tabelle fulltext1 oder fulltext2 verwende, wobei ich immer eine verwende und die andere in der nacht im zuge des allgemeinen updates neu anlege.
Ganz glücklich ich mit der Postgresql nicht. Das kostet mich jetzt schon Tage. Ich muss jede Update-Routine extra überarbeiten, damit sie mir nicht den Server lahmlegt. Was ich nicht verstehe und vielleicht aufgeklärt werden kann:
update tablename set meinfeld=17 <-- wenn ich das bei der postgres 5x hintereinander ausführe, sind immer alle rows affected und es ist jedes Mal langsam. wenn ich das selbe unter mysql ausführe, sind nur die rows affected, die vorher einen anderen wert drin haben. die mysql schreibt nix, wo es nix zum schreiben gibt. beim 2. mal ausführen dauert das nur noch einen einstelligen ms-Wert und die Festplatte blitzt vielleicht noch kurz auf, während bei der Postgres wieder megabyteweise Zeug auf Platte schremmt.
Deshalb hab ich die ganzen updates so umbauen müssen, dass ich vorher selbst mittels subselects rausfinde, welche datensätze sich überhaupt geändert haben. ziemlich umständlich. Muss man das so machen? Bei mySQL hab ich mich da noch nie darum gekümmert.
Danke!