Re: [HACKERS] PostgreSQL Tuning Results

2003-02-13 Thread Curt Sampson
On Wed, 12 Feb 2003, [ISO-8859-1] Hans-J$B|(Brgen Sch$Bv(Bnig wrote:
(B
(B Be careful with sort_mem - this might lead to VERY unexpected results. I
(B did some testing on my good old Athlon 500 with a brand new IBM 120 Gigs
(B HDD. Reducing the sort_mem gave me significantly faster results when
(B sorting/indexing 20.000.000 randon rows.
(B
(BActually, the results are completely expected once you know what's
(Bexactly is going on. I found it weird that my sorts were also slowing
(Bdown with more sort memory until Tom or Bruce or someone pointed out to
(Bme that my stats said my sorts were swapping.
(B
(BIf I'm understanding this correctly, this basically meant that my sort
(Bresults would start hitting disk becuase they were being paged out to
(Bswap space, but then once the block was sorted, it would be read in
(Bagain from disk, and then written out to disk again (in a different
(Bplace), creating a lot more I/O than was really necessary.
(B
(BThis strikes me, too, as another area where mmap might allow the system
(Bto do a better job with less tuning. Basically, the sort is getting
(Bsplit into a bunch of smaller chunks, each of which is individually
(Bsorted, and then you merge at the end, right? So if all those individual
(Bchunks were mmaped, the system could deal with paging them out if and
(Bwhen necessary, and for the sorts you do before the merge, you could
(Bmlock() the area that you're currently sorting to make sure that it
(Bdoesn't thrash.
(B
(BIf the VM system accepts hints, you might also get some further
(Boptimizations because you can tell it (using madvise()) when you're
(Bdoing random versus sequential access on a chunk of memory.
(B
(Bcjs
(B-- 
(BCurt Sampson  [EMAIL PROTECTED]   +81 90 7737 2974   http://www.netbsd.org
(BDon't you know, in this new Dark Age, we're all light.  --XTC
(B
(B---(end of broadcast)---
(BTIP 4: Don't 'kill -9' the postmaster



Re: [HACKERS] PostgreSQL Tuning Results

2003-02-13 Thread Hans-Ju"rgen Scho"nig

(B
(BActually, the results are completely expected once you know what's
(Bexactly is going on. I found it weird that my sorts were also slowing
(Bdown with more sort memory until Tom or Bruce or someone pointed out to
(Bme that my stats said my sorts were swapping.
(B  
(B
(B
(Bthis way my first expectation but since the machine was newly booted and
(Bhad 1/2 gig of ram (nothing running but PostgreSQL) I couldn't believe
(Bin that theory ...
(BMaybe but I couldn't verify that ...
(BOf course swapping is worse than anything else.
(B
(BThis strikes me, too, as another area where mmap might allow the system
(Bto do a better job with less tuning. Basically, the sort is getting
(Bsplit into a bunch of smaller chunks, each of which is individually
(Bsorted, and then you merge at the end, right? So if all those individual
(Bchunks were mmaped, the system could deal with paging them out if and
(Bwhen necessary, and for the sorts you do before the merge, you could
(Bmlock() the area that you're currently sorting to make sure that it
(Bdoesn't thrash.
(B
(B
(BAs far as I have seen in the source code they use Knuth's tape
(Balgorithm. It is based on dividing, sorting, and merging together.
(B
(BIf the VM system accepts hints, you might also get some further
(Boptimizations because you can tell it (using madvise()) when you're
(Bdoing random versus sequential access on a chunk of memory.
(B
(Bcj
(B
(B
(Bit is an interesting topic. the result of the benchmark is very clock
(Bspeed depedent (at least in case my of my data structure).
(B
(BHans
(B
(B-- 
(BCybertec Geschwinde . Schoenig
(BLudo-Hartmannplatz 1/14; A-1160 Wien
(BTel.: +43/1/913 68 09 oder +43/664/233 90 75
(BURL: www.postgresql.at, www.cybertec.at, www.python.co.at, www.openldap.at
(B
(B
(B
(B---(end of broadcast)---
(BTIP 3: if posting/reading through Usenet, please send an appropriate
(Bsubscribe-nomail command to [EMAIL PROTECTED] so that your
(Bmessage can get through to the mailing list cleanly



Re: [HACKERS] PostgreSQL Tuning Results

2003-02-12 Thread Ron Mayer

Christopher Kings-Lynne wrote:

I reckon that sort_mem is the hardest thing to optimise1


Agreed... in part because it depends a lot on the query.

Also, if I understand correctly sort_mem not only affects sorts
but also hash table stuff as well, right?  If that's true for
the new hash aggregates, I think this means large sort_mem settings 
will become even more useful for data-warehouse-type applications.


One thing I've been wondering, is if sort_mem could be 
per connection/backend-process instead of per sorting operation 
so that sort_mem could be set more aggressivelly without running out
of memory so easily with large queries.

If that's not possible (i.e. one couldn't know how many simultaneous 
sorts are needed beforehand), how about only let the first one or 
two get all the memory and make the rest use a smaller one.


Anecdote:
I have a reasonably large data warehouse (1e6 to 1e8 rows
in various tables) with quite a bit of data (500k rows) added
each day.  A lot of processing (7 hours/day) is spent loading
data and generating various aggregates.  In a couple places
in the ETL part of the data warehouse code I have:

  set sort_mem = 25;
  /// something that only needs a single sort
  set sort_mem =  65536;
  ...
  set sort_mem = 4096;
  /// some ugly aggregate-creating join generated by a reporting tool 
  set sort_mem = 65536;

Ron

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] PostgreSQL Tuning Results

2003-02-11 Thread Dann Corbit
 -Original Message-
 From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]] 
 Sent: Tuesday, February 11, 2003 8:54 PM
 To: Hackers; Advocacy
 Subject: [HACKERS] PostgreSQL Tuning Results
 
 
 Hi Everyone,
 
 I have just completed a basic set of benchmarking on our new 
 database server.  I wanted to figure out a good value for 
 shared_buffers before we go live.
 
 We are a busy ecommerce-style website and so we probably get 
 10 or 20 to 1 read transactions vs. write transactions.  We 
 also don't have particularly large tables.
 
 Attached are the charts for select only and tpc-b runs.  Also 
 attached is an OpenOffice.org spreadsheet with all the 
 results, averages and charts.  I place all these attachments 
 in the public domain, so you guys can use them how you wish.
 
 I installed pgbench, and set up a pgbench database with scale 
 factor 1.
 
 I then set shared_buffers to all the values between 2000 and 
 11000 and tested select and tcp-b with each.  I ran each test 
 3 times and averaged the values.  TPC-B was run after select 
 so had advantages due to the buffers already being filled, 
 but I was consistent with this.
 
 Machine:
 256MB RAM, FreeBSD 4.7, EIDE HDD,  1 Ghz
 
 TPC-B config:
 pgbench -c 64 -t 100 pgbench (Note: only 64 users here)
 
 SELECT config:
 pgbench -c 128 -t 100 -S pgbench (Note: full 128 users here)
 
 I'm not sure why 8000 and 9000 are low on tpc-b, it's odd.
 
 Anyway, from the attached results you can see that 4000 
 buffers gave the best SELECT only performance, whereas the 
 TPC-B stuff seemed to max out way up at 1 or so.  Since 
 there is a 20% gain in performance on TPC-B going from 4000 
 buffers to 5000 buffers and only a 2% loss in performance for 
 SELECTs, I have configured my server to use 5000 shared 
 buffers, eg. 45MB RAM.
 
 I am now going to leave it on 5000 and play with wal_buffers. 
  Is there anything else people are interested in me trying?

Keenly interested.  Who wouldn't want to know how to optimize it?
That's the hardest guideline to find.
 
 Later on, I'll run pg_autotune to see how its recommendation 
 matches my findings.

I would like to hear about that also.  Please report on it.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] PostgreSQL Tuning Results

2003-02-11 Thread Gavin Sherry
Hi Chris,

On Wed, 12 Feb 2003, Christopher Kings-Lynne wrote:

 Machine:
 256MB RAM, FreeBSD 4.7, EIDE HDD,  1 Ghz

Seems like a small amount of memory to be memory based tests with.

What about testing sort_mem as well. It would system to me that there
would be no negative to having infinite sort_mem given infinite memory,
though.

Gavin


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] PostgreSQL Tuning Results

2003-02-11 Thread Christopher Kings-Lynne
  Machine:
  256MB RAM, FreeBSD 4.7, EIDE HDD,  1 Ghz

 Seems like a small amount of memory to be memory based tests with.

Perhaps, but I'm benchmarking for that machine, not for any other.  The
results have to include the 256MB spec.

Also, the peak was 25MB of SHM, which still leave 231MB for the rest of the
system, so surely RAM is not the bottleneck here?

 What about testing sort_mem as well. It would system to me that there
 would be no negative to having infinite sort_mem given infinite memory,
 though.

Yeah, however I'm pretty sure that pgbench doesn't perform any sorts.

I reckon that sort_mem is the hardest thing to optimise1

Chris


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Re: [HACKERS] PostgreSQL Tuning Results

2003-02-11 Thread Hans-Jürgen Schönig
Gavin Sherry wrote:


Hi Chris,

On Wed, 12 Feb 2003, Christopher Kings-Lynne wrote:

 

Machine:
256MB RAM, FreeBSD 4.7, EIDE HDD,  1 Ghz
   


Seems like a small amount of memory to be memory based tests with.

What about testing sort_mem as well. It would system to me that there
would be no negative to having infinite sort_mem given infinite memory,
though.

Gavin


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
 


Be careful with sort_mem - this might lead to VERY unexpected results. I 
did some testing on my good old Athlon 500 with a brand new IBM 120 Gigs 
HDD. Reducing the sort_mem gave me significantly faster results when 
sorting/indexing 20.000.000 randon rows.
However, it would be nice to see the results of concurrent sorts.

   Hans






--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at http://www.postgresql.at, cluster.postgresql.at 
http://cluster.postgresql.at, www.cybertec.at 
http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org