Search Postgresql Archives

Re: Tuning Postgres for single user manipulating large amounts of data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 09/12/2010 15:51, tv@xxxxxxxx wrote:
Hi, Im using Postgres 8.3 on a Macbook Pro Labtop.
I using the database with just one db connection to build a lucene
search index from some of the data, and Im trying to improve
performance. The key thing is that I'm only a single user but
manipulating large amounts of data , i.e processing tables with upto 10
million rows in them, so I think want to configure Postgres so that it
can create large temporary tables in memory
I'm not sure what exactly you mean by "manipulating data" - does that mean
reading or writing?

I'd definitely increase shared_buffers, work_mem (don't be afraid to set
work_mem to say 32MB or 64MB - this should be safe with a single
connection, although it depends on the queries). To improve writes,
increase checkpoint_segments etc.

If you really don't need extra safety - e.g. if you have all the data
backed up and just need to run some ad-hoc analysis (and it does not
matter if it crashes as you can recover it from backup), you can disable
fsync. This will make writes much faster, but it won't be safe in case of
crash.

DON'T DO THIS IF YOU NEED TO KEEP YOUR DATA SAFE!

But as you're runninng the app on your laptop, I guess you can live with
frync=off. You'll loose the consistency but you'll get better performance.

Hi ( sorry for the double posting, thought Id use the wrong email address but both have been posted anyway). As far as the db is concerned Im just reading data then writing the data to a lucene search index (which is outside of the database) , but my labtop is jut a test machine I want to run the same code on production. Why would work_mem not be safe at 64MB if I have 2GB of memory - what have I got to be careful of.

Paul

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux