Kann ich ein Query "nicen" - weniger Priorität geben? Legt Server komplett lahm :(

Hallo!

Ist mein erstes Posting hier! Willkommen an alle andere, würde mich freuen, wenn ich zukünftig auch etwas beitragen kann, hier jemandem zu helfen. Angemeldet habe ich mich natürlich deshalb, weil ich gleich ein blödes Problem habe. :frowning:

Ich hab das Problem, dass die Postgres beim Schreiben (insert oder update) extrem langsam ist, bzw. die IO-Zugriffe so arg sind, dass der Server lahmgelegt wird. Das Problem hatte ich auch schon bei meiner lokalen Installation unter Windows (superschnelle CPU, 8 GB RAM), als auch auf dem Produktivserver (Linux, 64bit, superschnelle Kiste, mehr als genügend RAM).

Wenn ich unten stehende Script starte (wobei der Inhalt jetzt eh weniger eine Rolle spielt), dann knallt die load auf dem Server auf über 5 rauf, CPU-Auslastung rattert gemütlich bei ca. 20% hin. Offenbar sinds die ganzen IO-Zugriffe, die den Server lahm legen. Mit lahm legen mein ich, dass der gesamte Online-Shop offline geht. Tot.

Kann ich solche Update-Statements irgendwie priorisieren? "Nice"n? Kann ich sagen, er darf nicht mehr als 1000 inserts pro sekunde machen?

Konkret geht um den folgenden Block. Wenn ich da statt dem INSERT ein SELECT COUNT(*) FROM … herummache, dann seh ich, dass der innere select 1,1 sec dauert und mit 944.000 datensätze liefert. aber sobald ich einen insert mache, ist das system tot.

BEGIN;


TRUNCATE TABLE fulltext;


insert into fulltext (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),' ')
   )    
   from articles a
   left join video v on v.ean=a.ean
   left join games g on g.ean=a.ean
);


COMMIT;

Die fulltext-tabelle schaut so aus:

CREATE TABLE fulltext(
  id integer NOT NULL,
  fulltext tsvector,
  CONSTRAINT fulltext_pkc PRIMARY KEY (id )
)

und hat einen “GIN”-index auf dem fulltext-feld.

CREATE INDEX fulltext_gin_index
  ON fulltext
  USING gin
  (fulltext );

Ich bin echt am Verzweifeln. Es würde alles perfekt funktionieren, aber ich kann einfach kein Update des Suchindexes mehr machen. :frowning: (((

Wie sieht der Anfrageplan für diese Anfrage aus?
Welche PostgreSQL Version?
Welche Konfigurationsänderungen wurden vorgenommen?

Neben den Fragen von ads:

  • warum haust Du jedesmal die ganze Tabelle weg, ich unterstelle mal, daß von den 944.000 Datensätzen sich nur wenige % geändert haben. Also könntest Du mit einer besseren Logig da sicher einiges machen, z.B. einem TRIGGER auf Änderungen auf den 3 Tabellen anstatt mit TRUNCATE immer alles wegzuwerfen
  • da die Tabelle berechenbar ist, könntest Du diese, wenn Du das schon so machst, als ‘unlogged table’ anlegen, falls Du 9.1 hast
  • was für eine Shop-Software verwendet PostgreSQL?
  • warum hostest Du sowas nicht bei uns?


    Andreas

Btw.: vielleicht ginge das etwas schneller, wenn Du statt Deinem CASE-Konstruct coalesce(var1, var2, var3) verwendest und statt dem Hin- und hergecaste der ARRAYs mal “array_to_string(array_agg(t),’ ') from …” probierst. Aber das grundlegende Problem ist die Neuerstellung der großen Tabelle incl. WAL und dem Neuberechnen des Indexes.


Andreas

Und um die Frage allgemein zu beantworten: Nein. Aber mit passenden Parametern in der postgresql.conf lassen sich so manche Schmerzen lindern. Daher auch die Fragen von ads.


Andreas

Hallo!

Danke für die vielen INfos! :sunglasses:

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? :sunglasses:

…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! :slight_smile:

"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. :angry: 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!

Dann ist das aber klar ein Fehler von MySQL. Du sagst “Alle rows updaten”, wenn MySQL dann nicht alle ändert, ist das schlichtweg falsch.
Ich kenne keine Datenbank die bei update tablename set meinfeld=17 nicht alle rows ändern würde. Schliesslich fehlt da ein WHERE und damit sind halt dann alle rows gemeint.

Naja, falsch ists nicht, nachdem ja das selbe rauskommt. Ein kleiner Vergleich, obs überhaupt notwendig ist, den Records mit all seinen Indexen & Co neu zu schreiben, ist halt eine Optimierungsmassnahme. Das kommt konkret bei mysql raus:

update jenstest set ctr=27;
/* 210.263 rows affected, 0 rows found. Duration for 1 query: 1,279 sec. */
update jenstest set ctr=39;
/* 210.263 rows affected, 0 rows found. Duration for 1 query: 1,248 sec. */
update jenstest set ctr=39;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,483 sec. */
update jenstest set ctr=39;
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,484 sec. */
update jenstest set ctr=23;
/* 210.263 rows affected, 0 rows found. Duration for 1 query: 1,248 sec. */
update jenstest set ctr=23; 
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,483 sec. */

ch finds schon “nett” von der mySQL, dass sie beim Überschreiben mit dem selben Wert nur noch ein Drittel der Zeit braucht. In der Postgres hätte ich jetzt noch dazuschreiben müssen: WHERE jenstest.id NOT IN (select id from jenstest where ctr<>27) damit ich einen Performanceschub kriege.

Schlecht. Du solltest da was machen, ganz echt. Es gibt massig Doku dazu. Wichtig wäre z.B. shared_buffers hochzunehmen, 25% RAM ist ein guter Start, maintenace_work_mem hoch, damit die Indexe schneller erstellt werden, checkpoint_segments hoch, …

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.

Wir (also PostgreSQL) kann conditional trigger.

“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? > > :sunglasses: >

Wer spricht von ‘unserem’ Server’? Ihr bekommt einen Mitserver, und wenn der nicht reicht, einen größeren. Ich würde Dir dann auch nicht mehr helfen, mache ich ja nur, solange Du bei der Konkurrenz bist, damit Du dort keinen größeren Server kaufst und die sich dumm und dusselig verdienen, Du verstehst?

…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! > > :slight_smile: >

“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**> . >

Kann es sein, daß Du über Dein TRUNCATE stolperst? Das sperrt Dir Deine table exclusive.
\
<br>
> Ein simples select count(*) from anderetabelle >

Ein select * ist nicht simpel, zumindest nicht in einer Welt von Transaktionen.
\
<br>
> hat viele, viele Sekunden gebraucht. Irgendwie zweifle ich an der Postgres. Weil DAS darf ja nicht sein. > > :angry: > 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](http://magazine.redhat.com/2007/12/12/tip-from-an-rhce-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:
>
> ```text
-- 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!

Andreas

Sodala, ich wollte mich nochmals bedanken.

Ein Arbeitstag ist vergangen, es ist fast 21 Uhr mit dem Resultat, dass ich eine Tabelle updaten kann. Juhuuuu, das nenn ich mal einen produktiven Arbeitstag. :wink:

Falls mal jemand darüber stolpert, hier meine Lösung: Ich hab zwei von diesen Tabellen: fulltext1 und fulltext2. Im Shop ist irgendwo verzeichnet, welche gerade verwendet wird. Das Update-Script verwendet dann immer die andere tabelle.

Folgende Dinge hab ich gemacht:

  1. Beim Booten wird die RAM-DISK lt. dem oben angeführten Link angelegt. Hab mich einfach beim start/stop-Script des Shop-Daemons in /etc/init.d eingeklinkt:
       #######################################################################
       # RAM-DISK anlegen fuer temporaere Tabellen XXXXXX  20.12.2011
       #######################################################################
       if [ -d /mnt/ramdisk/pgdata ]
         then
           echo "RAM-Disk bereits angelegt"
         else
           echo "RAM-Disk wird angelegt ...."
           mount -t ramfs none /mnt/ramdisk
           mkdir /mnt/ramdisk/pgdata
           chown postgres :p ostgres /mnt/ramdisk/pgdata
           chmod go-rwx /mnt/ramdisk/pgdata
           echo "Done."
        fi
        ######################################################################
  1. Meine fulltext1 und fulltext2-Tabellen neu angelegt, mit CREATE UNLOGGED TABLE. Danke für den Tipp. Achtung: Crasht die Datenbank und die Tabellen sind korrumpiert, sind sie hinüber. Ist aber nicht weiter tragisch - ists ja nur eine Hilfstabelle und ausserdem wird im Nicht-Update-Betrieb ja nix reingeschrieben.

    \

  2. Mein Update-script überprüft, ob es einen tablespace auf dieser ram-disk gibt, wenn ja, legt es ihn an.
    Prüfen, ob der Tablespace existiert: SELECT spcname FROM pg_tablespace where spcname=‘temptablespace’
    Falls nein, öffne ich eine Shell und dieses commando:
    pfad/zur/postgres/psql --username=meinpostgresuser <createtemptablespace.sql
    Im .sql-file steht das drinnen:
    CREATE TABLESPACE temptablespace LOCATION ‘/mnt/ramdisk/pgdata’
    Um keine Passwort-Abfrage zu kriegen, eine .pgpass-Datei im HOmeverzeichnis anlegen und das reingeben:
    ::::meingeheimespasswort
    Dass man hier ::: verwenden muss, und nichts konkreter angeben darf, hat mich sage und schreibe mindestens 2 Stunden gekostet.

  3. Dann lösch ich alle Inhalte aus meiner Arbeitstabelle und verschieb dann die leere Tabelle auf die RAM-Disk:

TRUNCATE TABLE fulltext1;
-- Tabelle auf RAM-Disk schieben:
ALTER TABLE fulltext1 SET TABLESPACE temptablespace;
-- Index auf RAM-Disk schieben:
ALTER INDEX fulltext1_gin_index SET TABLESPACE temptablespace;
-- Primaerschluessel auf RAM-Disk schieben:
ALTER INDEX fulltext1_pkc SET TABLESPACE temptablespace;

Was ich nicht mehr mache: Meinen GIN-Index löschen und nachher separat anlegen. Die RAM-Disk check das schon schnell genug, um das on-the-fly zu machen.

  1. Dann kommt mein SQL-Statement von oben, das STUNDENLANG lief und alles andere auf dem Server tötete.
    Und voila: Query returned successfully: 944624 rows affected, 67492 ms execution time.

  2. Dann schieb ich es wieder auf die Platte zurück:

ALTER TABLE fulltext1 SET TABLESPACE pg_default;
-- Index auf RAM-Disk schieben:
ALTER INDEX fulltext1_gin_index SET TABLESPACE pg_default;
-- Primaerschluessel auf RAM-Disk schieben:
ALTER INDEX fulltext1_pkc SET TABLESPACE pg_default;
  1. Ich vermerke im Shop, das ab sofort die andere Tabelle zu verwenden ist.

Eine Gefahr gibts noch: Wenn der Rechner rebootet wird, oder abstürzt, während eine fulltext-Tabelle gerade auf der RAM-Disk liegt - dann ist sie natürlich weg. :wink:

Jetzt geh ich nach Hause, mach mir zwei Bier gleichzeitig auf und trink noch ein Schnapserl. Danke fürs Mitdenken! Ganz besonders das “unlogged” hat mir noch wertvolle Zeit rausgeholt!

Es ist knapp vor 22 :astonished: o Uhr, mein Arbeitstag beginnt jetzt …

Andreas

falls dich das tröstet - mir sind aufm nachhauseweg grad noch 2 dinge für andere kunden eingefallen, die ebenfalls morgen fertig werden müssen. ich häng auch noch 2 stunden an meinen bisherigen 12-stunden-tag dran. GRRRRRRRRRRRRRRR :angry:

aber bald ich weihnachten und 3 wochen urlaub stehen bevor.

Ganz im Ernst: dann nimm doch wieder MySQL. Man sollte immer das Tool nehmen welches den Job besser erledigt.

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.

Warum benennst Du die Tabelle nicht einfach um? Da das in einer Transaktion läuft, wartet diese bis keine Zugriffe mehr stattfinden und die Anwendung kriegt von dem Switch gar nichts mit.

Allerdings glaube ich, dass bei deiner Konfiguration irgendwas schief ist. Wir haben einen online Shop am Laufen der. mehrere 1000 Page Impressions pro Minute mit PostgreSQL abfackelt. Und das bei gleichzeitigen Schreibe- und Lesezugriffen.

Die Standard-Konfiguration von Postgres ist (leider) vor allem für Systeme mit viel Last völlig ungeeignet.

Möglicherweise stimmt ja auch mit der Hardware was nicht. Unter Windows könnte ich das Verhalten noch verstehen, da NTFS bei hoher Last wirklich das System ausbremst. Unter Linux würde ich das allerdings nicht erwarten.

Möglicherweise ist MySQL auch so eingestellt, dass es die Daten gar nicht wirklich wegschreibt sondern nur so tut (fsync). Das könnte den Performance Unterschied schon erklären.

Du kannst ja mal probehalber (!!!) den Parameter fsync auf off umstellen.

Aber ACHTUNG: das kann zu Datenverlusten führen, wenn der Rechner mitten drin abschmiert. Wenn das aber die gleiche Performance liefert wie mit MySQL, dann ist das vermutlich einer der Gründe.
Aber Nochmal die Warnung: man sollte kein Produktivsystem so laufen lassen. Zur Problemeingrenzung könnte es aber helfen.

Mit Sicherheit solltest Du checkpoint_segments, work_mem und shared_buffers erhöhen. synchronous_commit = off könnte auch helfen.

Ich kann das Buch PostgreSQL 9.0 High Performance nur wärmstens empfehlen.

Wenn Du ein Produktivsystem mit PostgreSQL betreibst (vor allem einen Shop), musst Du Dich mit solchen Sachen auseinander setzen. Ein “Die Standard-Konfiguration wird schon passen” ist nicht die Richtige Herangehensweise.

Hallo!
Ja, das mit der Standard-Konfiguration werde ich auf jeden Fall auch noch anschauen und optimieren. Ging sich halt jetzt nicht alles innerhalb eines Tages aus. :slight_smile: Würde mich natürlich entsprechend freuen, wenn ich durch eine Konfigurationsänderung noch mehr Speed rausholen kann.

Das Umbenennen der Tabelle wär genauso möglich, aber es ist sogar für mich einfacher, einfach den Tabellennamen des JOINS in der Suchabfrage mal auf fulltext1 oder fulltext2 zu wechseln. Das funktioniert schon und ich seh jetzt keinen Vorteil vom Umbenennen. (Ich bin mir auch nicht sicher, ob ein Umbenennen der Tabelle tatsächlich transaktionsfähig durchführbar ist, weil eigentlich hört sich das schon stark nach DCL und nicht nach DML an. Ob da also nicht sogar im Gegenteil irgendein Lock-Table notwendig ist?)

Hardware ist alles OK - brandneuer Server, top Ausstattung. Und ich hatte bei mir lokal ja das selbe Problem. Die Probleme treten auch nur bei Massen-Updates auf. Sonst im normalen Betrieb gibts ja kein Problem. Ist nicht so, dass da zB so lächerliche Dinge wie Warenkorb-, Bestellungen-Operationen oder dergleichen nur den Funken eines Problems machen.

Aber ich gebs zu - ich taste mich derzeit bei der postgres im Dunkeln durch den Raum, bis ich die Türe ins Helle finde. Da wär natürlich das Buch (Danke für den Tipp!) schon hilfreich, weil die Online-Doku ist für eine Suche nach einer Strategie absolut unbrauchbar. Alleine die Doku über die fulltext-Suche ist dermassen durcheinander und unstrukturiert, dass ich ohne zusätzlichen Quellen nix zum Funktionieren gebracht hätte.

Hast du gerade geschrieben, das du Tabellen auf eine RAM-Disk legst?

Bitte bitte bitte geh wieder zurück zu MySQL und werde dort glücklich, bitte.
Du hast überhaupt nicht vor, irgendetwas richtig zu machen - du bastelst einfach nur ein bischen rum und was irgendwie mal ein bischen schneller wird, geht dann produktiv.

Ach und bezüglich Fulltext-Index. Der ist nicht dein Problem.
Dein wirkliches Problem ist, das du völlig ohne Vorwissen an die Sache ran gehst und einfach mal herumprobierst.
Mit Frust und Überstunden für dich und mit katastrophalen Folgen für deine Anwendung und Datenbank.

Ok, genug gemeckert, du gehst zu MySQL und ich gehe schlafen.

So sind ernsthaft die Super-Moderatoren hier in diesem Forum?! Kurz mal sinnlose Kritik auf persönlicher, teils beleidigender Ebene in den Thread reinkacken, anstatt nur irgendwas produktives beizutragen oder etwas konkretes sagen, warum die Lösung doch nicht toll ist, obwohl sie zu 100% das Problem löst?

Und ich mach mir auch noch die Mühe, das sogar noch extra genau zu beschreiben. Tolle Stimmung hier. Tolle Moderatoren!

Das glaube ich nicht, Tim :wink:
Dein Problem liegt IMHO nicht bei der Datenbank und auch nicht bei Postgres sondern bei der Anwendung selbst.
Warum willst du überhaupt die Datenbank wechseln? Unter MySQL scheint ja alles nach Wunsch zu funktionieren?


Jedenfalls lässt das wüstes Gebastel vermuten.

In der Tat. Ja.

Gruß Uwe

Wenn es im Code für Dich kein Problem ist dann hat die Lösung natürlich keinen echten Vorteil.

(Ich bin mir auch nicht sicher, ob ein Umbenennen der Tabelle tatsächlich transaktionsfähig durchführbar ist

Ja ist es. Alle Befehle bei Postgres (mit ein paar wenigen Ausnahmen wie CREATE/DROP DATABASE) sind transaktional.


weil eigentlich hört sich das schon stark nach DCL und nicht nach DML an.

DCL? Digital Command Language? Ich denke Du meinst DDL.


Ob da also nicht sogar im Gegenteil irgendein Lock-Table notwendig ist?

Ja, ein Lock ist notwendig so wie bei jedem Update. Aber das rename wartet einfach bis alle Zugriffe auf die Tabelle beendet sind. Wenn die Tabelle natürlich ständig abgefragt wird, dann ist die Lösung mit zwei Tabellen vermutlich einfacher - vor allem wenn es im Code keine Probleme macht.

Die Probleme treten auch nur bei Massen-Updates auf.

Ich würde bei den checkpoint/WAL Einstellungen anfangen. Vermutlich musst Du auch die VACUUM Einstellungen für die beiden Tabellen anpassen.

Noch eine Frage: die INSERTs laufen schon alle in einer Transaktion, oder? Bei Massen-Updates auf keinen Fall mit auto-commit arbeiten!