Обсуждение: RESTORE IS TO SLOW
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.
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
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.
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.
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.
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.
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.