On Fri, Jan 2, 2009 at 18:46, Tom Lane wrote:
>
> It would be fairly easy, I think, to add some reloption fields that
> would let these parameters be controlled on a per-table level.
+1
Or something easier that just lets you use PGLZ_Strategy strategy_always_data.
ALTER TABLE SET STORAGE ALWAYS
On Fri, Jan 2, 2009 at 20:30, Tom Lane wrote:
> One point that nobody seems to have focused on is whether Alex's
> less-compressed table is faster or slower to access than the original.
> I dunno if he has any easy way of investigating that for his typical
> query mix, but it's certainly a fair qu
Robert Haas wrote:
Compression is
generally bad for performance, though there are certainly exceptions.
What it is good for is saving disk space, and that is why people use
it.
I don't think disk space is still the primary reason for using compression;
shoot.. I can buy a terabyte drive for
>> Why didn't they just turn off compression for the relevant columns?
>
> They did --- with the pre-8.4 code, they had no choice, because the
> toast compressor would kick in if it could save even one byte on the
> total field size. That's clearly silly. We might have gone too far
> in the other
On Fri, Jan 2, 2009 at 11:13 PM, Robert Haas wrote:
> When I did that, it when back from 50 s to 33 s, which I think means
> that posix_fadvise is getting called and that that is what is making
> it slower.
>
>> And is this on a system with multiple spindles? How many?
>
> Latitude D830 laptop. S
Tom Lane writes:
> The point of the suggestion is to prove that the patch works as
> advertised. How wide the sweet spot is for this test isn't nearly as
> interesting as proving that there *is* a sweet spot. If you can't
> find one it suggests that either the patch or the local posix_fadvise
> Any chance you could put back the code in explain.c which showed
> whether posix_fadvise is actually getting used? Another thing I did
> when testing was attaching with strace to see if posix_fadvise (the
> syscall on linux was actually fadvise64 iirc) is actually getting
> called.
I tried chang
Oleksiy Shchukin wrote:
Reproduce how-to
I send bad SQL statement via dblink_send_query(text,text), and than try to
grab result with dblink_get_result(connname, /*fail_on_error=*/ false), the
bad sql statement error is issued on client side inspite of
'/*fail_on_error=*/ false'
"Robert Haas" writes:
> On Fri, Jan 2, 2009 at 8:36 PM, Tom Lane wrote:
>> "Stephen R. van den Berg" writes:
>>> - I currently have difficulty imagining applications that actually do
>>> lots of substring extractions from large compressible fields.
>>
>> The code that's in there to make this ha
I wrote:
> * the startup/shutdown hooks will be installed in the postmaster
> process, but the patch expects them to be executed in a child process.
> I think nothing will happen.
OK, I figured out what is happening there: the patch makes it work by
means of this expedient:
diff -cprN HEAD/src/ba
On Fri, Jan 2, 2009 at 8:42 PM, Robert Haas wrote:
>> Hm, what were those plans? You might want to put the old code back in
>> explain.c to print the prefetching target to see how well it's doing.
>
> Well, bad news. Here's one where prefetching seems to make it WORSE.
>
> rhaas=# explain select
On Fri, Jan 2, 2009 at 8:36 PM, Tom Lane wrote:
> "Stephen R. van den Berg" writes:
>> - I currently have difficulty imagining applications that actually do
>> lots of substring extractions from large compressible fields.
>
> The code that's in there to make this happen was written by people wh
On Fri, Jan 2, 2009 at 18:30, Tom Lane wrote:
> "Alex Hunsaker" writes:
>> On Fri, Jan 2, 2009 at 11:44, Tom Lane wrote:
>>> An easy way to prove or disprove the point would be to go into
>>> src/backend/utils/adt/pg_lzcompress.c, and change the second entry
>>> in strategy_default_data from "10
Gregory Stark writes:
> I think the right value for this setting is going to depend on the
> environment. If the system is starved for cpu cycles then you won't want to
> compress large data. If it's starved for i/o bandwidth but has spare cpu
> cycles then you will.
> If that's true then we real
> Hm, what were those plans? You might want to put the old code back in
> explain.c to print the prefetching target to see how well it's doing.
Well, bad news. Here's one where prefetching seems to make it WORSE.
rhaas=# explain select sum(1) from enormous where l_shipdate in
('1992-01-01', '199
On Fri, Jan 2, 2009 at 03:13, Magnus Hagander wrote:
> Andrew Chernow wrote:
>> Yes, the homedir variable is used again later in the function. homedir
>> could be invalid since pqGetHomeDirectory might not get called. Maybe
>> something like below would do the trick:
> How about this patch?
lo
"Stephen R. van den Berg" writes:
> - I currently have difficulty imagining applications that actually do
> lots of substring extractions from large compressible fields.
The code that's in there to make this happen was written by people who
needed the feature. They're going to be upset with yo
On Fri, Jan 2, 2009 at 18:23, Tom Lane wrote:
> "Alex Hunsaker" writes:
>> Its seems to me a linear list would make the "common" case where the
>> query is already in the list but we need to update the stats slow.
>
> No, the hashtable is still there for lookups. The list would be a means
> of d
"Alex Hunsaker" writes:
> On Fri, Jan 2, 2009 at 11:44, Tom Lane wrote:
>> An easy way to prove or disprove the point would be to go into
>> src/backend/utils/adt/pg_lzcompress.c, and change the second entry
>> in strategy_default_data from "1024 * 1024" to "INT_MAX",
> And the toast file size i
"Alex Hunsaker" writes:
> Its seems to me a linear list would make the "common" case where the
> query is already in the list but we need to update the stats slow.
No, the hashtable is still there for lookups. The list would be a means
of determining which hashtable entry to release when we're o
On Fri, Jan 2, 2009 at 17:22, Tom Lane wrote:
> A couple of other possibilities that seem a bit saner:
>
> 1. Use a self-organizing list: any time an entry is referenced,
> move it to front, and when you need a new entry take the oldest
> one off the back. I don't see a way to do that without a g
Gregory Stark writes:
> Tom Lane writes:
>> In principle you should be able to adjust the constant so that vmstat
>> shows about 50% CPU busy, and then enabling fadvise should improve
>> matters significantly.
> I think in practice individual queries don't interleave much cpu with i/o
> work. A
Robert Treat wrote:
> On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote:
> > David Fetter wrote:
Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php
> > > 1. Create a generic (possibly overloaded) trigger function, bundled
> > > with PostgreSQL, which sets a field to some
The pending contrib/pg_stat_statements patch has an interesting method
for dealing with the limited size of its shared-memory hash table
(which has got one entry per unique statement text, userid, and database
id, so it's not exactly likely to be small). When the hash table fills
up, it enumerates
* Stephen Frost (sfr...@snowman.net) wrote:
> Please find attached an updated patch for column-level privileges
> which incorporates Alvaro's suggested changes and is updated to the
> latest CVS HEAD. Regression tests have been added as well as
> documentation (though this could probably b
On Friday 02 January 2009 23:33:34 Aidan Van Dyk wrote:
> Has this gone anywhere? Is the CVS repo safe yet?
Nothing has been done about this.
>
> a.
>
> > * Peter Eisentraut [081229 09:50]:
> > > I had originally sent this mail in April, but it appears to have been
> > > blocked because the att
On Fri, Jan 2, 2009 at 5:36 PM, Robert Haas wrote:
>> I've got a stack of hardware I can do performance testing of this patch on,
>> what I haven't been able to find time for is setting up any sort of test
>> harness right now. If you or Greg have any benchmark or test program you
>> could sugges
On Fri, Jan 02, 2009 at 03:35:18PM -0500, Robert Haas wrote:
> Any compression algorithm is going to require you to decompress the
> entire string before extracting a substring at a given offset. When
> the data is uncompressed, you can jump directly to the offset you want
> to read. Even if the
Peter Eisentraut wrote:
On Saturday 20 December 2008 19:33:17 Tom Lane wrote:
Peter wrote:
SQL/MED catalog manipulation facilities
This doesn't do any remote or external things yet, but it gives modules
like plproxy and dblink a standardized and future-proof system for
managing their connectio
> I've got a stack of hardware I can do performance testing of this patch on,
> what I haven't been able to find time for is setting up any sort of test
> harness right now. If you or Greg have any benchmark or test program you
> could suggest that should show off the improvements here, I'd be gla
On Fri, Jan 2, 2009 at 4:19 PM, Stephen R. van den Berg wrote:
> Robert Haas wrote:
>>On Fri, Jan 2, 2009 at 3:23 PM, Stephen R. van den Berg wrote:
>>> Three things:
>>> a. Shouldn't it in theory be possible to have a decompression algorithm
>>> which is IO-bound because it decompresses faster
Has this gone anywhere? Is the CVS repo safe yet?
a.
> * Peter Eisentraut [081229 09:50]:
> > I had originally sent this mail in April, but it appears to have been
> > blocked
> > because the attachment was too large.
> >
> > While we are in the process of dealing with CVS-Git conversion, we
Robert Haas wrote:
>On Fri, Jan 2, 2009 at 3:23 PM, Stephen R. van den Berg wrote:
>> Three things:
>> a. Shouldn't it in theory be possible to have a decompression algorithm
>> which is IO-bound because it decompresses faster than the disk can
>> supply the data? (On common current hardware)
"Stephen R. van den Berg" writes:
> Alex Hunsaker wrote:
>>I think we could just add another toast storage type: alter table
>>alter column set storage compress; ? It seems overkill to expose
>>PGLZ_Strategy knobs per column...
>
> Three things:
> a. Shouldn't it in theory be possible to have a
Greg Smith wrote:
> On Fri, 2 Jan 2009, Tom Lane wrote:
>
> > ISTM that you *should* be able to see an improvement on even
> > single-spindle systems, due to better overlapping of CPU and I/O effort.
>
> The earlier synthetic tests I did:
>
> http://archives.postgresql.org/pgsql-hackers/2008-09/
Tom Lane writes:
> In principle you should be able to adjust the constant so that vmstat
> shows about 50% CPU busy, and then enabling fadvise should improve
> matters significantly.
I think in practice individual queries don't interleave much cpu with i/o
work. A single random page fetch is 5ms
On Fri, Jan 2, 2009 at 3:23 PM, Stephen R. van den Berg wrote:
> Alex Hunsaker wrote:
>>I think we could just add another toast storage type: alter table
>>alter column set storage compress; ? It seems overkill to expose
>>PGLZ_Strategy knobs per column...
>
> Three things:
> a. Shouldn't it in t
Alex Hunsaker wrote:
>I think we could just add another toast storage type: alter table
>alter column set storage compress; ? It seems overkill to expose
>PGLZ_Strategy knobs per column...
Three things:
a. Shouldn't it in theory be possible to have a decompression algorithm
which is IO-bound b
Greg Smith writes:
> On Fri, 2 Jan 2009, Tom Lane wrote:
>> ISTM that you *should* be able to see an improvement on even
>> single-spindle systems, due to better overlapping of CPU and I/O effort.
> The earlier synthetic tests I did:
> http://archives.postgresql.org/pgsql-hackers/2008-09/msg01401
> I mean that if someone attempts to maintain referential integrity with
> SQL code, without using explicit locks, it is not reliable.
> Presumably the implementation of foreign keys in PostgreSQL takes this
> into account and blocks the kind of behavior shown below. This
> behavior would not occu
On Fri, Jan 2, 2009 at 11:44, Tom Lane wrote:
> "Robert Haas" writes:
>> I've seen gzip approach 10X on what was basically a large
>> tab-separated values file, but I agree that some more experimentation
>> to determine the real cause of the problem would be useful.
>
> If I'm counting on my fing
On Fri, Jan 2, 2009 at 10:44, Robert Haas wrote:
> Here, we have a case where the space savings are potentially much
> larger, and the only argument against it is that someone might be
> disappointed in the performance of substring operations, if they
> happen to do any. What if they know that th
On Fri, 2 Jan 2009, Tom Lane wrote:
ISTM that you *should* be able to see an improvement on even
single-spindle systems, due to better overlapping of CPU and I/O effort.
The earlier synthetic tests I did:
http://archives.postgresql.org/pgsql-hackers/2008-09/msg01401.php
Showed a substantial
On Fri, Jan 2, 2009 at 09:01, Tom Lane wrote:
>The argument was that compressing/decompressing such large chunks would
>require a lot of CPU effort; also it would defeat attempts to fetch
>subsections of a large string. In the past we've required people to
>explicitly "ALTER TABLE SET STORAGE ext
"Kevin Grittner" writes:
>> What do you mean by referential integrity? I don't believe you can
>> construct a foreign key problem at any transaction isolation level.
> I mean that if someone attempts to maintain referential integrity with
> SQL code, without using explicit locks, it is not reli
"Robert Haas" writes:
> On Fri, Jan 2, 2009 at 11:01 AM, Tom Lane wrote:
>> I'm not entirely convinced by Alex' analysis anyway; the only way
>> those 39 large values explain the size difference is if they are
>> *tremendously* compressible, like almost all zeroes. The toast
>> compressor isn't
>>> "Robert Haas" wrote:
>> Not sure about "most". Referential integrity is a pretty common
use
>> case, and it is not covered without explicit locking. Many other
>> common use cases are not, either. I agree many are, and that the
rest
>> can be worked around easily enough that I wouldn't wa
On Fri, Jan 2, 2009 at 11:01 AM, Tom Lane wrote:
> "Stephen R. van den Berg" writes:
>> What seems to be hurting the most is the 1MB upper limit. What is the
>> rationale behind that limit?
>
> The argument was that compressing/decompressing such large chunks would
> require a lot of CPU effort;
On Fri, 2009-01-02 at 11:02 +0100, Guillaume Lelarge wrote:
> Simon Riggs a écrit :
> > On Wed, 2008-12-17 at 15:21 +, Simon Riggs wrote:
> >> http://wiki.postgresql.org/wiki/Hot_Standby
> >>
> >> now contains a link to latest version of this patch.
> >
> > v6 of Hot Standby now uploaded to
The pg_stat_statements patch tries to introduce a custom GUC variable
that's marked with context PGC_POSTMASTER, betokening the fact that
it's setting the allocated size of a portion of shared memory and so
changing it after startup is pointless/impossible.
This doesn't actually work in the curren
> Not sure about "most". Referential integrity is a pretty common use
> case, and it is not covered without explicit locking. Many other
> common use cases are not, either. I agree many are, and that the rest
> can be worked around easily enough that I wouldn't want to see
> blocking introduced
"Stephen R. van den Berg" writes:
> What seems to be hurting the most is the 1MB upper limit. What is the
> rationale behind that limit?
The argument was that compressing/decompressing such large chunks would
require a lot of CPU effort; also it would defeat attempts to fetch
subsections of a la
>>> Simon Riggs wrote:
> On Mon, 2008-12-29 at 18:13 -0600, Kevin Grittner wrote:
>
>> I hope someone can show me something good I've missed so far.
>
> You're viewing this in problem-exposed language, unintentionally I'm
> sure.
Hmmm My meaning was, "I hope someone can point me to a goo
Greg Smith writes:
> On Thu, 1 Jan 2009, Robert Haas wrote:
>> The only thing I haven't been able to do is demonstrate that this change
>> actually produces a performance improvement. Either I'm testing the
>> wrong thing, or it just doesn't provide any benefit on a single-spindle
>> system.
Magnus Hagander writes:
> Alvaro Herrera wrote:
>> Not that this affects me in any way, but should there be a GUC variable
>> to set the default behavior system-wide?
> I thought about that, but I don't want to add extra gucs without a good
> reason. You'd typically not have very many different l
Alex Hunsaker wrote:
>On Thu, Jan 1, 2009 at 22:44, Alex Hunsaker wrote:
>> Looking at the patch we dont compress things > 1M anymore so I thought
>> maybe I was hitting that. But no luck there are only 39 rows where
>> the row size > 1M... With those 39 being about 22M each.
>Oh my... 25 * 40
Also, it looks like we have the same problem with the private key, in
client_cert_cb(), agreed?
//Magnus
Yeah, same issue in that function. I missed that. My grep'n was
obviously brain dead.
It almost feels like there should be some util functions like
get_sslrootcert(conn, path_buf, b
Tom Lane wrote:
Basically, we have no information about what the proper parameters of external
languages would be. (We have some pretty good ideas, but that's not the
same.) Especially if we override the trusted/untrustedness, we could create
complete disaster.
Presumably we'd only insert s
Alvaro Herrera wrote:
> Magnus Hagander wrote:
>> Here's the patch allowing for the parameter include_realm on
>> pg_hba.conf, that makes the authentication system pass the u...@realm
>> format username to the identmap, instead of stripping the realm.
>
> Not that this affects me in any way, but s
Magnus Hagander wrote:
> Here's the patch allowing for the parameter include_realm on
> pg_hba.conf, that makes the authentication system pass the u...@realm
> format username to the identmap, instead of stripping the realm.
Not that this affects me in any way, but should there be a GUC variable
t
Here's the patch allowing for the parameter include_realm on
pg_hba.conf, that makes the authentication system pass the u...@realm
format username to the identmap, instead of stripping the realm. This
was the original reason for having regexp support in the ident maps..
The idea is to make it a lo
I've for some reason been sitting on this patch for a while, it was
supposed to be a part of the pg_hba changes from a few months ago.
Anyway. Here's a patch that makes it possible to set krb_realm and
krb_server_hostname on a per-hba-row basis, instead of just for the
whole server.
Comments?
//
On Thu, 11 Dec 2008, Tom Lane wrote:
On the whole I think we have some evidence here to say that upping the
default value of default_stats_target to 100 wouldn't be out of line,
but 1000 definitely is. Comments?
Circling back to where this started from now that the discussion has
slowed down
Andrew Chernow wrote:
> Magnus Hagander wrote:
>> Alex Hunsaker wrote:
>>> On Sat, Dec 27, 2008 at 11:50, Andrew Chernow wrote:
Why does pqGetHomeDirectory have to succeed to use
conn->sslrootcert. Maybe
this should be an OR of the two since sslrootcert is not dependent on
home
On Thu, 1 Jan 2009, Robert Haas wrote:
The only thing I haven't been able to do is demonstrate that this change
actually produces a performance improvement. Either I'm testing the
wrong thing, or it just doesn't provide any benefit on a single-spindle
system.
When I did a round of testing o
Simon Riggs a écrit :
> On Wed, 2008-12-17 at 15:21 +, Simon Riggs wrote:
>> http://wiki.postgresql.org/wiki/Hot_Standby
>>
>> now contains a link to latest version of this patch.
>
> v6 of Hot Standby now uploaded to Wiki (link above), with these changes:
>
> * Must ignore_killed_tuples and
Tom Lane wrote:
> Magnus Hagander writes:
>> The kerberos configuration options (krb_caseinsens_users, krb_realm,
>> krb_server_keyfile, krb_srvname, krb_server_hostname) are all set as
>> PGC_POSTMASTER. From what I can see, the only places where kerberos code
>> is called is from ClientAuthentic
67 matches
Mail list logo
