Re: [PERFORM] explanation of some configs
Joshua D. Drake wrote: On Sat, 2009-02-07 at 01:52 +0100, Thomas Finneid wrote: effective_cache_size This is just a hint to tell the planner how much cache will generally be available. ok, but available for what? The number should be reflective of your shared buffers + available operating system cache. If you database is postgresql only you can generally set this very high 75% of available ram. If not then you need to tone it down. So that means, if I have 8GB ram and have set shared buffer to e.g. 4GB, I should set the effective_cache_size to at least 4GB otherwise the planner will assume I dont have as much memory available so it would be sort of pointless so set shared_buffer to 4GB? checkpoint_segments - specifies the number of segments? The number of segments that will be used before a checkpoint is forced. So to sum it up: - Specifies the number of memory segments the WAL will use before a checkpoint occur. (A checkpoint guarantees the data has been written to disk, including dirty pages.) - A segment is 16MB and the number of actually used segments are dynamic. - If this number is too low or the transaction is large, PG will spend more time on performing checkpoint operations which decreases performance. Q1: So checkpoint_time is then just another way of expressing the same? I.e. to ensure that if the segments have not been filled, which would foce a checkpoint, a checkpoint is at least forced at the specified time lapse? Q2: So how does this relate to WAL buffers? It seems to me that wal_buffers are not needed. Based on the above explanation. regards thomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
that helped, thanks a lot Tom. Looks like additional thing on 'pet peeves' list (from -general). :P -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
On Fri, Feb 6, 2009 at 9:50 PM, Tom Lane t...@sss.pgh.pa.us wrote:
The UNION arms have to all be the same data type in order to have
restrictions pushed down through the UNION. You did not show us
the table declarations for your first example, but I bet that updateid
isn't the same type in both. (And yes, a domain is different from its
underlying type for this purpose.)
I think you're right. The domain's in both cases (updateid and uri)
are bigints default nextval('something') not null;
and the r.history table's ones are just bigints not null. Same
underlying type, but not a domain. I'll try to alter it to domain
type, and see.
thanks.
--
GJ
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] explanation of some configs
Thomas Finneid wrote: Joshua D. Drake wrote: On Sat, 2009-02-07 at 01:52 +0100, Thomas Finneid wrote: effective_cache_size This is just a hint to tell the planner how much cache will generally be available. ok, but available for what? for storing the data/tables/rows in memory so it does not have the disk subsystem. The number should be reflective of your shared buffers + available operating system cache. If you database is postgresql only you can generally set this very high 75% of available ram. If not then you need to tone it down. So that means, if I have 8GB ram and have set shared buffer to e.g. 4GB, I should set the effective_cache_size to at least 4GB otherwise the planner will assume I dont have as much memory available so it would be sort of pointless so set shared_buffer to 4GB? No because other parts of Postgresql use the shared_buffer to store tables/data in memory. If shared_buffer is set low then the system will be during more disk IO as it can't fit that much into memory. Another critical setting to look at is work_mem where all the complex sorting, and joins are done which is not related to shared buffers. So leave room in memory for these processes checkpoint_segments - specifies the number of segments? The number of segments that will be used before a checkpoint is forced. So to sum it up: - Specifies the number of memory segments the WAL will use before a checkpoint occur. (A checkpoint guarantees the data has been written to disk, including dirty pages.) - A segment is 16MB and the number of actually used segments are dynamic. - If this number is too low or the transaction is large, PG will spend more time on performing checkpoint operations which decreases performance. Q1: So checkpoint_time is then just another way of expressing the same? I.e. to ensure that if the segments have not been filled, which would foce a checkpoint, a checkpoint is at least forced at the specified time lapse? Yes and No Checkpoint_time does forces a check point regardless if the segment is full or not. Checkpoint_segment is used to force a check point based on size. In a big databases a checkpoint could get very large before time had elapsed and if server cashed all that work would be rolled back. Q2: So how does this relate to WAL buffers? It seems to me that wal_buffers are not needed. Based on the above explanation. This is number of pages in shared memory the Postgresql uses before WAL is written to disk this is used to improve performance for large writes. regards thomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] explanation of some configs
effective_cache_size This is just a hint to tell the planner how much cache will generally be available. ok, but available for what? The documentation on these parameters is really very good. http://www.postgresql.org/docs/8.3/interactive/runtime-config-query.html http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html effective_cache_size doesn't actually reserve any memory, but it enables the planner to know something about what will probably happen when attempting to execute queries (and therefore generate better plans). The number should be reflective of your shared buffers + available operating system cache. If you database is postgresql only you can generally set this very high 75% of available ram. If not then you need to tone it down. So that means, if I have 8GB ram and have set shared buffer to e.g. 4GB, I should set the effective_cache_size to at least 4GB otherwise the planner will assume I dont have as much memory available so it would be sort of pointless so set shared_buffer to 4GB? You might want to look at pgtune as a starting point for tuning these settings. http://pgfoundry.org/projects/pgtune/ But, yes, if you read the documentation (links above) it says to make effective_cache_size equal to shared_buffers plus however much of the operating system disk cache you expect PostgreSQL to get. checkpoint_segments - specifies the number of segments? The number of segments that will be used before a checkpoint is forced. So to sum it up: - Specifies the number of memory segments the WAL will use before a checkpoint occur. (A checkpoint guarantees the data has been written to disk, including dirty pages.) - A segment is 16MB and the number of actually used segments are dynamic. - If this number is too low or the transaction is large, PG will spend more time on performing checkpoint operations which decreases performance. I think this is pretty much right. The WAL logs are always flushed to disk right away (unless you fool with the asynchronous_commit or fsync parameters), so you are not at risk of losing data even if the server crashes before the next checkpoint. But the checkpoints keep you from accumulating too much WAL (which eats disk space and makes recovery slower in the event of a crash). Q1: So checkpoint_time is then just another way of expressing the same? I.e. to ensure that if the segments have not been filled, which would foce a checkpoint, a checkpoint is at least forced at the specified time lapse? http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html The server's background writer process will automatically perform a checkpoint every so often. A checkpoint is created every checkpoint_segments log segments, or every checkpoint_timeout seconds, whichever comes first. Q2: So how does this relate to WAL buffers? It seems to me that wal_buffers are not needed. Based on the above explanation. wal_buffers are in-memory buffers that hold WAL that has not yet been flushed to disk. WAL segments are files hold the WAL that has been written to disk but not yet recycled (perhaps because the corresponding data blocks haven't yet been written out). ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
so Tom,
with a little help on Irc from Andrew (RhodiumToad) I got it 'fixed',
but I know this is just a hack:
Index: src/backend/utils/adt/selfuncs.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v
retrieving revision 1.258
diff -u -r1.258 selfuncs.c
--- src/backend/utils/adt/selfuncs.c1 Jan 2009 17:23:50 - 1.258
+++ src/backend/utils/adt/selfuncs.c7 Feb 2009 17:20:21 -
@@ -3392,7 +3392,7 @@
static double
convert_numeric_to_scalar(Datum value, Oid typid)
{
- switch (typid)
+ switch (getBaseType(typid))
{
case BOOLOID:
return (double) DatumGetBool(value);
ndex: src/backend/optimizer/path/allpaths.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.179
diff -u -r1.179 allpaths.c
--- src/backend/optimizer/path/allpaths.c 1 Jan 2009 17:23:43 -
1.179
+++ src/backend/optimizer/path/allpaths.c 7 Feb 2009 17:21:25 -
@@ -33,6 +33,9 @@
#include parser/parse_clause.h
#include parser/parsetree.h
#include rewrite/rewriteManip.h
+#include utils/lsyscache.h
+#include utils/syscache.h
+
/* These parameters are set by GUC */
@@ -1042,7 +1045,7 @@
continue; /* ignore resjunk
columns */
if (colType == NULL)
elog(ERROR, wrong number of tlist entries);
- if (exprType((Node *) tle-expr) != lfirst_oid(colType))
+ if (exprType((Node *) tle-expr) != lfirst_oid(colType)
getBaseType(exprType((Node *) tle-expr)) != lfirst_oid(colType))
differentTypes[tle-resno] = true;
colType = lnext(colType);
}
This is just a hack, and I know it won't work. But the question is, is
it possible to fix it ?
I think something should convert types to base type somewhere up the
call level. Perhaps we should pass on oid of type+baseType ?
I also noticed that the convert_numeric_to_scalar() family of
functions is considered a future change too, atm everything there is
hardcoded pretty much.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes: with a little help on Irc from Andrew (RhodiumToad) I got it 'fixed', but I know this is just a hack: You seem to be laboring under the delusion that this is considered a bug. It's a necessary semantic restriction, because the pushed-down expression could mean different things when applied to different data types. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
On Sat, Feb 7, 2009 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: You seem to be laboring under the delusion that this is considered a bug. It's a necessary semantic restriction, because the pushed-down expression could mean different things when applied to different data types. Very true Tom, still I was hoping this could be seen as an improvement - because in fact in my case it would be . Thanks. -- GJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
and frankly I still (and few others) think it is a defect, for domain with some base type should be treated as such. It is after all treated that way when you create index. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] inheritance, and plans
Tom == Tom Lane t...@sss.pgh.pa.us writes: [domain - base type conversion interfering with optimization] Tom You seem to be laboring under the delusion that this is Tom considered a bug. Of course it's a bug, or at least a missing feature - there is no justification for putting performance deathtraps in the way of using domains. Tom It's a necessary semantic restriction, because the pushed-down Tom expression could mean different things when applied to different Tom data types. How? Type-dependent selection of operators has already been done as part of parse analysis, no? And the domain - base conversion is purely a relabelling, no? So what semantic change is possible as a result? -- Andrew (irc:RhodiumToad) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
