> would one not have to repeat this operation regularly, to keep
> any advantage of this ? my impression was that this is a relatively
> heavy operation on a large table.
Yeah, it requires an exclusive lock and a table rebuild. It might be
useful to a message board type database since (one presum
Tom Lane wrote:
> The difference would be pretty marginal --- especially if you choose
to
> use bigints instead of ints. (A timestamp is just a float8 or bigint
> under the hood, and is no more expensive to compare than those
datatypes.
> Timestamps *are* expensive to convert for I/O, but comparis
"Merlin Moncure" <[EMAIL PROTECTED]> wrote:
-- optional
cluster user_message_idx messages;
would one not have to repeat this operation regularly, to keep
any advantage of this ? my impression was that this is a relatively
heavy operation on a large table.
gnari
---(en
> I don't have a huge amount of experience with this in pg, but one of
the
> tricks we do in the ISAM world is a 'reverse date' system, so that you
> can scan forwards on the key to pick up datetimes in descending order.
> This is often a win because the o/s cache may assume read/forwards
> giving
"Merlin Moncure" <[EMAIL PROTECTED]> writes:
> The following suggestion works in two principles: one is that instead of
> using timestamps for ordering, integers are quicker,
The difference would be pretty marginal --- especially if you choose to
use bigints instead of ints. (A timestamp is just
> This example looks fine, but since userid 51 evidently only has 35
> posts, there's not much time needed to read 'em all and sort 'em. The
> place where the double-column index will win big is on userids with
> hundreds of posts.
>
> You have to keep in mind that each index costs time to mainta
On Wed, 11 Aug 2004 20:29:04 -0400, Jason Coene <[EMAIL PROTECTED]> wrote:
gf=# EXPLAIN ANALYZE SELECT id FROM comments WHERE userid = 51 ORDER BY
timestamp DESC LIMIT 5;
QUERY
PLAN
---
"Jason Coene" <[EMAIL PROTECTED]> writes:
> We have a lot of:
> SELECT whatever
> FROM ourtable
> WHERE field1 = X
> AND field2 = Y
> AND field3 = Z
> ORDER BY id DESC
> LIMIT 5
> With indexes:
> ourtable(id)
> ourtable(field1, field2, field3)
> Is it standar
> You mean you are doing
> SELECT ... WHERE userid = 42 ORDER BY timestamp DESC LIMIT 5;
> and hoping that separate indexes on userid and timestamp will get the
> job done? They won't. There are only two possible plans for this,
> neither very good: select all of user 42's posts and sort them
[snip]
>
> One question I do have though - you specifically mentioned NOW() as
> something to watch out for, in that it's mutable. We typically use COUNT()
> as a subselect to retrieve the number of associated rows to the current
> query. Additionally, we use NOW a lot, primarily to detect the s
"Jason Coene" <[EMAIL PROTECTED]> writes:
> A good example, a comments table where users submit TEXT data. A common
> query is to find the last 5 comments a user has submitted. The scan, while
> using an index, takes a considerable amount of time (> 0.5 sec is about as
> good as it gets). Again,
> > Hi Rod,
> >
> > I was looking at top and vmstat - which always show under 300MB
> "Active".
> > We may hit 400MB at peak. Everything I see (though this isn't my area
> of
> > expertise) points to most of the memory simply being unused. Results
> below,
> > am I missing something?
>
> This lo
On Wed, 2004-08-11 at 18:03, Jason Coene wrote:
> > -Original Message-
> > From: Rod Taylor [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, August 11, 2004 5:46 PM
> > To: Jason Coene
> > Cc: 'Merlin Moncure'; Postgresql Performance
> > Subj
> -Original Message-
> From: Rod Taylor [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 11, 2004 5:46 PM
> To: Jason Coene
> Cc: 'Merlin Moncure'; Postgresql Performance
> Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database
>
> &g
On Wed, 2004-08-11 at 17:31, Brian Hirt wrote:
> On Aug 11, 2004, at 3:18 PM, Jason Coene wrote:
> >
> > I'm wondering why our PG server is using so little memory... The
> > system has
> > 2GB of memory, though only around 200MB of it are used. Is there a PG
> > setting to force more memory usag
> I'm wondering why our PG server is using so little memory... The system has
> 2GB of memory, though only around 200MB of it are used. Is there a PG
This is the second time you've said this. Surely you're not implying
there is 1.8GB Free Memory -- rather than 1.8GB in Buffers or Cache.
Send ou
On Aug 11, 2004, at 3:18 PM, Jason Coene wrote:
I'm wondering why our PG server is using so little memory... The
system has
2GB of memory, though only around 200MB of it are used. Is there a PG
setting to force more memory usage towards the cache? Additionally,
we use
FreeBSD. I've heard that
>
> Right. The point is: is your i/o bottle neck on the read side or the
> write side. With 10-30 inserts/sec and fsync off, it's definitely on
> the read side. What's interesting is that such a low insert load is
> causing i/o storm problems. How does your app run with fsync on?
>
> With rea
> We do have a lot of INSERT/UPDATE calls, specifically on tables that
track
> user sessions, then of course things like comments, etc (where we'll
see
> 10-30 INSERT's per second, with TEXT field, and hundreds of reads per
> second). Additionally, our system does use a lot of aggregate
functions.
Jason,,
One suggestion i have, stay away from adaptec ZCR RAID products, we've
been doing testing on them, and they don't perform well at all.
--brian
On Aug 11, 2004, at 1:08 PM, Jason Coene wrote:
Thanks for all the feedback. To clear it up, we are definitely not CPU
bound at the moment. Any
PROTECTED] On Behalf Of Merlin Moncure
Sent: Wednesday, August 11, 2004 1:04 PM
To: [EMAIL PROTECTED]
Cc: Postgresql Performance; [EMAIL PROTECTED]
Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database
> On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote:
>
> > One thi
> On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote:
>
> > One thing you might consider is materialized views. Your aggregate
> > functions are killing you...try to avoid using them (except min/max
on
> > an index). Just watch out for mutable functions like now().
> >
> > http://www.varlena.com/
On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote:
> One thing you might consider is materialized views. Your aggregate
> functions are killing you...try to avoid using them (except min/max on
> an index). Just watch out for mutable functions like now().
>
> http://www.varlena.com/varlena/Gener
> The issue that I think we're seeing is that the performance on the
3Ware
> RAID is quite bad, watching FreeBSD systat will show it at "100% busy"
at
> around "3.5 MB/s". When it needs to seek across a table (for, say, an
> aggregate function - typically a COUNT()), it slows the entire server
dow
We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4,
2GB
ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!).
Cheap solution while you look for another server :
Try to use something other than RAID5.
You have 4 disks, so you could use a striping+mirroring RAID which would
> Our database is about 20GB on disk, we have some quite large tables - 2M
> rows with TEXT fields in a sample table, accessed constantly. We average
> about 4,000 - 5,000 queries per second - all from web traffic. As you can
99% is reads? and probably the same data over and over again? You migh
On Tue, 2004-08-10 at 13:17, Jason Coene wrote:
> Hi All,
>
> We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4, 2GB
> ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!).
>
> Our database is about 20GB on disk, we have some quite large tables - 2M
> rows with TEXT fields in a s
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 10, 2004 7:07 PM
To: Jason Coene
Cc: Postgresql Performance
Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database
> Our database is about 20GB on disk, we have some quite large tables - 2M
> rows with TEXT fields in a sample tabl
28 matches
Mail list logo
