On Mon, Nov 24, 2008 at 9:40 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Mon, 24 Nov 2008, Scott Marlowe wrote:
>
>> My guess is that the period of time for which pg_buffercache takes locks
>> on the buffer map are short enough that it isn't a real big deal on a fast
>> enough server.
>
> As the
On Mon, 24 Nov 2008, Scott Marlowe wrote:
My guess is that the period of time for which pg_buffercache takes locks
on the buffer map are short enough that it isn't a real big deal on a
fast enough server.
As the server involved gets faster, the amount of time the locks are
typically held for
On Mon, 24 Nov 2008, Kevin Kempter wrote:
Currently I run this each 15min via cron:
insert into buffercache_stats select now(), isdirty, count(*) as buffers,
(count(*) * 8192) as memory from pg_buffercache group by 1,2;
This query isn't going to save the information you need to figure out if
I believe his earlier (original) complaint was that it was slower with the
LIMIT than with no limit. As in, the select (*) query was faster to get the
whole thing than apply the limit. Wherever that is the case, it is broken.
Certainly a complex join makes this more difficult, but one would ag
SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 100 LIMIT 100
I think pagination is overrated.
If the query produces, for instance, something like 100 rows or less,
more often than not, getting all the rows will take the exact same time as
getting a portion of the rows... in
Andrus,
My first thought on the query where a pattern being faster than the query with
an exact value is that the planner does not have good enough statistics on that
column. Without looking at the explain plans further, I would suggest trying
something simple. The fact that it is fasster on
Andrus wrote:
Scott,
And how exactly should it be optimized? If a query is even moderately
interesting, with a few joins and a where clause, postgresql HAS to
create the rows that come before your offset in order to assure that
it's giving you the right rows.
SELECT ... FROM bigtable ORDER B
it was veery fast. To be honest I do not know what is happening?!
This is really weird.
It seems that PostgreSql OFFSET / LIMIT are not optimized and thus typical
paging queries
SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET pageno*100 LIMIT 100
or even first page query
SELECT ... F
On Mon, Nov 24, 2008 at 12:52 PM, Brad Nicholson
<[EMAIL PROTECTED]> wrote:
>> I just ran it in a loop over and over on my 8 core opteron server and
>> it ran the load factor up by almost exactly 1.0. Under our normal
>> daily load, it sits at 1.9 to 2.5, and it climbed to 2.9 under the new
>> loa
Tomas,
OK, what was the number of unused pointer items in the VACUUM output?
I posted it in this thread:
VACUUM FULL ANALYZE VERBOSE;
...
INFO: free space map contains 14353 pages in 314 relations
DETAIL: A total of 2 page slots are in use (including overhead).
89664 page slots are requ
Richard,
These are the same but the times are different. I'd be very surprised if
you can reproduce these times reliably.
I re-tried today again and got same results: in production database pattern
query is many times slower that equality query.
toode and rid base contain only single produc
Not yet no, but that's a good suggestion and I do intend to give it a
whirl. I get about 27MB/s from raid 1 (10 is about the same) so
hopefully I can up the throughput to the speed of about one disk with
sw raid.
FYI I get more than 200 MB/s out of a Linux Software RAID5 of 3 SATA
drives (
Scott,
And how exactly should it be optimized? If a query is even moderately
interesting, with a few joins and a where clause, postgresql HAS to
create the rows that come before your offset in order to assure that
it's giving you the right rows.
SELECT ... FROM bigtable ORDER BY intprimarykey
On Mon, 2008-11-24 at 12:46 -0700, Scott Marlowe wrote:
> On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter
> <[EMAIL PROTECTED]> wrote:
> > Hi All;
> >
> > I've installed pg_buffercache and I want to use it to help define the
> > optimal
> > shared_buffers size.
> >
> > Currently I run this each 15
On Mon, Nov 24, 2008 at 11:43 AM, Kevin Kempter
<[EMAIL PROTECTED]> wrote:
> Hi All;
>
> I've installed pg_buffercache and I want to use it to help define the optimal
> shared_buffers size.
>
> Currently I run this each 15min via cron:
> insert into buffercache_stats select now(), isdirty, count(*)
On Mon, 2008-11-24 at 11:43 -0700, Kevin Kempter wrote:
> Hi All;
>
> I've installed pg_buffercache and I want to use it to help define the optimal
> shared_buffers size.
>
> Currently I run this each 15min via cron:
> insert into buffercache_stats select now(), isdirty, count(*) as buffers,
>
On Mon, Nov 24, 2008 at 10:26 AM, Andrus <[EMAIL PROTECTED]> wrote:
>> it was veery fast. To be honest I do not know what is happening?!
>
> This is really weird.
> It seems that PostgreSql OFFSET / LIMIT are not optimized and thus typical
> paging queries
And how exactly should it be optimized?
Hi All;
I've installed pg_buffercache and I want to use it to help define the optimal
shared_buffers size.
Currently I run this each 15min via cron:
insert into buffercache_stats select now(), isdirty, count(*) as buffers,
(count(*) * 8192) as memory from pg_buffercache group by 1,2;
and here
On Mon, Nov 24, 2008 at 8:41 AM, Simon Waters <[EMAIL PROTECTED]> wrote:
> That said a couple of weeks back ours corrupted a volume on replacing a dead
> hard disk, so I'm never touching these cheap and tacky LSI RAID cards ever
> again. It is suppose to just start rebuilding the array when you in
On Monday 24 November 2008 14:49:17 Glyn Astill wrote:
> --- On Mon, 24/11/08, Steve Clark <[EMAIL PROTECTED]> wrote:
> > > Yeah the battery's on it, that and the 128Mb is
> >
> > really the only reason I thought I'd give it a whirl.
> >
> >
> > Is the battery functioning? We found that the unit h
On Mon, Nov 24, 2008 at 8:06 AM, Glyn Astill <[EMAIL PROTECTED]> wrote:
> --- Scott Marlowe <[EMAIL PROTECTED]> wrote:
>
>> >
>> > Yeah the battery is on there, and in the BIOS it says it's
>> "PRESENT" and the status is "GOOD".
>>
>> If I remember correctly, older LSI cards had pretty poor
>> perf
--- Scott Marlowe <[EMAIL PROTECTED]> wrote:
> >
> > Yeah the battery is on there, and in the BIOS it says it's
> "PRESENT" and the status is "GOOD".
>
> If I remember correctly, older LSI cards had pretty poor
> performance
> in RAID 1+0 (or any layered RAID really). Have you tried setting
> up
On Mon, Nov 24, 2008 at 7:49 AM, Glyn Astill <[EMAIL PROTECTED]> wrote:
> --- On Mon, 24/11/08, Steve Clark <[EMAIL PROTECTED]> wrote:
>
>> > Yeah the battery's on it, that and the 128Mb is
>> really the only reason I thought I'd give it a whirl.
>> >
>> >
>> Is the battery functioning? We found t
--- On Mon, 24/11/08, Steve Clark <[EMAIL PROTECTED]> wrote:
> > Yeah the battery's on it, that and the 128Mb is
> really the only reason I thought I'd give it a whirl.
> >
> >
> Is the battery functioning? We found that the unit had to
> be on and charged before write back caching
> would work
>> Given the fact that the performance issues are caused by bloated tables
>> and / or slow I/O subsystem, moving to a similar system won't help I
>> guess.
>
> I have ran VACUUM FULL ANALYZE VERBOSE
> and set MAX_FSM_PAGES = 15
>
> So there is no any bloat except pg_shdepend indexes which shou
Glyn Astill wrote:
--- On Sat, 22/11/08, Scott Marlowe <[EMAIL PROTECTED]> wrote:
You really have two choices. First is to try and use it as
a plain
SCSI card, maybe with caching turned on, and do the raid in
software.
Second is to cut it into pieces and make jewelry out of it.
Haha, I'm
Tomas,
Let's suppose you set a reasonable value (say 8096) instead of 2GB. That
gives about 160MB.
Anyway this depends - if you have a lot of slow queries caused by on-disk
sorts / hash tables, use a higher value. Otherwise leave it as it is.
Probably product orders table is frequently joined
Andrus wrote:
> Both queries return same result (19) and return same data.
> Pattern query is a much slower (93 sec) than equality check (13 sec).
> How to fix this ?
> Using 8.1.4, utf-8 encoding, et-EE locale
They're different queries. The fact that they return the same results is
a coincidence
28 matches
Mail list logo