Hilfe bei MYSQL Datenbank Innodb

[katze]

Liebe Coder-Freunde

Meine Datenbankauslastung (PC/RAM) ist bei größeren Datenbanken oftmals am Limit und immer wieder Wechsel auf stärkere Hardware macht bei einer stark wachsenden Datenbank keinen Sinn.
Auch Mysql-Tweaks bin ich schon mehr als durch. Daher würde ich euch gerne nach Alternativen fragen.

Als Beispiel speichere ich Besucherinformationen in einer InnoDB mit bereits über 60Mio Rows.


https://www.szenebox.org/images/_imported/2017/08/10.jpg

https://www.szenebox.org/images/_imported/2017/08/11.jpg

Habt Ihr vielleicht einen Tip was ich anders machen kann?
Bin für alles offen und möchte auch meine Scheuklappen ablegen :)

[Nimbus]

Zur Tabelle fällt mir jetzt spontan nur auf, dass du Informationen in unterschiedlichen Formen mehrfach ablegst (das Land; die OS/Browser-Informationen und den User-Agent; das Land und die IP). Je nachdem wie du die Informationen nutzt wäre es eventuell denkbar einige diese Dopplungen abzuschaffen und nur "on-demand" zu bestimmen. Performance Wunder bringt das aber nicht :D

Ein Mittel, dass extrem effektiv sein kann ist Caching. Damit würdest du die Anfragen an die DB reduzieren und folglich auch die Auslastung verringern.

[katze]

caching verwende ich schon. unter anderem phpfastcache. aber ich hab leider ziemlich viele Write/Read Process zum Updaten der Daten.
Dachte eventuell Nosql aber das ist komplett neu für mich :/

[JepFig]

Für Besucherinformationen würde sich ein Elasticsearch anbieten. Dort ist die horizontale Skalierung direkt drin vorhanden und du kannst zB für jeden Monat einen eigenen Index anlegen. Wenn du dann nach 2 Jahren oder so mal die alten Daten wegschmeißen willst, geht das damit sehr einfach. Da wäre die Lösch-Performance bei einer MySQL oder auch einer MongoDB deutlich schlechter.

Machst du auf die Daten auch Updates? Dann könnten die VARCHARs ein Problem sein. TEXT-Spalten sind bei MySQL auch sehr unoptimal. Schau mal, ob dir da nicht vielleicht ein VARCHAR/CHAR reicht.

[daniels2000]

Wie sieht denn die Hardware aus?

[y0l0sw4gg3r]

Eine Frage zu den Use-Cases: Müssen die Daten permanent in der MySQL gehalten werden? Oder wäre es eine Idee, pro Tag eine Flatfile zu erstellen und die dann bei bedarf zu durchsuchen?

[katze]

Erstmal großes Lob an die Community. Ich hab gehofft, dass ich hier der Eine oder Andere herumläuft und ich vielleicht eine Antwort erhalt.
Es zeigt aber, dass hier im Entwicklerbereich einiges an Potential steckt. @Syrius, wenn du das liest, vielleicht kannst du das Potential nutzen ;)

Nun y0l0sw4gg3r ich habe tatsächlich drei use-case

Usecase1 - filehoster url store und filecheck

Hier habe ich eine Datenbank die bereits 5 Jahre alt ist. Ich versuche Daten zu löschen, z.b. wenn ein Filehoster den Service einstellt aber das steht nicht im Verhältnis. Die Datenbank wächst rasant.

1 Upload = 1 Container
Zugehörige Daten wie Beispielsweise das Password zum entpacken werden bereits in einer eigenen InnoDB gespeichert

Die einzelnen Links werden dann nochmal in einer eigenen Innob einzeln abgelegt.
https://www.szenebox.org/images/_imported/2017/08/12.jpg

Ein Webauftritt gibt es dazu keinen, daher auch keine Last durch Besucher.
Aber 3 Bots (je eigener vps) überprüfen ständig die Links und updaten die Daten und das überlastet oft den eigen Mysql Server

Hardware Mysql Server only:
Intel Xeon E5-1650V2
HDD2x SSD 240 GB SATA
RAM4x RAM DDR3 16384 MB ECC reg.

Usecase2 - Eigene Meta Search Engine

@JepFig Danke für den Tip werden Elasticsearch dazu verwenden. Für alle die wie komplette Neulinge sind >>Hier<< (https://www.schwarzer.de/blog/elasticsearch-tutorial-das-konzept-verstehen/) eine Interessante erste Erklärung.

Hardware: Noch Keine

Usecase3 - Traffic Analye aller Google

Für meinen Traffic Exchange Service hätte ich gerne als Zusatz eine Trafficanalyse je User.
Eigentlich habe ich mir die Struktur bei dem Open Source Tool Piwik abgeschaut dennoch ist es es irgendwie nicht massentauglich. (Siehe Screens Threadstart)
Daher würde ich gerne auch Alternativen in Betracht ziehen.

[FLX]

Moin, also was mir direkt auffällt du bist recht grosszügig mit den Feldern innerhalb der DB.
Ob das jetzt einen wirklich relevanten Unterschied darstellt sei mal dahingestellt aber Kleinvieh macht auch Mist.

Ich beziehe mich mal auf die Bilder vom ersten Post.

form_website und to_website haben ein INT10 das entspricht 10x0 Einträgen selbst mit einem INT6 könntest du
vermutlich genug Websites IDs vergeben um ein Grossteil des Webs zu definieren. Visitor Country und Visitor_Country_Short sind nur reine bequemlichkeit. Eins von beiden kann Weg und durch eine Funktion zugeordnet werden. Was der Visitor Tag macht ist auf Anhieb nicht erkennbar da immer auf NULL.
Die Visitor Adress ist ebenfalls zu lang mit varchar Hundert, selbst eine IPv6 würde sich mit 39 Zeichen begnügen. Der Traffic Type mit 0/1 anstatt Enum sollte ebenfalls performanter sein.

Das sind die Dinge die mir direkt ins Auge fallen das schon in der DB an sich grosszügig Ressourcen verteilt werden. Die weiteren Stichworte wären dann wirklich Caching und Query Optimierung also mal Tracken welche Querys fressen am meißten und da gezielt ansetzen. Kompression durch Hashen der DB Felder macht da ebenfalls durchaus Sinn.

[katze]

FLX: Da ich mir alles selbst als Hobby aneigne merkt das der Profi gleich :emoji04: Daher Super Danke für die Hinweise, werde ich berücksichtigen.

Hab noch etwas recherchiert. Der Fork xtradb für Mariadb soll mit hohen Lasten etwas besser zurecht kommen.

[daniels2000]

Hardware Mysql Server only:
Intel Xeon E5-1650V2
HDD2x SSD 240 GB SATA
RAM4x RAM DDR3 16384 MB ECC reg.
Das ist ja eher Low-End Hardware für einen Server.

SSDs laufen wahrscheinlich im RAID 1? Dann wären 4x SSD in RAID 10 mal ein Ansatz.

Außerdem Dual Xeon und min. 128 GB RAM.

[y0l0sw4gg3r]

Interessieren würden mich Profiler-Daten, wie bspw. die häufigsten Querys mit den jeweiligen IO-Zeiten. Hast du dazu eine Auswertung?
Denkbar scheint mir auch der Einsatz eines Document-Store DBMS - jeh nach Komplexität der normalisierten relationalen Datenbank und der Strukrut der am häufigsten abgefragten/geänderten Daten.