Thread: Re: 7.1.3 : copy from stdin is very slow!
>root@database:~ #zcat dump.out.gz | psql -Upostgres dbname -c "copy >table_name from stdin;" > >This crawls on 7.1.3, and it went very quickly on 7.0.3 ... I must >have a feature turned on that I shouldn't. Any ideas? If I need to >share more info, just let me know what I need to share. > >CG Been copying from stdin for about 33 hours... I hate to stop it now. Here's what I've done this morning to aid in perhaps getting this dump over with! Edited /usr/local/pgsql/data/postgresql.conf: max_connections = 64 sort_mem = 1024 shared_buffers = 2*max_connections fsync=false wal_buffers = 16 wal_files = 64 wal_sync_method = fsync wal_debug = 0 commit_delay = 0 commit_siblings = 5 checkpoint_segments = 6 checkpoint_timeout = 300 then: root@database:~ #killall -HUP postmaster ... Still crawling. I thought I understood properly the idea for removing fsync, and increasing the buffer and wal settings. No effect on the insert, tho... Any ideas or advice welcome! CG __________________________________________________ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com
Chris Gamache wrote: <snip> > shared_buffers = 2*max_connections This looks interesting. I thought the "2*max_connections" was supposed to be a commented line? Chris, do you really have the line : shared_buffers = 2*max_connections in your postgresql.conf, uncommented and all? I'm thinking that somehow PostgreSQL might actually be accepting it, and interpreting it as the # 2. And THAT would crawl. You should instead (by hand), get the value of your max_connections parameter (in the same .conf file), double it, and that's the lowest number you should use. i.e. if max_connections = 32, then you should be using shared_buffers of at least 64. if max_connections = 120, then you should be using shared_buffers of at least 240. etc. Hope that's helpful. :-) Regards and best wishes, Justin Clift <snip> > ... > Still crawling. I thought I understood properly the idea for removing fsync, > and increasing the buffer and wal settings. No effect on the insert, tho... > > Any ideas or advice welcome! > > CG > > __________________________________________________ > Do You Yahoo!? > Send FREE Valentine eCards with Yahoo! Greetings! > http://greetings.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
It didn't balk at the 2*max_connections, but I changed it to 128 anyway. I stopped the import after 34 hours... I shutdown and restarted the postmaster, hoping the new config values would make a difference. It seems very strange that I was able to import this data into my 7.0.3 database in under three hours. It would seem the same import is taking longer by several orders of magnitude with 7.1.3. There is one difference that I might make note of. Before (7.0.3) I used a mount point for the data drive of /usr/local/pgsql/data/base/ ... I have since moved the mount point up one level ... /usr/local/pgsql/data/ ... So data/ resides on the same drive that base/ does (7.1.3). It seemed more logical to do it that way. However, I have heard that by moving the pg_xlog to a different drive the performance is increased. I can't confirm this in the documentation, though. I'm still at a loss for what could be going on. --- Justin Clift wrote: > > > Chris Gamache wrote: > <snip> > > > shared_buffers = 2*max_connections > > This looks interesting. I thought the "2*max_connections" was supposed > to be a commented line? > > Chris, do you really have the line : > > shared_buffers = 2*max_connections > > in your postgresql.conf, uncommented and all? I'm thinking that somehow > PostgreSQL might actually be accepting it, and interpreting it as the # > 2. > > And THAT would crawl. > > You should instead (by hand), get the value of your max_connections > parameter (in the same .conf file), double it, and that's the lowest > number you should use. > > i.e. > > if max_connections = 32, then you should be using shared_buffers of at > least 64. > if max_connections = 120, then you should be using shared_buffers of at > least 240. > > etc. > > Hope that's helpful. > > :-) > > Regards and best wishes, > > Justin Clift > > > <snip> > > ... > > Still crawling. I thought I understood properly the idea for removing > fsync, > > and increasing the buffer and wal settings. No effect on the insert, tho... > > > > Any ideas or advice welcome! > > > > CG > > > > __________________________________________________ > > Do You Yahoo!? > > Send FREE Valentine eCards with Yahoo! Greetings! > > http://greetings.yahoo.com > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > -- > "My grandfather once told me that there are two kinds of people: those > who work and those who take the credit. He told me to try to be in the > first group; there was less competition there." > - Indira Gandhi > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __________________________________________________ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com
Chris Gamache <cgg007@yahoo.com> writes:
> I'm still at a loss for what could be going on.
You still haven't given us any real details, like say the schema for the
table being loaded. I'm wondering about triggers and foreign-key
references, myself.
regards, tom lane
I apologise for not giving details. I didn't know what details were necessary to give. I now know. As I found out, it was schema which was my downfall! Before my upgrade I dumped the schema, dropped the database, and recreated the database, and imported the schema back. I didn't remove a secondary index I had from 7.0.3. While it was copying, it was updating the index, and taking forever to do it! Tom, thank you for the kick in the right direction. (Note to self: Remove secondary indexes when using "COPY ... FROM STDIN" unless being paid by the hour.) Once removed, the table copy took less than three hours. CG --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Chris Gamache <cgg007@yahoo.com> writes: > > I'm still at a loss for what could be going on. > > You still haven't given us any real details, like say the schema for the > table being loaded. I'm wondering about triggers and foreign-key > references, myself. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org __________________________________________________ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com