We do something similar here. We use Netapp and I carve one aggregate
per data volume. I generally keep the pg_xlog on the same data LUN,
but I don't mix other databases on the same aggregate.
In the NetApp world because they use RAID DP (dual parity) you have a
higher wastage of drives,
Assuming we have 24 73G drives is it better to make one big metalun
and carve it up and let the SAN manage the where everything is, or is
it better to specify which spindles are where.
Currently we would require 3 separate disk arrays.
one for the main database, second one for WAL logs,
Dave Cramer [EMAIL PROTECTED] writes:
Assuming we have 24 73G drives is it better to make one big metalun and carve
it up and let the SAN manage the where everything is, or is it better to
specify which spindles are where.
This is quite a controversial question with proponents of both
On Tue, Jul 10, 2007 at 08:17:05PM +0530, Gauri Kanekar wrote:
Is there anyway so as to indicate the Query Analyser not to use the
plan which it is using regularly, and use a new plan ?
You can't dictate the query plan but you can influence the planner's
decisions with various configuration
On 11-Jul-07, at 10:05 AM, Gregory Stark wrote:
Dave Cramer [EMAIL PROTECTED] writes:
Assuming we have 24 73G drives is it better to make one big
metalun and carve
it up and let the SAN manage the where everything is, or is it
better to
specify which spindles are where.
This is quite
Setting spec for a postgresql server.
The hard drive distribution is going to be
8 x 750GB Seagate on a 3ware 9650SE RAID 6
2 x 160GB Seagate on a 3ware 2 port
The question is, would I be better off putting WAL on the second, OS,
controller or in the 8 port controller? Specially since the 2
In my sporadic benchmark testing, the only consistent 'trick' I found
was that the best thing I could do for performance sequential
performance was allocating a bunch of mirrored pair LUNs and stripe
them with software raid. This made a huge difference (~2X) in sequential
performance, and a
How can I get the time it takes a query to execute - explain analyze is
taking over 5 hours to complete...can I use \timing??? I don't get any time
when using the \timing option...
Thanks...Marsha
--
View this message in context:
On Wed, Jul 11, 2007 at 09:03:27AM -0400, Dave Cramer wrote:
Problem with dedicating the spindles to each array is that we end up
wasting space. Are the SAN's smart enough to do a better job if I
create one large metalun and cut it up ?
In my experience, this largely depends on your SAN
Hi,
I've two questions for which I not really found answers in the web.
Intro:
I've a Website with some traffic.
2 Million queries a day, during daylight.
Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
Mainly updates on 1 tuple. And more or less complex SELECT
On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote:
How can I get the time it takes a query to execute - explain analyze is
taking over 5 hours to complete
You can't get it any faster than what explain analyse does: it runs
the query. How else would you get the answer?
...can I use
Its hard to do direct comparison since that one used a different
commercial application server than the PostgreSQL test.. As we do more
tests with the help of the community, hopefully we can understand where
the CPU cycles are spent and see how to make them more efficient...
Stay tuned!!
We have a few tables that we need to pull relatively accurate aggregate
counts from, and we found the performance of SELECT COUNT(*) to be
unacceptable. We solved this by creating triggers on insert and delete to
update counts in a secondary table which we join to when we need the count
Hi,
I'm having a weird problem on a query :
I've simplified it to get the significant part (see end of message).
The point is I've got a simple
SELECT field FROM table WHERE 'condition1'
Estimated returned rows : 5453
Then
SELECT field FROM table WHERE 'condition2'
Estimated returned rows : 705
Jignesh K. Shah wrote:
Hello all,
I think this result will be useful for performance discussions of
postgresql against other databases.
http://www.spec.org/jAppServer2004/results/res2007q3/
More on Josh Berkus's blog:
On Wednesday 11 July 2007 08:36, Andrew Sullivan [EMAIL PROTECTED]
wrote:
Put the WAL where the battery is. Even if it's slower (and I don't
know whether it will be), I assume that having the right data more
slowly is better than maybe not having the data at all, quickly.
Presumably he'll
Andrew Sullivan [EMAIL PROTECTED] writes:
On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote:
How can I get the time it takes a query to execute - explain analyze is
taking over 5 hours to complete
You can't get it any faster than what explain analyse does: it runs
the query. How
Andrew Sullivan [EMAIL PROTECTED] writes:
On Wed, Jul 11, 2007 at 08:21:40AM -0700, smiley2211 wrote:
How can I get the time it takes a query to execute - explain analyze is
taking over 5 hours to complete
You can't get it any faster than what explain analyse does: it runs
the query. How
Heikki Linnakangas wrote:
May I ask you why you set max_prepared_transactions to 450, while
you're apparently not using prepared transactions, according to this
quote:
Recoverable 2-phase transactions were used to coordinate the
interaction between
the database server and JMS server
Patric de Waha [EMAIL PROTECTED] writes:
Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
When you don't even mention your disk hardware, that's a bad sign.
In a database server the disk is usually more important than the CPU.
Why do long readers influence the rest of
On Wed, Jul 11, 2007 at 05:35:33PM +0200, Patric de Waha wrote:
Mainly updates on 1 tuple.
Are you vacuuming that table enough?
And more or less complex SELECT statements.
I noticed that the overall performance of postgres is decreasing
when one or more long
readers are present.
I don't this so, because DB2 is running on a Sun Sparc T1 processor
(http://www.sun.com/processors/UltraSPARC-T1/) that's implements much
more features, like thread level parelelism, than AMD Opteron.
the DB2 installation:
J2EE AppServer HW (SUT hardware)
Hardware Vendor: Sun
On Wed, Jul 11, 2007 at 12:10:55PM -0400, Tom Lane wrote:
Well, on some platforms (ie consumer-grade PCs) explain analyze can be a
lot slower than just running the query,
Yes, I suppose I exaggerated when I said can't get any faster, but
given that the OP was talking on the order of hours for
Jignesh K. Shah [EMAIL PROTECTED] writes:
Heikki Linnakangas wrote:
May I ask you why you set max_prepared_transactions to 450, while
you're apparently not using prepared transactions, according to this
quote:
Yep.. one of the things that we didn't revert back and got strayed out
there.
[EMAIL PROTECTED] (Dave Cramer) writes:
On 11-Jul-07, at 10:05 AM, Gregory Stark wrote:
Dave Cramer [EMAIL PROTECTED] writes:
Assuming we have 24 73G drives is it better to make one big
metalun and carve
it up and let the SAN manage the where everything is, or is it
better to
specify
On Jul 11, 2007, at 12:39 PM, Chris Browne wrote:
- Split off a set (6?) for WAL
In my limited testing, 6 drives for WAL would be complete overkill in
almost any case. The only example I've ever seen where WAL was able
to swamp 2 drives was the DBT testing that Mark Wong was doing at
On Wed, Jul 11, 2007 at 01:39:39PM -0400, Chris Browne wrote:
load causes. A fallout of this is that those disks are likely to be
worked harder than the disk used for storing plain old data, with
the result that if you devote disk to WAL, you'll likely burn thru
replacement drives faster
Hi,
Okay, i know, not really a recent version:
PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian
1:3.3.5-13)
I have a fresh ANALYZED table with some indexes.
scholl=*# set enable_bitmapscan=1;
SET
scholl=*# explain analyse select sum(flaeche) from bde_meldungen
Can you list others that seemed out of place?
Thanks.
Regards,
Jignesh
Tom Lane wrote:
Jignesh K. Shah [EMAIL PROTECTED] writes:
Heikki Linnakangas wrote:
May I ask you why you set max_prepared_transactions to 450, while
you're apparently not using prepared transactions, according
On Wed, 11 Jul 2007, Jim Nasby wrote:
I suppose an entirely in-memory database might be able to swamp a 2
drive WAL as well.
You can really generate a whole lot of WAL volume on an EMC SAN if you're
doing UPDATEs fast enough on data that is mostly in-memory. Takes a
fairly specific type of
On 11-Jul-07, at 2:35 PM, Greg Smith wrote:
On Wed, 11 Jul 2007, Jim Nasby wrote:
I suppose an entirely in-memory database might be able to swamp a
2 drive WAL as well.
You can really generate a whole lot of WAL volume on an EMC SAN if
you're doing UPDATEs fast enough on data that is
On 7/11/07, Andreas Kretschmer [EMAIL PROTECTED] wrote:
Hi,
Okay, i know, not really a recent version:
PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian
1:3.3.5-13)
I have a fresh ANALYZED table with some indexes.
scholl=*# set enable_bitmapscan=1;
SET
scholl=*#
On Wed, 11 Jul 2007, Alan Hodgson wrote:
Presumably he'll have the 2-port configured for write-through operation.
This is the real key to his question. In order to get acceptable
performance for the operating system, Francisco may very well need the OS
disks to be configured in write-back
am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes:
Okay, i got a really different plan, but i expected _NOT_ a
performance-boost like this. I expected the opposite.
It's not a really problem, i just played with this. But i'm confused
about this...
your results
Alan Hodgson writes:
I would spring for a 4-port with a BBU, though, and then put the WAL on the
drives with the OS.
The machine is already over budget. :-(
I will check the price difference but unlikely I will get approval.
---(end of
Francisco Reyes wrote:
Alan Hodgson writes:
I would spring for a 4-port with a BBU, though, and then put the WAL
on the drives with the OS.
The machine is already over budget. :-(
I will check the price difference but unlikely I will get approval.
Without a BBU you are guaranteed at some
On 7/11/07, A. Kretschmer [EMAIL PROTECTED] wrote:
am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes:
Okay, i got a really different plan, but i expected _NOT_ a
performance-boost like this. I expected the opposite.
It's not a really problem, i just played with
Andreas Kretschmer [EMAIL PROTECTED] writes:
Okay, i know, not really a recent version:
PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian
1:3.3.5-13)
You need a newer one.
- BitmapAnd (cost=1217.69..1217.69 rows=39 width=0) (actual
Tom Lane [EMAIL PROTECTED] schrieb:
Thanks you and Alex for the response.
PostgreSQL 8.1.4 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5
(Debian 1:3.3.5-13)
You need a newer one.
I know ;-)
This is simply a stupid choice on the part of choose_bitmap_and() ---
it's adding on
On 7/11/07, Alex Deucher [EMAIL PROTECTED] wrote:
On 7/11/07, A. Kretschmer [EMAIL PROTECTED] wrote:
am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes:
Okay, i got a really different plan, but i expected _NOT_ a
performance-boost like this. I expected the opposite.
Joshua D. Drake writes:
Without a BBU you are guaranteed at some point to have catastrophic
failure unless you turn off write cache, which would then destroy your
performance.
I am re-working the specs of the machine to try and get a 4port 3ware to
have the battery backup.
Marc Cousin [EMAIL PROTECTED] writes:
Nevertheless, shouldn't the third estimate be smaller or equal to the sum of
the two others ?
The planner's estimation for subplan conditions is pretty primitive
compared to joinable conditions. When you add the OR, it's no longer
possible to treat the IN
Ok thanks.
iostat confirmed it's an IO bottleneck.
Will add some discs to the RAID unit.
Used 4 Raptor discs in Raid 10 until now.
best regards,
patric
Tom Lane wrote:
Patric de Waha [EMAIL PROTECTED] writes:
Postgres is running on a dedicated server P4 DualCore, 4 Gig Ram.
Recently, I have been doing extensive profiling of a version 8.1.4 Postgres DB with about 175
tables and 5 GB of data (the server running on Fedora Linux and the clients on Windows XP).
Surprisingly, one of the bottlenecks is TRUNCATE TABLE and that command is really slow as compared
to other
Adriaan van Os [EMAIL PROTECTED] writes:
Surprisingly, one of the bottlenecks is TRUNCATE TABLE and that
command is really slow as compared to other operations.
When you don't quantify that statement at all, it's hard to make an
intelligent comment on it, but TRUNCATE per se shouldn't be slow.
Adriaan van Os [EMAIL PROTECTED] writes:
Recently, I have been doing extensive profiling of a version 8.1.4 Postgres DB
with about 175 tables and 5 GB of data (the server running on Fedora Linux and
the clients on Windows XP). Surprisingly, one of the bottlenecks is TRUNCATE
TABLE and that
On Wed, 11 Jul 2007, Francisco Reyes wrote:
I am re-working the specs of the machine to try and get a 4port 3ware to have
the battery backup.
That's really not necessary, it just would be better (and obviously more
expensive). The warnings you've been getting here have been to let you
know
Greg Smith writes:
During peak operation there will be about 5 to 20 updates per second
with a handfull of reads.
There really is no reason you need to be concerned about WAL from a
performance perspective if this is your expected workload.
I was able to get the second controller with
All this talk of WAL writing lately has me wondering something I haven't
spent enough time looking at the source to figure out myself this
week...any good rules of thumb out there for estimating WAL volume? I'm
used to just measuring it via benchmarking but it strikes me a formula
would be
am Wed, dem 11.07.2007, um 22:19:58 +0200 mailte Andreas Kretschmer folgendes:
Also, part of the problem here looks to be an overestimate of the number
of rows matching ab = 347735. It might help to increase the statistics
target for that column.
I will try this tomorrow and inform you
50 matches
Mail list logo
