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
Hi, Bryan.
I was just about to reply to the thread, thanks for asking. Clustering was
key. After rebooting the machine (just to make sure absolutely nothing was
cached), I immediately ran a report on Toronto: 5.25 seconds!
Here's what I did:
1. Created a new set of tables that matched the old
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/27 David Jarvis :
> Hi, Bryan.
>
> I was just about to reply to the thread, thanks for asking. Clustering was
> key. After rebooting the machine (just to make sure absolutely nothing was
> cached), I immediately ran a report on Toronto: 5.25 seconds!
>
> Here's what I did:
>
> Created a new
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/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
Hello,
Sorry for the re-post - not sure list is the relevant one, I included
slightly changed query in the previous message, sent to bugs list.
I have an ORM-generated queries where parent table keys are used to
fetch the records from the child table (with relevant FK indicies),
where child tabl
Hi All,
In my application we are using postgres which runs on an embedded
box. I have configured autovacuum to run once for every one hour. It has 5
different databases in it. When I saw the log messages, I found that it is
running autovacuum on one database every hour. As a result, on my da
One more question " Is is expected ?"
On Fri, May 21, 2010 at 3:08 PM, venu madhav wrote:
> Hi All,
>In my application we are using postgres which runs on an embedded
> box. I have configured autovacuum to run once for every one hour. It has 5
> different databases in it. When I saw the l
We're using a function that when run as a select statement outside of the
function takes roughly 1.5s to complete whereas running an identical
query within a function is taking around 55s to complete.
We are lost as to why placing this query within a function as opposed to
substituting the variab
Hi group,
I could really use your help with this one. I don't have all the
details right now (I can provide more descriptions tomorrow and logs
if needed), but maybe this will be enough:
I have written a PG (8.3.8) module, which uses Flex Lexical Analyser.
It takes text from database field and fi
Hello,
Thank you for the clarifications. The plan as run from the psql looks ok,
also did not notice any specific locks for this particular query.
Logs of the system running queries are not utterly clear, so chasing the
parameters for the explosive query is not that simple (shared logs between
mul
Dear all and Tom，
Recently my company’s postgres DB server sluggish suddenly with a
hight Context-switching value as below:
2010-04-07 04:03:15 procs memory swap io
system cpu
2010-04-07 04:03:15 r b swpd free buff cache si sobi
=?gb2312?B?u8bTwM7A?= writes:
> My postgres version: 8.1.3;
You do realize that version was obsoleted four years ago last week?
If you're encountering multiprocessor performance problems you
really need to get onto 8.3.x or later.
regards, tom lane
--
Sent via pgsql-p
venu madhav wrote:
> Hi All,
>In my application we are using postgres which runs on an embedded
> box. I have configured autovacuum to run once for every one hour. It has 5
> different databases in it. When I saw the log messages, I found that it is
> running autovacuum on one database ever
venu madhav wrote:
> Thanks for the reply..
>I am using postgres 8.01 and since it runs on a client box, I
> can't upgrade it. I've set the auto vacuum nap time to 3600 seconds.
That is an older version of autovacuum that wasn't very capable.
--
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 :
>
>> 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
On 21/05/2010 9:54 PM, Tyler Hildebrandt wrote:
We're using a function that when run as a select statement outside of the
function takes roughly 1.5s to complete whereas running an identical
query within a function is taking around 55s to complete.
We are lost as to why placing this query within
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
Salut, Cédric.
I wonder what the plan will be if you replace sc.taken_* in :
> m.taken BETWEEN sc.taken_start AND sc.taken_end
> by values. It might help the planner...
>
That is a fairly important restriction. I will try making it *
(year1||'-01-01')::date*, but I have no constant value for it -
On 27/05/2010 11:33 PM, Craig Ringer wrote:
On 21/05/2010 9:54 PM, Tyler Hildebrandt wrote:
We're using a function that when run as a select statement outside of the
function takes roughly 1.5s to complete whereas running an identical
query within a function is taking around 55s to complete.
We
Excerpts from venu madhav's message of vie may 21 05:38:43 -0400 2010:
> Hi All,
>In my application we are using postgres which runs on an embedded
> box. I have configured autovacuum to run once for every one hour. It has 5
> different databases in it. When I saw the log messages, I found
Krzysztof Nienartowicz writes:
> Logs of the system running queries are not utterly clear, so chasing the
> parameters for the explosive query is not that simple (shared logs between
> multiple threads), but from what I see there is no difference between them
> and the plan looks like (without rem
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 David Jarvis :
> Salut, Cédric.
>
>> I wonder what the plan will be if you replace sc.taken_* in :
>> m.taken BETWEEN sc.taken_start AND sc.taken_end
>> by values. It might help the planner...
>
> That is a fairly important restriction. I will try making it
> (year1||'-01-01')::date, but
Sample code:
SELECT *
FROM MyTable
WHERE foo = 'bar' AND MySlowFunc('foo') = 'bar'
Let's say this required a SEQSCAN because there were no indexes to support
column foo. For every row where foo <> 'bar' would the filter on the SEQSCAN
short-circuit the AND return false right away, or would it
"Carlo Stonebanks" wrote:
> SELECT *
> FROM MyTable
> WHERE foo = 'bar' AND MySlowFunc('foo') = 'bar'
>
> Let's say this required a SEQSCAN because there were no indexes to
> support column foo. For every row where foo <> 'bar' would the
> filter on the SEQSCAN short-circuit the AND return fals
On 5/18/10 3:28 PM, Carlo Stonebanks wrote:
Sample code:
SELECT *
FROM MyTable
WHERE foo = 'bar' AND MySlowFunc('foo') = 'bar'
Let's say this required a SEQSCAN because there were no indexes to
support column foo. For every row where foo <> 'bar' would the filter on
the SEQSCAN short-circuit th
Craig James wrote on 27.05.2010 23:13:
It would be nice if Postgres had a way to assign a cost to every
function.
Isn't that what the COST parameter is intended to be:
http://www.postgresql.org/docs/current/static/sql-createfunction.html
Thomas
--
Sent via pgsql-performance mailing list (pg
Craig James wrote:
> It would be nice if Postgres had a way to assign a cost to every
> function.
The COST clause of CREATE FUNCTION doesn't do what you want?
http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-p
On 5/27/10 2:28 PM, Kevin Grittner wrote:
Craig James wrote:
It would be nice if Postgres had a way to assign a cost to every
function.
The COST clause of CREATE FUNCTION doesn't do what you want?
http://www.postgresql.org/docs/8.4/interactive/sql-createfunction.html
Cool ... I must have
Agree with Tom on his point about avoidance of cost param adjustments to fit
specific test cases.
A few suggestions...as I assume you own this database...
- check out pg_statio_user_tables - optimize your cache hit ratio on blocks
read...different time durations... pg_stat_bgwriter (read from a scr
Scott Marlowe writes:
> So, Tom, so you think it's possible that the planner isn't noticing
> all those nulls and thinks it'll just take a row or two to get to the
> value it needs to join on?
I've committed a patch for this, if you're interested in testing that
it fixes your situation.
Hi all,
Are there any HP Smart Array disk controller users running linux that
have experimented with the new scsi based hpsa driver over the block
based cciss driver? I have a p800 controller that I'll try out soon.
(I hope.)
Regards,
Mark
--
Sent via pgsql-performance mailing list (pgsql-perf
On Thu, May 27, 2010 at 7:16 PM, Tom Lane wrote:
> Scott Marlowe writes:
>> So, Tom, so you think it's possible that the planner isn't noticing
>> all those nulls and thinks it'll just take a row or two to get to the
>> value it needs to join on?
>
> I've committed a patch for this, if you're int
Scott Marlowe writes:
> On Thu, May 27, 2010 at 7:16 PM, Tom Lane wrote:
>> I've committed a patch for this, if you're interested in testing that
>> it fixes your situation.
> Cool, do we have a snapshot build somewhere or do I need to get all
> the extra build bits like flex or yacc or bison or
Hi, Bryan.
Thanks for the notes. I thought about using a prepared statement, but I
cannot find any examples of using a PREPARE statement from within a
function, and don't really feel like tinkering around to figure it out.
Performance is at the point where the Java/PHP bridge and JasperReports ar
38 matches
Mail list logo
