Postgres/Perl performance help needed

[cbbrowne-HInyCGIudOg at public.gmane.org]

> Hi all,
> 
>    The intrepid "Asker of questions" is back (so sorry!).
> 
>    I am hoping I can pick the brains of those of you here who play with 
> postgresql a lot. Maybe you can help me configure the server to be more 
> efficient or suggest a different way to approach my code.
> 
>    I have run into a problem where I need to write data into a 
> postgresql database and it is unbearably slow to do the task. I have 
> been reading (and will continue to read after sending this) what I can 
> find on the postgresql.org site and elsewhere that google points me but 
> so far my attempts to resolve the problem have in fact made it worse.
> 
>    Specs: I have Fedora Core 1 on a Pentium3 650MHz CPU with 448MB RAM 
> (it's a box make of parts) running in runlevel 3 with no X up.
> 
>    I have tried increasing the amount of swap space by passing:
> 
> echo 128000000 > /proc/sys/kernel/shmmax
> 
>    to the kernel and editing /var /lib/pgsql/data/postgresql.cong to have:
> 
> shared_buffers = 15200
> sort_mem = 32168

Those changes are unlikely to have been helpful; they probably made
things WAY worse.

You just asked to allocate 121MB of shared memory (NOT SWAP), and that
every time you initiate a sort, 256MB will be allocated to that.  Drop
shared buffers down to a couple thousand 8K blocks, at most.  The
default sort_mem was probably OK.

You're probably going to do well enough with:
   shared_buffers = 2000
   sort_mem = 4096

>    Before I started messing with things I record a directory with 2,490 
> files and folders (just the names, obviously!) in 23 seconds which was 
> not reasonable. When I tried to record a filesystem with 175,000 records 
> it took 32 minutes... Since I have started tweaking the same number of 
> records takes 35 seconds.

Some _BIG_ wins come from grouping changes together into a single
transaction.

Another big win comes in having an index on file_dir on file_src_uuid...

   create index fduuid on file_dir (file_src_uuid);

It's probably good to have the following index:

   create index fd_name_parent on file_dir (file_name, file_parent_dir);

If you're running a modern version of PostgreSQL, running pg_autovacuum
would solve the need to periodically run ANALYZE to keep table
statistics up to date, and to clean out dead tuples via VACUUM.  If
that's not readily available, it's a good idea to run VACUUM ANALYZE on
this table every few thousand updates, as your processing will be
generating lots of garbage, as well as changing the statistics.
--
let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/postgresql.html
Rules of the Evil Overlord  #98. "If an attractive young couple enters
my realm,  I will carefully monitor  their activities. If  I find they
are  happy   and  affectionate,  I  will  ignore   them.   However  if
circumstance  have forced them  together against  their will  and they
spend  all their  time  bickering and  criticizing  each other  except
during the  intermittent occasions when  they are saving  each others'
lives  at  which point  there  are hints  of  sexual  tension, I  will
immediately order their execution." <http://www.eviloverlord.com/>
--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml