On 1/17/07, Steve <[EMAIL PROTECTED]> wrote:
Hey there;
I've been lurking on this list awhile, and I've been working with postgres
for a number of years so I'm not exactly new to this. But I'm still
having trouble getting a good balance of settings and I'd like to see what
other people think. W
On 18-1-2007 23:11 Tom Lane wrote:
Increase work_mem? It's not taking the hash because it thinks it won't
fit in memory ...
When I increase it to 128MB in the session (arbitrarily selected
relatively large value) it indeed has the other plan.
Best regards,
Arjen
--
Arjen van der Meijden <[EMAIL PROTECTED]> writes:
> PS, In case any of the planner-hackers are reading, here are the plans
> of the first two queries, just to see if something can be done to
> decrease the differences between them.
Increase work_mem? It's not taking the hash because it thinks i
On 18-1-2007 18:28 Jeremy Haile wrote:
I once had a query which would operate on a recordlist and
see whether there were any gaps larger than 1 between consecutive
primary keys.
Would you mind sharing the query you described? I am attempting to do
something similar now.
Well it was over a
> I once had a query which would operate on a recordlist and
> see whether there were any gaps larger than 1 between consecutive
> primary keys.
Would you mind sharing the query you described? I am attempting to do
something similar now.
---(end of broadcast)--
On 18-1-2007 17:20 Scott Marlowe wrote:
Besides that, mysql rewrites the entire table for most table-altering
statements you do (including indexes).
Note that this applies to the myisam table type. innodb works quite
differently. It is more like pgsql in behaviour, and is an mvcc storage
A
On Thu, 2007-01-18 at 04:24, Arjen van der Meijden wrote:
> On 18-1-2007 0:37 Adam Rich wrote:
> > 4) Complex queries that might take advantage of the MySQL "Query Cache"
> > since the base data never changes
>
> Have you ever compared MySQL's performance with complex queries to
> PostgreSQL's? I
On Wed, 2007-01-17 at 18:27, Steve wrote:
> > Generally speaking, once you've gotten to the point of swapping, even a
> > little, you've gone too far. A better approach is to pick some
> > conservative number, like 10-25% of your ram for shared_buffers, and 1
> > gig or so for maintenance work_mem
On 18-1-2007 0:37 Adam Rich wrote:
4) Complex queries that might take advantage of the MySQL "Query Cache"
since the base data never changes
Have you ever compared MySQL's performance with complex queries to
PostgreSQL's? I once had a query which would operate on a recordlist and
see whether
The thought:
- Load the big chunk of data into a new table
- Generate some minimal set of indices on the new table
- Generate four queries that compare old to new:
q1 - See which tuples are unchanged from yesterday to today
q2 - See which tuples have been deleted from yesterday to today
q3
Anderson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 17, 2007 3:29 PM
To: Adam Rich
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Configuration Advice
Adam Rich wrote:
Doesn't sound like you want postgres at all Try mysql.
Could you explain your reason for suggesti
Generally speaking, once you've gotten to the point of swapping, even a
little, you've gone too far. A better approach is to pick some
conservative number, like 10-25% of your ram for shared_buffers, and 1
gig or so for maintenance work_mem, and then increase them while
exercising the system, and
[EMAIL PROTECTED] (Steve) writes:
> I'm wondering what we can do to make
> this better if anything; would it be better to leave the indexes on?
> It doesn't seem to be.
Definitely NOT. Generating an index via a bulk sort is a LOT faster
than loading data into an index one tuple at a time.
We s
On 1/17/07, Steve <[EMAIL PROTECTED]> wrote:
However, I will look into this and see if I can figure out this
average value. This may be a valid idea, and I'll look some more at it.
It must be, Oracle sells it pretty heavily as a data warehousing feature
;). Oracle calls it a materia
Note that you only need to have the ASC and DESC versions of opclasses when
you are going to use multicolumn indexes with some columns in ASC order and
some in DESC order. For columns used by themselves in an index, you don't
need to do this, no matter which order you are sorting on.
Yeah, I as
Would it be possible to just update the summary table, instead of recreating
it from scratch every night?
Hrm, I believe it's probably less work for the computer to do if
it's rebuilt. Any number of rows may be changed during an update, not
including additions, so I'd have to pull out what's
On 17-Jan-07, at 3:41 PM, Steve wrote:
Hey there;
I've been lurking on this list awhile, and I've been working with
postgres for a number of years so I'm not exactly new to this. But
I'm still having trouble getting a good balance of settings and I'd
like to see what other people think.
ECTED]
Sent: Wednesday, January 17, 2007 3:29 PM
To: Adam Rich
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Configuration Advice
Adam Rich wrote:
> Doesn't sound like you want postgres at all Try mysql.
Could you explain your reason for suggesting mysql
How many rows do you typically load each night? If it is say less than 10%
of the total rows, then perhaps the suggestion in the next paragraph is
reasonable.
Hrm. It's very, very variable. I'd say it's more than 10% on
average, and it can actually be pretty close to 50-100% on certain days
On Wed, 2007-01-17 at 15:58, Steve wrote:
> On Wed, 17 Jan 2007, Benjamin Minshall wrote:
>
> >
> >> Building these indexes takes forever!
> >
> >> Anyway -- ANYTHING we can do to make this go faster is appreciated :)
> >> Here's some vital statistics:
> >
> >> - Machine is a 16 GB, 4 actual CPU
> From: "Steve" <[EMAIL PROTECTED]>
> To: pgsql-performance@postgresql.org
> Sent: 1/17/2007 2:41 PM
> Subject: [PERFORM] Configuration Advice
>
> SO ... our goal here is to make this load process take less time. It
> seems the big part is building th
Bricklen Anderson wrote:
> Adam Rich wrote:
>> Doesn't sound like you want postgres at all Try mysql.
>
> Could you explain your reason for suggesting mysql? I'm simply curious
> why you would offer that as a solution.
He sound a little trollish to me. I would refer to the other actually
help
Building these indexes takes forever!
Anyway -- ANYTHING we can do to make this go faster is appreciated :)
Here's some vital statistics:
- Machine is a 16 GB, 4 actual CPU dual-core opteron system using SCSI
discs. The disc configuration seems to be a good one, it's the best of
all the
On Wed, 17 Jan 2007, Benjamin Minshall wrote:
Building these indexes takes forever!
Anyway -- ANYTHING we can do to make this go faster is appreciated :)
Here's some vital statistics:
- Machine is a 16 GB, 4 actual CPU dual-core opteron system using SCSI
discs. The disc configuration
1) What is the size of the whole database? Does that fit in your memory?
That's the first thing I'd like to know and I can't find it in your
post.
Current on-disk size is about 51 gig. I'm not sure if there's a different
size I should be looking at instead, but that's what du tells me the
Steve wrote:
SO ... our goal here is to make this load process take less time. It
seems the big part is building the big summary table; this big summary
table is currently 9 million rows big. Every night, we drop the table,
re-create it, build the 9 million rows of data (we use COPY to put ht
On 1/17/07, Steve <[EMAIL PROTECTED]> wrote:
However, each night we load data from a legacy cobol system into the SQL
system and then we summarize that data to make the reports faster. This
load process is intensely insert/update driven but also has a hefty
amount of selects as well. This load
> Any finally, any ideas on planner constants? Here's what I'm using:
>
> seq_page_cost = 0.5 # measured on an arbitrary scale
> random_page_cost = 1.0 # same scale as above
> cpu_tuple_cost = 0.001 # same scale as above
> cpu_index_tuple_cost
Adam Rich wrote:
Doesn't sound like you want postgres at all Try mysql.
Could you explain your reason for suggesting mysql? I'm simply curious
why you would offer that as a solution.
---(end of broadcast)---
TIP 5: don't forget to increase
Doesn't sound like you want postgres at all Try mysql.
-Original Message-
From: "Steve" <[EMAIL PROTECTED]>
To: pgsql-performance@postgresql.org
Sent: 1/17/2007 2:41 PM
Subject: [PERFORM] Configuration Advice
Hey there;
I've been lurking on this list awh
Hey there;
I've been lurking on this list awhile, and I've been working with postgres
for a number of years so I'm not exactly new to this. But I'm still
having trouble getting a good balance of settings and I'd like to see what
other people think. We may also be willing to hire a contractor
31 matches
Mail list logo
