Thread: Migrating data from MSAccess
I'm at the stage to rehearse data migration from MSAccess to PG. The PG db actually consolidates 4 MSAccess dbs, with all the possible data validations and integrity in place. I have some queries over the Access tables which then write the data to the ODBC-linked tables in the PG db. Data coming from Access cannot be fully trusted, so it must be loaded with regular inserts, in order for PG to perform all the validations. The problem is speed. If I run the query over the Access db only to view the records, it takes a couple of seconds. When it also writes to the PG db, it takes a couple of hours. The following is the output of a vmstat 60 2 on the PG server (PG7.2.1 on RH7.3) while appending records: procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 1 1 1 19496 10096 176188 617472 0 0 7 3 1 1 4 1 4 4 0 0 19496 10036 176196 617532 0 0 0 661 244 6560 78 20 2 Please comment. Is the PG server the bottleneck? Is there a faster way to perform this task? Thank you all.
Sounds like you have some on insert triggers that are taking a while to validate the data. You probably want to look for ways to speed up the triggers, and reduce the amount of validation that has to be done for every record if at all possible. Wes "Mihai Gheorghiu" <admin@planwithtan.com>@postgresql.org on 11/21/2002 12:45:26 PM Sent by: pgsql-general-owner@postgresql.org To: <pgsql-general@postgresql.org> cc: Subject: [GENERAL] Migrating data from MSAccess I'm at the stage to rehearse data migration from MSAccess to PG. The PG db actually consolidates 4 MSAccess dbs, with all the possible data validations and integrity in place. I have some queries over the Access tables which then write the data to the ODBC-linked tables in the PG db. Data coming from Access cannot be fully trusted, so it must be loaded with regular inserts, in order for PG to perform all the validations. The problem is speed. If I run the query over the Access db only to view the records, it takes a couple of seconds. When it also writes to the PG db, it takes a couple of hours. The following is the output of a vmstat 60 2 on the PG server (PG7.2.1 on RH7.3) while appending records: procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 1 1 1 19496 10096 176188 617472 0 0 7 3 1 1 4 1 4 4 0 0 19496 10036 176196 617532 0 0 0 661 244 6560 78 20 2 Please comment. Is the PG server the bottleneck? Is there a faster way to perform this task? Thank you all. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
On Thursday 21 Nov 2002 9:26 pm, wsheldah@lexmark.com wrote: > Sounds like you have some on insert triggers that are taking a while to > validate the data. You probably want to look for ways to speed up the > triggers, and reduce the amount of validation that has to be done for every > record if at all possible. I think Wes is right here, but there are a couple of other things to check: 1. Try updating 1000 rows of a simple dummy table - how long does it take? 2. Same again but wrapped within BEGIN;...COMMIT; so it's one transaction. How long now? If both are slow, look to your ODBC. If only the first is slow, try batching updates into transactions containing somewhere between 100-10,000 rows. If it turns out PG is slow, we'll need to know more about the database and the validation etc. you have on it. -- Richard Huxton > "Mihai Gheorghiu" <admin@planwithtan.com>@postgresql.org on 11/21/2002 > 12:45:26 PM > > Sent by: pgsql-general-owner@postgresql.org > > > To: <pgsql-general@postgresql.org> > cc: > Subject: [GENERAL] Migrating data from MSAccess > > I'm at the stage to rehearse data migration from MSAccess to PG. The PG db > actually consolidates 4 MSAccess dbs, with all the possible data > validations > and integrity in place. I have some queries over the Access tables which > then write the data to the ODBC-linked tables in the PG db.