# SQL Datenbank Speicherplatz sparen?



## deintag85 (1. Juni 2009)

Hallo,
ich betreibe einen CounterService, der für jeden Counter folgende Tabelle erstellt :


```
$sql="CREATE TABLE IF NOT EXISTS `$counter` (
	  `IP` text NOT NULL,
	  `Datum` datetime NOT NULL,
	  `OS` varchar(50) NOT NULL,
	  `Browser` varchar(50) NOT NULL,
	  `Screen` varchar(25) NOT NULL,
	  `Depth` int(10) NOT NULL,
	  `Location` varchar(50) NOT NULL,
	  `Referer` varchar(255) NOT NULL,
	  `Keyword` varchar(255) NOT NULL,
	  `Java` varchar(10) NOT NULL,
	  `Cookie` varchar(10) NOT NULL
	) ENGINE=MyISAM DEFAULT CHARSET=latin1";
```

Jeder Zugriff auf einer Seite führt zu einem Eintrag in der Datenbank, sodass ich sehen kann wieviele verschiedene Besucher online waren und zu welcher Uhrzeit sie die Website aufgerufen haben.

Nun denn. Ich habe gerade eine Testphase und habe einen Benutzer der mir ca. 20.000 Einträge pro Tag macht. Das entspricht ca. 2 MB pro Tag an Speicherplatzverbrauch für seine Tabelle.

Wenn ich mir ausrechne, was wäre wenn ich 1000 Counter betreiben würde, das wären 2GB pro Tag / 62 GB pro Monat Speicherplatz. Dann wäre mein Server schneller voll als man denkt.

Nun. Welche Möglichkeiten gibt es um die Speicherplatzmenge möglichst gering zu halten? Ich habe etwas gelesen über "Tabellenkompression". Wie genau funktioniert das? Ist das denn angebracht für so etwas? Welche Alternativen gibt es? Gibt es eine?

Soll ich vielleicht die Datentypen meiner Tabellenfelder nochmal überdenken auf Speicherärmere Typen?

Lieben Gruß


----------



## Sven Mintel (2. Juni 2009)

Moin,



deintag85 hat gesagt.:


> Soll ich vielleicht die Datentypen meiner Tabellenfelder nochmal überdenken auf Speicherärmere Typen?



Naja, du hast da einen Haufen varchars-diese alleine machen ca. 700 Bytes aus.

Ich würde erstmal versuchen, die Sache  zu normalisieren.

Location,OS,Browser,Screen,depth,Keyword,JAVA und Cookie schreien geradezu danach(falls sich dahinter das verbirgt, was ich denke).

Dann Referer.....falls es dort ausreichen würde, nur externe Referers zu zählen, bietet das auch Sparpotential.

Denkbar wäre auch, die varchar-Felder in text umzuwandeln, und die Werte vorher mit PHP auf die vorgesehene Länge zu stutzen.

Und last but not least:
Irgendwann (täglich,monatlich, etc.)solltest du die Datensätze in passender Form archivieren, also in einer Zusammenfassung abspeichern, und aus der Tabelle entfernen...tust du das nicht, fliegt dir die Sache früher oder später eh um die Ohren


----------



## dbwizard (2. Juni 2009)

Sven Mintel hat gesagt.:


> Moin,
> 
> 
> 
> ...



Hallo,

Nun, varchar heissen deswegen varchar, weil es Textfelder variabler Länge sind, es wird also nur der Platz benötigt, welcher der String effektiv beanspricht plus 1 oder Byte für interne Zwecke (Im gegensatz zu Char, dort wird mit Blanks aufgefüllt bis zur deklarierten Länge

(Kann natürlich je nach DB varieren....)



gruss


----------



## Sven Mintel (2. Juni 2009)

Stimmt, sorry, Fehlinfo von meiner Seite 
Die anderen Vorschläge bleiben davon unbeeinflusst.


----------



## Biber2 (2. Juni 2009)

Moin deintag85,

Du könntest - je nach Anteil Deiner Stammkunden auf Deiner Site- das Datenvolumen um 50-95% eindampfen, wenn Du nicht für jeden Anlauf eines Benutzers (vom immer gleich bleibenden Rechner aus) einen neuen Datensatz anlegen würdest.
Wenn User "Heinz"  am heutigen Datum 29x auf Deiner Site vorbeischaut, dann hat er doch 29 Datensätze in Deiner Tabelle - mit Einträgen, die bis auf die Uhrzeit identisch sind.
Okay, vielleicht wird nicht jeder Benutzer immer die gleiche IP den ganzen Tag lang bzw. Monat für Monat benutzen, aber geben soll es solche User.

Da nun außerdem das Ganze nicht Usernamen-bezogen ist (bzw. von einer etwaigen Anmeldung abhängt), ob Du den Satz nun speicherst---> dann reicht es doch für jede noch so abgedrehte Statistikauswertung, wenn Du meinetwegen alle IPs mit "OS=Windows" und Location="South Africa" in EINEM Datensatz subsummierst mit einem Feld "ANZAHL", dass jeweils bei einem neuen Hit um 1 hochgezählt wird.

Oder aber- Ein Feld "AnzahlDiesenMonat", welches Du jeweils am Monatsletzten um 23:59 automatisch umbuchst in ein Feld "AnzahlGesamt" und auf 0 setzt .. etc pp.

Und nichts gegen "Normalisieren einer Tabelle"... ist ja auch durchaus richtig.
Aber ein Schritt kommt doch noch vorher: erstmal die Überlegung, welche "Daten" müssen/sollten denn überhaupt gesammelt werden, weil sie gleichzeitig auch "Informationen" sind oder gar "erhaltenswerte Informationen"?

Wenn eine Seitenzählertabelle "62 GByte pro Monat" brauchen könnte.... ja hey... dann würde ich ich erstmal die Finger von der Tastatur nehmen und einen kleinen Spaziergang machen.... mal einen anderen Blick bekommen.

Kann ja nicht sein, dass da pro Monat mehr zusammengeharkt wird, als auf einer 10-Jahres-Ausgabe "Telefonnummern-Deutschland".steht oder mehr als der Stoiber über uns sammelt.

Grüße
Biber


----------



## deintag85 (24. Juni 2009)

Biber2 hat gesagt.:


> Moin deintag85,
> 
> Du könntest - je nach Anteil Deiner Stammkunden auf Deiner Site- das Datenvolumen um 50-95% eindampfen, wenn Du nicht für jeden Anlauf eines Benutzers (vom immer gleich bleibenden Rechner aus) einen neuen Datensatz anlegen würdest.
> Wenn User "Heinz"  am heutigen Datum 29x auf Deiner Site vorbeischaut, dann hat er doch 29 Datensätze in Deiner Tabelle - mit Einträgen, die bis auf die Uhrzeit identisch sind.
> ...



Hallo!
Ich bin wieder hier ;-) Ich glaube du könntest Recht haben mit dem was du sagst. Ich habe einen Counter der nun etwa 400.000 Datenbankeinträge produziert hat. Die Abfrage dauert (warum auch immer) ca. 40-60 Sekunden. Das ist unmenschlich und unzumutbar für meine Besucher.

Ich werde wohl das Konzept überdenken, dass ich nicht jedesmal einen neuen Eintrag mache sondern für Seitenaufrufe einfach ein weiteres Feld mache.

Aber dennoch wird sicherlich das Problem wieder kommen, dass ich soviele Einträge habe, dass eine Abfrage zu lange dauert. Was mache ich bei meiner Abfrage falsch? Ich frage ja nur COUNT(DISTINCT IP) VON DEM TAG X ab.

Wie kann man denn die Abfrage beschleunigen?
LG


----------



## dbwizard (24. Juni 2009)

deintag85 hat gesagt.:


> Hallo!
> 
> Aber dennoch wird sicherlich das Problem wieder kommen, dass ich soviele Einträge habe, dass eine Abfrage zu lange dauert. Was mache ich bei meiner Abfrage falsch? Ich frage ja nur COUNT(DISTINCT IP) VON DEM TAG X ab.
> 
> ...



Hallo,

- Erstmal, welches DBMS verwendest du denn ? Sind Indizies gesetzt und korrekt ? Aufgrund deines Beispiel SQL wäre sicher auf IP und dem Datumsfeld ein Index notwendig.
- Sind die Statistiken aktuell resp. überhaupt vorhanden ?
- 400000 Datensätze sind "nicht viel", dies solle in der Regel sehr performant sein
- Wenn deine DB dies unterstützt, könntest du die Tabelle auch Range-partitionieren, z.b. Monatspartitionen


Gruss


----------



## deintag85 (24. Juni 2009)

dbwizard hat gesagt.:


> Hallo,
> 
> - Erstmal, welches DBMS verwendest du denn ? Sind Indizies gesetzt und korrekt ? Aufgrund deines Beispiel SQL wäre sicher auf IP und dem Datumsfeld ein Index notwendig.
> - Sind die Statistiken aktuell resp. überhaupt vorhanden ?
> ...



Das war jetzt zu hoch für mich, glaube ich. Also ich nutze mySQL in verbindung mit php. Ich habe keine Ahnung was Indizes sind. Was heisst "resp."?

LG


----------



## dbwizard (24. Juni 2009)

deintag85 hat gesagt.:


> Das war jetzt zu hoch für mich, glaube ich. Also ich nutze mySQL in verbindung mit php. Ich habe keine Ahnung was Indizes sind. Was heisst "resp."?
> 
> LG



Hallo..



> Ich habe keine Ahnung was Indizes sind



Dann hast du schlechte Karten...Ich würde mich zuest mal schlau machen  :

Indizies :

http://dev.mysql.com/doc/refman/5.1/en/optimizing-database-structure.html

Partitionierung  :
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

...und vielleicht mal die Doku heranziehen

In deinem Fall würden 2 Indizies notwendig sein :

CREATE INDEX ip_idx ON mytable (IP);

CREATE INDEX Datumsfeld_idx ON mytable (Datumsfeld);



gruss


----------



## SeeKing (25. Juni 2009)

Moin,

jetzt schalte ich mich auch mal ein. 

Also ich hab mehrere MySQl´s am laufen die Datenmengen von > 60GB handeln. Die größte DB ist ein 6 Maschinen MySQL-Cluster auf 2,4 TB "Nutzdaten" (ohne Indexes, HEAP´s u. VIEWS; mit > 3TB). 
Und selbst bei solchen Datenmengen sind die Abfragen rasent schnell. Also das Argument, die Datenmenge wird zu hoch ist eher kritisch zu betrachten.

Allerdings sind um solche Datenmengen zu handeln die Standardeinstellungen von MySQL eher suboptimal. Auch empfiehlt es sich im MySQL selber bzw. den DBMS-Engines schon beim kompilieren einige neue Einstellungen mitzugeben.

z.B. gibts bei InnoDB eine Einstellung (my.ini) in welcher du die Engine zwings für jede Tabelle eine eigene File zu erstellen oder eine RIESIGE File für alles was InnoDB ist... Leider ist letzteres Standard und ein absoluter Performance Killer. 

Weiterhin wichtig sind diverse Finetunings an den Hostmaschinen selber (unter Linux z.B. READ AHEAD Einstellungen)

Auch die richtige Hardware will für sowas gewählt sein. Wir setzen ausschließlich auf RAID 10 Systeme mit 2,5`` SAS Scheiben (147GB bei 10k U/min).

Im Grunde bringt aber die beste Maschine u. DB effektiv NICHTS! wenn das Modell nix taugt.
Dies bezüglich haben ja dir ja schon einige erschöpfende Informationen gegeben. Im Zweifelsfall die Doku von MySQL ist zwar neuerdings etwas "häßlich" zu lesen aber tu dir das mal an, dann wird sich vieles von selbst klären.

An irgendwelche "Speichergrenzen" wirst du wohl solange der Plattenplatz reicht nicht stoßen..


----------



## Biber2 (28. Juni 2009)

Moin SeeKing,

alles richtig, was Du schreibst - Speichergrenzen und Datenvolumen sind handlebar... und mit ein bisschen Feintuning macht die DB dann trotzdem noch Spaß. Bzw. lässt sich auch für "produktiven Einsatz" vorzeigen.

Aber, wie auch dbwizard durch seine Nachfragen ans Tageslicht gebracht hat - das ist eben keine Datenbank, die nun mal dazu gezwungen ist, mit ein paar TeraByte klarkommen zu müssen.

Sondern eine teilweise Drauflos-Zusammengeklickte Applikation, die Daten in einer Form sammelt, wie andere Anwendungen bestenfalls Debug-Logfiles mit gesetztem Schalter /TraceWhateverMoves wegschreiben.

Da muss erstmal ein wenig an den Grundlagen und am Design nachgearbeitet werden - und nach der Sommerpause können wir dann ein bisschen tunen und einzelne Statements flotter machen.

@deintag85
Das ist jetzt nicht abwertend gemeint - niemand von uns ist im ersten Anlauf an allen Stolpersteinen vorbeigelaufen.
Aber für Dich und Deine Appz nützen jetzt kleine partielle Tipps nicht viel.
Stell die (konkrete) Entwicklung für ein paar Tage zurück und quäle Dich durch ein Tutorial/einen Kurs/ein Buch zm Thema.

Selbst mit einer "problemlösenden Antwort" zu Deiner _eigentlichen_ Frage bekommst Du sonst nur eine Scheinlösung.

Grüße
Biber


----------



## SeeKing (29. Juni 2009)

Moin Biber,

deswegen auch meine letzten 3 Zeilen. Ich wollte nur das Argument entkräften, dass MySQL mit solchen Mengen nicht zurecht kommen würde. Manche glauben das leider wirklich noch 

Allerdings so ab 5 TB sollte man doch mal langsam Richtung Oracle, DB2 etc. denken...

Als Einstieg (zwar steinig und sehr steile Lernkurve aber RICHTIG gut) kann ich dir das Buch "High Performance MySQL" vom O´Reilly Verlag empfehlen. Allerdings setzen die schon einiges voraus. Es wird zwar kurz erklärt du wirst aber nicht drumherumkommen einiges in der MySQL Doku nachzuschlagen. Aber in den Kapiteln 2 - 7 werden genau deine Fragen und Probleme behandelt. (Modeldesign, Variablenspaces, Partions, Growing Tablespaces etc.)

Wenn du soweit bist kannst du gerne auch nochmal ein Datenbankmodel posten. Ich bin sicher einige werden es liebend gerne für dich "zerpflücken". Nicht umsonst bilden wir Datenbankdesigner 3 Jahre lang aus 

Man kann Datenbank nicht von heute auf morgen lernen. Access kann jeder zusammenklicken aber richtig große DB´s (die auch noch schnell sein müssen) das ist eine Wissenschaft für sich. UND GANZ WICHTIG immer im Kopf behalten. Eine Datenbank basiert immer auf der Mengenlehre! Du hast immer deine "Tabellenmengen" (mit Absicht in Klammern da gibts einen anderen Begriff). Wenn du schonmal von dieser Ansicht an die Sache rangehst kannst du schonmal nicht mehr zu 100% falsch gehen. 

Gruss
SeeKing

@Biber u. dbwizard
Ich denke gerade darüber nach einen Datenbankmodellierungs "Workshop" mal zu schreiben. Nur allein wird das wohl etwas heftig. Weil solche Threads sind mir jetzt schon ein paar Mal (nicht nur hier) aufgefallen. Wärt ihr dabei?


----------



## deintag85 (29. Juni 2009)

dbwizard hat gesagt.:


> Hallo..
> 
> CREATE INDEX ip_idx ON mytable (IP);
> 
> ...



Hallo,
ich hab diverse Befehle ausprobiert in PHPMYADMIN aber keines hat irgendwie funktioniert. Dauernd bekam ich eine Fehlermeldung dass der Befehl falsch sei.

Dann bin ich darauf gestossen dass man mit einem Klick einen Index erstellen kann. Das hab ich für beide Spalten gemacht. "ALTER TABLE xxx ADD INDEX(IP)" wurde dann ausgeführt ebenso wie für das Datum. Das heisst ja in meiner Abfrage muss ich nichts ändern, oder?... Weil von der Geschwindigkeit hat sich nichts getan :-/

-----
Man findet überall neue Infos....Jetzt les ich dass man CREATE INDEX nur machen kann wenn man grad eine neue Tabelle erstellt hat? Alternativ MUSS man ALTER TABLE machen....hmmmm, aber ist dann der tabellenname der gleiche?

---- edit 2

Ich glaub ich habs gebacken gekriegt. Also man erstellt ein INDEX direkt nach der Tabelle oder macht ALTER TABLE tabellenname ADD INDEX index_name (SPALTENNAME)....
Wow, von 50 Sekunden auf 6 Sekunden runtergeschraubt und das bei 50.000 Einträgen. Unfassbar...Gut ich probier erstmal meinen Code zu optimieren, vllt hol ich noch was raus...

^^

---edit 3

Ich bin total verwirrt? Ich finde einfach nichts Sinnvolles. Wie soll ein Laie da überhaupt etwas erfahren? OK. Ich erstelle einen INDEX auf eine Spalte und dann? Muss ich dann die Spalte abfragen oder den INDEX_NAMEN? Ich finde einfach keine Antwort und suche wie ein verrückter seit mehrerereren Stunden. !!

--- edit 4

Selbstgespräche machen Spass =)

Hab was gefunden


```
$abfrage = "SELECT COUNT(DISTINCT IP) as Besucher FROM `".$counter."` USE KEY (idx_datum) WHERE DATE(Datum) = '".$sqldatum."'";
```

Also mit USE KEY (index_name) greift man wohl auf den index zu. scheeeeeint theoretisch zu klappen. also ich sehe eine 10fache verschnellerung beim ersten blick, von 50 auf 5 sekunden...jetzt müsst ich nochmal die unnötig doppelten abfragen optimieren und könnt es eventuell halbieren....


--edit 5

ich kann nicht mehr :-! ich hab im falschen ordner programmiert als da wo ich mir das ergebnis angeschaut habe. ich hab nie was verändert Oô ich versteh die welt nicht mehr.
und ich hab mich zu früh gefreut. ich habe null ahnung warum es auf einmal so schnell ging. jedenfalls ist es wieder sehr langsam :/


----------



## SeeKing (30. Juni 2009)

deintag85 hat gesagt.:


> Hallo,
> ich hab diverse Befehle ausprobiert in PHPMYADMIN aber keines hat irgendwie funktioniert. Dauernd bekam ich eine Fehlermeldung dass der Befehl falsch sei.
> 
> Dann bin ich darauf gestossen dass man mit einem Klick einen Index erstellen kann. Das hab ich für beide Spalten gemacht. "ALTER TABLE xxx ADD INDEX(IP)" wurde dann ausgeführt ebenso wie für das Datum. Das heisst ja in meiner Abfrage muss ich nichts ändern, oder?... Weil von der Geschwindigkeit hat sich nichts getan :-/
> ...


Also um mal ein wenig Licht in diesem Dschungel zu bekommen.

Du kannst Indexe auf mehrere Arten erstellen:

```
ALTER TABLE vorhandene_table ADD INDEX 'ie_vorhandene_table_IP' (IP);
```
Mit diesem Kommando fügst du der existierenden Tabelle "vorhandene_table" den Index ie_vorhandene_table_IP hinzu (Namenskonvention: Inverted Entry auf Tabelle "vorhandene_table" verknüpftes Feld(er) "IP")

statt "ADD" kannst du auch "DROP" oder "MODIFY" schreiben. Hierzu empfehle ich dir die Dokumentation unter folgendem Link:
http://dev.mysql.com/doc/refman/5.1/de/alter-table.html

Eine zweite Möglichkeit einer vorhandenen Tabelle Indexe hinzuzufügen (nicht zu modifizieren) ist diese:

```
CREATE INDEX 'ie_vorhandene_table_IP' (IP) ON 'vorhandene_table';
```
Dieses Kommando tut exakt das selbe wie auch das obere. Natürlich gibts für beide Möglichkeiten noch etliche optionale Parameter die ein Finetuning ermöglichen.

Die Doku zu diesem Befehl findest du unter folgendem Link:
http://dev.mysql.com/doc/refman/5.1/de/create-index.html

Abfragen (Selektieren) tust du immer die Tabellenspalte niemals den Index. Es gibt zwar bei einigen exotischen Anwendungsfällen die Möglichkeit direkt auf die Indexes zurückzugreifen aber das wirst du bei der Datenbankgröße NIE! brauchen. Das sind noch Spielerreien.


```
SELECT vt.`IP` AS IP_Adresse
FROM vorhandene_table vt
WHERE vt.`IP` = '127.0.0.1';
```
Dieses SQL selektiert jetzt alle "localhost" Einträge aus deiner DB und benutzt automatisch den erstellten Index. Du kannst dies überprüfen in dem du einfach mal vor das "SELECT" noch "EXPLAIN" schreibst. Also so:


```
EXPLAIN SELECT vt.`IP` AS IP_Adresse
FROM vorhandene_table vt
WHERE vt.`IP` = '127.0.0.1';
```
Dann zeigt er dir an wie er was benutzt. Hab gerade nen Notfall bekommen schreib nachher weiter.

Gruss
SeeKing


----------

