Seite 1 von 2 12 LetzteLetzte
Ergebnis 1 bis 10 von 16

Thema: Performance bei vielen Selects gegenüber MySQL

  1. #1
    Fabi ist offline Neuer Benutzer
    Registriert seit
    20.07.2011
    Beiträge
    7

    Frage 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
    Geändert von Fabi (20.07.2011 um 11:22 Uhr) Grund: typos

  2. #2
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    525

    Standard

    Zitat Zitat von Fabi Beitrag anzeigen
    Inwieweit ist die Performance von SPs in PostgresSQL allgemein vergleichbar mit MySQL? Können _signifikante_ Performanceverbesserungen erzielt werden?
    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.
    A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

  3. #3
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    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
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  4. #4
    Fabi ist offline Neuer Benutzer
    Registriert seit
    20.07.2011
    Beiträge
    7

    Standard

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

    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.

  5. #5
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von Fabi Beitrag anzeigen
    Ja, solche antworten hatte ich erwartet. Es wird anscheinend doch ein Spaß für die ganze Familie

    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
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  6. #6
    Fabi ist offline Neuer Benutzer
    Registriert seit
    20.07.2011
    Beiträge
    7

    Standard

    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

  7. #7
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    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.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  8. #8
    Fabi ist offline Neuer Benutzer
    Registriert seit
    20.07.2011
    Beiträge
    7

    Standard

    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?

  9. #9
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    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?
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  10. #10
    Fabi ist offline Neuer Benutzer
    Registriert seit
    20.07.2011
    Beiträge
    7

    Standard

    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.

Seite 1 von 2 12 LetzteLetzte

Ähnliche Themen

  1. Like mit vielen werten
    Von Painstation im Forum SQL
    Antworten: 4
    Letzter Beitrag: 26.07.2010, 09:17
  2. 2 Selects verknüpfen
    Von msamesch im Forum SQL
    Antworten: 3
    Letzter Beitrag: 28.10.2009, 12:55
  3. Performance bei parallelen SELECTs
    Von henry.hinze im Forum Konfiguration
    Antworten: 1
    Letzter Beitrag: 16.02.2007, 12:58
  4. Antworten: 1
    Letzter Beitrag: 04.02.2007, 13:31
  5. MySql-->Postgresql ==> schlechtere Performance
    Von hretif im Forum Migration
    Antworten: 9
    Letzter Beitrag: 09.11.2006, 18:37

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum