Php postgres select nach mysql kopieren

[knuffiwuffi]

Hallo,

habe auf meinem lokalen Rechner XAMPP mit MySQL laufen und ebenfalls (für eine Software) eine postgres Datenbank. In dieser Datenbank liegen an die 2mio Datensätze, die ich aber erst über ein Select aus verschiedenen Tabellen zusammensetzen muss. Nutze Win8.1 mit 16GB Ram 4x3,2Ghz.

Problem ist klar, mein php Script würde mir bei dem Select und anschließenden MySQL Import abschmieren.

Da ich den Import gerne über das PHP Script automatisiert haben möchte, muss ich mir nun eine Strategie überlegen, wie ich das Script in mehreren Schritten ausführen kann.

Über das postgres Select suche ich mir zunächst alle IDs, diese Abfrage ist nicht so groß wie wenn ich alle Daten direkt zusammenführe. Dann wollte ich in Schleifen die ID's in der MySQL mit den passenden Daten aus der postgres auffüllen.

So...und als Anfänger stehe ich nun im dunkeln. Ich habe mir mal 1mio IDs aus der postgres gezogen und als array ausgegeben. Das Browser-Fenster zeigt am Ende nur noch schwarz an.

$arr = pg_fetch_all($result);
    echo '<pre>';
    print_r($arr);
    echo '</pre>'; 

Ich vermute ich muss mit größeren Datenmengen anders umgehen. Mir kommen da "LOAD DATA" für MySQL und "Copy" für postgres in den Sinn. Aber wie kann ich das ganze mit meiner Select Abfrage als Filter in postgres verbinden? MySQL kenne ich ganz gut, postgres überhaupt nicht.

Wie gesagt, ich würde es gerne über ein php script lösen. Datenbankverbindungen klappen soweit. Ist ein privates Projekt mit dessen Hilfe ich mich in PHP verbessern möchte.

PS: Thread ist vielleicht bei den Datenbank-Freaks besser aufgehoben

[nikosch]

die ich aber erst über ein Select aus verschiedenen Tabellen zusammensetzen muss

die Frage ist viel zu allgemein gestellt.

[knuffiwuffi]

Die postgres Select Abfrage ist sehr umfangreich und würde den Speicher sprengen (2870 Wörter). Wenn ich nur die ID mit entsprechendem WHERE auf 2 weitere Spalten ausführe, kann ich die Anweisung auf 54 Wörter herunterbrechen. Ist bei 2mio Datensätzen aber immer noch zu aufwendig.

Das eigentliche Problem ist, daß ich aktuell keine Idee habe wie ich die Daten überführen kann. Einzig Schrittweise in Schleifen fällt mir ein. In einem Rutsch wird das problematisch.

Was mir jetzt noch einfallen würde: Das gesamte postgres Schema 1zu1 nach MySQL und in MySQL dann über Anweisungen anfangen zu arbeiten. Bei 100 neuen Einträgen in der postgres fällt dann aber ein ständiges update in der MySQL flach, da ich ja immer das gesamte Schema kopieren muss (außer ich setze eine flag in der postgres bei den Daten, die schon drüben sind).

[mermshaus]

Ich verstehe ehrlich gesagt noch immer nur Bahnhof.

Du führst eine ID auf zwei Spalten aus? Was soll so was bedeuten?

[nikosch]

Was mir jetzt noch einfallen würde: Das gesamte postgres Schema 1zu1 nach MySQL und in MySQL dann über Anweisungen anfangen zu arbeiten. Bei 100 neuen Einträgen in der postgres fällt dann aber ein ständiges update in der MySQL flach, da ich ja immer das gesamte Schema kopieren muss (außer ich setze eine flag in der postgres bei den Daten, die schon drüben sind).

Also wenn Du die Aktion öfter als ein mal ausführen musst, stimmt mit der Idee sowieso etwas nicht.

[knuffiwuffi]

Deshalb schreibe ich ja ins Anfänger Forum...ist für mich nicht so einfach zu erklären. Ich versuche es noch einmal...

Eine Anwendung auf meinem PC schreibt Daten, aufgeteilt auf mehrere Tables, in eine Postgres Datenbank. Ich kann mir die Daten über die Software als csv ausgeben und dann wieder in MySQL einlesen. Der Select ist entsprechend meinen Angaben anspruchsvoll für den PC, daher kann ich immer nur ca. 100.000 Datensätze auf einmal exportieren.

Anstelle der csv Lösung habe ich nun folgende Idee:

Ich möchte die Daten aus der Postgres mit einem PHP script direkt auslesen und in eine MySQL überführen. In der MySQL werden die Daten dann von mir aufbereitet und weiter ausgewertet. Das php Script hat natürlich auch das Problem mit dem Speicher. Ich kann nicht alle 2mio Datensätze auf einmal auslesen, da der Select für die Postgres so brutal ist.

Mein Lösungsansatz ist also das ganze in mehreren Schritten per Schleife durchzuführen und die Anzahl der gleichzeitig zu überführenden Datensätze zu begrenzen. Das Script kann ich theoretisch sehr lange laufen lassen. Nur das Problem mit dem Speicher muss ich lösen, da nicht alle Daten auf einmal reinpassen.

Edit:
Oder ich lasse mir die Daten (anstelle aus der Software) über das PHP Script in eine tmp csv ausgeben und lese diese dann per load data in die MySQL ein. Ich schlafe mal eine Nacht drüber.

[Arne Drews]

Auslesen und speichern in Blöcken, nicht alles auf mal, dann hast Du die Probleme nicht.
Ich weiß nicht ob man einen Dump von PostgreSQL zu MySQL anpassen kann, aber das wäre eine Alternative, zu der akretschmer evtl. was sagen kann, wobei Du dann natürlich das auch nicht über ein PHP-Script löst...

[akretschmer]

Auslesen und speichern in Blöcken, nicht alles auf mal, dann hast Du die Probleme nicht.
Ich weiß nicht ob man einen Dump von PostgreSQL zu MySQL anpassen kann, aber das wäre eine Alternative, zu der akretschmer evtl. was sagen kann, wobei Du dann natürlich das auch nicht über ein PHP-Script löst...

Kann man sicherlich. Stellen sich natürlich viele Fragen, hier nur mal 2:

• warum will man von PG zu MySQL? Das macht keinen Sinn
• Datentypen: PG kann erheblich mehr als MySQL, eine Migration PG->MySQL ist mehr als nur ein Schritt zurück

prinzipiell ist IMHO der Weg über CSV am einfachsten. In PG kannst Du das Resultat eines Select auch direkt als CSV ausgeben:

copy (hier dein select) to stdout csv;

Das kannst Du noch mit diversen Optionen für z.B. Trennzeichen etc. würzen.

MySQL sollte das dann einlesen können. Und noch einmal: ich halte das für eine Irrweg.

Andreas

[knuffiwuffi]

Ich hatte in MySQL schon die meisten Anweisungen für die Auswertung geschrieben. Ich denke aber, ich werde mir die Mühe machen tiefer in postgres zu schauen und einfach alles über postgres laufen zu lassen. Dann kann ich mir das exportieren sparen.

[akretschmer]

Ich denke aber, ich werde mir die Mühe machen tiefer in postgres zu schauen und einfach alles über postgres laufen zu lassen.

Gute Entscheidung!

[knuffiwuffi]

Erstmal Danke für eure Anregungen. Die Arbeit geht gut voran und die Angst vor Postgres hat sich gelegt. Das Prog bindet sich nun direkt an die Postgres der anderen Applikation an und mit den SQL Befehlen kann ich die Daten schnell umschichten. Damit habe ich bald die Grundlagen für 2 Datenbanken

Nun habe ich eine weitere Verständnißfrage, die mir unser Tutor (PHP/SQL) leider nicht gänzlich beantworten konnte (was nicht schlimm ist).

Ich habe das PHP Skript lokal mit XAMPP am laufen (Win8.1/ 65bit/ 3 x 3,2Ghz/ 16GB RAM). Jetzt geht es um die Performance Frage. Angenommen ich kopiere mit INSERT()SELECT() innerhalb der Postgres 1Mio Datensätze in einen anderen Table, dann meldet sich das Skript aufgrund Arbeitsspeicher überlauf ab (bis ca. 100.000 auf einmal geht). Postgres kann wohl nur max 2GB-3GB Arbeitsspeicher nutzen. In der php.ini unter C:\xampp\php habe ich nun folgende Werte für die Performance angepasst:

memory_limit=5000M
max_execution_time = 0
max_input_time = 0

Wenn ich alles richtig verstanden habe, dann kann das php script in einer unendlich Schleife durchgehend laufen.

1.
max_input_time = 0 Was genau macht diese Einstellung und sollte ich die Einstellungen in der php.ini vornehmen bzw. geht auch direkt im Skript? Welches sind die wichtigsten Performance Einstellungen? Da steht so viel in der php.ini

2.
Wird die unendlich Schleife unterbrochen, wenn ich mein Browser Fenster schließe oder erst wenn ich den Apache in XAMPP abschalte?

3.
Wie sieht das aus, wenn ich in der Schleife das kopieren mit INSERT()SELECT() einem LIMIT unterwerfe und diese Anweisung immer wieder in Schleife ausführe? Die Datenbank soll also solange 10.000 Datensätze rüberschaufeln, bis die 1Mio Datensätze kopiert sind. Kann ich damit ein überlaufen des Arbeitsspeichers verhindern, oder gibt es ggf. Befehle die da etwas zurück setzen können? In meiner vorherigen Lösung hatte ich einen Cronjob laufen, der das Script alle 5min neu angestoßen hat. Nun soll das Script lokal aber durchlaufen und nach dem kopieren ggf. noch weitere UPDATES ausführen.

[akretschmer]

Ich habe das PHP Skript lokal mit XAMPP am laufen (Win8.1/ 65bit/ 3 x 3,2Ghz/ 16GB

Du hast ein 65bit - Windows? Mit Paritätsbit, oder wie?

RAM). Jetzt geht es um die Performance Frage. Angenommen ich kopiere mit INSERT()SELECT() innerhalb der Postgres 1Mio Datensätze in einen anderen Table, dann meldet sich das Skript aufgrund Arbeitsspeicher überlauf ab (bis ca. 100.000 auf einmal geht).

welches Script?

Postgres kann wohl nur max 2GB-3GB Arbeitsspeicher nutzen.

Nein. Mehr. Das, was Du ihm gibst. Shared_buffers, work_mem sind entscheident.

test=*# create table foo as select s as id, random() as r, 'value ' || s::text as val from generate_Series(1,10000000) s;
SELECT 10000000
Time: 18133,158 ms
test=*# \d foo;
          Table "public.foo"
 Column |       Type       | Modifiers
--------+------------------+-----------
 id     | integer          |
 r      | double precision |
 val    | text             |

test=*# select count(*) from foo;
  count
----------
 10000000
(1 row)
test=*# create table foo2(id int, r float, val text);
CREATE TABLE
Time: 7,343 ms
test=*# insert into foo2 select * from foo;
INSERT 0 10000000
Time: 30768,828 ms

Ich fahre dabei sehr knappe Speicherwerte: 128MB Shared_buffers, 1 MB work_mem. Dennoch ausreichend. (Rechner ist Destop-Kiste mit Billig-SATA, 7 Jahre alt)

[knuffiwuffi]

Äh, 64bit Das php script meldet sich anscheinend doch nicht ab. Ich habe 1Mio Datensätze rüber bekommen, was aber 1-2min gedauert hat. Der SELECT ist sehr aufwendig da er mit JOINS über mehrere Tables geht.

Aber zurück zu der Frage nach dem Arbeitsspeicher. Wenn das php script anläuft, werden dann alle Anweisungen nacheinander abgearbeitet ohne den Speicher zwischendurch "zurück zu setzen"? Wie gehen php Skripte mit dem Arbeitsspeicher um? Mit unset z.B. kann ich doch variablen oder arrays wieder rausschmeißen aus dem Arbeitsspeicher, oder?

Als Ergänzung zu meinen Fragen: PostgreSQL 9.0.15 wird genutzt

Was kann ich in Postgres bzw. php.ini für die Performance einstellen? Wieviel % des Arbeitsspeichers z.B.?

[akretschmer]

Äh, 64bit Das php script meldet sich anscheinend doch nicht ab. Ich habe 1Mio Datensätze rüber bekommen, was aber 1-2min gedauert hat. Der SELECT ist sehr aufwendig da er mit JOINS über mehrere Tables geht.

Was dauert denn? Ich vermute ja mal, du machst das über PHP, also fädelst alle Datensätze durch PHP durch. Das dauert natürlich. Ich hab Dir gestern in 30 Sekunden 10Millionen Rows zwischen 2 Tabellen gezeigt - auf relativ schwacher Hardware.

Ansonsten: explain (analyse, buffers) select ...

Da siehst Du dann u.a.:

• den kompletten Abfrageplan
• geschätzte und reale Anzahl von Rows in jedem Schritt
• Verhältniss Hit/Miss für shared_buffers
• Speicherverbrauch und Art von Sortierungen (in memory, on disk)
• Verwendung welcher Indexe
• Zeitverbrauch je Schritt und gesamt

Alles andere ist keine Diskussionsgrundlage

Aber zurück zu der Frage nach dem Arbeitsspeicher.

Als Ergänzung zu meinen Fragen: PostgreSQL 9.0.15 wird genutzt

Was kann ich in Postgres bzw. php.ini für die Performance einstellen? Wieviel % des Arbeitsspeichers z.B.?

Faustregel: 25% RAM fü shared_buffers bei dedizierten Servern, aber auch nicht mehr als 10GB. Du hast Winddows und PG 9.0: ich bin mir nicht ganz sicher, ab wann auf diesem Exoten-OS PG 64bittig läuft, ich glaub das war erst ab 9.1. In Deinem Falle würde ich shared_buffers aber eh nicht größer als 2GB setzen (das geht mit 32bit), da ja noch mehr auf der Kiste ist. PHP auf Windows zu betreiben ist, naja, ...

Work_mem nur vorsichtig erhöhen, das gilt je laufende Query und je Abfrageknoten. Setzt man das zu hoch und hat z.B. 100 laufende Abfragen mit je 10 Knoten, die alle das Maximum allokieren und Du setzt das auf 1GB dann fliegt Dir die Kiste um die Ohren.
Also für den Start reichen da 4MB, dann schaut man sich explain wie oben gezeigt an.
Kann man sogar per session ändern.

Maintenance_work_mem kann man höher setzen, dient aber nicht für Abfragen, sondern z.B. wenn Indexe erstellt werden als RAM-Pool.

Bei großen Transaktionen dann noch wal_buffers auf bis zu 16MB anheben.


Du solltest in jeden Falle Dich mit Explain beschäftigen, Google nach 'postgres explaining explain', da findest diverse Vorträge und Erklärungen.

[knuffiwuffi]

Danke Dir für die Hinweise...aktuell läuft alles gut von der Hand. Ggf. frage ich noch einmal nach. Gruss