On Tue, Nov 22, 2005 at 11:20:09PM +0100, Yves Vindevogel wrote:
> 8.1, hmm, that's brand new.
Yes, but give it a try, at least in a test environment. The more
people use it, the more we'll find out if it has any problems.
> But, still, it's quite some coding for a complete recordset, not ?
On Tue, Nov 22, 2005 at 11:17:41PM +0100, Yves Vindevogel wrote:
> But this does not work without the second line, right ?
What second line? Instead of returning a specific composite type
a function can return RECORD or SETOF RECORD; in these cases the
query must provide a column definition lis
Alan Stange wrote:
> Bruce Momjian wrote:
> > Right now the file system will do read-ahead for a heap scan (but not an
> > index scan), but even then, there is time required to get that kernel
> > block into the PostgreSQL shared buffers, backing up Luke's observation
> > of heavy memcpy() usage.
>
Alan Stange <[EMAIL PROTECTED]> writes:
> For sequential scans, you do have a background reader. It's the kernel. As
> long as you don't issue a seek() between read() calls, the kernel will get the
> hint about sequential IO and begin to perform a read ahead for you. This is
> where the above
Tom Lane wrote:
Ralph Mason <[EMAIL PROTECTED]> writes:
Is there any way I can say make ret a binary cursor?
It's possible to determine that at the protocol level, if you're using
V3 protocol; but whether this is exposed to an application depends on
what client-side software you are us
Ralph Mason <[EMAIL PROTECTED]> writes:
> Is there any way I can say make ret a binary cursor?
It's possible to determine that at the protocol level, if you're using
V3 protocol; but whether this is exposed to an application depends on
what client-side software you are using. Which you didn't say
Bruce Momjian wrote:
Greg Stark wrote:
Alan Stange <[EMAIL PROTECTED]> writes:
The point your making doesn't match my experience with *any* storage or program
I've ever used, including postgresql. Your point suggests that the storage
system is idle and that postgresql is broken beca
Hi,
I am trying to get better performance reading data from postgres, so I
would like to return the data as binary rather than text as parsing it
is taking a considerable amount of processor.
However I can't figure out how to do that! I have functions like.
function my_func(ret refcursor) re
Greg Stark wrote:
>
> Alan Stange <[EMAIL PROTECTED]> writes:
>
> > The point your making doesn't match my experience with *any* storage or
> > program
> > I've ever used, including postgresql. Your point suggests that the storage
> > system is idle and that postgresql is broken because it is
Yes, it's turned on, unfortunately it got overlooked during the setup,
and until now...!
It's mostly a 'read' application, I increased the vm.max-readahead to
2048 from the default 256, after which I've not seen the CS storm,
though it could be incidental.
Thanks,
Anjan
-Original Message
8.1, hmm, that's brand new.
But, still, it's quite some coding for a complete recordset, not ?
On 22 Nov 2005, at 19:59, Michael Fuhr wrote:
On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel wrote:
Is there another way in PG to return a recordset from a function than
to declare a type
But this does not work without the second line, right ?
BTW, the thing returned is not a record. It's a bunch of fields, not a complete record or fields of multiple records.
I'm not so sure it works.
On 22 Nov 2005, at 19:42, Jim Buttafuoco wrote:
create function abc() returns setof RECORD ...
P.s., followup to my last post, I don't know if turning of HT actually
lowered the number of context switches, just that it made my server run
faster.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.post
On Tue, 2005-11-22 at 14:33, Anjan Dave wrote:
> Is there any way to get a temporary relief from this Context Switching
> storm? Does restarting postmaster help?
>
> It seems that I can recreate the heavy CS with just one SELECT
> statement...and then when multiple such SELECT queries are coming i
Is there any way to get a temporary relief from this Context Switching
storm? Does restarting postmaster help?
It seems that I can recreate the heavy CS with just one SELECT
statement...and then when multiple such SELECT queries are coming in,
things just get hosed up until we cancel a bunch of qu
"Anjan Dave" <[EMAIL PROTECTED]> writes:
> Would this problem change it's nature in any way on the recent Dual-Core
> Intel XEON MP machines?
Probably not much.
There's some evidence that Opterons have less of a problem than Xeons
in multi-chip configurations, but we've seen CS thrashing on Opter
Thanks, guys, I'll start planning on upgrading to PG8.1
Would this problem change it's nature in any way on the recent Dual-Core
Intel XEON MP machines?
Thanks,
Anjan
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 22, 2005 12:36 PM
To: Vivek Khera
Cc
On Tue, Nov 22, 2005 at 07:29:37PM +0100, Yves Vindevogel wrote:
> Is there another way in PG to return a recordset from a function than
> to declare a type first ?
In 8.1 some languages support OUT and INOUT parameters.
CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$
BE
create function abc() returns setof RECORD ...
then to call it you would do
select * from abc() as (a text,b int,...);
-- Original Message ---
From: Yves Vindevogel <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org
Sent: Tue, 22 Nov 2005 19:29:37 +0100
Subject: [PERFORM]
Is there another way in PG to return a recordset from a function than to declare a type first ?
create function fnTest () returns setof myDefinedTypeIDontWantToDefineFirst ...
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: [EMAIL PROTECTED] - Mobile:
Vivek Khera <[EMAIL PROTECTED]> writes:
> On Nov 22, 2005, at 11:59 AM, Anjan Dave wrote:
>> This is a Dell Quad XEON. Hyperthreading is turned on, and I am
>> planning to turn it off as soon as I get a chance to bring it down.
> You should probably also upgrade to Pg 8.0 or newer since it is a
On Nov 22, 2005, at 9:22 AM, Marek Dabrowski wrote:
Hello
On my serwer Linux Fedora, HP DL360G3 with 2x3.06 GHz 4GB RAM
working postgresql 7.4.6. Cpu utilization is about 40-50% but
system process queue is long - about 6 task. Do you have nay
sugestion/solution?\
High run queue (loadav
At 10:26 AM 11/22/2005, Guillaume Smet wrote:
Ron,
First of all, thanks for your time.
Happy to help.
As has been noted many times around here, put the WAL on its own
dedicated HD's. You don't want any head movement on those HD's.
Yep, I know that. That's just we supposed it was not so im
On Nov 22, 2005, at 11:59 AM, Anjan Dave wrote: This is a Dell Quad XEON. Hyperthreading is turned on, and I am planning to turn it off as soon as I get a chance to bring it down.You should probably also upgrade to Pg 8.0 or newer since it is a known problem with XEON processors and older postgres
Hi,
One of our PG server is experiencing extreme slowness and
there are hundreds of SELECTS building up. I am not sure if heavy context
switching is the cause of this or something else is causing it.
Is this pretty much the final word on this issue?
http://archives.postgresql.org/p
On Tue, 22 Nov 2005 15:22:59 +0100
Marek Dabrowski <[EMAIL PROTECTED]> wrote:
> Hello
>
> On my serwer Linux Fedora, HP DL360G3 with 2x3.06 GHz 4GB RAM working
> postgresql 7.4.6. Cpu utilization is about 40-50% but system process
> queue is long - about 6 task. Do you have nay sugestion/soluti
Ron,
First of all, thanks for your time.
As has been noted many times around here, put the WAL on its own
dedicated HD's. You don't want any head movement on those HD's.
Yep, I know that. That's just we supposed it was not so important if it
was nearly a readonly database which is wrong acc
At 09:26 AM 11/22/2005, Guillaume Smet wrote:
Ron wrote:
If I understand your HW config correctly, all of the pg stuff is on
the same RAID 10 set?
No, the system and the WAL are on a RAID 1 array and the data on
their own RAID 10 array.
As has been noted many times around here, put the WAL
Claus and Andrew,
Claus Guttesen wrote:
Isn't sort_mem quite high? Remember that sort_mem size is allocated
for each sort, not for each connection. Mine is 4096 (4 MB). My
effective_cache_size is set to 27462.
I tested sort mem from 4096 to 32768 (4096, 8192, 16384, 32768) this
afternoon and
Hello
On my serwer Linux Fedora, HP DL360G3 with 2x3.06 GHz 4GB RAM working
postgresql 7.4.6. Cpu utilization is about 40-50% but system process
queue is long - about 6 task. Do you have nay sugestion/solution?
Regards
Marek
---(end of broadcast)--
Luke,
- XFS will probably generate better data rates with larger files. You
really need to use the same file size as does postgresql. Why compare
the speed to reading a 16G file and the speed to reading a 1G file.
They won't be the same. If need be, write some code that does the test
or
Ron wrote:
If I understand your HW config correctly, all of the pg stuff is on the
same RAID 10 set?
No, the system and the WAL are on a RAID 1 array and the data on their
own RAID 10 array.
As I said earlier, there's only a few writes in the database so I'm not
really sure the WAL can be a l
Tom Lane wrote:
Rich Doughty <[EMAIL PROTECTED]> writes:
However, the following query (which i believe should be equivalent)
SELECT *
FROM
tokens.ta_tokenhist h INNER JOIN
tokens.ta_tokens t ON h.token_id = t.token_id LEFT JOIN
tokens.ta_tokenhist i ON t.tok
If I understand your HW config correctly, all of the pg stuff is on
the same RAID 10 set?
If so, give WAL its own dedicated RAID 10 set. This looks like the
old problem of everything stalling while WAL is being committed to HD.
This concept works for other tables as well. If you have a tabl
Qingqing Zhou wrote:
Someone is doing a massive *write* at this time, which makes your query
*read* quite slow. Can you find out which process is doing write?
Indexes should be in memory so I don't expect a massive write to slow
down the select queries. sdb is the RAID10 array dedicated to our
Andrew,
I would be very suspicious of that much memory for sort. Please see
the docs for what that does. That is the amount that _each sort_ can
allocate before spilling to disk.
If some set of your users are
causing complicated queries with, say, four sorts apiece, then each
user is potential
36 matches
Mail list logo
