Thread: RESTORE IS TO SLOW

RESTORE IS TO SLOW

From
"marvin.deoliveira"
Date:
Hi.
I'm restoring a database (only rows) that has some tables with 9 millions
rows and others have even more.
It's going to slow ( more than 24 hours by now ).
I'm disabling the triggers but I guess if I drop the indexes it will have
more performance. Am I right?
If yes, does anyone have a script that generates the drop and create
indexes?






-----
Marcos Oliveira
And I still haven't found what I'm looking for...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RESTORE-IS-TO-SLOW-tp4808653p4808653.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: RESTORE IS TO SLOW

From
Lonni J Friedman
Date:
On Thu, Sep 15, 2011 at 3:32 PM, marvin.deoliveira
<marvin.deoliveira@gmail.com> wrote:
> Hi.
> I'm restoring a database (only rows) that has some tables with 9 millions
> rows and others have even more.
> It's going to slow ( more than 24 hours by now ).
> I'm disabling the triggers but I guess if I drop the indexes it will have
> more performance. Am I right?
> If yes, does anyone have a script that generates the drop and create
> indexes?

What part of the import process is slow?  If you're running with the
-v option to pg_restore you should be able to monitor what portion is
taking a long time.  Is it the indices, or the actual data as well?

Also, the number of rows isn't necessarily a reason for an import to
be slow.  An extreme example, if 9 millions rows only had a single
column that would likely import faster than a much smaller number of
rows with many columns.

Additionally, what kind of HW are you using and which version of PostgreSQL?

Anyway, here are some good suggestions on how to improve restore performance:
http://stackoverflow.com/questions/2094963/postgresql-improving-pg-dump-pg-restore-performance
http://postgresql.1045698.n5.nabble.com/Fastest-pq-restore-td3911438.html

Re: RESTORE IS TO SLOW

From
"marvin.deoliveira"
Date:
I'm using postgres 9.0.2 32 bits on Debian 5.
The hardware is a pc with 2 GB RAM, with 2 sata disks. Well, that's what I
have at the moment.

The restore was started like:  pg_restore -U postgres --data-only
--disable-triggers -v /bck/<dump file>.sql -d <database>
The pg_restore shows:
pg_restore: disabling triggers for <table name>
pg_restore: restoring data for table "<table name>"  <- that's taking more
time that I'd like.
pg_restore: enabling triggers for <table name>

I've read the posts that you send, and saw a lot of things that I didn't do.
I'm pretty sure that I'm going to have much better performance after change
the parameters.
But I still have a doubt: Droping the indexes, could give me more
performance?


-----
Marcos Oliveira
And I still haven't found what I'm looking for...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RESTORE-IS-TO-SLOW-tp4808653p4808758.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: RESTORE IS TO SLOW

From
Lonni J Friedman
Date:
On Thu, Sep 15, 2011 at 4:12 PM, marvin.deoliveira
<marvin.deoliveira@gmail.com> wrote:
> I'm using postgres 9.0.2 32 bits on Debian 5.
> The hardware is a pc with 2 GB RAM, with 2 sata disks. Well, that's what I
> have at the moment.
>
> The restore was started like:  pg_restore -U postgres --data-only
> --disable-triggers -v /bck/<dump file>.sql -d <database>
> The pg_restore shows:
> pg_restore: disabling triggers for <table name>
> pg_restore: restoring data for table "<table name>"  <- that's taking more
> time that I'd like.
> pg_restore: enabling triggers for <table name>
>
> I've read the posts that you send, and saw a lot of things that I didn't do.
> I'm pretty sure that I'm going to have much better performance after change
> the parameters.
> But I still have a doubt: Droping the indexes, could give me more
> performance?

The output that you included doesn't show it taking a long time on
indices.  Regardless, assuming that this PC has more than a single CPU
core, you should make use of the -j option to better parallelize the
import.  Beyond that, you should attempt to determine where the
bottleneck is in the import.  Is it CPU bound or disk(IO) bound?

You stated that you have two disks.  Hopefully your dumpfile is not on
the same disk as $PGDATA, or its going to have to read & write the
same disk, which is definitely going to hurt performance.

Re: RESTORE IS TO SLOW

From
"marvin.deoliveira"
Date:
The dump file is in a different disk than a database.
I need to have this restore finished ( it is about 80% done )
I will have to do it again, but with the changes.
I will return the results to this post.

-----
Marcos Oliveira
And I still haven't found what I'm looking for...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RESTORE-IS-TO-SLOW-tp4808653p4809160.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: RESTORE IS TO SLOW

From
"marvin.deoliveira"
Date:
I'll certainly follow your advices.
The log_truncate_on_rotation parameter is set to on.
I figured that saves a lot of disk space in this context. this is hurting
the performance?


-----
Marcos Oliveira
And I still haven't found what I'm looking for...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/RESTORE-IS-TO-SLOW-tp4808653p4810213.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: RESTORE IS TO SLOW

From
Lonni J Friedman
Date:
On Fri, Sep 16, 2011 at 3:27 AM, marvin.deoliveira
<marvin.deoliveira@gmail.com> wrote:
> I'll certainly follow your advices.
> The log_truncate_on_rotation parameter is set to on.
> I figured that saves a lot of disk space in this context. this is hurting
> the performance?

That option shouldn't have any impact on performance.