Обсуждение: postgres.conf settings
Hello, I'm trying to tune the parameters in postgres.conf to improve the performance of my database (where performance = 'make queries run faster'). I would be very grateful if you could give me some comments about my choice of configuration settings below (did I do anything very silly? Am I missing something relevant?). This my situation: 1. I installed postgresql (8.4) on my own machine to manage my own data. So I'm its only user. Queries are not complicated but should handle tables with (tens of) millions of rows. 2. Hardware: Laptop (DELL Latitude E6500) with 3.48 GB of RAM; Intel Core 2 Duo Mobile Processor P8600 2.40 GHz; running Windows XP. 160 GB hard disk (+ an external one of 640 GB). 3. Size of database: The /data directory is c.ca 37 GB, 88 tables in the main schema. 4. Raw data is backed-up elsewhere so I'm not terribly worried about risks of losing data. And these are the entries in postgres.conf that I changed from default (after reading some literature/docs): max_connections = 20 # Default was 100 shared_buffers = 512MB # min 128kB # Default was 32MB work_mem = 256MB # min 64kB default was 1MB wal_buffers = 1MB # min 32kB default was 64kb checkpoint_segments = 30 # in logfile segments, min 1, 16MB each; default was 3 checkpoint_timeout = 30min # range 30s-1h; default was 3 effective_cache_size = 1GB # Default 128MB I understand that tuning is a very database specific issue, but even some general pointers would help me... Many thanks! Dario -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.
On Sun, Aug 29, 2010 at 06:02:14PM +0100, Dario Beraldi wrote: > Hello, > > I'm trying to tune the parameters in postgres.conf to improve the > performance of my database (where performance = 'make queries run > faster'). I would be very grateful if you could give me some comments > about my choice of configuration settings below (did I do anything very > silly? Am I missing something relevant?). Not a bad start, but to be sure you really need to provide a bit more info, like: - How many simultaneous clients you're expecting? - Lots of updates, or is it read only? - Lots of simple queries, or fewer but more complex queries? Basically, what's the workload? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle
Вложения
Do check a previous thread:
http://www.mail-archive.com/pgsql-general@postgresql.org/msg149691.html
On Sun, Aug 29, 2010 at 10:32 PM, Dario Beraldi <dario.beraldi@ed.ac.uk> wrote:
Hello,
I'm trying to tune the parameters in postgres.conf to improve the performance of my database (where performance = 'make queries run faster'). I would be very grateful if you could give me some comments about my choice of configuration settings below (did I do anything very silly? Am I missing something relevant?).
This my situation:
1. I installed postgresql (8.4) on my own machine to manage my own data. So I'm its only user. Queries are not complicated but should handle tables with (tens of) millions of rows.
2. Hardware: Laptop (DELL Latitude E6500) with 3.48 GB of RAM; Intel Core 2 Duo Mobile Processor P8600 2.40 GHz; running Windows XP. 160 GB hard disk (+ an external one of 640 GB).
3. Size of database: The /data directory is c.ca 37 GB, 88 tables in the main schema.
4. Raw data is backed-up elsewhere so I'm not terribly worried about risks of losing data.
And these are the entries in postgres.conf that I changed from default (after reading some literature/docs):
max_connections = 20 # Default was 100
shared_buffers = 512MB # min 128kB # Default was 32MB
work_mem = 256MB # min 64kB default was 1MB
wal_buffers = 1MB # min 32kB default was 64kb
checkpoint_segments = 30 # in logfile segments, min 1, 16MB each; default was 3
checkpoint_timeout = 30min # range 30s-1h; default was 3
effective_cache_size = 1GB # Default 128MB
I understand that tuning is a very database specific issue, but even some general pointers would help me...
Many thanks!
Dario
--
Dr. Dario Beraldi
Institute of Evolutionary Biology
University of Edinburgh
West Mains Road
Edinburgh EH9 3JT
Scotland, UK
--
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
And don't forge the wiki! http://wiki.postgresql.org/wiki/Performance_Optimization On Sun, Aug 29, 2010 at 12:14 PM, Sandeep Srinivasa <sss@clearsenses.com> wrote: > Do check a previous thread: > http://www.mail-archive.com/pgsql-general@postgresql.org/msg149691.html > > On Sun, Aug 29, 2010 at 10:32 PM, Dario Beraldi <dario.beraldi@ed.ac.uk> > wrote: >> >> Hello, >> >> I'm trying to tune the parameters in postgres.conf to improve the >> performance of my database (where performance = 'make queries run faster'). >> I would be very grateful if you could give me some comments about my choice >> of configuration settings below (did I do anything very silly? Am I missing >> something relevant?). >> >> This my situation: >> >> 1. I installed postgresql (8.4) on my own machine to manage my own data. >> So I'm its only user. Queries are not complicated but should handle tables >> with (tens of) millions of rows. >> 2. Hardware: Laptop (DELL Latitude E6500) with 3.48 GB of RAM; Intel Core >> 2 Duo Mobile Processor P8600 2.40 GHz; running Windows XP. 160 GB hard disk >> (+ an external one of 640 GB). >> 3. Size of database: The /data directory is c.ca 37 GB, 88 tables in the >> main schema. >> 4. Raw data is backed-up elsewhere so I'm not terribly worried about risks >> of losing data. >> >> And these are the entries in postgres.conf that I changed from default >> (after reading some literature/docs): >> >> max_connections = 20 # Default was 100 >> shared_buffers = 512MB # min 128kB # Default was 32MB >> work_mem = 256MB # min 64kB default was 1MB >> wal_buffers = 1MB # min 32kB default was 64kb >> checkpoint_segments = 30 # in logfile segments, min 1, 16MB each; >> default was 3 >> checkpoint_timeout = 30min # range 30s-1h; default was 3 >> effective_cache_size = 1GB # Default 128MB >> >> >> I understand that tuning is a very database specific issue, but even some >> general pointers would help me... >> >> Many thanks! >> >> Dario >> >> -- >> >> Dr. Dario Beraldi >> Institute of Evolutionary Biology >> University of Edinburgh >> West Mains Road >> Edinburgh EH9 3JT >> Scotland, UK >> >> >> -- >> The University of Edinburgh is a charitable body, registered in >> Scotland, with registration number SC005336. >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- To understand recursion, one must first understand recursion.
Thanks Martijn and to the others who replied. Here's some more detail... Quoting Martijn van Oosterhout <kleptog@svana.org>: > Not a bad start, but to be sure you really need to provide a bit more > info, like: > > - How many simultaneous clients you're expecting? > - Lots of updates, or is it read only? > - Lots of simple queries, or fewer but more complex queries? > > Basically, what's the workload? > About the workload, I'll try to be a bit more specific: - Number of clients: I am the only person using my database. Usually I run one or two queries at the same time but I can have more sessions open at the same time (but typically <10). Hence I set max_connections = 20. - INSERT, UPDATE, DELETE queries are rare. - However, I use a lot of CREATE [TEMP] TABLE, CREATE INDEX - Also frequent is the use of COPY TO/FROM to import/export datafiles that will be handled by other applications (R, Python etc.) - SELECT queries: usually simple in design but handling large datasets which require JOIN and GROUP BY (e.g. "SELECT a, b FROM t1 INNER JOIN t2 ON t1.c = t2.c ORDER BY a;" where t1 and t2 could have millions of rows). - Background information: I'm a geneticist/bioinformatician using postgresql to manage the data I or others produce. I'm not a proper database programmer who run a web server. Many thanks for all and any feedback! Dario -- Dr. Dario Beraldi Institute of Evolutionary Biology University of Edinburgh West Mains Road Edinburgh EH9 3JT Scotland, UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.