viele kleine tabellen oder eine riesen tabelle?

MySQL, PostgreSQL, SQLite
mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-16 19:04

hallo!

habe die frage schon in einem anderen zusammenhang aber die passt hier seperat glaub ich besser rein.

also in einer großen community bekommt bei uns jeder ein gästebuch als user. pro gästebuch wird eine tabelle angelegt, so dass jetzt mittlerweile über 20000 tabellen in einer datenbank vorhanden sind. damals dachte ich es ist vielleicht besser wegen der abfragen, da sie schneller gehen. heute bin ich mir aufgrund der kenntnisse über table_cache und key_buffer nicht mehr so sicher.

ist es vielleicht besser statt wie bisher viele tabellen, nur eine riesen tabelle zu haben mit den gästebucheinträgen aller user? (das wären derzeit 22 Millionen Zeilen!)

da würde eine abfrage dann doch länger dauern da er nach user-id innerhalb vieler zeilen suchen müsste. andererseits wären dann statt 100er von tabellen gleichzeitig nur eine auf. .... helft mir!!! :D

outofbound
Posts: 470
Joined: 2002-05-14 13:02
Location: Karlsruhe City

Re: viele kleine tabellen oder eine riesen tabelle?

Post by outofbound » 2006-08-17 11:57

Hi,

Warum machst du nicht einfach einen Test?

Ich würde mir in dem Zusammenhang noch überlegen Clustered Indices zu verwenden.

Je nach DBMS kann mit einer Tabelle evtl. das Cachingverhalten und natürlich das Queryverhalten Riesenunterschiede machen. Vor allem die größe des Caches kann da wichtig sein.

Ohne genauere Infos und Queryanalyze kann man da weder das eine noch das andere als "besser" bezeichnen, da müsste man schon genauer nachschauen. ;)

Gruss,

Out

duergner
Posts: 923
Joined: 2003-08-20 11:30
Location: Pittsburgh, PA, USA

Re: viele kleine tabellen oder eine riesen tabelle?

Post by duergner » 2006-08-17 15:11

Sehe ich grundsaetzlich genauso wie Outi. Kommt auf das DBMS drauf an. Aber wenn man einen Index ueber die User_ids anlegt wuerde ich mal aus dem Bauch heraus sagen, dass eine Tabelle schneller geht. Aber kommt natuerlich wie gesagt drauf an, wieviele Gaestebuecher regelmaessig gelesen werden, wieviele Hits man hat, etc.

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-17 15:36

ja natürlich muss ich das testen, das ist jedoch mit sehr viel aufwand verbunden sowie einen ausfall des projektes für mindestens eine stunde, daher wollte ich ja gern ein paar meinungen einholen ob das überhaupt sinnvoll ist :) ok danke ich werde es denn mal testen und hier posten was besser war :)

outofbound
Posts: 470
Joined: 2002-05-14 13:02
Location: Karlsruhe City

Re: viele kleine tabellen oder eine riesen tabelle?

Post by outofbound » 2006-08-17 15:56

Wieso denn Ausfall?

Kopiere die Daten in eine Tabelle, schreib ein Script das Querries drauf macht und schau dir die Laufzeiten an. ;)

Brauchst ja nicht konsistente Daten, also kannst du ja auf niedrigem Isolation Level rausziehen.

Einen Ausfall brauchts da wirklich nicht. ;)

Gruss,

Out

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by isotopp » 2006-08-17 16:59

Mr_Vista wrote:ist es vielleicht besser statt wie bisher viele tabellen, nur eine riesen tabelle zu haben mit den gästebucheinträgen aller user? (das wären derzeit 22 Millionen Zeilen!)
In MySQL ist es mit Sicherheit besser, eine Tabelle als hunderte kleiner Tabellen anzulegen. Wenn Deine Tabelle in InnoDB erzeugt wird, und Du den Primaerschluessel als (userid, nachrichtenid) definierst, dann sorgst Du außerdem dafür, daß die Nachrichten eines Users beeinander gespeichert werden (also auf nahe beieinander liegenden 16K-Seiten der Datenbank).

Der Rest ist dann im Grunde nur eine Frage der Bereitstellung eines ausreichend großen InnoDB Buffer Pools.

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-17 17:33

OutOfBound wrote:Wieso denn Ausfall?

Kopiere die Daten in eine Tabelle, schreib ein Script das Querries drauf macht und schau dir die Laufzeiten an. ;)

Brauchst ja nicht konsistente Daten, also kannst du ja auf niedrigem Isolation Level rausziehen.

Einen Ausfall brauchts da wirklich nicht. ;)

Gruss,

Out
na doch :) ...also erstmal muss das script ja 22Millionen Zeilen, aus über 20000 Tabellen in eine Tabelle umschreiben (das wird dauern) ... während dieser umstellung muss die seite eben vom netz :)

...und Du den Primaerschluessel als (userid, nachrichtenid) definierst...
oh danke das war ein sehr guter rat, der logisch ist, aber auf den wäre ich im stress nich drauf gekommen. mein primärschlüssel wäre einfach nachrichten_id gewesen... über 2 spalten den anzulegen is natürlich vorteilhafter. danke euch beiden, ich werde es wahrscheinlich morgen oder im laufe des wochenendes testen und das ergebnis bekanntgeben.

outofbound
Posts: 470
Joined: 2002-05-14 13:02
Location: Karlsruhe City

Re: viele kleine tabellen oder eine riesen tabelle?

Post by outofbound » 2006-08-17 17:37

Vorab: Wenn du einen kombinierten Index verwendest, muss der erste die user_id sein UND der Index von Typ Clustered sein. Sonst passt das mit der Abbildung der Daten in Reihenfolge der ersten Index- Spalte nicht. Und vergiss bitte nicht das dir ein Select auf nur die nachrichten_id immer noch einen Table- Scan einbringt, weil der Index
nur was bringt wenn du mindestestens die erste Spalte verwendest.

Mal angenommen es sind alle Tabellen gleich:

foreach(tabellen as kleinetabelle=>neueuserid) {
insert into grossetabelle (userid, alle, meine, spalten) select
neueuserid as userid, alle, meine, spalten from kleinetabelle at
isolation 0;
}

Warum abschalten? Danach kannst du den Test in Ruhe machen:

Wenn du dann umschalten willst baust du ein

(If exists(tabelle) select from tabelle else select from grossetabelle) in deinen Code ein und kannst danach die einzelnen Tabellen in Ruhe migrieren.

begin tran
insert into grossetabelle select from kleinetabelle
delete from kleinetabelle. (Oder je nach DBMS ein drop table, das
tut nur manchmal net in transaktionen.)
commit

Wieder in ne schleife. ;)

Etwas kurzgehackt jetzt, aber vom Prinzip her verständlich hoffe ich.

Just my 2 cents. ;)

Gruss,

Out

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-17 17:47

ganz bescheuert bin ich ja nicht , das script is auch nicht das problem :) ...nur wenn ich die daten umschaufel und währenddessen neue daten geschrieben werden, fehlen die bei der umstellung ... weisst!? :D ...und erklär mal den usern warum die gästebucheinträge der letzten 20 minuten auf einmal weg sind :D

outofbound
Posts: 470
Joined: 2002-05-14 13:02
Location: Karlsruhe City

Re: viele kleine tabellen oder eine riesen tabelle?

Post by outofbound » 2006-08-17 17:48

Ich gehe davon aus das du beim Testen natürlich nicht die Live- Daten verwendest, sondern erstmal "dirty" umbaust. WENN du dann die Daten echt migrierst nimmst du die zweite Variante in einer Transaktion. So geht immer nur das GB offline das du grade umstellst anstatt alle. Und das halt je nach grösse der Tabelle für ein paar Sekunden oder so. ;)

Gruss,

Out

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-17 18:04

doch ... WENN dann muss es live getestet werden... es geht ja um die performance... und wenn ich allein teste und die anderen 600 leute die grad online sind im alten system rumeiern, bingt mir das nichts :)

os-t
Posts: 65
Joined: 2006-06-05 16:06

Re: viele kleine tabellen oder eine riesen tabelle?

Post by os-t » 2006-08-17 18:59

Wieso? Für sowas baut man sich schnell nen kleinen Benchmark, der mal eben ein paar Tausend Queries in der Minute auf die DB abfeuert und misst die Zeit. Dann machste das Gleiche mit der anderen Tabellenstruktur. Je nachdem welche Zahl kleiner ist haste Deine Lösung. Alles andere ist ja auch nur geraten, denn im Live-System hast Du niemals identische Werte, d.h. Deine Vergleichszahlen müssen falsch sein.

Gruß Markus

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-17 19:36

nun ja, ich denke aber dass sich das realistisch besser testen lässt als durch künstliche abfragen. es gibt mehrere verschiedene und jeweils auch verschieden oft genutzte querys die sich aus den verschiedensten funktionen zusammensetzen... diese in diesem verhältnis zu simulieren ist nicht ohne weiteres möglich... das testen soll aber auch gar nicht gegenstand dieses threads sein, bitte lasst mich doch einfach testen wie ich möchte :D

grundlegende ausgangsfrage war die meinung nach der performance zwischen "jedes usergästebuch hat eine tabelle für sich" und "alle user teilen sich eine große gästebuchtabelle" :)

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by isotopp » 2006-08-17 21:25

Mr_Vista wrote:na doch :) ...also erstmal muss das script ja 22Millionen Zeilen, aus über 20000 Tabellen in eine Tabelle umschreiben (das wird dauern) ... während dieser umstellung muss die seite eben vom netz :)
Wären Deine Gästebuch-Tabellen mit der ENGINE=InnoDB definiert, könntest Du eine Transaktion starten ("BEGIN WORK"). Da InnoDB per Default im Transaction Isolation Level REPEATABLE-READ läuft, erzeugt dies für diese Connection einen Snapshot der Datenbank, der sich nicht verändert. Dennoch können unterdessen alle anderen Connection auf den Tabellen weiter arbeiten und diese verändern. Sobald Deine Connection ein COMMIT oder ROLLBACK macht, die Transaktion also beendet, wird der Snapshop aufgegben.

Solange Deine Connection den Snapshot hält, wird InnoDB alle Daten vor der Veränderung aus den eigentlichen Tabellen in das Undo Log kopieren. Während die eigentlichen Tabellen entweder in Deinem ibdata1 Tablespace liegen oder (bei innodb_file_per_table = 1) in den .ibd-Dateien liegen können, liegt das Undo Log immer im ibdata1-Tablespace. Es kann, wenn dieser Tablespace mit AUTOEXTEND definiert ist, sehr groß werden - je nachdem wie lange Du den Snapshot hältst und wieviele Änderungen in dieser Zeit stattfinden.

Ein InnoDB Tablespace File schrumpft nie: Der Platz in diesem File kann wieder freigegeben werden, aber die Datei wird niemals kleiner.

Wären Deine 20000 Mio Tabellen also InnoDB, könntest Du BEGIN WORK und dann INSERT INTO grossetabelle SELECT * FROM kleinetabelle1; INSERT INTO grossetabelle SELECT * FROM kleinetabelle2; und so weiter, und dann am Ende COMMIT. Das solltest Du natürlich nicht für 22 Mio Rows am Stück machen, aber Du kannst es für jeweils eine Tabelle oder für jeweils einen Block von 100 Tabellen oder so am Stück machen.
...und Du den Primaerschluessel als (userid, nachrichtenid) definierst...
oh danke das war ein sehr guter rat, der logisch ist, aber auf den wäre ich im stress nich drauf gekommen. mein primärschlüssel wäre einfach nachrichten_id gewesen...
Das hat nur dann Auswirkungen, wenn Du als Engine InnoDB verwendest: In InnoDB liegen Daten in einem B+ Baum, der durch den Primärschlüssel gebildet wird, d.h. der Primärschlüssel ist automatisch ein Clustered Index.

In MyISAM ist das nicht der Fall, hier spielt es tatsächlich keine Rolle, ob Du ( nachrichtenid ) oder ( userid, nachrichtenid ) verwendest.

Es ist wichtig, daß die Daten eines Users dicht beisammen stehen, weil Du so Locality of reference erzeugst und so die Caching-Algorithmen der Datenbank zum Zuge kommen können und die Zugriffe auf die Daten auch dann zügig sein können, wenn die Tabelle sehr viel größer als Dein Cache ist.

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by isotopp » 2006-08-17 21:32

OutOfBound wrote:foreach(tabellen as kleinetabelle=>neueuserid) {
insert into grossetabelle (userid, alle, meine, spalten) select
neueuserid as userid, alle, meine, spalten from kleinetabelle at
isolation 0;
}
Er verwendet MySQL, wie man an seinen Anmerkungen über den table cache und den key cache sehen kann (am Threadanfang). "at isolation 0" ist keine MySQL Syntax. Wollte man den Transaction Isolation Level ändern, würde man zu Anfang der Connection ein

Code: Select all

mysql> set transaction isolation level read uncommitted;
oder was immer gefordert ist machen. Das nützt allerdings nur dann etwas, wenn von vorneherein eine Engine verwendet worden ist, die so etwas wie Transaktionen überhaupt kennt - per Default wird MyISAM verwendet, das kein MVCC macht und auch keine Transaktionen kennt.

Hier kann man ein LOCK TABLES ... READ auf die Quelltabellen machen, aber 20.000 Table Locks sind auch kein richtiger Spaß (und alle User würden in dieser Zeit abprallen, wenn sie versuchten, neue Gästebucheinträge zu comitten). Die korrekte Weise dies in dieser Situation zu handhaben wäre es, die SUBMIT-Knöpfe auf der Seite zu disabled und durch einen Erklärungstext zu ersetzen. Dann stimmt wenigstens das Userinterface.

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-24 09:08

hallöchen!

habe die datenbankstruktur nun geändert und bisher läuft es sehr gut... die tabelle ist 3,2 GB groß und die Indizis 700MB ... heute abend zur stoßzeit werde ich den server genauestens beobachten und das ergebnis bekanntgeben...

lohnt es sich den key_buffer jetzt auf 700MB hochzusetzen? denke mal das wäre übertrieben oder?

lg

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by isotopp » 2006-08-24 09:22

Mr_Vista wrote:lohnt es sich den key_buffer jetzt auf 700MB hochzusetzen? denke mal das wäre übertrieben oder?
Wenn man unendlich viel Speicher hat, ist die optimale Größe für den key_buffer die Summe der Größen aller MYI-Dateien.

Wenn man endlich viel Speicher hat, paßt ein so großer key_buffer unter Umständen nicht in den Speicher. Ein key_buffer sollte bei einem reinem MyISAM-Server nicht mehr als 20-30% des gesamten Speichers belegen und bei einem reinem MyISAM-Server sollten etwa 40-50% des Speichers im Buffer Cache verbleiben, d.h.

Code: Select all

kris@h3118:~> free -m
             total       used       free     shared    buffers     cached
Mem:          1011        964         47          0        109        321
-/+ buffers/cache:        533        477
Swap:         2550        179       2370
sollte bei "-/+ buffers/cache" in der Spalte used und free ca. gleich große Zahlen haben. Der Buffer Cache kann dann als Puffer für die MYD verwendet werden.

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-27 21:51

also habe jetzt mehrere tage getestet und kann sagen, dass sich, zumindest bei dieser großen anzahl von gästebucheinträgen, die vielen kleinen tabellen besser machen.

vorher hat der server bei etwa 900 usern angefangen zu swappen jetzt schon bei 800. ist also merklich langsamer geworden.

zudem hab ich festgestellt dass der server besser lief je weniger speicher ich mysql zugewiesen habe. mit 512MB table cache und 64MB key buffer kann es nichts anfangen und der server wird deutlich langsamer.

dass diese art der struktur langsamer ist (eine riesen tabelle) liegt zum teil aber auch daran, dass die abfragen komplizierte sind als vorher. so hat er pro abfrage mindestens eine where-klausel mehr als mit der variante mit den vielen kleinen tabellen.

wollte euch das nur mal mitteilen damit ihr das ergebnis der ganzen mühe habt. danke für eure hilfe!

lg

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by isotopp » 2006-08-30 16:55

Mr_Vista wrote:vorher hat der server bei etwa 900 usern angefangen zu swappen jetzt schon bei 800. ist also merklich langsamer geworden.
Ein Datenbankserver darf nie swappen.
zudem hab ich festgestellt dass der server besser lief je weniger speicher ich mysql zugewiesen habe. mit 512MB table cache und 64MB key buffer kann es nichts anfangen und der server wird deutlich langsamer.
Ein Table_Cache hat keine MB. Er hat Slots, Einträge für Filehandles und geparste FRM-Dateien. 512 Millionen Einträge für den Table Cache ist ein unsinniger Wert.
dass diese art der struktur langsamer ist (eine riesen tabelle) liegt zum teil aber auch daran, dass die abfragen komplizierte sind als vorher. so hat er pro abfrage mindestens eine where-klausel mehr als mit der variante mit den vielen kleinen tabellen.
Eine SQL-Query hat immer maximal eine WHERE-Clause. Du meinst sicherlich, daß eine weitere AND-Bedingung in der WHERE-Clause dazu gekommen ist. Dir ist schon klar, daß Du den Primärschlüssel nun von ( eintragsid ) auf ( kundennummer, eintragsid) ändern mußt bzw. von ( timestamp) auf ( kundennummer, timestamp ), wenn sich die WHERE-Bedingung von "WHERE eintragsid = ?" auf "WHERE kundennummer = ? and EINTRAGSID = ?" bzw. "WHERE timestamp < NOW() - INTERVAL 3 DAY" auf "WHERE kundennummer = ? and timestamp < NOW() - INTERVAL 3 DAY" ändert?

Hast Du EXPLAIN auf Deine Queries angewendet, nachdem Du die Daten migriert hast?

mr_vista
Posts: 57
Joined: 2006-08-01 22:33
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by mr_vista » 2006-08-31 06:24

Ein Datenbankserver darf nie swappen.
ach wat :) und was tu ich dagegen wenns trotzdem geschieht?

Ein Table_Cache hat keine MB. Er hat Slots, Einträge für Filehandles und geparste FRM-Dateien. 512 Millionen Einträge für den Table Cache ist ein unsinniger Wert.
alles klar danke, wusste ich nicht. dann hab ich ein schlechtes dokument gelesen :-/

Eine SQL-Query hat immer maximal eine WHERE-Clause. Du meinst sicherlich, daß eine weitere AND-Bedingung in der WHERE-Clause dazu gekommen ist.
mal davon abgesehen dass nicht jede mysql query eine where klausel hat, habe ich das, was du meintest was ich eigentlich meine auch gesagt... und zwar dass mindestens eine where-klausel MEHR vorhanden ist :)

der primärschlüssel ist aufgrund der struktur und der benötigten abfragen etwas anders, aber im groben stimmt er mit dem überein. und ja die abfragen hab ich mit EXPLAIN mal überprüft.


übrigens läuft die große tabelle jetzt doch schneller als die vielen kleinen. ein "OPTIMIZE tabelle" wirkt wahre wunder...

lg

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by flo » 2006-08-31 10:01

Mr_Vista wrote:ach wat :) und was tu ich dagegen wenns trotzdem geschieht?
Die Speichernutzung soweit reduzieren, daß er nicht versucht, alles zu cachen. Die effektve Geschwindigkeit eines Servers mit nicht (für Abfragen) genutztem Cache, der dadurch aber gzwungen ist zu swappen ist schlechter als ohne Caching.
Mr_Vista wrote:mal davon abgesehen dass nicht jede mysql query eine where klausel hat, habe ich das, was du meintest was ich eigentlich meine auch gesagt... und zwar dass mindestens eine where-klausel MEHR vorhanden ist :)
Haarspaltereien traue ich da eher Isotopp zu, da er auch die Kompetenz hat, sein Wissen dadurch selbst zu persiflieren. ;-) Die WHERE-Clause ist das komplette Konstrukt von "WHERE ... " bis zum nächsten Block, der z.B. GROUP, ORDER, LIMIT sein kann. Was Du meinst, ist eine Bedingung innerhalb der Clause. Dementsprechend hat ein SELECT auch nur max. eine WHERE-Clause.

Ausnahmen bestätigen die Regel - Subselects sind eine andere Baustelle, weil eigentlich Abkürzungen für mehrere Selects, aber IMHO auch noch nicht komplett unterstützt - irgendetwas hat mir der meinige neulich meckernd um die Ohren gehauen.

flo.

User avatar
isotopp
Posts: 471
Joined: 2003-08-21 10:21
Location: Berlin

Re: viele kleine tabellen oder eine riesen tabelle?

Post by isotopp » 2006-08-31 16:10

Mr_Vista wrote:
Ein Datenbankserver darf nie swappen.
ach wat :) und was tu ich dagegen wenns trotzdem geschieht?
Den Speicherbedarf des Servers verkleinern sodaß er in den zugewiesenen Hauptspeicherbereich paßt. Ein reiner InnoDB Server darf maximal 80% des Speichers belegen (also bei 1 GB RAM maximal bis zu 800M groß sein). Ein reiner MyISAM-Server darf bis zu 50% des Speichers belegen (also bei 1 GB RAM maximal 500M groß sein).

Den Server dann mit "memlock" in der [mysqld]-Sektion der my.cnf in den Hauptspeicher locken, oder mit "vm.swappiness = 0" in /etc/sysctl.conf die Swapneigung des Systems herabsetzen.
Eine SQL-Query hat immer maximal eine WHERE-Clause. Du meinst sicherlich, daß eine weitere AND-Bedingung in der WHERE-Clause dazu gekommen ist.
mal davon abgesehen dass nicht jede mysql query eine where klausel hat,
Drum schrieb ich "maximal".
habe ich das, was du meintest was ich eigentlich meine auch gesagt... und zwar dass mindestens eine where-klausel MEHR vorhanden ist :)
Eine AND-Bedingung in der WHERE-Clause mehr.