MySQL Datenbank zu langsam

[siddy81]

Hallo Leute,
Ich habe ein Problem mit der Geschwindigkeit einer MySql Datenbank.
Genauer gesagt eine der Tabellen enthält mehr als 20 Millionen Datensätze und ist seit sie eine gewisse Datenmenge (ca. 3,5 Millionen )überschritten hat übertrieben langsam geworden.


Woran könnte es denn liegen?
Wenn ich unter 3.5 Millionen Datensätze habe und die selbe Datenmenge übertrage wird die Ausgabe in weniger als 1 Sek angezeigt - und ist relativ Konstant. Liegt die Anzahl der Datensätze höher, bricht die Abfragedauer enorm ab. Teilweise bis zu 2 Minuten was einfach nicht mehr Nutzbar ist.

 

[Lukullus]

Welche Engine nutzt Du? MyIsam, InnoDB oder Berkeley?
Single oder Cluster?
Grundsätzlich gilt für jede Datenbank, daß mit der Größe die
Geschwindigkeit abnimmt, durchaus auch exponential.
Bei MySQL gibt es die freie Version, die nicht mehr weiter
Entwickelt wird, und die Bezahlversionen.
Als Alternative gibt es MariaDB von denselben Entwicklern.
Es gibt einige «Stellschrauben» an denen Du drehen mußt um
ein besseres Antwortverhalten zu erzielen.
Zusätzlich solltest Du die Abfrage genauestens überprüfen,
ob alle Parameter sich auf indizierte Felder beziehen.

 

[Lukullus]

Ist wohl nicht dringend!

Naja, ist wohl nicht dringend.
Auch das Betriebssystem spielt eine gewisse Rolle.
Nicht jeder Parameter wirkt bei jedem Linux gleich.

 

[Luckyze]

Hallo Leute,
Ich habe ein Problem mit der Geschwindigkeit einer MySql Datenbank.
Genauer gesagt eine der Tabellen enthält mehr als 20 Millionen Datensätze und ist seit sie eine gewisse Datenmenge (ca. 3,5 Millionen )überschritten hat übertrieben langsam geworden.


Woran könnte es denn liegen?
Wenn ich unter 3.5 Millionen Datensätze habe und die selbe Datenmenge übertrage wird die Ausgabe in weniger als 1 Sek angezeigt - und ist relativ Konstant. Liegt die Anzahl der Datensätze höher, bricht die Abfragedauer enorm ab. Teilweise bis zu 2 Minuten was einfach nicht mehr Nutzbar ist.

Kurz und bündig^^

https://technet.microsoft.com/de-de/library/ms177500(v=sql.105).aspx

 

[pyrosland.de]

Hatte ich auch mal...Über 100 Mio Datensätze und DB is abgekackt...

Einfach n Index setzen an der richtigen Stelle und dann flutscht es wieder.

 

[siddy81]

Die Abfrage läuft nur über die indizierten Felder. Selbst wenn ich die Abfrage nur über 1 einziges indiziertes Feld zu testzwecken laufen lasse gibt es keine Verbesserung.
Also die Idizies sind auf jeden Fall gesetzt - und auch nur an den benötigten Feldern. Es werden keine Indizies zuviel gesetzt.
Und es ist eine Bezahlversion der MySql Datenbank auf den Servern von Hetzner. Es ist KEIN V-Server sondern ein eigener mit hinreichend Trafficleistung.

Ich gehe davon auch aus dass es irgendwo ein Fehler ist den ich gemacht habe oder etwas übersehen habe

 

[Lukullus]

Hetzner

Da läuft in der Regel Linux. Welche Distribution ist nicht ersichtlich.
MySQL: Welche Engine benutzt Du?
Poste doch die Abfrage.
Es kann an der Abfrage oder an den MySQL-Einstellungen liegen.
Jede Engine hat eigene Einstellungen.
Bei InnoDB wird die Datenbanktabelle mit der ersten Abfrage initialisiert
(Memory-Tabellen), deshalb dauert diese etwas länger.
Kann beim Hochfahren durch eine SELECT 1 FROM tbl_name LIMIT 1 Abfrage schon erledigt werden.
Der Primärschlüssel sollte der vorgeschlagene Auto-Increment-Schlüssel sein.
Lange unique Primärschlüssel verlangsamen die Datenbank.
Die Arbeit sollte von MySQL erledigt werden und nicht erst in PHP oder einem
sonstigen Abfrageprogramm. Das heißt MySQL sollte genau das liefern was benötigt
wird. Bei manchen PHP-Funktionen wird das gewünschte Ergebnis erst nach Übergabe
massenhafter Daten herausgefiltert oder berechnet.
Mit $mysqlcheck kann man die Abfragen analysieren.
Server-Parameter Tuning:
Key-buffer-size
Table-cache
Für Abfragen mit Group by / Order by: read_rnd_buffer_size
Bei InnoDB: innodb_buffer_pool_size

 

[flaschenkind]

Selten soviel Halbwissen auf einen Haufen gelesen.

Bei MySQL gibt es die freie Version, die nicht mehr weiter
Entwickelt wird, und die Bezahlversionen.
Als Alternative gibt es MariaDB von denselben Entwicklern.

Falsch. MySQL wird wie gewohnt von Oracle weiterentwickelt. MariaDB wird von den "Erfindern" von MySQL als Fork von MySQL weiterentwickelt, weil diese wenig Vertrauen in Oracle als Maintainer von MySQL haben.

Zusätzlich solltest Du die Abfrage genauestens überprüfen,
ob alle Parameter sich auf indizierte Felder beziehen.

Nur der Bezug auf indizierte Felder reicht hier wohl nicht aus. Weitere Infos gibts hier

https://technet.microsoft.com/de-de/library/ms177500(v=sql.105).aspx

Hier ist die Rede von MySQL, was soll ihm da der Link zum MSSQLServer weiterhelfen?

Einfach n Index setzen an der richtigen Stelle und dann flutscht es wieder.

Wär ich vorsichtig mit. Mal eben einfach funktioniert nicht, man sollte sich schon mit der Materie genauer auseinandersetzen.Kannst mit nem "richtigen" Index auch deine Datenbank zum abrauchen bringen.

Die Abfrage läuft nur über die indizierten Felder. Selbst wenn ich die Abfrage nur über 1 einziges indiziertes Feld zu testzwecken laufen lasse gibt es keine Verbesserung.
Also die Idizies sind auf jeden Fall gesetzt - und auch nur an den benötigten Feldern. Es werden keine Indizies zuviel gesetzt.

Pro Query kann maximal ein Index verwendet werden

Und es ist eine Bezahlversion der MySql Datenbank auf den Servern von Hetzner. Es ist KEIN V-Server sondern ein eigener mit hinreichend Trafficleistung.

Was hat denn Traffic mit der Leistung deiner Datenbank zu tun? Auch nen V-Server kann wunderbare Leistungen vollbringen.

Ich gehe davon auch aus dass es irgendwo ein Fehler ist den ich gemacht habe oder etwas übersehen habe

Davon gehe ich ganz stark aus

Bei InnoDB wird die Datenbanktabelle mit der ersten Abfrage initialisiert
(Memory-Tabellen), deshalb dauert diese etwas länger.
Kann beim Hochfahren durch eine SELECT 1 FROM tbl_name LIMIT 1 Abfrage schon erledigt werden.

Was du wohl meinst sind die Caches bzw. Buffer und keine Memory-Tabellen, die den Datenbankinhalt von der Festplatte in den Arbeitsspeicher laden. Damit dies zuverlässig funktioniert ist vor allem eine richtige Serverkonfiguration und genügend Arbeitsspeicher nötig. So ein pauschaler Hinweis, wie du ihn gegeben hast hilft da auch nicht weiter bzw. hat keinen Effekt.

Der Primärschlüssel sollte der vorgeschlagene Auto-Increment-Schlüssel sein.

Warum?

Lange unique Primärschlüssel verlangsamen die Datenbank.

was sind denn lange Primärschlüssel? Wie definierst du "lang"?

Die Arbeit sollte von MySQL erledigt werden und nicht erst in PHP oder einem
sonstigen Abfrageprogramm. Das heißt MySQL sollte genau das liefern was benötigt
wird.

Kann man so pauschal auch nicht sagen. Oftmals mag das vielleihct zutreffen, aber auch hier gibt es gerne Ausnahmen, die es zu beachten gilt.

Bei manchen PHP-Funktionen wird das gewünschte Ergebnis erst nach Übergabe massenhafter Daten herausgefiltert oder berechnet.

Ist ja auch logisch. Wenn du PHP zur Berechnung der Daten verwendest müssen die entsprechenden Daten natürlich vorher gelade nworden sein und im Arbeitsspeicher vorliegen.[/QUOTE]

 

[apolle]

Selten soviel Halbwissen auf einen Haufen gelesen.

Das mußt Du positiv sehen: Es gibt hier Experten für jeden Mist. Sogar für Halbwissen...

 

[theHacker]

@siddy81:
Hast du die Datenbank denn schon mal gefragt, ob du zu doof bist oder ob es vielleicht an ihr liegt?
Stichwort EXPLAIN.

Manchmal wählt der Query-Optimizer auch einen dämlichen Weg. Wenn du schon schreibst, dass da viele Indexes liegen, vielleicht nimmt die DB den falschen. Ein Hint könnte da schon Abhilfe schaffen. Siehe oben.

 

[siddy81]

@siddy81:
Hast du die Datenbank denn schon mal gefragt, ob du zu doof bist oder ob es vielleicht an ihr liegt?
Stichwort EXPLAIN.

Manchmal wählt der Query-Optimizer auch einen dämlichen Weg. Wenn du schon schreibst, dass da viele Indexes liegen, vielleicht nimmt die DB den falschen. Ein Hint könnte da schon Abhilfe schaffen. Siehe oben.

Nein in der tat da war ich bisher "zu doof" dafür um zu schauen was der Query Optimizer da wirklich macht - hab schlicht nicht dran gedacht.

 

[440ci]

Ich habe noch nie einen "Query Optimizer" gebraucht, wenn es mal ein Problem geben sollte, packe ich die Query direkt in die Konsole, da brauche ich keine Software, die mir eventuell alles vermurkst.
In der Konsole hast du alles Schwarz auf Weiß, aber Grundwissen sollte man da trotzdem haben und nicht ein Select *, wenn du nur 1 Feld brauchst.

 

[theHacker]

Ich habe noch nie einen "Query Optimizer" gebraucht, wenn es mal ein Problem geben sollte, packe ich die Query direkt in die Konsole, da brauche ich keine Software, die mir eventuell alles vermurkst.

Hä!? Du weißt schon, was der Query-Optimizer is?

 

[440ci]

Hä!? Du weißt schon, was der Query-Optimizer is?

Was es ist, weiß ich...aber gebraucht habe ich es nie, weil ich bei Probleme lieber meine Konsole nutze...wenn ich zum Lösen meiner SQL-Probleme ein zusätzliches Programm brauche, nutze ich bald auch Frontpage und bastel ein Tabellen-Layout

 

[theHacker]

Was es ist, weiß ich...aber gebraucht habe ich es nie, weil ich bei Probleme lieber meine Konsole nutze...wenn ich zum Lösen meiner SQL-Probleme ein zusätzliches Programm brauche, nutze ich bald auch Frontpage und bastel ein Tabellen-Layout

Offenbar scheinst du keine Ahnung zu haben, wie du hier eindrucksvoll unter Beweis stellst YMMD.

Der Query-Optimizer is kein zusätzliches Programm, sondern ein Teil des DBMS. Es gibt also kein Brauchen oder Nicht-Brauchen - er is nun mal da und wird benutzt. Und manchmal optimiert er eben nicht so toll und dann musst du ihm helfen, indem du ihn anweist, welchen Index er benutzen soll und welchen besser nicht, um die Performance einer Abfrage zu steigern.

 

[tleilax]

In der Konsole hast du alles Schwarz auf Weiß, aber Grundwissen sollte man da trotzdem haben und nicht ein Select *, wenn du nur 1 Feld brauchst.

Nur am Rande: SELECT * ist zwar unschön, aber das allerkleinste Problem, wenn es um langsame Queries geht. Da passiert ja nix "Falsches" in der DB. Die Geschwindigkeitseinbussen kommen einzig und allein durch den erhöhten Traffic.

 

[440ci]

Nur am Rande: SELECT * ist zwar unschön, aber das allerkleinste Problem, wenn es um langsame Queries geht. Da passiert ja nix "Falsches" in der DB. Die Geschwindigkeitseinbussen kommen einzig und allein durch den erhöhten Traffic.

Wenn du dieses Select im Zusammenhang mit Join über 17 Tabellen mit rund 33 Bedingungen hast, wirst du deine Meinung über "allerkleinstes" Problem schnell überdenken
Da könntest du auch schon mal in der Entwicklung eine Wartezeit bis zum Timeout haben

 

[flaschenkind]

Wenn du dieses Select im Zusammenhang mit Join über 17 Tabellen mit rund 33 Bedingungen hast, wirst du deine Meinung über "allerkleinstes" Problem schnell überdenken
Da könntest du auch schon mal in der Entwicklung eine Wartezeit bis zum Timeout haben

Die Bedingungen haben nix mit dem SELECT * zu tun. Und wenn du 17 Tabellen joinst, dann machst du was falsch.

 

[440ci]

Die Bedingungen haben nix mit dem SELECT * zu tun. Und wenn du 17 Tabellen joinst, dann machst du was falsch.

Falsch, bzw. Resourcenlastig sind die dabei aufkommenden doppelte Keys, falls diese vorhanden sind, denn diese würden zu einem unlogischen Result führen.
Was findest du falsch daran, wenn man Querys einsparen kann? Die meisten hier haben doch selber ein PM-Mail/Paidmailer oder sogar eines dieser VMS am laufen...schau dir doch mal an, wie viele unsinnige Querys es in Scripten dieser Art gibt.
Kommen die unter 50 Querys ohne Caching?


Zum Vergleich mal mein eigenes CMS (inkl Backend) ohne Caching:

9 Querys :: Parsetime: 0.249 Sekunden :: 4456448 bytes

Gecachte Ausgabe:

4 Querys :: Parsetime: 0.0223 Sekunden :: 4456448 bytes

So falsch kann es also nicht sein, eine Querys über 17 Tabellen mit 33 Bedingungen zu bauen, wenn man dadurch rund 30 Querys einsparen kann, die Parsetime und die Belastung am Server klein hält