On Fri, May 28, 2010 at 5:02 PM, Greg Smith wrote:
> Merlin Moncure wrote:
>>
>> I'm of the opinion (rightly or wrongly) that the prevailing opinions
>> on how to configure shared_buffers are based on special case
>> benchmarking information or simply made up.
>
> Well, you're wrong, but it's OK;
If, like me, you came from the Oracle world, you may be tempted to throw a
ton of RAM at this. Don't. PG does not like it.
On Fri, May 28, 2010 at 4:11 PM, Scott Marlowe wrote:
> On Mon, May 24, 2010 at 12:25 PM, Merlin Moncure
> wrote:
> > *) shared_buffers is one of the _least_ important perfo
On Mon, May 24, 2010 at 12:25 PM, Merlin Moncure wrote:
> *) shared_buffers is one of the _least_ important performance settings
> in postgresql.conf
Yes, and no. It's usually REALLY helpful to make sure it's more than
8 or 24Megs. But it doesn't generally need to be huge to make a
difference.
Merlin Moncure wrote:
I'm of the opinion (rightly or wrongly) that the prevailing opinions
on how to configure shared_buffers are based on special case
benchmarking information or simply made up.
Well, you're wrong, but it's OK; we'll forgive you this time. It's true
that a lot of the earlier
On Fri, May 28, 2010 at 2:57 PM, Greg Smith wrote:
> Merlin Moncure wrote:
>>
>> I would prefer to see the annotated performance oriented .conf
>> settings to be written in terms of trade offs (too low? X too high? Y
>> setting in order to get? Z). For example, did you know that if crank
>> max_l
Merlin Moncure wrote:
I would prefer to see the annotated performance oriented .conf
settings to be written in terms of trade offs (too low? X too high? Y
setting in order to get? Z). For example, did you know that if crank
max_locks_per_transaction you also increase the duration of every
query
2010/5/28 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> Exactly. And the time to browse depend on the number of blocks already
>> in core memory.
>> I am interested by tests results and benchmarks if you are going to do some
>> :)
>
> I am still thinking whether I want to do it on this prod m
2010/5/27 Cédric Villemain :
> Exactly. And the time to browse depend on the number of blocks already
> in core memory.
> I am interested by tests results and benchmarks if you are going to do some :)
I am still thinking whether I want to do it on this prod machine.
Maybe on something less critic
2010/5/27 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> well, that is the projection of file in memory. only projection, but
>> the memory is still acquire. It is ok to rework this part and project
>> something like 128MB and loop. (in fact the code is needed for 9.0
>> because segment can be
2010/5/27 Cédric Villemain :
> well, that is the projection of file in memory. only projection, but
> the memory is still acquire. It is ok to rework this part and project
> something like 128MB and loop. (in fact the code is needed for 9.0
> because segment can be > 1GB, I didn't check what is th
2010/5/27 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> pgmincore() and pgmincore_snapshot() both are able to mmap up to 1GB.
>
> Does it mean they can occupy 1 GB of RAM? How does it relate to amount
> of page buffers mapped by OS?
well, that is the projection of file in memory. only project
2010/5/27 Cédric Villemain :
> pgmincore() and pgmincore_snapshot() both are able to mmap up to 1GB.
Does it mean they can occupy 1 GB of RAM? How does it relate to amount
of page buffers mapped by OS?
--
Konrad Garus
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or
2010/5/27 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> It works thanks to mincore/posix_fadvise stuff : you need linux.
>> It is stable enough in my own experiment. I did use it for debugging
>> purpose in production servers with succes.
>
> What impact does it have on performance?
pgmincore
2010/5/27 Cédric Villemain :
> It works thanks to mincore/posix_fadvise stuff : you need linux.
> It is stable enough in my own experiment. I did use it for debugging
> purpose in production servers with succes.
What impact does it have on performance?
Does it do anything, is there any interacti
2010/5/27 Konrad Garus :
> 2010/5/26 Cédric Villemain :
>
>> At the moment where a block is requested for the first time (usualy
>> 8kb from postgres, so in fact 2 blocks in OS), you have 'duplicate'
>> buffers.
>> But, depending of your workload, it is not so bad because those 2
>> blocks should n
2010/5/26 Cédric Villemain :
> At the moment where a block is requested for the first time (usualy
> 8kb from postgres, so in fact 2 blocks in OS), you have 'duplicate'
> buffers.
> But, depending of your workload, it is not so bad because those 2
> blocks should not be requested untill some time
2010/5/24 Konrad Garus :
> 2010/3/11 Paul McGarry :
>
>> I'm basically wondering how the postgresql cache (ie shared_buffers)
>> and the OS page_cache interact. The general advice seems to be to
>> assign 1/4 of RAM to shared buffers.
>>
>> I don't have a good knowledge of the internals but I'm won
On Tue, May 25, 2010 at 5:58 AM, Konrad Garus wrote:
> 2010/5/24 Merlin Moncure :
>
>> *) a page fault to disk is a much bigger deal than a fault to pg cache
>> vs os/ cache.
>
> That was my impression. That's why I did not touch our 2/16 GB setting
> right away. I guess that 2 more gigabytes in O
2010/5/24 Merlin Moncure :
> *) a page fault to disk is a much bigger deal than a fault to pg cache
> vs os/ cache.
That was my impression. That's why I did not touch our 2/16 GB setting
right away. I guess that 2 more gigabytes in OS cache is better than 2
more (duplicated) gigabytes in PG share
On Wed, Mar 10, 2010 at 9:28 PM, Paul McGarry wrote:
> Hi there,
>
> I'm after a little bit of advice on the shared_buffers setting (I have
> read the various docs on/linked from the performance tuning wiki page,
> some very helpful stuff there so thanks to those people).
>
> I am setting up a 64b
On May 24, 2010, at 4:25 AM, Konrad Garus wrote:
> Do shared_buffers duplicate contents of OS page cache? If so, how do I
> know if 25% RAM is the right value for me? Actually it would not seem
> to be true - the less redundancy the better.
You can look into the pg_buffercache contrib module.
>
2010/3/11 Paul McGarry :
> I'm basically wondering how the postgresql cache (ie shared_buffers)
> and the OS page_cache interact. The general advice seems to be to
> assign 1/4 of RAM to shared buffers.
>
> I don't have a good knowledge of the internals but I'm wondering if
> this will effectively
Greg Smith writes:
> However, that doesn't actually solve any of the problems I was talking about
> though, which is why I'm not even talking about that part. We need the glue
> to pull out software releases, run whatever testing tool is appropriate, and
> then save the run artifacts in some stan
Dimitri Fontaine wrote:
I still think the best tool around currently for this kind of testing is
tsung
I am happy to say that for now, pgbench is the only actual testing tool
supported. Done; now I don't need tsung.
However, that doesn't actually solve any of the problems I was talking
abo
Greg Smith writes:
> I'm not sure how to make progress on similar ideas about
> tuning closer to the filesystem level without having something automated
> that takes over the actual benchmark running and data recording steps; it's
> just way too time consuming to do those right now with every too
See below:
On Wed, Mar 10, 2010 at 9:28 PM, Paul McGarry wrote:
> Hi there,
>
> I'm after a little bit of advice on the shared_buffers setting (I have
> read the various docs on/linked from the performance tuning wiki page,
> some very helpful stuff there so thanks to those people).
>
> I am se
Alvaro Herrera wrote:
Maybe it would make more sense to try to reorder the fsync calls
instead.
The pretty obvious left behind idea from 8.3 spread checkpoint
development was to similarly spread the fsync calls around. Given that
we know, for example, Linux with ext3 is going to dump the
Tom Lane escribió:
> Alvaro Herrera writes:
> > Tom Lane escribió:
> >> That's not going to do anything towards reducing the actual I/O volume.
> >> Although I suppose it might be useful if it just cuts the number of
> >> seeks.
>
> > Oh, they had no problems with I/O volume. It was relation ext
Alvaro Herrera writes:
> Tom Lane escribió:
>> That's not going to do anything towards reducing the actual I/O volume.
>> Although I suppose it might be useful if it just cuts the number of
>> seeks.
> Oh, they had no problems with I/O volume. It was relation extension
> lock that was heavily c
Tom Lane escribió:
> Alvaro Herrera writes:
> > Tom Lane escribi�:
> >> Reorder to what, though? You still have the problem that we don't know
> >> much about the physical layout on-disk.
>
> > Well, to block numbers as a first step.
>
> fsync is a file-based operation, and we know exactly zip
Alvaro Herrera writes:
> Tom Lane escribió:
>> Reorder to what, though? You still have the problem that we don't know
>> much about the physical layout on-disk.
> Well, to block numbers as a first step.
fsync is a file-based operation, and we know exactly zip about the
relative positions of dif
Tom Lane escribió:
> Alvaro Herrera writes:
> > Maybe it would make more sense to try to reorder the fsync calls
> > instead.
>
> Reorder to what, though? You still have the problem that we don't know
> much about the physical layout on-disk.
Well, to block numbers as a first step.
However, th
Alvaro Herrera writes:
> Maybe it would make more sense to try to reorder the fsync calls
> instead.
Reorder to what, though? You still have the problem that we don't know
much about the physical layout on-disk.
regards, tom lane
--
Sent via pgsql-performance mailing l
Greg Stark escribió:
> On Tue, Mar 16, 2010 at 2:30 PM, Tom Lane wrote:
> > "Pierre C" writes:
> >> Does PG issue checkpoint writes in "sorted" order ?
> >
> > No. IIRC, a patch for that was submitted, and rejected because no
> > significant performance improvement could be demonstrated. We don
Greg Stark wrote:
On Tue, Mar 16, 2010 at 2:30 PM, Tom Lane wrote:
"Pierre C" writes:
Does PG issue checkpoint writes in "sorted" order ?
No. IIRC, a patch for that was submitted, and rejected because no
significant performance improvement could be demonstrated.
If the OS fi
Pierre C wrote:
Actually, I meant that in the case of a seq scan, PG will try to use
just a few buffers (a ring) in shared_buffers instead of thrashing the
whole buffers. But if there was actually a lot of free space in
shared_buffers, do the pages stay, or do they not ?
Pages inserted into t
On Tue, Mar 16, 2010 at 2:30 PM, Tom Lane wrote:
> "Pierre C" writes:
>> Does PG issue checkpoint writes in "sorted" order ?
>
> No. IIRC, a patch for that was submitted, and rejected because no
> significant performance improvement could be demonstrated. We don't
> have enough information abou
On Tue, Mar 16, 2010 at 1:48 PM, Pierre C wrote:
> Actually, I meant that in the case of a seq scan, PG will try to use just a
> few buffers (a ring) in shared_buffers instead of thrashing the whole
> buffers. But if there was actually a lot of free space in shared_buffers, do
> the pages stay, or
"Pierre C" writes:
> Does PG issue checkpoint writes in "sorted" order ?
No. IIRC, a patch for that was submitted, and rejected because no
significant performance improvement could be demonstrated. We don't
have enough information about the actual on-disk layout to be very
intelligent about thi
I think the logic you are referring to is the clock sweep buffer
accounting
scheme. That just makes sure that the most popular pages stay in the
buffers. If your entire db fits in the buffer pool then it'll all get in
there real fast.
Actually, I meant that in the case of a seq scan, PG wil
On Tue, Mar 16, 2010 at 7:24 AM, Pierre C wrote:
>
> I wonder about something, too : if your DB size is smaller than RAM, you
> could in theory set shared_buffers to a size larger than your DB provided
> you still have enough free RAM left for work_mem and OS writes management.
> How does this int
-My warnings about downsides related to checkpoint issues with larger
buffer pools isn't an opinion at all; that's a fact based on limitations
in how Postgres does its checkpoints. If we get something more like
Oracle's incremental checkpoint logic, this particular concern might go
away.
Dave Crooke wrote:
There seems to be a wide range of opinion on this I am new to PG
and grew up on Oracle, where more SGA is always a good thing ... I
know people who run Oracle on 2TB Superdome's with titanic SGA sizes
to keep the whole DB in RAM. I'd be using a 40GB+ Oracle SGA on that
There seems to be a wide range of opinion on this I am new to PG and
grew up on Oracle, where more SGA is always a good thing ... I know people
who run Oracle on 2TB Superdome's with titanic SGA sizes to keep the whole
DB in RAM. I'd be using a 40GB+ Oracle SGA on that box of yours.
A lot of
On Thu, Mar 11, 2010 at 5:19 PM, Paul McGarry wrote:
> On 11 March 2010 16:16, Ben Chobot wrote:
>
>> I *can* say a 10GB shared_buffer value is working "well" with my 128GB of
>> RAM. whether or not it's "optimal," I couldn't say without a lot of
>> experimentation I can't afford to do righ
On 11 March 2010 16:16, Ben Chobot wrote:
> I *can* say a 10GB shared_buffer value is working "well" with my 128GB of
> RAM. whether or not it's "optimal," I couldn't say without a lot of
> experimentation I can't afford to do right now. You might have a look at the
> pg_buffercache contri
Hi there,
I'm after a little bit of advice on the shared_buffers setting (I have
read the various docs on/linked from the performance tuning wiki page,
some very helpful stuff there so thanks to those people).
I am setting up a 64bit Linux server running Postgresql 8.3, the
server has 64gigs of m
On Mar 11, 2010, at 12:39 AM, Greg Smith wrote:
>
> Giving all the buffers to the database doesn't work for many reasons:
> -Need a bunch leftover for clients to use (i.e. work_mem)
> -Won't be enough OS cache for non-buffer data the database expects
> cached reads and writes will perform well o
Paul McGarry wrote:
IE when Postgres reads something from disk it will go into both the OS
page cache and the Postgresql shared_buffers and the OS page cache
copy is unlikely to be useful for anything.
That's correct. However, what should happen over time is that the
popular blocks in Post
On Mar 10, 2010, at 6:22 PM, Paul McGarry wrote:
> Hi there,
>
> I'm after a little bit of advice on the shared_buffers setting (I have
> read the various docs on/linked from the performance tuning wiki page,
> some very helpful stuff there so thanks to those people).
>
> I am setting up a 64bit
Hi there,
I'm after a little bit of advice on the shared_buffers setting (I have
read the various docs on/linked from the performance tuning wiki page,
some very helpful stuff there so thanks to those people).
I am setting up a 64bit Linux server running Postgresql 8.3, the
server has 64gigs of m
51 matches
Mail list logo
