viele große Texte in MySQL verwalten

whyte
Posts: 61
Joined: 2006-01-09 14:21

viele große Texte in MySQL verwalten

Post by whyte »

Hallo,

ich habe mal eine Frage, wie ich am geschicktesten vorgehe.

Folgende Situation. Ich speicher eine Preis-Historie ab. Diese umfasst ca. 700000 Artikel mit mehreren Preisau- und abschwüngen.
Pro Artikel werden so zw. 5 und teilweise 20 Preise mit dem dazugehörigen Timestamp hinterlegt.

Ich hatte bisher eine Tabelle, in der es einen incemental ID, die Artikelnr sowie dann den Timestamp und dne neuen Preis gab.
Indexe hatte ich dann auf Artikelnummer und Timestamp.
Das Auslesen der Preise eines Artikels dauerte dann teilweise ca. 1.5 Sekunden.

Nun bin ich dazu übergegangen, eine neue Tabelle zu generieren, die den primary Key die Artikelnummer hat, sowie ein Textfeld. In das Textfeld serialize ich den Preishistorie Array (in PHP den Array mit serialize($preise) wegschreiben).
Hat den Vorteil, dass SQL nur noch einen Satz suchen muss, allerdings ist hier natürlich eine unbekannte Größe an Daten dahinter.

Hier muss dann zusätzlich bei jeder Preisänderung aber der komplette Datensatz gelesen werden, die neuen Preise hintendrangesetzt und dann wieder neu reingeschrieben werden. Da es auch neue Artikel geben kann, benutze ich hierfür dann den REPLACE.

Auch hier ist es nun, dass die Abfragen um die ca. 1.5 Sekunden dauern, der REPLACE sowie auch der SELECT.

Gibt es noch eine Möglichkeit, Texte mit unterschiedlichen Größen vernünftig abzuspeichern oder eine der 2 Möglichkeiten zu optimieren ?

gruß + danke
whyte
Top

User avatar
daemotron
Administrator
Administrator
Posts: 2800
Joined: 2004-01-21 17:44

Re: viele große Texte in MySQL verwalten

Post by daemotron »

Eine Historie ist eigentlich ein typisches Szenario für 1:n mit einer Foreign Key Constraint. Wie sieht es denn mit der Historie aus - soll die ins (theoretisch) Unendliche fortgeführt werden, oder hast Du eine Grenze vorgesehen, ab der alte Preise aus dem System fliegen können (also eine Art Fifo)?
“Some humans would do anything to see if it was possible to do it. If you put a large switch in some cave somewhere, with a sign on it saying 'End-of-the-World Switch. PLEASE DO NOT TOUCH', the paint wouldn't even have time to dry.” — Terry Pratchett, Thief of Time
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

eigentlich unendlich, deswegen sind bei einigen Artikeln auch 5 Preise, bei anderen schon 20 oder mehr da ...
Top

User avatar
daemotron
Administrator
Administrator
Posts: 2800
Joined: 2004-01-21 17:44

Re: viele große Texte in MySQL verwalten

Post by daemotron »

Dann ist serialisieren keine gute Lösung - stell Dir mal vor, Du hast ein Array mit einer drei- oder vielleicht 4stelligen Anzahl an Preisen da drin... die Zeit zum (de)serialisieren musst Du ja zur Query dazurechnen.

Welche Engine verwendest Du?
“Some humans would do anything to see if it was possible to do it. If you put a large switch in some cave somewhere, with a sign on it saying 'End-of-the-World Switch. PLEASE DO NOT TOUCH', the paint wouldn't even have time to dry.” — Terry Pratchett, Thief of Time
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Ja, das ist keine gute Lösung, stimmt.
myisam benutze ich.

Ich habe mir als Alternative noch das Auslagern auf die Platte ausgedacht, aber bei run 700.000 Artikeln werden das schon einige MB Plattenplatz ...
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Ok, die Randinfos:

System läuft in einem Proxmox VM, Host ist eine DualCore mit 2 GB Ram, der VM habe ich 1 GB Ram gegeben.
Da die Seite nicht wirklich gut besucht ist, kann man von Auslastung nicht so reden.

descripe PRICE_History:

Code: Select all

mysql> DESCRIBE PRICE_History;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| ARTid   | int(15) | NO   |     | 0       |       |
| History | text    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)


Der SQL Befehl:

Code: Select all

UPDATE PRICE_History SET
                                History = 'a:52:{i:1290913462;s:4:\"9.74\";i:1290788413;s:5:\"10.55\";i:1290600737;s:5:\"11.23\";i:1290133337;s:5:\"11.96\";i:1289718850;s:5:\"13.36\";i:1289266407;s:5:\"14.38\";i:1289178778;s:5:\"15.50\";i:1287210822;s:5:\"17.99\";i:1285824933;s:5:\"18.99\";i:1285400717;s:5:\"10.99\";i:1285301709;s:5:\"18.99\";i:1285128911;s:4:\"9.37\";i:1284695934;s:5:\"10.15\";i:1284523166;s:5:\"10.99\";i:1284353784;s:5:\"18.99\";i:1284093258;s:5:\"10.99\";i:1283746838;s:5:\"18.99\";i:1283571558;s:5:\"10.99\";i:1283398479;s:5:\"18.99\";i:1283138077;s:5:\"11.99\";i:1282792865;s:5:\"10.99\";i:1282620089;s:4:\"6.83\";i:1282540738;s:4:\"6.63\";i:1282446709;s:4:\"7.03\";i:1282373912;s:4:\"7.21\";i:1282301737;s:4:\"7.39\";i:1282097173;s:4:\"7.58\";i:1282023115;s:4:\"7.02\";i:1281933577;s:4:\"7.20\";i:1281842116;s:4:\"7.38\";i:1281763501;s:4:\"7.57\";i:1281671096;s:4:\"7.76\";i:1281591980;s:4:\"7.96\";i:1281497419;s:4:\"8.16\";i:1281393482;s:4:\"8.37\";i:1281322789;s:4:\"8.58\";i:1281229323;s:4:\"8.80\";i:1281150549;s:4:\"9.03\";i:1281068719;s:4:\"9.26\";i:1280977542;s:4:\"9.50\";i:1280808673;s:5:\"10.56\";i:1280471160;s:5:\"11.36\";i:1280208376;s:5:\"11.99\";i:1280113310;s:5:\"12.23\";i:1279596464;s:5:\"11.99\";i:1279508391;s:5:\"14.16\";i:1279070682;s:5:\"10.99\";i:1278900578;s:5:\"16.40\";i:1278360666;s:5:\"10.99\";i:1278298336;s:5:\"18.99\";i:1278000384;s:5:\"10.99\";i:1277693470;s:5:\"18.99\";}'
                        WHERE
                                ARTid = 522923
Date: 28.11.2010 04:04:25
Time: 3.00535297
File:/var/www/preismitteilung.de/www/Classes/class.PriceHandler.php Line:160


Vorher gab es eine andere Tabelle, in der pro Preiswechsel ein eigener Satz abgestellt wurde, bestehend aus id, ARTid,Datetime,NewPrice
vielleicht war das die bessere Wahl ... nur hier war die DB inkl de, Indexes mehr als 3 mal so groß
Last edited by whyte on 2010-11-28 14:54, edited 1 time in total.
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Der ARTid ist der primary Key, dachte das ist gleichzusetzen wie ein Index
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Du hast Recht, der hat ARTid nicht als Primary Key gemacht, ich hab jetzt sogar doppelte Einträge drin ...
Danke, das war wohl mal eins der Probleme.
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Also ich bin gerade an den Daten übertragen von der Table ohne PriKey in eine mit. Ich werd danach dann auch auf ARTid mal einen Index setzen, dachte immer, Primary Key reicht dazu aus.
Die DB ist auf der Platte ca. 120 MB groß, da ich keinen Index gesetzt habe, ist die Index Datei natürlich klein. Datensätze wie gesagt rund 700000 Sätze
Last edited by whyte on 2010-11-28 15:17, edited 1 time in total.
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Danke, ich habe nun das hier:

Code: Select all

mysql> SHOW INDEX FROM PRICE_History;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| PRICE_History |          0 | PRIMARY  |            1 | ARTid       | A         |      335186 |     NULL | NULL   |      | BTREE      |         |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)


Und auch ARTid ist die einzige Stelle, worüber gesucht wird.
Ich guck jetzt mal, ob das passt.
Wundert mich echt, dass ich den Primary Key vergessen habe ...
Top

Roger Wilco
Administrator
Administrator
Posts: 6001
Joined: 2004-05-23 12:53

Re: viele große Texte in MySQL verwalten

Post by Roger Wilco »

Du versuchst MySQL hier als Cache zu missbrauchen. Das ist keine allzu gute Idee.

Das von dir beschriebene Problem ließe sich viel einfacher und vermutlich auch performanter lösen, entweder durch einen naiv implementierten Disk Cache (Dateiname == ARTid, Dateiinhalt == History) oder einen beliebigen Key/Value Store.

Ersteres hat den Vorteil, dass sich Informationen problemlos und in konstanter Zeit an einen Datensatz anhängen lassen.
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Ja das hatte ich früher, nur das sind dann mehrere 100 MB Daten, das liegt an der Blocksize, da für jeden Artikel eine Datei angelegt wird.
Andererseits ist das echt das performanteste gewesen ...
Ich werd darauf zurück greifen, denke ich.
Top

Roger Wilco
Administrator
Administrator
Posts: 6001
Joined: 2004-05-23 12:53

Re: viele große Texte in MySQL verwalten

Post by Roger Wilco »

Was hindert dich daran, die Blockgröße an die zu speichernden Daten anzupassen?

Für so einen dateibasierten Cache würde ich ohnehin ein eigenes Dateisystem ohne Journal und solche Scherze anlegen.
Top

whyte
Posts: 61
Joined: 2006-01-09 14:21

Re: viele große Texte in MySQL verwalten

Post by whyte »

Ja, in einer VM wäre das eine Anpassung der Blockgröße für das komplette System ... andererseits hab ich genug Speicherplatz und beim Backup fällt das Problem ja weg.
Top

Roger Wilco
Administrator
Administrator
Posts: 6001
Joined: 2004-05-23 12:53

Re: viele große Texte in MySQL verwalten

Post by Roger Wilco »

Du kannst doch einfach ein Dateisystem in eine "Container-Datei" packen. Dazu musst du nicht dein Root-Dateisystem o. ä. ändern. ;)
Top