--- Tom Lane [EMAIL PROTECTED] wrote:
Brian Herlihy [EMAIL PROTECTED] writes:
My options seem to be
- Fudge the analysis results so that the selectivity estimate changes. I
have tested reducing n_distinct, but this doesn't seem to help.
- Combine the columns into one column,
Hello
I have a sql statement that takes 108489.780 ms with 8.0.7 in a
RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with
2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM.
Some information:
- There is no IO
Rafael Martinez Guerrero wrote:
Hello
I have a sql statement that takes 108489.780 ms with 8.0.7 in a
RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with
2xIntel(R) Xeon(TM) CPU 2.40GHz / 4GB RAM.
Some
On Fri, 2006-04-07 at 15:31, Richard Huxton wrote:
Rafael Martinez Guerrero wrote:
Hello
I have a sql statement that takes 108489.780 ms with 8.0.7 in a
RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux
I have a web server with PostgreSQL and RHEL. It hosts a search
engine, and each time some one makes a query, it uses the HDD Raid
array. The DB is not very big, it is less than a GB. I plan to add
more RAM anyway.
What I'd like to do is find out how to keep the whole DB in RAM so
that each time
Rafael Martinez Guerrero wrote:
Any ideas of what I can test/configurate to find out why this happens?
Thanks in advance.
I haven't looked in detail at the plans, but what stands out to me is
that you've got a sort with a lot of columns and you've halved sort_mem
(work_mem). Try increasing it
On Fri, 2006-04-07 at 16:41 +0200, Gábriel Ákos wrote:
Any ideas of what I can test/configurate to find out why this happens?
Thanks in advance.
Increase work_mem to 50% of memory, and don't care about
maintenance_work_mem and effective_cache_size, they don't matter in this
case.
On 4/7/06, Matt Davies | Postgresql List [EMAIL PROTECTED] wrote:
Out of curiosity, what are you using as the search engine?
Thank you. We designed the search engine ourself (we didn't use a
ready-to-use solution).
--
Charles A. Landemaine.
---(end of
If memory serves me correctly I have seen several posts about this in
the past.
I'll try to recall highlights.
1. Create a md in linux sufficiently large enough to handle the data set
you are wanting to store.
2. Create a HD based copy somewhere as your permanent storage mechanism.
3. Start
Tom Lane wrote:
Brian Herlihy [EMAIL PROTECTED] writes:
Before I go, I have a question - From discussions on the Postgresql irc
channel, and from reading the TODO list on the website, I am under the
impression that there are no plans to allow optimizer hints, such as use index
table_pkey. Is
Richard Huxton dev@archonet.com writes:
Tom - does the planner/executor know it's got row estimates wrong? That
is, if I'm not running an EXPLAIN ANALYSE is there a point at which we
could log planner estimate for X out by factor of Y?
Not at the moment, but you could certainly imagine
Tom Lane wrote:
Richard Huxton dev@archonet.com writes:
Tom - does the planner/executor know it's got row estimates wrong? That
is, if I'm not running an EXPLAIN ANALYSE is there a point at which we
could log planner estimate for X out by factor of Y?
Not at the moment, but you could
Charles A. Landemaine [EMAIL PROTECTED] writes:
What I'd like to do is find out how to keep the whole DB in RAM so
that each time some one does a query, it doesn't use the HDD. Is it
possible, if so, how?
That should happen essentially for free, if the kernel doesn't have any
better use for
Rafael Martinez Guerrero [EMAIL PROTECTED] writes:
I have a sql statement that takes 108489.780 ms with 8.0.7 in a
RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with
2xIntel(R) Xeon(TM) CPU 2.40GHz /
On 4/7/06, Charles A. Landemaine [EMAIL PROTECTED] wrote:
I have a web server with PostgreSQL and RHEL. It hosts a search
engine, and each time some one makes a query, it uses the HDD Raid
array. The DB is not very big, it is less than a GB. I plan to add
more RAM anyway.
What I'd like to do
** This has not been tested.
Create a ramdisk of required size
Create a Linux software RAID mirror between the ramdisk, and a partition
of the same size.
Mark the physical-disk as write-mostly (reads will go to the ramdisk)
Format it and load data...
On reboot
I wrote:
Rafael Martinez Guerrero [EMAIL PROTECTED] writes:
I have a sql statement that takes 108489.780 ms with 8.0.7 in a
RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
8GB RAM and only 4193.588 ms with 7.4.12 in a RHEL3/386linux server with
2xIntel(R) Xeon(TM) CPU
Bing-bong, passenger announcement.. the panic train is now pulling into
platform 8.1.3. Bing-bong. =)
OK, having moved from our quad-xeon to an 8-CPU IBM pSeries 650
(8x1.45GHz POWER4 instead of 4 x 3GHz Xeon), our query times have shot
up and our website is next to unusable. The IBM is not
Gavin Hamill [EMAIL PROTECTED] writes:
OK, having moved from our quad-xeon to an 8-CPU IBM pSeries 650
(8x1.45GHz POWER4 instead of 4 x 3GHz Xeon), our query times have shot
up and our website is next to unusable. The IBM is not swapping (not
with 16GB of RAM!), disk i/o is low, but there must
On Fri, 2006-04-07 at 12:58, Gavin Hamill wrote:
Bing-bong, passenger announcement.. the panic train is now pulling into
platform 8.1.3. Bing-bong. =)
OK, having moved from our quad-xeon to an 8-CPU IBM pSeries 650
(8x1.45GHz POWER4 instead of 4 x 3GHz Xeon), our query times have shot
up
On Fri, 2006-04-07 at 13:36 -0400, Tom Lane wrote:
I wrote:
Rafael Martinez Guerrero [EMAIL PROTECTED] writes:
I have a sql statement that takes 108489.780 ms with 8.0.7 in a
RHEL4/amd64linux server with 2xAMD Opteron(tm) Processor 275 2.00GHz /
8GB RAM and only 4193.588 ms with 7.4.12 in
Ok, so I did a few runs for each of the sync methods, keeping all the
rest constant and got this:
open_datasync 0.7
fdatasync 4.6
fsync 4.5
fsync_writethrough not supported
open_sync 0.6
in arbitrary units - higher is faster.
Quite impressive!
On Fri, 07 Apr 2006 14:41:39 -0400
Tom Lane [EMAIL PROTECTED] wrote:
Gavin Hamill [EMAIL PROTECTED] writes:
OK, having moved from our quad-xeon to an 8-CPU IBM pSeries 650
(8x1.45GHz POWER4 instead of 4 x 3GHz Xeon), our query times have shot
up and our website is next to unusable. The IBM
On Fri, 07 Apr 2006 13:54:21 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
Are the same queries getting the same basic execution plan on both
boxes? Turn on logging for slow queries, and explain analyze them on
both machines to see if they are.
See reply to Tom Lane :)
I'd put the old 4 way
Gavin Hamill [EMAIL PROTECTED] writes:
Scott Marlowe [EMAIL PROTECTED] wrote:
My guess is that this is an OS issue. Maybe there are AIX tweaks that
will get it up to the same or higher level of performance as your four
way xeon. Maybe there aren't.
The pSeries isn't much older than our
On Fri, 7 Apr 2006 20:59:19 +0100
Gavin Hamill [EMAIL PROTECTED] wrote:
I'd put the old 4 way Xeon back in production and do some serious
testing of this pSeries machine. IBM should be willing to help you, I
hope.
They probably would if this had been bought new - as it is, we have
On Fri, 2006-04-07 at 14:59, Gavin Hamill wrote:
On Fri, 07 Apr 2006 13:54:21 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
Are the same queries getting the same basic execution plan on both
boxes? Turn on logging for slow queries, and explain analyze them on
both machines to see if they
Gavin Hamill [EMAIL PROTECTED] writes:
This is one hell of a moving target and I can't help but think I'm just
missing something that's right in front of my nose, too close to see.
I'm assuming you compiled postgres yourself? Do you have the output from the
configure script? I'm wondering if
Gavin Hamill wrote:
Bing-bong, passenger announcement.. the panic train is now pulling into
platform 8.1.3. Bing-bong. =)
OK, having moved from our quad-xeon to an 8-CPU IBM pSeries 650
(8x1.45GHz POWER4 instead of 4 x 3GHz Xeon), our query times have shot
up and our website is next to
On Fri, 7 Apr 2006 16:16:02 -0400
D'Arcy J.M. Cain darcy@druid.net wrote:
We also had problems with a high end AIX system and we got no help
from IBM. They expected you to put Oracle on and if you used
anything else you were on your own.
Urk, I thought IBM were supposedly Linux sycophants
On Fri, 07 Apr 2006 15:24:18 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
See reply to Tom Lane :)
I didn't see one go by yet... Could be sitting in the queue.
If it's not arrived by now - EXPLAIN ANALYZE doesn't tell me
anything :)
Let us know if changing the fsync setting helps.
On Fri, 07 Apr 2006 16:06:02 -0400
Tom Lane [EMAIL PROTECTED] wrote:
The pSeries isn't much older than our Xeon machine, and I expected
the performance level to be exemplary out of the box..
I'm fairly surprised too. One thing I note from your comparison of
settings is that the default
Title: Re: [PERFORM] pg 8.1.3, AIX, huge box, painfully slow.
Gavin,
On 4/7/06 2:24 PM, Gavin Hamill [EMAIL PROTECTED] wrote:
I did look into the specs of the system, and the memory bw on the
pSeries was /much/ greater than the Xeon - it's one of the things that
really pushed me towards it
Gavin Hamill [EMAIL PROTECTED] writes:
There's truss installed which seems to do the same as strace on
Linux... and here's a wildly non-scientific glance.. I watched the
'topas' output (top for AIX) , identified a PID that was doing a lot of
work, then attached truss to that pid. In addition
Luke Lonergan [EMAIL PROTECTED] writes:
That said, I find typical memory bandwidth for the P4 in applications is
limited at about 2GB/s. See here for more detail:
http://www.cs.virginia.edu/stream/standard/Bandwidth.html
In fact, looking at the results there, the IBM 650m2 only gets 6GB/s
Tom,
On 4/7/06 3:02 PM, Tom Lane [EMAIL PROTECTED] wrote:
On the other hand, we already know that Xeons suck about as badly as
can be on that same measure; could the pSeries really be worse?
I wouldn't be too surprised, but it sounds like it needs a test. Do we have
a test for this? Is
Luke Lonergan [EMAIL PROTECTED] writes:
On 4/7/06 3:02 PM, Tom Lane [EMAIL PROTECTED] wrote:
On the other hand, we already know that Xeons suck about as badly as
can be on that same measure; could the pSeries really be worse?
I wouldn't be too surprised, but it sounds like it needs a test.
On Fri, 07 Apr 2006 17:56:49 -0400
Tom Lane [EMAIL PROTECTED] wrote:
This is not good. Did the semop storms coincide with visible
slowdown? (I'd assume so, but you didn't actually say...)
If I'd been able to tell, then I'd tell you =) I'll have another go...
Yes, there's a definate
Gavin Hamill [EMAIL PROTECTED] writes:
On Fri, 07 Apr 2006 17:56:49 -0400
Tom Lane [EMAIL PROTECTED] wrote:
This is not good. Did the semop storms coincide with visible
slowdown? (I'd assume so, but you didn't actually say...)
Yes, there's a definate correlation here.. I attached truss to
Gavin,
On 4/7/06 3:27 PM, Gavin Hamill [EMAIL PROTECTED] wrote:
278774: __semop(15728650, 0x0FFF7E80, 1)= 0
155712: __semop(15728650, 0x0FFF5920, 1)= 0
278774: __semop(15728649, 0x0FFF6F10, 1)
114914: __semop(15728649, 0x0FFF6A40, 1)= 0
Luke Lonergan [EMAIL PROTECTED] writes:
On 4/7/06 3:27 PM, Gavin Hamill [EMAIL PROTECTED] wrote:
278774: __semop(15728650, 0x0FFF7E80, 1)= 0
155712: __semop(15728650, 0x0FFF5920, 1)= 0
278774: __semop(15728649, 0x0FFF6F10, 1)
Seems like you're hitting
On Fri, 07 Apr 2006 18:52:20 -0400
Tom Lane [EMAIL PROTECTED] wrote:
Are you in a position to try your workload using PG CVS tip? There's
a nontrivial possibility that we've already fixed this --- a couple
months ago I did some work to reduce contention in the lock manager:
Well, there's a
On Fri, 07 Apr 2006 15:56:52 -0700
Luke Lonergan [EMAIL PROTECTED] wrote:
Seems like you're hitting a very small target in RAM with these semop
calls. I wonder what part of the code is doing this - Tom would know
better how to trace it, but the equivalent of oprofile output would
be nice.
43 matches
Mail list logo
