MySQL + Indizierung

Hallo Leute,
wir haben hier eine relativ große Datenbank (MySQL) ..
Leider sind Abfragen mittlerweile so langsam geworden, das hier was gemacht werden muss.
Indizierung! Leider wurde es nicht von Anfang an verwendet. Nun zur der "Beratung" die ich gerne von euch gehört hätte.
Allgemein auf was muss ich achten?
Wie weit kann ich damit gehen?
Es ist doch bestimmt sinnlos die auf jede Spalte in jede Tabelle zu erstellen ..

Ich habe in dieser Hinsicht null Erfahrung ..
Danke für die Anteilnahme.
Liebe Grüße

Wie jetzt? Gibt es hier echt keine die Erfahrung damit haben? Es müssen doch hier ein paar Datenbankadministratoren existieren.

RED-BARON

Schau Dir zunächst Deine Statements an und finde heraus welche genau wie
viel Zeit benötigen. Das kannst Du auch vom Server logen lassen.

( EXPLAIN wird allerdinsg nur für Selects unterstützt )
http://phpperformance.de/langsame-mysql-statements-finden-und-analysieren/

Bei Updates musst Du mal schauen wie die where Klauseln aussehen und diese
Felder evtl. indizieren, sonst tuts wohl immer ein Tabellen Scan machen

Inserts ansich dürften nicht *so* kritisch sein.

Viel Erfolg !

RED-BARON schrieb:

Schau Dir zunächst Deine Statements an und finde heraus welche genau wie
viel Zeit benötigen. Das kannst Du auch vom Server logen lassen.

( EXPLAIN wird allerdinsg nur für Selects unterstützt )
http://phpperformance.de/langsame-mysql-statements-finden-und-analysieren/

Bei Updates musst Du mal schauen wie die where Klauseln aussehen und diese
Felder evtl. indizieren, sonst tuts wohl immer ein Tabellen Scan machen

Inserts ansich dürften nicht *so* kritisch sein.

Viel Erfolg !

Das ist eine gute Idee .. werde ich mir gleich Anschauen. Obwohl ich die meisten Spalten kenne ..
Können überhaut Inserts durch Indizierung beschleunigt werden? Bei mir ist es nicht nötig, aber Wissenshungrig bin ich in diesem Fall schon.

Zur der Ursprünglichen Frage "Wie weit kann ich damit gehen?" .. Also ab wann schlägt Indizierung ins Negative .. kann mir hierzu jemand noch ein paar Takte sagen?

Was noch zu sagen wäre: Wir benutzen hier InnoDB. Hat das Auswirkungen?

Ich danke euch.

nman

Inserts werden durch Indizes nicht schneller, genaugenommen sogar ein wenig langsamer.

Zum Setzen von Indizes gibts kein Rundum-Glücklich-Rezept, Du indizierst einfach die Spalten, nach denen Du häufig abfragst.

Billy T. schrieb:

Was noch zu sagen wäre: Wir benutzen hier InnoDB. Hat das Auswirkungen?

Ja, insofern, als Du wohl keine ganz so groben Performanceschwierigkeiten durch Locking-Probleme wie mit MyISAM bekommen wirst.

RED-BARON

Billy,

mir ist noch kein Extremfall persönlich begegnet, aber gelesen hab
ich darüber schon ( finde leider die Quellen nicht auf die schnelle ).

MySql versucht zu optimieren. Wenn mehrere Indizes festgelegt werden,
wohl auch über mehrere Spalten ( ein Indizes ), dann kann es sein der
Server "optimiert" falsch und Du suchst Dich dumm und doof warum die
Abfrage lange dauert.

Also von mehreren Indizes welche jeweils über mehrere Spalten definiert
sind und womöglich sich hierbei noch eine gemeinsame Schnittmenge ergibt
würde ich tunlichst abraten.

Viel Erfolg !

nman schrieb:

Billy T. schrieb:

Was noch zu sagen wäre: Wir benutzen hier InnoDB. Hat das Auswirkungen?

Ja, insofern, als Du wohl keine ganz so groben Performanceschwierigkeiten durch Locking-Probleme wie mit MyISAM bekommen wirst.

Ahh .. ok, danke. Gut zu wissen das ich damit besser dran bin

@RED-BARON: Ok .. weiß Bescheid. Irgendwie hab ich ein gutes Gefühl

Danke für eure Anteilnahme ..
Meine Kollegen haben bedenken das wir zu viel Indizieren und dadurch das ganze nur noch langsamer machen. Also das es sich ins negative Ausschlägt. Ich sagte ihnen wenn wir nicht jede Spalte einer Tabelle indizieren dann müsste alles gut gehen. Es ist egal wie viele Tabellen wir da haben und davon indizieren. Hauptsache wir indizieren nicht alle Spalten.
Stimmt doch soweit oder?
Liebe Grüße.

Unix-Tom

Ja das stimmt. Es macht keine Sinn alles zu indizieren. Man sollte sich aber gedanken übers Datenbankdesign machen. Eine Tabelle für alle Programme gibt es nicht. Die Tabellen werden an die Anforderung der Aufgabe angepasst und nicht umgekehrt. Somit kannst du die Spalten indizieren welche häufig für suchen verwendet werden.
Natürlich macht es für eine Spalte wo nur 1 oder 0 als Flag drin ist keinen SInn diese zu indizieren auch wenn dananch gesucht wird.

Unix-Tom schrieb:

Natürlich macht es für eine Spalte wo nur 1 oder 0 als Flag drin ist keinen SInn diese zu indizieren auch wenn dananch gesucht wird.

Hmm, das sehe ich genauso. Am Sinnvollsten ist es doch bei Spalten mit Zeichenketten und überhaupt bei langen Zeichenketten, stimmt?
Danke für die Anteilnahme.
Liebe Grüße

nman

Billy T. schrieb:

Hmm, das sehe ich genauso. Am Sinnvollsten ist es doch bei Spalten mit Zeichenketten und überhaupt bei langen Zeichenketten, stimmt?

Überall da, wonach Du häufig selektierst. Auch bei Daten, Zahlen oä.

Würde allerdings "High Performance MySQL" empfehlen, da sind einige Einschränkungen und Eigenheiten von MySQL recht gut dokumentiert:
High performance MySQL | ISBN: 9780596003067

Unix-Tom

Ich würde Dir sowieso ein anderes RDBMS empfehlen.
MySQL wurde nie für diese Datenmengen gebaut.
Problem ist nicht der Select sonderen INSERT UPDATE. Das sperrt die ganze Tabelle.
Wenn auch noch ein SELECTQuery sehr lange dauert dann bekommen andere auch keine Daten.

Ich bin deshalb von MySQL auf MSSQL umgestiegen und der Zeitgewinn waren 1434%

nman

Unix-Tom schrieb:

Ich würde Dir sowieso ein anderes RDBMS empfehlen.
MySQL wurde nie für diese Datenmengen gebaut.
Problem ist nicht der Select sonderen INSERT UPDATE. Das sperrt die ganze Tabelle.

Nein, mit InnoDB nicht bzw. nur in Ausnahmefällen.

Ich empfehle zwar trotzdem immer gerne PostgreSQL, aber ich tue mir schwer, irgendwo einen Hinweis auf Datenmengen zu finden, mit denen MySQL nicht klarkommen könnte bzw. die einen Umstieg erzwingen würden.

Unix-Tom

Von INNO war ja hier nicht die Rede und MySQL verwendet Standardm. MyISAM.
Dort ist es klar ein sehr großes Problem.

Die Frage ist für mich wie groß Deine DB auf mySQL waren.
Ich habe hier eine MySQL mit Tabellen die 51Mill rows haben. Die gesamte DB hat 110GB.
Die andere hat am Tag 16 Mill Rows bekommen und irgendwann war es mit dem Tempo bei SELECT INSERT UPDATE für die User vorbei.Wenn man dann noch Statistiken haben wollte Stand alles. Dies habe ich seit MSSQL 2005/2008 nicht erlebt.

Um ehrlich zu sein bin ich ein überzeugter Gegner von MySQL. Auch wenn sich da seit der Version 5 viel getan hat. Etwas 100% überzeugendes habe ich auch nicht gefunden. Ich habe immer darauf verwiesen das MySQL sich nicht an den Standard hält und das irgendwann Probleme hervorrufen wird. Es hat auch meine Meinung nach zu große Einschränkungen -> wiki. Aber das wird im INet heiß diskutiert.
Leider konnte ich meine Kollegen bis jetzt nicht davon überzeugen ein anderes RDBMS zu verwenden und um ehrlich zu sein hab ich noch nie PostgreSQL verwendet

nman schrieb:

Unix-Tom schrieb:

Ich würde Dir sowieso ein anderes RDBMS empfehlen.
MySQL wurde nie für diese Datenmengen gebaut.
Problem ist nicht der Select sonderen INSERT UPDATE. Das sperrt die ganze Tabelle.

Nein, mit InnoDB nicht bzw. nur in Ausnahmefällen.

Ich empfehle zwar trotzdem immer gerne PostgreSQL, aber ich tue mir schwer, irgendwo einen Hinweis auf Datenmengen zu finden, mit denen MySQL nicht klarkommen könnte bzw. die einen Umstieg erzwingen würden.

Ich habe auch mittlerweile bei der Vorbereitung für die Änderungen usw. herausbekommen, dass wir nicht mal InnoDB verwenden. Soll aber darauf geändert werden. nman Beitrag bezog sich sicherlich teilweise auf meine Aussage InnoDB verwenden zu würden.

Unix-Tom schrieb:

Die Frage ist für mich wie groß Deine DB auf mySQL waren.
Ich habe hier eine MySQL mit Tabellen die 51Mill rows haben. Die gesamte DB hat 110GB.
Die andere hat am Tag 16 Mill Rows bekommen [...]

Meine DB ist noch nicht sehr Groß ... die steht aber auch noch am Anfang. Bekommt aber später sicherlich 10 Mio Datensätze und mehr pro Tag. Momentan liegt das ungefär bei 1 Mio.

Liebe Grüße und danke.

PS: "Mill" ist keine standardisierte Schreibform
Mio => Million oder
Mrd => Milliarden
Bei "Mill" weiß man nie was gemeint ist

hustbaer

Billy T. schrieb:

Meine DB ist noch nicht sehr Groß ... die steht aber auch noch am Anfang. Bekommt aber später sicherlich 10 Mio Datensätze und mehr pro Tag.

Wenn ich davon ausgehe dass hier einzelne Zeilen eingefügt werden (=keine BULK INSERTs), dann sind das > 100 INSERTs pro Sekunde. Das heisst weit über 100 IOPS -- umso mehr, je mehr Indexe gepflegt werden müssen.

Eine "erwachsene" Datenbank ala MS-SQL oder Oracle wäre hier also sicherlich nicht unangebracht, und vor allem ein sau-schneller Storage (RAID oder SSDs ala X25-E).

nman

Unix-Tom schrieb:

Von INNO war ja hier nicht die Rede

Doch, siehe Seite 1:

Billy T. schrieb:

Was noch zu sagen wäre: Wir benutzen hier InnoDB. Hat das Auswirkungen?

Aber mit den neuen Informationen (10Mio Datensätze am Tag) teile ich hustbaers Einschätzung. Wobei das überhaupt nicht nach einem Einsatzszenario klingt, das ich ohne einen halbwegs erfahrenen DBA bewältigen wollen würde.

Hallo Leute.
Das ist alles eine Frage des Geldes.
Von Anfang an wurde nur an MySQL gedacht, weil es ja Kostenlos und mittlerweile wesentlich bekannt ist. Nun ist es mittlerweile bei Abfragen so langsam geworden, dass nicht nur installiert und verwendet wird. Sondern auch nachgedacht wird. Darum die Indizierung.
Ich werde meine Kollegen nicht überreden können, etwas anderes zu verwenden. Aber ich weiß das es später sehr langsam sein wird. Wir haben ja unsere gesamten Datenbanken dort drauf.

Übrigens sind die momentanen 1 Mio nicht nur Inserts sondern auch Updates. Da musste ich mich korrigieren ...

phlox81

Wenns kostenlos sein soll, wäre ja auch Postgres eine Alternative.
Auch ist es eine Überlegung wert, ob man nicht einen Teil der Updates/Inserts mittels BULK Statements effektiver bekommt.
Für die SELECTs ließen sich evtl. auch noch Views anlegen.

hustbaer

Billy T. schrieb:

Das ist alles eine Frage des Geldes.

Ja klar. Ich solltet euch aber nicht nur fragen ob ihr das Geld habt, sondern auch ob ihr Lust habe, in ein paar Monaten mit einem System dazustehen, welches viel zu langsam ist. Und dann kaum mehr zu ändern (weil man schon soviel rumgetrickst hat, dass eine Umstellung auf eine andere DB "nichtmehr geht", und vor allem weil das System schon "live" ist). Manchmal kann man sich eben nicht leisten sich etwas nicht leisten zu können.

(...) Sondern auch nachgedacht wird. Darum die Indizierung. Ich werde meine Kollegen nicht überreden können, etwas anderes zu verwenden.

D.h. ihr habt niemanden der Ahnung von Datenbanken hat. Meine Fresse, und da wollt ihr ein Projekt welches Datenbanken in der Grössenordnung verwendet aufziehen?
Seht euch schleunigst nach Hilfe um. Idealerweise in Form eines neuen Team-Mitglieds/Mitarbeiters.
Wenn MySQL wirklich nichtmehr wegzubekommen ist, dann würde ich an deiner Stelle auf jeden Fall auch in einem auf MySQL spezialisierten Forum fragen.

Aber ich weiß das es später sehr langsam sein wird. Wir haben ja unsere gesamten Datenbanken dort drauf.

Ja, Fehler.
Können wir dir auch nicht helfen

nman

Nicht, dass die Sache mit MySQL das Hauptproblem wäre, aber Ihr wisst schon, dass es auch Gratisversionen von Oracle, DB2, MSSQL und Konsorten gibt?

Haben zwar idR einen Haufen Einschränkungen, lassen sich aber recht gut als Übergangslöung verwenden, wenn man später auf die größeren Versionen umsteigen möchte.