Greg Smith writes:
> You might note that only one of these sources--a backend allocating a
> buffer--is connected to the process you want to limit. If you think of
> the problem from that side, it actually becomes possible to do something
> useful here. The most practical way to throttle some
Dave Crooke writes:
> This is the second time I've heard that "PG shared buffer on Windows doesn't
> matter" ... I'd like to understand the reasoning behind that claim, and why
> it differs from other DB servers.
AFAIK we don't really understand why, but the experimental evidence is
that increasi
Craig Ringer wrote:
It's also complicated by the fact that Pg's architecture is very poorly
suited to prioritizing I/O based on query or process. (AFAIK) basically
all writes go through shared_buffers and the bgwriter - neither Pg nor
in fact the OS know what query or what backend created a given
Eduardo Piombino wrote:
> I think pg is wasting resources, it could be very well taking advantage
> of, if you guys just tell me get better hardware. I mean ... the IO
> subsystem is obviously the bottleneck of my system. But most of the time
> it is on very very light load, actually ALL of the ti
Eduardo Piombino wrote:
But already knowing that the base system (i.e. components out of pg's
control, like OS, hardware, etc) may be "buggy" or that it can fail in
rationalizing the IO, maybe it would be nice to tell to whoever is
responsible for making use of the IO subsystem (pg_bg_writer?),
Dave Crooke wrote:
My reasoning goes like this:
a. there is a significant performance benefit to using a large
proportion of memory as in-process DB server cache instead of OS level
block / filesystem cache
b. the only way to do so on modern hardware (i.e. >>4GB) is with a
64-bit binary
c. t
A few months ago the worst of the bugs in the ext4 fsync code started
clearing up, with
http://git.kernel.org/?p=linux/kernel/git/torvalds/linux-2.6.git;a=commit;h=5f3481e9a80c240f169b36ea886e2325b9aeb745
as a particularly painful one. That made it into the 2.6.32 kernel
released last month.
This is the second time I've heard that "PG shared buffer on Windows doesn't
matter" ... I'd like to understand the reasoning behind that claim, and why
it differs from other DB servers.
though that's much less important for Pg than for most other things, as
> Pg uses a one-process-per-connec
Just opinion, and like Greg, I was suggesting it along the lines of "it's
the platform most production PG instances run on, so you're following a well
trodden path, and any issue you encounter is likely to have been found and
fixed by someone else".
It's not about the general suitability of the OS
I will give it a try, thanks.
However, besides all the analysis and tests and stats that I've been
collecting, I think the point of discussion turned into if my hardware is
good enough, and if it can keep up with the needs in normal, or even
heaviest users load. And if that is the question, the an
Kenneth Marshall writes:
> We have just upgraded our monitoring server software and
> now the following query for graphing the data performs
> abysmally with the default settings. Here is the results
> of the EXPLAIN ANALYZE run with nestloops enabled:
That plan seems a bit wacko --- I don't see
On Fri, Jan 15, 2010 at 04:58:57PM -0600, Kevin Grittner wrote:
> Kenneth Marshall wrote:
>
> > with the default settings
>
> Do you mean you haven't changed any settings in your postgresql.conf
> file from their defaults?
>
> -Kevin
>
Sorry, here are the differences from the default:
max_
I'd second this a database is doing all kinds of clever things to
ensure ACID consistency on every byte that gets written to it.
If you don't need that level of consistency for your 8MB blobs, write them
to plain files named with some kind of id, and put the id in the database
instead of the
Kenneth Marshall wrote:
> with the default settings
Do you mean you haven't changed any settings in your postgresql.conf
file from their defaults?
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresq
Dear performance group:
We have just upgraded our monitoring server software and
now the following query for graphing the data performs
abysmally with the default settings. Here is the results
of the EXPLAIN ANALYZE run with nestloops enabled:
SET enable_nestloop = 'on';
EXPLAIN SELECT g.graphid
Eduardo Piombino wrote:
Going to the disk properties (in windows), I just realized it does not
have the Write Cache enabled, and it doesn't also allow me to set it
up. I've read in google that the lack of ability to turn it on (that
is, that the checkbox remains checked after you apply the chan
Scott Marlowe wrote:
> I will say that XFS seems to be a very stable file system, and we
> use it for some of our databases with no problems at all. But
> most of our stuff sits on ext3 because it's stable and reliable
> and fast enough.
Our PostgreSQL data directories are all on xfs, with ev
2010/1/15 Fernando Hevia :
>
>
>> -Mensaje original-
>> De: Pierre Frédéric Caillaud
>> Enviado el: Viernes, 15 de Enero de 2010 15:00
>> Para: pgsql-performance@postgresql.org
>> Asunto: Re: [PERFORM] new server I/O setup
>>
>>
>> No-one has mentioned SSDs yet ?...
>>
>
> The post is
On Fri, Jan 15, 2010 at 11:28 AM, Greg Smith wrote:
> Tom Lane wrote:
>>
>> Given the Linux kernel hackers' apparent disinterest in fixing their
>> OOM kill policy or making write barriers work well (or at all, with
>> LVM), I think arguing that Linux is the best database platform requires
>> a ce
On Thu, Jan 14, 2010 at 9:29 AM, fka...@googlemail.com
wrote:
> Hello together,
>
> I need to increase the write performance when inserting
> bytea of 8MB. I am using 8.2.4 on windows with libpq.
>
> The test setting is simple:
>
> I write 100x times a byte array (bytea) of 8 MB random data
> into
Pierre Frédéric Caillaud:
> At install, or use a symlink (they exist on windows too !...)
>
> http://stackoverflow.com/questions/1901405/postgresql-wal-on-windows
Very interesting! Did not help much though (see other
posting).
Thank You
Felix
--
Sent via pgsql-performance mailin
Matthew Wakeling:
> On Thu, 14 Jan 2010, fka...@googlemail.com wrote:
> > Nevertheless: If your explanation covers all what can be
> > said about it then replacing the hard disk by a faster one
> > should increase the performance here (I'll try to check that
> > out).
>
> Probably. However, it is
> -Mensaje original-
> De: Pierre Frédéric Caillaud
> Enviado el: Viernes, 15 de Enero de 2010 15:00
> Para: pgsql-performance@postgresql.org
> Asunto: Re: [PERFORM] new server I/O setup
>
>
> No-one has mentioned SSDs yet ?...
>
The post is about an already purchased server ju
Tom Lane wrote:
Given the Linux kernel hackers' apparent disinterest in fixing their
OOM kill policy or making write barriers work well (or at all, with
LVM), I think arguing that Linux is the best database platform requires
a certain amount of suspension of disbelief.
Don't forget the gener
Matthew Wakeling wrote:
CFQ is the default scheduler, but in most systems I have seen, it
performs worse than the other three schedulers, all of which seem to
have identical performance. I would avoid anticipatory on a RAID array
though.
It seems to me that CFQ is simply bandwidth limited by
No-one has mentioned SSDs yet ?...
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Fri, 15 Jan 2010, Craig James wrote:
That's the perception I get. CFQ is the default scheduler, but in most
systems I have seen, it performs worse than the other three schedulers, all
of which seem to have identical performance. I would avoid anticipatory on
a RAID array though.
I thought
Ug, sorry! As soon as I hit "enter" I realised this was the wrong
list even for OT :-)
On Fri, Jan 15, 2010 at 12:16 PM, Alan McKay wrote:
> Hey folks,
>
> Sorry for the OT - we are most of the way through a Db2 --> PG
> migration that is some 18 months in the making so far. We've got
> maybe
Hey folks,
Sorry for the OT - we are most of the way through a Db2 --> PG
migration that is some 18 months in the making so far.We've got
maybe another 3 to 6 months to go before we are complete, and in the
meantime have identified the need for connection pooling in Db2, a-la
the excellent pgb
On Fri, 15 Jan 2010, Fernando Hevia wrote:
I was wondering if disabling the bbu cache on the RAID 1 array would make
any difference. All 256MB would be available for the random I/O on the RAID
10.
That would be pretty disastrous, to be honest. The benefit of the cache is
not only that it smoot
Matthew Wakeling wrote:
On Thu, 14 Jan 2010, Greg Smith wrote:
Andy Colson wrote:
So if there is very little io, or if there is way way too much, then
the scheduler really doesn't matter. So there is a slim middle
ground where the io is within a small percent of the HD capacity
where the sch
> -Mensaje original-
> De: Matthew Wakeling [mailto:matt...@flymine.org]
> Enviado el: Viernes, 15 de Enero de 2010 08:21
> Para: Scott Marlowe
> CC: Fernando Hevia; pgsql-performance@postgresql.org
> Asunto: Re: [PERFORM] new server I/O setup
>
> On Thu, 14 Jan 2010, Scott Marlowe wro
Richard Broersma writes:
> On Fri, Jan 15, 2010 at 8:10 AM, Tony McC wrote:
>>> most stable platform for that RDBMS. For Oracle, that's HP-UX (but 10
>>> years ago, it was Solaris). For PostgreSQL, it's Linux.
>> I am interested in this response and am wondering if this is just
>> Dave's opinion
> -Mensaje original-
> De: Greg Smith
>
>> Fernando Hevia wrote:
>>
>> I justified my first choice in that WAL writes are
>> sequentially and OS pretty much are too, so a RAID 1 probably
>> would hold ground against a 12 disc RAID 10 with random writes.
>>
>
> The problem wit
On Thu, Jan 14, 2010 at 8:17 PM, Carlo Stonebanks
wrote:
> . 48 GB RAM
> 2) Which Windows OS would you recommend? (currently 2008 x64 Server)
There is not a 64-bit windows build now - You would be limited to
shared_buffers at about a gigabyte. Choose Linux
Greetings
Marcin Mańk
--
Sent via pgs
> 2) Which Windows OS would you recommend? (currently 2008 x64 Server)
Would not recommend Windows OS.
BTW, I'd be interested to know the NTFS fragmentation stats of your
database file.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your
On Fri, Jan 15, 2010 at 11:10 AM, Tony McC wrote:
> what is it about Linux specifically (as
> contrasted with other Unix-like OSes, especially Open Source ones) that
> makes it particularly suitable for running PostgreSQL?
Nothing that I know of.
...Robert
--
Sent via pgsql-performance mailing
On Fri, Jan 15, 2010 at 8:10 AM, Tony McC wrote:
>> most stable platform for that RDBMS. For Oracle, that's HP-UX (but 10
>> years ago, it was Solaris). For PostgreSQL, it's Linux.
>
> I am interested in this response and am wondering if this is just
> Dave's opinion or some sort of official Post
On Thu, 14 Jan 2010 16:35:53 -0600
Dave Crooke wrote:
> For any given database engine, regardless of the marketing and support
> stance, there is only one true "primary" enterprise OS platform that
> most big mission critical sites use, and is the best supported and
> most stable platform for tha
> -Mensaje original-
> De: Scott Marlowe
>
> I think your first choice is right. I use the same basic
> setup with 147G 15k5 SAS seagate drives and the pg_xlog / OS
> partition is almost never close to the same level of
> utilization, according to iostat, as the main 12 disk RAID-1
El 15/01/2010 14:43, Ivan Voras escribió:
hi,
You wrote a lot of information here so let's confirm in a nutshell
what you have and what you are looking for:
* A database that is of small to medium size (5 - 10 GB)?
* Around 10 clients that perform constant write operations to the
database (U
On Fri, Jan 15, 2010 at 8:43 AM, Ivan Voras wrote:
> Have you tried decreasing random_page_cost in postgresql.conf? Or setting
> (as a last resort) enable_seqscan = off?
If you need to set enable_seqscan to off to get the planner to use
your index, the chances that that index are actually going t
hi,
You wrote a lot of information here so let's confirm in a nutshell what
you have and what you are looking for:
* A database that is of small to medium size (5 - 10 GB)?
* Around 10 clients that perform constant write operations to the
database (UPDATE/INSERT)
* Around 10 clients that occ
On Thu, 14 Jan 2010, fka...@googlemail.com wrote:
Nevertheless: If your explanation covers all what can be
said about it then replacing the hard disk by a faster one
should increase the performance here (I'll try to check that
out).
Probably. However, it is worth you running the test again, and
On Thu, 14 Jan 2010, Scott Marlowe wrote:
I've just received this new server:
1 x XEON 5520 Quad Core w/ HT
8 GB RAM 1066 MHz
16 x SATA II Seagate Barracuda 7200.12
3ware 9650SE w/ 256MB BBU
2 discs in RAID 1 for OS + pg_xlog partitioned with ext2.
12 discs in RAID 10 for postgres data, sole par
On Thu, 14 Jan 2010, fka...@googlemail.com wrote:
The data needs to be written first to the WAL, in order to provide
crash-safety. So you're actually writing 1600MB, not 800.
I understand. So the actual throughput is 32MB/s which is
closer to 43 MB/s, of course.
Can I verify that by temporaril
On Thu, 14 Jan 2010, Greg Smith wrote:
Andy Colson wrote:
So if there is very little io, or if there is way way too much, then the
scheduler really doesn't matter. So there is a slim middle ground where
the io is within a small percent of the HD capacity where the scheduler
might make a diffe
http://www.hagander.net/talks/Advanced%20PostgreSQL%20on%20Windows.pdf
Great doc ! I'm keeping that ;)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
> Florian Weimer:
>
>> > Do you have any further idea why 16MB/s seems to be the limit here?
>>
>> BYTEA deserialization is very slow, and this could be a factor here.
>> Have you checked that you are in fact I/O bound?
>
> Could you elaborate that a bit? It sounds interesting but I
> do not get w
49 matches
Mail list logo
