Grosse Tabelle uebernehmen

Ich stehe vor folgendem Problem:

Ich möcht meine Daten neu organisieren und deshalb eine alte Tabelle
auslesen und die Daten in einer neuen, etwas anders strukurierten Tabelle
in einer anderen Datenbank einfügen.

Im Prinzip heißt das die eine Tabelle auslesen und in die andere schreiben.
Leider ist die Tabelle ziemlich umfangreich (39.251 Datensätze mit jeweils
97 Bytes Zeilengröße). Mein Provider gibt mir nicht unendlich viel Speicher
für meine php-Skripte. Das Unterfangen bricht deshalb nach einiger Zeit mit
einer Fehlermeldung ab:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to
allocate 10240 bytes) in ...

Die alte Tabelle ist eine MyIsam-Tabelle, die neue InnoDB, falls diese Info
für die Lösung wichtig ist. Zugriff auf die Daten erfolgt mit php 5 und
MySQL 5 über PDO und ZendDB (Zend Framework).

Auch wenn ich das Auslesen mit Limit in Abschnitte auftreile ist nach
Datensatz Nummer 12568 schluss. Zum Testen lese ich die Daten erstmal nur
aus un gebe die auszuführenden SQL-Zeilen auf dem Bildschirma aus. Das
heißt, dass der Fehler schon beim reinen Auslesen der Daten auftritt.

Im Prinzip kann ich das Skript einfach in vier Etapen starten. Von
Datensatz 1-12000, 12001-24000, 24001-36000, 36001-Rest, aber das ist doch
wohl eher eine Hackerlösung.

Gibt es eine bessere?

Martin

Re: Grosse Tabelle uebernehmen

Martin Lemke wrote:

> Ich möcht meine Daten neu organisieren und deshalb eine alte Tabelle
> auslesen und die Daten in einer neuen, etwas anders strukurierten Tabelle
> in einer anderen Datenbank einfügen.
>
> Leider ist die Tabelle ziemlich umfangreich (39.251 Datensätze mit jeweils
> 97 Bytes Zeilengröße).

Also ein winziges Tabellchen von nicht mal 4MB - das haben moderne
Prozessoren als Cache!

> Mein Provider gibt mir nicht unendlich viel Speicher
> für meine php-Skripte. Das Unterfangen bricht deshalb nach einiger Zeit mit
> einer Fehlermeldung ab:
>
> Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to
> allocate 10240 bytes) in ...

[Frickel-Lösung]

> Gibt es eine bessere?

Na klar! Laß die Daten einfach in der Datenbank. Du brauchst dazu nur
ein einziges SQL-Statement abzusetzen:

INSERT INTO schema_neu.tabelle_neu SELECT ... FROM tabelle_alt

Die Umorganisation der Daten machst durch passende Ausdrücke im SELECT.
Notfalls in mehreren Schritten mit temporären Zwischentabellen.

Einzige Voraussetzung: neue und alte Tabelle müssen sich auf dem
gleichen MySQL-Server befinden.


XL

Re: Grosse Tabelle uebernehmen

Martin Lemke schrieb:

> Ich möcht meine Daten neu organisieren und deshalb eine alte Tabelle
> auslesen und die Daten in einer neuen, etwas anders strukurierten Tabelle
> in einer anderen Datenbank einfügen.
>
> Im Prinzip heißt das die eine Tabelle auslesen und in die andere schreiben.
> Leider ist die Tabelle ziemlich umfangreich (39.251 Datensätze mit jeweils
> 97 Bytes Zeilengröße).

Verzeihung, aber das ist alles andere als umfangreich. Die Tabellen in
meinen Datenbanken bewegen sich in der Größenordnung bis 2 Millionen
Datensätzen bei Recordgrößen bis 1KB, und auch das ist noch relativ klein.

> Mein Provider gibt mir nicht unendlich viel Speicher
> für meine php-Skripte. Das Unterfangen bricht deshalb nach einiger Zeit mit
> einer Fehlermeldung ab:
>
> Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to
> allocate 10240 bytes) in ...

Wie kopierst Du denn die Tabelle? Liest Du sie komplett ein und
schreibst sie dann komplett wieder raus? Ist das denn notwendig?
Unterscheiden sich die Tabellenstrukturen so stark, dass Du die
Umstrukturierung nicht on the fly erledigen kannst?

Tabellen zu kopieren kann man normalerweise komplett der Datenbank
überlassen. Bei umfangreicheren Manipulationen mag es sinnvoll (und
vielleicht auch notwendig) sein, die Datensätze einzeln zu lesen und
einzeln wieder zu speichern. Eine Tabelle komplett einzulesen und dann
wieder auszugeben, sieht mir aber eher nach einem grundsätzlichen
Designfehler aus.

> Die alte Tabelle ist eine MyIsam-Tabelle, die neue InnoDB, falls diese Info
> für die Lösung wichtig ist.

Irrelevant.

> Auch wenn ich das Auslesen mit Limit in Abschnitte auftreile ist nach
> Datensatz Nummer 12568 schluss. Zum Testen lese ich die Daten erstmal nur
> aus un gebe die auszuführenden SQL-Zeilen auf dem Bildschirma aus. Das
> heißt, dass der Fehler schon beim reinen Auslesen der Daten auftritt.

Erst einmal halte ich LIMIT für das schlechteste Mittel der Wahl, um
große Datenmengen in kleinen Häppchen zu verarbeiten. Damit zwingst Du
die Datenbank, jedesmal denselben Request durchzuführen, um danach die
gewünschten Datensätze auszuwählen zu können. Da ist es besser, _einen_
SELECT ohne LIMIT absetzen und danach _alle_ Datensätze _einzeln_
einzulesen, zu bearbeiten und wieder auszugeben.

Dann hätte mich interessiert, wann _genau_ der Fehler eintritt: beim
Abschicken des SELECT, beim Lesen des Satzes oder beim Ausgeben des
SQL-Statements.

> Im Prinzip kann ich das Skript einfach in vier Etapen starten. Von
> Datensatz 1-12000, 12001-24000, 24001-36000, 36001-Rest, aber das ist doch
> wohl eher eine Hackerlösung.

LIMIT ist auch "wohl eher eine Hackerlösung".

Gruß. Claus

Re: Grosse Tabelle uebernehmen

Claus Reibenstein schrieb:

> Verzeihung, aber das ist alles andere als umfangreich.

Wie auch immer. Die Menge macht Probleme; auch wenn die Tabelle vielleicht
nur die größte der Winzlinge darstellt.

> Wie kopierst Du denn die Tabelle? Liest Du sie komplett ein und
> schreibst sie dann komplett wieder raus?

So hatte ich mir das vorgestellt.

> Unterscheiden sich die Tabellenstrukturen so stark, dass Du die
> Umstrukturierung nicht on the fly erledigen kannst?

Das kann ich nicht beurteilen. Vermutlich jedoch nicht. Zwei Datenfelder
werden zu einem einzigen in einem anderen Format zusammengefasst. Ein
numerischer Index wird hinzugefügt.

> Tabellen zu kopieren kann man normalerweise komplett der Datenbank
> überlassen.

Da das keine Standardaufgabe ist, war mir die prinzipiell unständlichere
Lösung die näherliegende, weil sie ohne viel Nachschlagen implementierbar
war.

> Da ist es besser, _einen_
> SELECT ohne LIMIT absetzen und danach _alle_ Datensätze _einzeln_
> einzulesen, zu bearbeiten und wieder auszugeben.

Dabei ging dem Programm ja leider der Speicher aus.

> Dann hätte mich interessiert, wann _genau_ der Fehler eintritt: beim
> Abschicken des SELECT, beim Lesen des Satzes oder beim Ausgeben des
> SQL-Statements.

Das kann ich nicht näher eingrenzen. Inzwischen habe ihc die Tabelle in
Häppchen konvertiert, aber in meinem System harren noch andere Tabellen der
Bearbeitung.

Ich habe einige Designfehler gemacht, die ich als hinderlich hinsichtlich
der Wartbarkeit einstufen würde. "Schweren Designfehler" würde ich das
nicht nennen.

Die alten Datenstrukturen lasse ich erstmal bestehen, aber die neuen sollen
parallel aufgebaut werden um nach ausgiebiger Testung die alte abzulkösen.

Ich werde mir Euer beider Anregungen mal durch den Kopf gehen lassen und
über SQL nachlesen.

Martin

Re: Grosse Tabelle uebernehmen

Martin Lemke schrieb:

> Claus Reibenstein schrieb:
>
>> Da ist es besser, _einen_
>> SELECT ohne LIMIT absetzen und danach _alle_ Datensätze _einzeln_
>> einzulesen, zu bearbeiten und wieder auszugeben.
>
> Dabei ging dem Programm ja leider der Speicher aus.

Das kann eigentlich nicht sein. Wenn Du die Datensätze einzeln
bearbeitest, befindet sich ja immer nur einer im Arbeitsspeicher. Wie
soll da der Speicher ausgehen?

Ich würde mal auf einen Programmfehler tippen. Kannst Du Deinen Code mal
posten?

Gruß. Claus

Re: Grosse Tabelle uebernehmen

Martin Lemke wrote:
> Ich möcht meine Daten neu organisieren und deshalb eine alte Tabelle
> auslesen und die Daten in einer neuen, etwas anders strukurierten Tabelle
> in einer anderen Datenbank einfügen.

create table neu like alt;
alter table neu engine=innodb;
alter table neu drop column brauchnichtmehr;
alter table neu add column dieabernun integer not null default 10;

insert into neu ( c1, c2, c3, dieabernun )
select c1, c2, c3, brauchichnichtmehr/10
from alt
where c1 between 1 and 1000;

Braucht genau gar keinen Speicher in PHP.

Kris

Re: Grosse Tabelle uebernehmen

Claus Reibenstein schrieb:

> Das kann eigentlich nicht sein. Wenn Du die Datensätze einzeln
> bearbeitest, befindet sich ja immer nur einer im Arbeitsspeicher. Wie
> soll da der Speicher ausgehen?

Das ist meine Interpretation der Fehlermeldung:
Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to
allocate 10240 bytes) in ...

Gibt es eine andere?

> Ich würde mal auf einen Programmfehler tippen.

Das glaube ich nicht, denn in Etappen hat es ja funkioniert (die
Frickel-Lösung).

> Kannst Du Deinen Code mal posten?

$db=Zend_Db::factory('PDO_Mysql',array ('host' => 'mysql5.maaaddin.de1.cc',
'username' => 'XXXXXXXX', 'password' => 'YYYYYYYY', 'dbname' =>
'ZZZZZZ'));
$sql="SELECT * FROM sf_platnick_gd";
$rows=$db->fetchall($sql);

Martin

Re: Grosse Tabelle uebernehmen

Kristian Köhntopp schrieb:

> Braucht genau gar keinen Speicher in PHP.

Vielen Dank für den sehr konkreten Tipp. Zunächst einmal nehme ich mir
Axels Rat zu Herzen: "Laß die Daten einfach in der Datenbank." Damit bin
ich schon einmal ein selbstgemachtes Problem los.

Nun steht einer reinen SQL nicht mehr viel im Wege. Eventuell mangelnde
SQL-Kenntnisse kann ich durch Nachlesen aufstocken.

Und einmal mehr mache ich die immer gleiche Erfahrung: Quick'n dirty macht
nur Scherereien. Und letztenendes habe ich es quick'n dirty überhaupt zu
verdanken, dass ich nun meine Daten umorganisieren muss, weil
administrativer Aufwand anfällt, der bei vernünftiger Konzeption erheblich
geringer ausgefallen wäre.

Martin