Re: [PERFORM] [pgsql-hackers-win32] Performance on Win32 vs Cygwin

[Magnus Hagander]

>Hi,
>
>We are experiencing slow performance on 8 Beta 2 Dev3 on Win32 and are
>trying to determine why. Any info is appreciated.
>
>We have a Web Server and a DB server both running Win2KServer with all
>service packs and critical updates.
>
>An ASP page on the Web Server hits the DB Server with a simple 
>query that
>returns 205 rows and makes the ASP page delivered to the user 
>about 350K.
>
>On an ethernet lan a client pc perceives just under 1 sec 
>performance with
>the following DB Server configuration:
>PIII 550Mhz
>256MB RAM
>7200 RPM HD
>cygwin
>Postgresql 7.1.3
>PGODBC 7.3.2
>
>We set up another DB Server with 8 beta (same Web Server, same 
>network, same
>client pc) and now the client pc perceives response of just 
>over 3 sec with
>the following DB server config:
>PIII 700 Mhz
>448MB RAM
>7200 RPM HD
>8 Beta 2 Dev3 on Win32  running as a service
>
>Is the speed decrease because it's a beta?
>Is the speed decrease because it's running on Win instead of cygwin?
>
>We did not install cygwin on the new DB Server.

IIRC, previous versions of postgresql (< 8.0) did not correctly sync
disks when running on Cygwin. I'm not 100% sure, can someone confirm?
8.0 does, and I beleive it does both under native win32 and cygwin.

It's been my experience that the native version is slightly faster than
the cygwin one, but I've only compared 8.0 to 8.0.


//Magnus


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[PERFORM] Performance vs Schemas

[Igor Maciel Macaubas]

Hi all,
 
I recently migrated my database from schema 
'public' to multiple schema.
I have around 100 tables, and divided them in 
14 different schemas, and then adapted my application to use schemas as 
well.
I could percept that the query / insert / 
update times get pretty much faster then when I was using the old unique schema, 
and I'd just like to confirm with you if using schemas speed up the things. Is 
that true ?
 
What else I can do to speed up the query 
processing, best pratices, recommendations ... ? What about indexed views, does 
postgresql supports it?
 
Regards,
Igor--[EMAIL PROTECTED]
 

Re: [PERFORM] Performance vs Schemas

[Gregory S. Williamson]

Igor,

I'm not sure if it is proper to state that schemas are themselves speeding things up.

As an example, we have data that is usually accessed by county; when we put all of the 
data into one big table and select from it using a code for a county of interest, the 
process is fairly slow as there are several hundred thousand candidate rows from that 
county in a table with many millions of rows. When we broke out certain aspects of the 
data into schemas (one per county) the searches become very fast indeed because we can 
skip the searching for a specific county code with the relevant tables and there is 
less (unneeded) data in the table being searched.  

As always, "EXPLAIN ANALYZE ..." is your friend in understanding what the planner is 
doing with a given query.

See  for some useful information, 
especially under the performance tips section.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Igor Maciel Macaubas [mailto:[EMAIL PROTECTED]
Sent:   Thu 10/14/2004 11:38 AM
To: [EMAIL PROTECTED]
Cc: 
Subject:[PERFORM] Performance vs Schemas
Hi all,

I recently migrated my database from schema 'public' to multiple schema.
I have around 100 tables, and divided them in 14 different schemas, and then adapted 
my application to use schemas as well.
I could percept that the query / insert / update times get pretty much faster then 
when I was using the old unique schema, and I'd just like to confirm with you if using 
schemas speed up the things. Is that true ?

What else I can do to speed up the query processing, best pratices, recommendations 
... ? What about indexed views, does postgresql supports it?

Regards,
Igor
--
[EMAIL PROTECTED]




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

   http://archives.postgresql.org

Re: [PERFORM] Excessive context switching on SMP Xeons

[Dave Cramer]

Bill,

In order to manifest the context switch problem you will definitely
require clients to be set to more than one in pgbench. It only occurs
when 2 or more backends need access to shared memory.

If you want help backpatching Gavin's patch I'll be glad to do it for
you, but you do need a recent kernel.

Dave


On Thu, 2004-10-07 at 14:48, Bill Montgomery wrote:
> Michael Adler wrote:
> 
> >On Thu, Oct 07, 2004 at 11:48:41AM -0400, Bill Montgomery wrote:
> >  
> >
> >>Alan Stange wrote:
> >>
> >>The same test on a Dell PowerEdge 1750, Dual Xeon 3.2 GHz, 512k cache, 
> >>HT on, Linux 2.4.21-20.ELsmp (RHEL 3), 4GB memory, pg 7.4.5:
> >>
> >>Far less performance that the Dual Opterons with a low number of 
> >>clients, but the gap narrows as the number of clients goes up. Anyone 
> >>smarter than me care to explain?
> >>
> >>
> >
> >You'll have to wait for someone smarter than you, but I will posit
> >this: Did you use a tmpfs filesystem like Alan? You didn't mention
> >either way. Alan did that as an attempt remove IO as a variable.
> >
> >-Mike
> >  
> >
> 
> Yes, I should have been more explicit. My goal was to replicate his 
> experiment as closely as possible in my environment, so I did run my 
> postgres data directory on a tmpfs.
> 
> -Bill Montgomery
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
-- 
Dave Cramer
519 939 0336
ICQ # 14675561
www.postgresintl.com


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some wierdness ...

[Kevin Brown]

Tom Lane wrote:
> Kevin Brown <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> mmap() is Right Out because it does not afford us sufficient control
> >> over when changes to the in-memory data will propagate to disk.
> 
> > ... that's especially true if we simply cannot
> > have the page written to disk in a partially-modified state (something
> > I can easily see being an issue for the WAL -- would the same hold
> > true of the index/data files?).
> 
> You're almost there.  Remember the fundamental WAL rule: log entries
> must hit disk before the data changes they describe.  That means that we
> need not only a way of forcing changes to disk (fsync) but a way of
> being sure that changes have *not* gone to disk yet.  In the existing
> implementation we get that by just not issuing write() for a given page
> until we know that the relevant WAL log entries are fsync'd down to
> disk.  (BTW, this is what the LSN field on every page is for: it tells
> the buffer manager the latest WAL offset that has to be flushed before
> it can safely write the page.)
> 
> mmap provides msync which is comparable to fsync, but AFAICS it
> provides no way to prevent an in-memory change from reaching disk too
> soon.  This would mean that WAL entries would have to be written *and
> flushed* before we could make the data change at all, which would
> convert multiple updates of a single page into a series of write-and-
> wait-for-WAL-fsync steps.  Not good.  fsync'ing WAL once per transaction
> is bad enough, once per atomic action is intolerable.

Hmm...something just occurred to me about this.

Would a hybrid approach be possible?  That is, use mmap() to handle
reads, and use write() to handle writes?

Any code that wishes to write to a page would have to recognize that
it's doing so and fetch a copy from the storage manager (or
something), which would look to see if the page already exists as a
writeable buffer.  If it doesn't, it creates it by allocating the
memory and then copying the page from the mmap()ed area to the new
buffer, and returning it.  If it does, it just returns a pointer to
the buffer.  There would obviously have to be some bookkeeping
involved: the storage manager would have to know how to map a mmap()ed
page back to a writeable buffer and vice-versa, so that once it
decides to write the buffer it can determine which page in the
original file the buffer corresponds to (so it can do the appropriate
seek()).

In a write-heavy database, you'll end up with a lot of memory copy
operations, but with the scheme we currently use you get that anyway
(it just happens in kernel code instead of user code), so I don't see
that as much of a loss, if any.  Where you win is in a read-heavy
database: you end up being able to read directly from the pages in the
kernel's page cache and thus save a memory copy from kernel space to
user space, not to mention the context switch that happens due to
issuing the read().


Obviously you'd want to mmap() the file read-only in order to prevent
the issues you mention regarding an errant backend, and then reopen
the file read-write for the purpose of writing to it.  In fact, you
could decouple the two: mmap() the file, then close the file -- the
mmap()ed region will remain mapped.  Then, as long as the file remains
mapped, you need to open the file again only when you want to write to
it.


-- 
Kevin Brown   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 8: explain analyze is your friend

[PERFORM] View & Query Performance

[Igor Maciel Macaubas]

Hi all,
 
I'm trying to find smarter ways to dig data from 
my database, and have the following scenario:
 
table1
-- id
-- name
.
.
.
.
.
.
 
table2
-- id
-- number
.
.
.
.
.
.
 
I want to create a view to give me back just 
what I want:
The id, the name and the number.
I tought in doing the following:
create view my_view as select t1.id, 
t1.name, t2.number from table1 as t1, table2 as t2 where t1.id = 
t2.id;
 
Will this be enough fast ? Are there a faster 
way to make it work ?!
This table is mid-big, around 100K registers .. 

 
Regards,
Igor--[EMAIL PROTECTED]
 
 
 

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some wierdness ...

[Simon Riggs]

First off, I'd like to get involved with these tests - pressure of other
work only has prevented me.

Here's my take on the results so far:

I think taking the ratio of the memory allocated to shared_buffers against
the total memory available on the server is completely fallacious. That is
why they cannnot be explained - IMHO the ratio has no real theoretical
basis.

The important ratio for me is the amount of shared_buffers against the total
size of the database in the benchmark test. Every database workload has a
differing percentage of the total database size that represents the "working
set", or the memory that can be beneficially cached. For the tests that
DBT-2 is performing, I say that there is only so many blocks that are worth
the trouble caching. If you cache more than this, you are wasting your time.

For me, these tests don't show that there is a "sweet spot" that you should
set your shared_buffers to, only that for that specific test, you have
located the correct size for shared_buffers. For me, it would be an
incorrect inference that this could then be interpreted that this was the
percentage of the available RAM where the "sweet spot" lies for all
workloads.

The theoretical basis for my comments is this: DBT-2 is essentially a static
workload. That means, for a long test, we can work out with reasonable
certainty the probability that a block will be requested, for every single
block in the database. Given a particular size of cache, you can work out
what your overall cache hit ratio is and therfore what your speed up is
compared with retrieving every single block from disk (the no cache
scenario). If you draw a graph of speedup (y) against cache size as a % of
total database size, the graph looks like an upside-down "L" - i.e. the
graph rises steeply as you give it more memory, then turns sharply at a
particular point, after which it flattens out. The "turning point" is the
"sweet spot" we all seek - the optimum amount of cache memory to allocate -
but this spot depends upon the worklaod and database size, not on available
RAM on the system under test.

Clearly, the presence of the OS disk cache complicates this. Since we have
two caches both allocated from the same pot of memory, it should be clear
that if we overallocate one cache beyond its optimium effectiveness, while
the second cache is still in its "more is better" stage, then we will get
reduced performance. That seems to be the case here. I wouldn't accept that
a fixed ratio between the two caches exists for ALL, or even the majority of
workloads - though clearly broad brush workloads such as "OLTP" and "Data
Warehousing" do have similar-ish requirements.

As an example, lets look at an example:
An application with two tables: SmallTab has 10,000 rows of 100 bytes each
(so table is ~1 Mb)- one row per photo in a photo gallery web site. LargeTab
has large objects within it and has 10,000 photos, average size 10 Mb (so
table is ~100Gb). Assuming all photos are requested randomly, you can see
that an optimum cache size for this workload is 1Mb RAM, 100Gb disk. Trying
to up the cache doesn't have much effect on  the probability that a photo
(from LargeTab) will be in cache, unless you have a large % of 100Gb of RAM,
when you do start to make gains. (Please don't be picky about indexes,
catalog, block size etc). That clearly has absolutely nothing at all to do
with the RAM of the system on which it is running.

I think Jan has said this also in far fewer words, but I'll leave that to
Jan to agree/disagree...

I say this: ARC in 8.0 PostgreSQL allows us to sensibly allocate as large a
shared_buffers cache as is required by the database workload, and this
should not be constrained to a small percentage of server RAM.

Best Regards,

Simon Riggs

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Josh Berkus
> Sent: 08 October 2004 22:43
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: [PERFORM] First set of OSDL Shared Mem scalability results,
> some wierdness ...
>
>
> Folks,
>
> I'm hoping that some of you can shed some light on this.
>
> I've been trying to peg the "sweet spot" for shared memory using OSDL's
> equipment.   With Jan's new ARC patch, I was expecting that the desired
> amount of shared_buffers to be greatly increased.  This has not
> turned out to
> be the case.
>
> The first test series was using OSDL's DBT2 (OLTP) test, with 150
> "warehouses".   All tests were run on a 4-way Pentium III 700mhz
> 3.8GB RAM
> system hooked up to a rather high-end storage device (14
> spindles).Tests
> were on PostgreSQL 8.0b3, Linux 2.6.7.
>
> Here's a top-level summary:
>
> shared_buffers% RAM   NOTPM20*
> 1000  0.2%1287
> 23000 5%  1507
> 46000 10% 1481
> 69000 15% 1382
> 92000 20% 1375
> 115000

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some wierdness ...

[Josh Berkus]

Simon,



> If you draw a graph of speedup (y) against cache size as a 
> % of total database size, the graph looks like an upside-down "L" - i.e.
> the graph rises steeply as you give it more memory, then turns sharply at a
> particular point, after which it flattens out. The "turning point" is the
> "sweet spot" we all seek - the optimum amount of cache memory to allocate -
> but this spot depends upon the worklaod and database size, not on available
> RAM on the system under test.

Hmmm ... how do you explain, then the "camel hump" nature of the real 
performance?That is, when we allocated even a few MB more than the 
"optimum" ~190MB, overall performance stated to drop quickly.   The result is 
that allocating 2x optimum RAM is nearly as bad as allocating too little 
(e.g. 8MB).  

The only explanation I've heard of this so far is that there is a significant 
loss of efficiency with larger caches.  Or do you see the loss of 200MB out 
of 3500MB would actually affect the Kernel cache that much?

Anyway, one test of your theory that I can run immediately is to run the exact 
same workload on a bigger, faster server and see if the desired quantity of 
shared_buffers is roughly the same.  I'm hoping that you're wrong -- not 
because I don't find your argument persuasive, but because if you're right it 
leaves us without any reasonable ability to recommend shared_buffer settings.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

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

Re: [Testperf-general] Re: [PERFORM] First set of OSDL Shared Mem

[Timothy D. Witham]

On Thu, 2004-10-14 at 16:57 -0700, Josh Berkus wrote:
> Simon,
> 
> 
> 
> > If you draw a graph of speedup (y) against cache size as a 
> > % of total database size, the graph looks like an upside-down "L" - i.e.
> > the graph rises steeply as you give it more memory, then turns sharply at a
> > particular point, after which it flattens out. The "turning point" is the
> > "sweet spot" we all seek - the optimum amount of cache memory to allocate -
> > but this spot depends upon the worklaod and database size, not on available
> > RAM on the system under test.
> 
> Hmmm ... how do you explain, then the "camel hump" nature of the real 
> performance?That is, when we allocated even a few MB more than the 
> "optimum" ~190MB, overall performance stated to drop quickly.   The result is 
> that allocating 2x optimum RAM is nearly as bad as allocating too little 
> (e.g. 8MB).  
> 
> The only explanation I've heard of this so far is that there is a significant 
> loss of efficiency with larger caches.  Or do you see the loss of 200MB out 
> of 3500MB would actually affect the Kernel cache that much?
> 
In a past life there seemed to be a sweet spot around the
applications
working set.   Performance went up until you got just a little larger
than
the cache needed to hold the working set and then went down.  Most of
the time a nice looking hump.It seems to have to do with the
additional pages
not increasing your hit ratio but increasing the amount of work to get a
hit in cache.This seemed to be independent of the actual database
software being used. (I observed this running Oracle, Informix, Sybase
and Ingres.)

> Anyway, one test of your theory that I can run immediately is to run the exact 
> same workload on a bigger, faster server and see if the desired quantity of 
> shared_buffers is roughly the same.  I'm hoping that you're wrong -- not 
> because I don't find your argument persuasive, but because if you're right it 
> leaves us without any reasonable ability to recommend shared_buffer settings.
> 
-- 
Timothy D. Witham - Chief Technology Officer - [EMAIL PROTECTED]
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton OR, 97005
(503)-626-2455 x11 (office)(503)-702-2871 (cell)
(503)-626-2436 (fax)


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[PERFORM] mmap (was First set of OSDL Shared Mem scalability results, some wierdness ...

[Aaron Werman]

pg to my mind is unique in not trying to avoid OS buffering. Other
dbmses spend a substantial effort to create a virtual OS (task
management, I/O drivers, etc.) both in code and support. Choosing mmap
seems such a limiting an option - it adds OS dependency and limits
kernel developer options (2G limits, global mlock serializations,
porting problems, inability to schedule or parallelize I/O, still
having to coordinate writers and readers).

More to the point, I think it is very hard to effectively coordinate
multithreaded I/O, and mmap seems used mostly to manage relatively
simple scenarios. If the I/O options are:
- OS (which has enormous investment and is stable, but is general
purpose with overhead)
- pg (direct I/O would be costly and potentially destabilizing, but
with big possible performance rewards)
- mmap (a feature mostly used to reduce buffer copies in less
concurrent apps such as image processing that has major architectural
risk including an order of magnitude more semaphores, but can reduce
some extra block copies)
mmap doesn't look that promising.

/Aaron

- Original Message - 
From: "Kevin Brown" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 14, 2004 4:25 PM
Subject: Re: [PERFORM] First set of OSDL Shared Mem scalability
results, some wierdness ...


> Tom Lane wrote:
> > Kevin Brown <[EMAIL PROTECTED]> writes:
> > > Tom Lane wrote:
> > >> mmap() is Right Out because it does not afford us sufficient control
> > >> over when changes to the in-memory data will propagate to disk.
> > 
> > > ... that's especially true if we simply cannot
> > > have the page written to disk in a partially-modified state (something
> > > I can easily see being an issue for the WAL -- would the same hold
> > > true of the index/data files?).
> > 
> > You're almost there.  Remember the fundamental WAL rule: log entries
> > must hit disk before the data changes they describe.  That means that we
> > need not only a way of forcing changes to disk (fsync) but a way of
> > being sure that changes have *not* gone to disk yet.  In the existing
> > implementation we get that by just not issuing write() for a given page
> > until we know that the relevant WAL log entries are fsync'd down to
> > disk.  (BTW, this is what the LSN field on every page is for: it tells
> > the buffer manager the latest WAL offset that has to be flushed before
> > it can safely write the page.)
> > 
> > mmap provides msync which is comparable to fsync, but AFAICS it
> > provides no way to prevent an in-memory change from reaching disk too
> > soon.  This would mean that WAL entries would have to be written *and
> > flushed* before we could make the data change at all, which would
> > convert multiple updates of a single page into a series of write-and-
> > wait-for-WAL-fsync steps.  Not good.  fsync'ing WAL once per transaction
> > is bad enough, once per atomic action is intolerable.
> 
> Hmm...something just occurred to me about this.
> 
> Would a hybrid approach be possible?  That is, use mmap() to handle
> reads, and use write() to handle writes?
> 
> Any code that wishes to write to a page would have to recognize that
> it's doing so and fetch a copy from the storage manager (or
> something), which would look to see if the page already exists as a
> writeable buffer.  If it doesn't, it creates it by allocating the
> memory and then copying the page from the mmap()ed area to the new
> buffer, and returning it.  If it does, it just returns a pointer to
> the buffer.  There would obviously have to be some bookkeeping
> involved: the storage manager would have to know how to map a mmap()ed
> page back to a writeable buffer and vice-versa, so that once it
> decides to write the buffer it can determine which page in the
> original file the buffer corresponds to (so it can do the appropriate
> seek()).
> 
> In a write-heavy database, you'll end up with a lot of memory copy
> operations, but with the scheme we currently use you get that anyway
> (it just happens in kernel code instead of user code), so I don't see
> that as much of a loss, if any.  Where you win is in a read-heavy
> database: you end up being able to read directly from the pages in the
> kernel's page cache and thus save a memory copy from kernel space to
> user space, not to mention the context switch that happens due to
> issuing the read().
> 
> 
> Obviously you'd want to mmap() the file read-only in order to prevent
> the issues you mention regarding an errant backend, and then reopen
> the file read-write for the purpose of writing to it.  In fact, you
> could decouple the two: mmap() the file, then close the file -- the
> mmap()ed region will remain mapped.  Then, as long as the file remains
> mapped, you need to open the file again only when you want to write to
> it.
> 
> 
> -- 
> Kevin Brown   [EMAIL PROTECTED]
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
> 
-- 

Regar

Re: [PERFORM] Performance vs Schemas

[Neil Conway]

On Fri, 2004-10-15 at 04:38, Igor Maciel Macaubas wrote:
> I have around 100 tables, and divided them in 14 different schemas,
> and then adapted my application to use schemas as well.
> I could percept that the query / insert / update times get pretty much
> faster then when I was using the old unique schema, and I'd just like
> to confirm with you if using schemas speed up the things. Is that true
> ?

Schemas are a namespacing technique; AFAIK they shouldn't significantly
affect performance (either positively or negatively).

> What about indexed views, does postgresql supports it?

No, you'll need to create indexes on the view's base tables.

-Neil



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] mmap (was First set of OSDL Shared Mem scalability results, some wierdness ...

[Kevin Brown]

Aaron Werman wrote:
> pg to my mind is unique in not trying to avoid OS buffering. Other
> dbmses spend a substantial effort to create a virtual OS (task
> management, I/O drivers, etc.) both in code and support. Choosing mmap
> seems such a limiting an option - it adds OS dependency and limits
> kernel developer options (2G limits, global mlock serializations,
> porting problems, inability to schedule or parallelize I/O, still
> having to coordinate writers and readers).

I'm not sure I entirely agree with this.  Whether you access a file
via mmap() or via read(), the end result is that you still have to
access it, and since PG has significant chunks of system-dependent
code that it heavily relies on as it is (e.g., locking mechanisms,
shared memory), writing the I/O subsystem in a similar way doesn't
seem to me to be that much of a stretch (especially since PG already
has the storage manager), though it might involve quite a bit of work.

As for parallelization of I/O, the use of mmap() for reads should
signficantly improve parallelization -- now instead of issuing read()
system calls, possibly for the same set of blocks, all the backends
would essentially be examining the same data directly.  The
performance improvements as a result of accessing the kernel's cache
pages directly instead of having it do buffer copies to process-local
memory should increase as concurrency goes up.  But see below.

> More to the point, I think it is very hard to effectively coordinate
> multithreaded I/O, and mmap seems used mostly to manage relatively
> simple scenarios. 

PG already manages and coordinates multithreaded I/O.  The mechanisms
used to coordinate writes needn't change at all.  But the way reads
are done relative to writes might have to be rethought, since an
mmap()ed buffer always reflects what's actually in kernel space at the
time the buffer is accessed, while a buffer retrieved via read()
reflects the state of the file at the time of the read().  If it's
necessary for the state of the buffers to be fixed at examination
time, then mmap() will be at best a draw, not a win.

> mmap doesn't look that promising.

This ultimately depends on two things: how much time is spent copying
buffers around in kernel memory, and how much advantage can be gained
by freeing up the memory used by the backends to store the
backend-local copies of the disk pages they use (and thus making that
memory available to the kernel to use for additional disk buffering).
The gains from the former are likely small.  The gains from the latter
are probably also small, but harder to estimate.

The use of mmap() is probably one of those optimizations that should
be done when there's little else left to optimize, because the
potential gains are possibly (if not probably) relatively small and
the amount of work involved may be quite large.


So I agree -- compared with other, much lower-hanging fruit, mmap()
doesn't look promising.



-- 
Kevin Brown   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some wierdness ...

[Christopher Browne]

Quoth [EMAIL PROTECTED] ("Simon Riggs"):
> I say this: ARC in 8.0 PostgreSQL allows us to sensibly allocate as
> large a shared_buffers cache as is required by the database
> workload, and this should not be constrained to a small percentage
> of server RAM.

I don't think that this particularly follows from "what ARC does."

"What ARC does" is to prevent certain conspicuous patterns of
sequential accesses from essentially trashing the contents of the
cache.

If a particular benchmark does not include conspicuous vacuums or
sequential scans on large tables, then there is little reason to
expect ARC to have a noticeable impact on performance.

It _could_ be that this implies that ARC allows you to get some use
out of a larger shared cache, as it won't get blown away by vacuums
and Seq Scans.  But it is _not_ obvious that this is a necessary
truth.

_Other_ truths we know about are:

 a) If you increase the shared cache, that means more data that is
represented in both the shared cache and the OS buffer cache,
which seems rather a waste;

 b) The larger the shared cache, the more pages there are for the
backend to rummage through before it looks to the filesystem,
and therefore the more expensive cache misses get.  Cache hits
get more expensive, too.  Searching through memory is not
costless.
-- 
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://linuxfinances.info/info/linuxdistributions.html
"The X-Files are too optimistic.  The truth is *not* out there..."
-- Anthony Ord <[EMAIL PROTECTED]>

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [PERFORM] Performance vs Schemas

[Iain]

Hi Igor,
 
I expect that when you moved your tables 
to different schemas that you effectively did a physical re-organization (ie 
unload/reload of the tables). It's nothing to do with the use of 
schemas as such. If you had reloaded your tables into the same system 
schema you would have experienced the same speedup as the data tables would 
be more compact.
 
regards
Iain

  - Original Message - 
  From: 
  Igor 
  Maciel Macaubas 
  To: [EMAIL PROTECTED] 
  
  Sent: Friday, October 15, 2004 
  3:38 AM
  Subject: [PERFORM] Performance vs 
  Schemas
  
  Hi all,
   
  I recently migrated my database from schema 
  'public' to multiple schema.
  I have around 100 tables, and divided them in 
  14 different schemas, and then adapted my application to use schemas as 
  well.
  I could percept that the query / insert / 
  update times get pretty much faster then when I was using the old unique 
  schema, and I'd just like to confirm with you if using schemas speed up the 
  things. Is that true ?
   
  What else I can do to speed up the query 
  processing, best pratices, recommendations ... ? What about indexed views, 
  does postgresql supports it?
   
  Regards,
  Igor--[EMAIL PROTECTED]
   

Re: [PERFORM] First set of OSDL Shared Mem scalability results, some wierdness ...

[Tom Lane]

Kevin Brown <[EMAIL PROTECTED]> writes:
> Hmm...something just occurred to me about this.

> Would a hybrid approach be possible?  That is, use mmap() to handle
> reads, and use write() to handle writes?

Nope.  Have you read the specs regarding mmap-vs-stdio synchronization?
Basically it says that there are no guarantees whatsoever if you try
this.  The SUS text is a bit weaselly ("the application must ensure
correct synchronization") but the HPUX mmap man page, among others,
lays it on the line:

 It is also unspecified whether write references to a memory region
 mapped with MAP_SHARED are visible to processes reading the file and
 whether writes to a file are visible to processes that have mapped the
 modified portion of that file, except for the effect of msync().

It might work on particular OSes but I think depending on such behavior
would be folly...

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match