Performance bei vielen Selects gegenüber MySQL

Hallo,

Ich betreue/programmiere für ein Forschungsprojekt eine Anwendung die bisher auf MySQL aufsetzt. Hier erstmal ein grober Umriss:

  • Jeden Tag sammeln sich in der DB in 3 Tabellen (MyISAM) ca. 200k Messwerte (INSERT)
  • Abends/Nachts laufen Stored Procedures (SP) die für die Messwerte wiederum andere Ergebnisse berechnen (CURSOR über alle Daten des Tages (ca. 200k Zeilen), für jede Zeile ein Select). Diese SPs laufen je nach Datenlage 2-4 Stunden.
  • Auf diesen Ergebnissen setzt wiederum eine SP auf, die den Nutzern über Parameter bestimmte Wertebereiche berechnet bzw. anzeigt (Mehrere Selects). Diese SP läuftca. 20-30s (Nutzer sind gern mal genervt, dass sie diese SP oft nutzen und jedesmal warten müssen)

Meine eigentliche Frage: Die SPs die wir benutzen haben wir in den letzten Wochen so optimiert, dass sehr viel mehr nicht mehr rausgeholt werden kann ohne größeren Aufwand (v.a. mehr Hardware, mehr cores).

Inwieweit ist die Performance von SPs in PostgresSQL allgemein vergleichbar mit MySQL? Können signifikante Performanceverbesserungen erzielt werden? Wie hoch ist allgemein der Aufwand SPs nach PGSQL zu portieren?

Ich finde oft nur vergleiche die auf Szenarien basieren die für uns nicht passen, wie Webanwendungen, Blogs, Foren, etc.

Viele Grüße,

Fabi

Das kann man so nicht beantworten.
Die einzig seriöse Art das rauszukriegen ist einen Test auf der Hardware laufen zu lassen die in Produktion verwendet wird.

Wie hoch ist allgemein der Aufwand SPs nach PGSQL zu portieren?

Den würde ich als eher hoch einstufen, da beide doch sehr unterschiedliche Konzepte haben.

Wie castorp sagt, das ist so nicht vergleichbar.
Der Portierungsaufwand hängt sicher auch davon ab, wieviel MySQL-spezifische Sachen ihr verwendet habt.

Auf meiner Webseite gibt es ein paar Vorträge zu dem Thema: Writings Andreas ‘ads’ Scherbaum

Ja, solche antworten hatte ich erwartet. Es wird anscheinend doch ein Spaß für die ganze Familie :slight_smile:

Den Vortrag fand ich schonmal ganz interessant. Manchmal habe ich gejubelt, manchmal fast geweint (Datumsfunktionen!)

Ich werde wohl einen ersten Teil der MySQL DB mal portieren um einen Performancevergleich zu bekommen und mich dann mal mit dem Ergebnis in ein paar Tagen zurückmelden.

Du hast MyISAM-Tabellen?

Nun, lasse einfach das Backup laufen und fahre in dieser Zeit Selects.

Wir können bei uns quasi die Uhr danach stellen, daß dann unser Nagios die Röteln bekommt. Mit der Zeit weiß man, welche Kunden zu welcher Zeit Backup machen. Dann klingelt (wenn man Bereitschaft hat) das Nagios-SMS-Handy. Ich habe erst die letzten Monate so richtig gelernt, wie Scheiße MySQL ist, insbesondere mit MyISAM.


Andreas

Hallo,

wie versprochen melde ich mich zurück. Vorab: ich bin schon etwas enttäuscht von der performance und erhoffe mir evtl. noch ein paar Tipps.

Das Testszenario

  • Als Testhardware dient mein Laptop (i52410m, 4GB RAM, Win7)
  • MySQL Version: 5.5
  • Postgres Version: 9.0.4
  • Die Testanwendung ist in Java geschrieben und bindet beide Datenbanken per JDBC an.
  • Die Anwendung nutzt Threads. Es laufen immer 3 Worker Threads gleichzeitig.
  • Das führt dazu das ich 3 Postgres Prozesse mit je 25% CPU LAst habe, bzw. einen mysqld Prozess mit 75%
  • Auf dem 4 logischen Core nimmt sich java 1-3% CPU
  • Die Worker Threads durchlaufen mehrere Zählschleifen um am Ende eine Stored Procedure (SP) mit 6 double und einer String Variable aufzurufen

Hier die MySQL SP (Die Namen aller Variablen und Bezeichnungen habe ich anonymisiert)

CREATE DEFINER=root@localhost PROCEDURE analyze(IN minOne DOUBLE, IN maxOne DOUBLE, IN minTwo DOUBLE, IN maxTwo DOUBLE, IN minThree DOUBLE, IN maxThree DOUBLE, IN dataType VARCHAR(5))

BEGIN

SELECT
SUM(IF(specialValue = 1, 1, 0) / COUNT(*)),
SUM(resultValue),
AVG(resultValue),
STD(resultValue),
VARIANCE(resultValue)
FROM Analysis
WHERE
firstValue BETWEEN minOne AND maxOne
AND secondValue BETWEEN minTwo AND maxTwo
AND thirdValue BETWEEN minThree AND maxThree
AND origin = dataType;

END

Die dazugehörige Tabelle “Analysis” in der MySQL Version:

CREATE TABLE Analysis (
measured TIMESTAMP NULL DEFAULT NULL,
firstValue DOUBLE UNSIGNED NULL DEFAULT NULL,
secondValue DOUBLE UNSIGNED NULL DEFAULT NULL,
thirdValue DOUBLE NULL DEFAULT NULL,
specialValue TINYINT(1) UNSIGNED NULL DEFAULT NULL,
resultValue DOUBLE NULL DEFAULT NULL,
origin VARCHAR(5) NULL DEFAULT NULL
)
COLLATE=‘utf8_general_ci’
ENGINE=MEMORY
ROW_FORMAT=DEFAULT

Und hier die selbe function in Postgres (Feedback welcome!)

CREATE OR REPLACE FUNCTION analyze(“minOne” double precision, “maxOne” double precision, “minTwo” double precision, “maxTwo” double precision, “minThree” double precision, “maxThree” double precision, “dataType” character varying)
RETURNS SETOF record AS
$BODY$

BEGIN

RETURN QUERY
SELECT
CAST(SUM(CASE WHEN specialValue = 1 THEN 1 ELSE 0 END) AS double precision) / CAST(COUNT(*) AS double precision),
SUM(resultValue),
AVG(resultValue),
STDDEV(resultValue),
VARIANCE(resultValue)
FROM Analysis
WHERE
firstValue BETWEEN $1 AND $2
AND secondValue BETWEEN $3 AND $4
AND thirdValue BETWEEN $5 AND $6
AND origin = $7;

RETURN ;

END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION analyze(double precision, double precision, double precision, double precision, double precision, double precision, character varying) OWNER TO postgres;

Und hier die Tabelle Analysis in der Postgres Version (mit einem zusätzlichen Primary Key):

CREATE TABLE analysis
(
measured timestamp without time zone,
firstValue double precision,
secondValue double precision,
thirdValue double precision,
specialValue integer,
resultValue double precision,
origin character varying(5) DEFAULT NULL::character varying,
id serial NOT NULL,
CONSTRAINT id PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE analysis OWNER TO postgres;

Ergebnis:

Die Tabellen wurden jeweils mit den gleichen 12000 Zeilen befüllt. Die Worker Threads bekamen die gleiche Payload.

Die MySQL Variante ist in 50min durchgerechnet

Die Postgres Variante brauchte über 5 Stunden.

Interpretation

Also ich glaube, dass MySQL bei diesem Anwendungsfall mit dem MEMORY Table punkten kann. Da es in Postgres ja
keine unterschiedlichen Tabellentypen gibt, schien es mir eine gute Idee effective_cache_size, shared buffers und work_mem zu erhöhen,
was aber offensichtlich nicht viel gebracht hat.

Da ich ncoh sehr wenig Erfahrung mit Postgres habe, bin ich für Feedback dankbar.

Viele Grüße,

Fabi

Also vergleichst du Äpfel mit Tomaten? Super Konzept.

Schau dir mal temporäre Tabellen an, die könnten dir ein Stück weiterhelfen.
Außerdem sind dir deine Daten nicht wirklich wichtig, oder? Schließlich verwendest du MEMORY als Tabellentyp - obwohl du in deinem Eingangsposting von MyISAM gesprochen hast.

Der MEMORY Table wird aus den MyISAM Tabellen befüllt (Kopie) und nur zur Analyse benutzt. Die Ergebnisse landen dann wieder in MyISAM Tabellen. Das habe ich tatsächlich vergessen zu erwähnen im Ausgangsposting. Sorry dafür.

Du schreibst von temporären Tabellen, was ist das Konzept dahinter? Können temporäre Tabellen wesentlich schneller sein?

Sicher können sie das. Es sind keine Einträge im Transaktionslog notwendig.
Außerdem kann man mit passenden Einstellungen die Daten - oder jedenfalls einen guten Teil davon - im Speicher halten.

PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE TABLE
PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Resource Consumption

Über wieviele Daten sprichst du da eigentlich im Endausbau?
Und über wieviele Reports, die du da laufen lässt?

Ja, das liest sich schon so als ob es dem ganzen nochmal einen Schub geben könnte. Ich werde es mal ausprobieren.

effective_cache_size, shared buffers und work_mem hab ich schon entsprechend erhöht.

Für einen Tag füllt sich der Analysis Table je nach Datenlage mit 10k -15k Zeilen. Darauf laufen zwischen 30 und 50 Reports, je nach Nachfrage. Die Reports werden so in ihrem Wertebereich in Zusammenarbeit mit den Benutzern beschnitten, dass die Gesamtheit ca. 12 Stunden Rechenzeit (auf dem bisherigen MySQL Setup) nicht überschreitet, das reicht aktuell für die Bedürfnisse der Nutzer, kann aber mehr werden. Die Betreuung der Nutzer verhindert effektiv, dass stundenlang “Quatsch” berechnet wird und Rechenzeit verschwendet wird.

Hast Du gar keine Indexe?

Hast Du Dir mal den Plan angeschaut?


Thema temp. Tabellen etc.: PG 9.1 bringt unlogged Tables mit, das könnte für Dich u.U. recht nützlich sein. Aber darauf brauchst erst mal nicht zu warten, ein EXPLAIN ANALYSE und dann das, was in Deiner Function drin steht. Ich sehe da schone WHERE-Conditions, aber halt keine Indexe auf den Spalten.


Andreas

Da bin ich mir nicht ganz so sicher - werden diese Tabellen in anderen Transaktionen / Verbindungen benötigt oder NUR in der grad laufenden Session?


Andreas

Nur in der gerade laufenden - aber er will ja quasi Qubes für verschiedene Benutzer vorberechnen, damit die dann darauf ihre eigentlichen Reports fahren können.

Hi,

Ich habe heute morgen das ganze auf temporäre Tabellen umgestellt und Indizes für firstValue, SecondValue, ThirdValue, resultValue und origin angelegt und einen neuen Testlauf gestartet.

Es lief gerade in 22min durch. Vielen Dank für die Tipps. Ich hab gerade nicht so viel Zeit, da ich nen vollen Tag heute hab. Ich schreibe nochmal was wenn ich zuhause bin heute Abend.

Na schau an, MySQL braucht 50 Minuten, PG 22.

Andreas

Hallo,

Ja ich bin wirklich sehr positiv überrascht, dass Postgres SO zugelegt hat. Bisher habe ich ja nur den Kern migriert um den Vergleich machen zu können. Jetzt werd ich wohl auch die Zeit investieren den Rest “rüber” zu bringen. Danke nochmal für die Tipps.

Abschließend hier nochmal der Temporary Tabel der jetzt von jedem Worker Thread erst angelegt und befüllt wird bevor er anfängt zu rechnen.

CREATE TEMPORARY TABLE analysis
(
measured timestamp without time zone,
firstValue double precision,
secondValue double precision,
thirdValue double precision,
specialValue integer,
resultValue double precision,
origin character varying(5) DEFAULT NULL::character varying,
id serial NOT NULL,
CONSTRAINT id PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE analysis OWNER TO postgres;

CREATE INDEX “firstValueIndex”
ON analysis
USING btree
(firstValue);

CREATE INDEX “secondValueIndex”
ON analysis
USING btree
(secondValue);

CREATE INDEX “thirdValueIndex”
ON analysis
USING btree
(thirdValue);

CREATE INDEX “originIndex”
ON analysis
USING btree
(origin);

CREATE INDEX “resultValueIndex”
ON analysis
USING btree
(resultValue);