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