I've got a long-running, update-heavy transaction that increasingly slows
down the longer it runs. I would expect that behavior, if there was some
temp file creation going on. But monitoring vmstat over the life of the
transaction shows virtually zero disk activity. Instead, the system has
its
Ben <[EMAIL PROTECTED]> writes:
> Unfortunately, it's not at all clear to me from reading
> http://www.postgresql.org/docs/8.1/interactive/xoper-optimization.html#AEN33077
> how like impliments selectivity. Any pointers on where to look?
likesel() and subsidiary functions in src/backend/utils/adt
"Luke Lonergan" <[EMAIL PROTECTED]> writes:
> Oops - I missed the point earlier. Start and End are separate attributes so
> this is like an unbounded window in a Start,End space. PostGis provides
> quadtree indexing would provide a terse TID list but you still have the
> problem of how to ensure
Now that I have a little time to work on this again, I've thought about it
and it seems that an easy and somewhat accurate cop-out to do this is to
use whatever the selectivity function would be for the like operator,
multiplied by a scalar that pg_tgrm should already have access to.
Unfortuna
John,
On 10/31/06 8:29 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
>> 'chrX' and StartPosition > 1000500 and EndPosition < 200;
>
> Also, there's the PostGIS stuff, though it might be overkill for what
> you want.
Oops - I missed the point earlier. Start and End are separate attributes so
th
John Major <[EMAIL PROTECTED]> writes:
> My problem is, I often need to execute searches of tables like these
> which find "All features within a range".
> Ie: select FeatureID from SIMPLE_TABLE where FeatureChromosomeName like
> 'chrX' and StartPosition > 1000500 and EndPosition < 200;
A
On Tue, Oct 31, 2006 at 10:55:40PM +0100, Ivan Voras wrote:
> Ok, so MVCC is the best thing since a guy put a round stone on a stick
> and called it "the wheel", but I've seen several references on this list
> about "indexes not being under MVCC" - at least that's how I read it,
> the original post
Weslee,
On 10/31/06 3:57 PM, "Weslee Bilodeau"
<[EMAIL PROTECTED]> wrote:
> Basic question - What version, and what indexes do you have?
I'd expect the problem with this is that unless the indexed column is
correlated with the loading order of the rows over time, then the index will
refer to row
John Major wrote:
> Hello-
>
> #I am a biologist, and work with large datasets (tables with millions of
> rows are common).
> #These datasets often can be simplified as features with a name, and a
> start and end position (ie: a range along a number line. GeneX is on
> some chromosome from posit
John,
On 10/31/06 3:18 PM, "John Major" <[EMAIL PROTECTED]> wrote:
> Any advice on how I might be able to improve this situation would be
> very helpful.
I think table partitioning is exactly what you need.
There's a basic capability in current Postgres to divide tables into parent
+ children,
Hello-
#I am a biologist, and work with large datasets (tables with millions of
rows are common).
#These datasets often can be simplified as features with a name, and a
start and end position (ie: a range along a number line. GeneX is on
some chromosome from position 10->40)
I store these
Ivan Voras wrote:
> Ok, so MVCC is the best thing since a guy put a round stone on a stick
> and called it "the wheel", but I've seen several references on this list
> about "indexes not being under MVCC" - at least that's how I read it,
> the original posts were explaining why indexes can't be use
Ok, so MVCC is the best thing since a guy put a round stone on a stick
and called it "the wheel", but I've seen several references on this list
about "indexes not being under MVCC" - at least that's how I read it,
the original posts were explaining why indexes can't be used for solving
MIN()/MAX()/
On Tuesday 31 October 2006 21:11, Worky Workerson wrote:
> One thing which I never mentioned was that I
> am using ext3 mounted with noatime,data=writeback.
You might also want to try with data=ordered. I have noticed that
nowadays it seems to be a bit faster, but not much. I don't know why,
m
Merlin Moncure wrote:
> On 10/28/06, Simon Riggs <[EMAIL PROTECTED]> wrote:
>> On Thu, 2006-10-26 at 11:06 -0400, Merlin Moncure wrote:
>> > On 10/26/06, Carlo Stonebanks <[EMAIL PROTECTED]> wrote:
>> > > This is pretty interesting - where can I read more on this?
>> Windows isn't
>> > > actually h
1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
>>
>> iirc, he is running quad opteron 885 (8 cores), so if my math is
>>
Checkpoints are not an issue here, the vmstat you included was on a 5 second
interval, so the 'bursts' were bursting at a rate of 60MB/s.
- Luke
Msg is shrt cuz m on ma treo
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Maybe it is just the PK *build* that slows it down, but I just tried some
small scale experiments on my MacBook Pro laptop (which has the same disk
performance as your server) and I get only a 10-15% slowdown from having a
PK on an integer column. The 10-15% slowdown was on 8.1.5 MPP, so it used
>I'm guessing the high bursts are checkpoints. Can you check your log
> >files for pg and see if you are getting warnings about checkpoint
> >frequency? You can get some mileage here by increasing wal files.
>
> Nope, nothing in the log. I have set:
> wal_buffers=128
> checkpoint_segments=128
Worky (!),
On 10/31/06 12:11 PM, "Worky Workerson" <[EMAIL PROTECTED]> wrote:
> Any recommendations on what to
> look at to find a fix? One thing which I never mentioned was that I
> am using ext3 mounted with noatime,data=writeback.
You can try setting the max readahead like this:
/sbin/bloc
> And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks:
So, if we divide 32,000 MB by the real time, we get:
/data (data):
89 MB/s write
38 MB/s read
... snip ...
The read speed on your /data volume is awful to the point where you should
consider it broken and find a fix. A quick
Am Dienstag, den 31.10.2006, 13:04 +0900 schrieb Leif Mortenson:
> Hello,
> I have been having a problem with the following query ignoring an index
> on the foos.bar column.
>
> SELECT c.id
> FROM foos c, bars r
> WHERE r.id != 0
> AND r.modified_time > '2006-10-20 10:00:00.000'
> AND r.modified_t
soni de wrote:
Any response?
Couple of points:
1. You're on the wrong list. This is for performance issues. I'd
recommend one of the bugs/hackers/general lists instead.
2. You don't give details of any error message produced during the crash
(or if there is one).
3a. You don't give detai
23 matches
Mail list logo
