Greg Stark wrote:
However that's not enough to explain what you've shown. How about you show the
actual query and actual plan you're working with? The plan you've shown can't
result from the query you sent.
Mea culpa, sort of. But ... in fact, the plan I sent *was* from query I sent, with the
On Mon, Jun 12, 2006 at 10:44:01PM -0400, Tom Lane wrote:
(Personally, if I'd designed it, the libraries would actually live in
/usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
you needed it to be at the moment. Likewise for /usr/bin.)
Actually, there have been plans
Jim C. Nasby wrote:
On Mon, Jun 12, 2006 at 09:05:06AM -0600, Michael Fuhr wrote:
On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
I have two similar servers, one in production and another for testing
purposes.
Databases are equal (with a difference of some hours)
In
Tonight database has been vacumm full and reindex (all nights database
do it)
Now its working fine. Speed is as spected. I ll be watching that sql ...
Maybe the problem exists when database is busy, or maybe its solved ...
---(end of
On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:
Empirically... postgresql built for 64 bits is marginally slower
than that built
for a 32 bit api on sparc. None of my customers have found 64 bit x86
systems to be suitable for production use, yet, so I've not tested
on any
of those
On 13.06.2006, at 8:44 Uhr, Ruben Rubio Rey wrote:
Tonight database has been vacumm full and reindex (all nights
database do it)
Now its working fine. Speed is as spected. I ll be watching that
sql ...
Maybe the problem exists when database is busy, or maybe its
solved ...
Depending on
Guido Neitzer wrote:
On 13.06.2006, at 8:44 Uhr, Ruben Rubio Rey wrote:
Tonight database has been vacumm full and reindex (all nights
database do it)
Now its working fine. Speed is as spected. I ll be watching that sql
...
Maybe the problem exists when database is busy, or maybe its
J. Andrew Rogers wrote:
We have been using PostgreSQL on Opteron servers almost since the
Opteron was first released, running both 32-bit and 64-bit versions of
Linux. Both 32-bit and 64-bit versions have been bulletproof for us,
with the usual stability I've become accustomed to with both
Installation of a 32-bit PostgreSQL on a 64-bit Linux like RHEL 4 is
very easy. Make sure that you have installed all needed 32-bit libs and
devel packages.
Here is an example to call configure to get a 32-bit PostgreSQL:
CXX=/usr/bin/g++ -m32 \
CPP=/usr/bin/gcc -m32 -E \
LD=/usr/bin/ld -m
Seems autovacumm is working fine. Logs are reporting that is being useful.
But server load is high. Is out there any way to stop autovacumm if
server load is very high?
Thanks everyone!!!
---(end of broadcast)---
TIP 3: Have you checked our
On Tue, 13 Jun 2006 04:26:05 +0200, Alex Turner [EMAIL PROTECTED] wrote:
Anyone who has tried x86-64 linux knows what a royal pain in the ass it
is. They didn't do anything sensible, like just make the whole OS 64
bit,
no, they had to split it up, and put 64-bit libs in a new directory
Sven,
On 6/13/06 2:04 AM, Sven Geisler [EMAIL PROTECTED] wrote:
Please find attached a small patch with does apply a change to the
x86_64 part also to the i386 part of src/include/storage/s_lock.h.
Without this change the performance of PostgreSQL 8.0 was horrible on a
Opteron. The effect is
On 13.06.2006, at 12:33 Uhr, Ruben Rubio Rey wrote:
Seems autovacumm is working fine. Logs are reporting that is being
useful.
But server load is high. Is out there any way to stop autovacumm
if server load is very high?
Look at the cost settings for vacuum and autovacuum. From the
Usually we get complaints the other way around (that the NOT EXISTS
approach is a lot slower).
Yes, I know ;)
(I rephrased the query this way to exploit the fact that the planner
would choose a nested loop)
You did not show any statistics, but I
suspect the key point here is
Luke
Luke Lonergan schrieb:
On 6/13/06 2:04 AM, Sven Geisler [EMAIL PROTECTED] wrote:
Please find attached a small patch with does apply a change to the
x86_64 part also to the i386 part of src/include/storage/s_lock.h.
Without this change the performance of PostgreSQL 8.0 was horrible on a
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Nis Jorgensen)
wrote:
J. Andrew Rogers wrote:
We have been using PostgreSQL on Opteron servers almost since the
Opteron was first released, running both 32-bit and 64-bit versions of
Linux. Both 32-bit and 64-bit versions have
Sven,
On 6/13/06 5:03 AM, Sven Geisler [EMAIL PROTECTED] wrote:
Yes, I know. We had a problem last year with the performance of the
Opteron. We have started the futex patch to resolve the issue. The futex
patch itself did have no effect, but there was a side effect because the
futex patch
Hi,
I don't have a copy of the standard on hand and a collegue is claiming
that there must be a from clause in a select query (he is an oracle
guy). This doesn't seem to be the case for postgres... does anyone
know?
Cheers
Antoine
ps. any one of them will do...
--
This is where I should put some
Luke
Luke Lonergan schrieb:
Sven,
On 6/13/06 5:03 AM, Sven Geisler [EMAIL PROTECTED] wrote:
Yes, I know. We had a problem last year with the performance of the
Opteron. We have started the futex patch to resolve the issue. The futex
patch itself did have no effect, but there was a side effect
Sven,
On 6/13/06 5:46 AM, Sven Geisler [EMAIL PROTECTED] wrote:
You already get this change if you compile PostgreSQL 8.1.x in x86_64
(64-bit mode).
I see, so I think your point with the patch is to make the 32-bit compiled
version benefit as well.
- Luke
---(end
Luke,
Luke Lonergan schrieb:
On 6/13/06 5:46 AM, Sven Geisler [EMAIL PROTECTED] wrote:
You already get this change if you compile PostgreSQL 8.1.x in x86_64
(64-bit mode).
I see, so I think your point with the patch is to make the 32-bit compiled
version benefit as well.
Yup. I think you
Josh Berkus wrote:
Folks,
In which case, why was 64-bit such a big deal?
We had this discussion with 16/32 bit too, back in those 286/386 times...
Not too many 16bit apps left now :-)
Regards,
Andreas
---(end of broadcast)---
TIP 9: In
On Jun 13, 2006, at 1:40 AM, Nis Jorgensen wrote:
Since you sound like you have done extensive testing:
Do you have any data regarding whether to enable hyperthreading or
not?
I realize that this may be highly dependant on the OS, application and
number of CPUs, but I would be interested in
Craig A. James [EMAIL PROTECTED] writes:
But the weird thing is that, in spite of flattening, which would appear to
make the queries equivalent, the function gets called in one case, and not in
the other.
No, nothing particularly weird about it. ORDER BY in a subselect
acts as an
* Steinar H. Gunderson ([EMAIL PROTECTED]) wrote:
On Mon, Jun 12, 2006 at 10:44:01PM -0400, Tom Lane wrote:
(Personally, if I'd designed it, the libraries would actually live in
/usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
you needed it to be at the moment.
Just so I don't think I'm insane:
warehouse=# explain analyzeselect e.event_date::date
warehouse-# from l_event_log e
warehouse-# JOIN c_event_type t ON (t.id = e.event_type_id)
warehouse-# WHERE e.event_date now() - interval '2 days'
warehouse-# AND t.event_name =
I have a client who is running Postgresql 7.4.x series database
(required to use 7.4.x). They are planning an upgrade to a new server.
They are insistent on Dell.
I have personal experience with AMD dual Opteron, but I have not seen
any benchmarks on Intel's dual core Xeon. I've seen in the past
Shaun Thomas [EMAIL PROTECTED] writes:
Am I correct in assuming this terrible plan is due to our ancient
version of Postgres?
I missed the part where you explain why you think this plan is terrible?
412ms for what seems a rather expensive query doesn't sound so awful.
Do you know an alternative
warehouse-# WHERE e.event_date now() - interval '2 days'
Try explicitly querying:
WHERE e.event_date '2006-06-11 20:15:00'
In my understanding 7.4 does not precalculate this timestamp value for the
purpose of choosing a plan.
Greetings
Marcin
---(end of
On Tue, 2006-06-13 at 13:02, Steve Poe wrote:
I have a client who is running Postgresql 7.4.x series database
(required to use 7.4.x). They are planning an upgrade to a new server.
They are insistent on Dell.
Do they have a logical reason for this, or is it mostly hand-waving? My
experience
[EMAIL PROTECTED] (Steve Poe) writes:
I have a client who is running Postgresql 7.4.x series database
(required to use 7.4.x). They are planning an upgrade to a new server.
They are insistent on Dell.
Then they're being insistent on poor performance.
If you search for dell postgresql
My suggestion is to look at something like this:
http://www.abmx.com/1u-supermicro-amd-opteron-rackmount-server-p-210.html
1U rackmount opteron from Supermicro that can have two dual core
opterons and 4 drives and up to 16 gigs of ram. Supermicro server
motherboards have always treated me
David Boreham wrote:
My suggestion is to look at something like this:
http://www.abmx.com/1u-supermicro-amd-opteron-rackmount-server-p-210.html
1U rackmount opteron from Supermicro that can have two dual core
opterons and 4 drives and up to 16 gigs of ram. Supermicro server
On Mon, Jun 12, 2006 at 11:47:07PM +0200, Zydoon wrote:
Thank you for sharing this.
Coming back to my problem :) A very faithful partner accepted to
gracefully borrow us 3 Pseries (bi-ppc + 2G RAM not more). with linux on
them.
Now I'm trying to make my tests, and I'm not that sure I will
On Mon, 2006-06-12 at 16:47, Zydoon wrote:
-BEGIN PGP SIGNED MESSAGE-
Thank you for sharing this.
Coming back to my problem :) A very faithful partner accepted to
gracefully borrow us 3 Pseries (bi-ppc + 2G RAM not more). with linux on
them.
Now I'm trying to make my tests, and I'm
On Tue, 13 Jun 2006 14:28:49 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
Search the performance archives for the last 4 or 5 months for PPC /
pseries machines.
You'll find a very long thread about the disappointing performance the
tester got with a rather expensive P Series machine. And
On Mon, Jun 12, 2006 at 05:19:46PM -0600, David Boreham wrote:
What they are saying is strictly true : 64-bit pointers tend to increase
the working set size
of an application vs. 32-bit pointers. This means that any caches will
have somewhat lower
hit ratio. Also the bytes/s between the CPU
Scott Marlowe wrote:
On Tue, 2006-06-13 at 13:02, Steve Poe wrote:
I have a client who is running Postgresql 7.4.x series database
(required to use 7.4.x). They are planning an upgrade to a new server.
They are insistent on Dell.
Do they have a logical reason for this, or is it mostly
On Mon, Jun 12, 2006 at 08:04:41PM -0400, Stephen Frost wrote:
don't think). This is because Postgres lets the OS handle most of the
cacheing, so as long as your OS can see all the memory you have in the
Actually, in 8.1.x I've seen some big wins from greatly increasing the
amount of
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Joshua D. Drake
Sent: 13 June 2006 20:44
To: Scott Marlowe
Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Which processor runs better for Postgresql?
They
Uhm... stick with commodity CPUs?
Hehe, does this include Opterons ?
Still, I looked on the customize your server link someone posted and
it's amazing ; these things have become cheaper while I wasn't looking...
You can buy 10 of these boxes with raptors and 4 opteron cores and 8
On 6/13/2006 at 1:09 PM, Tom Lane [EMAIL PROTECTED] wrote:
I missed the part where you explain why you think this plan is
terrible?
412ms for what seems a rather expensive query doesn't sound so
awful.
Sorry, I based that statement on the estimated/actual disparity. That
particular query
On Tue, Jun 13, 2006 at 02:43:45PM +0200, Antoine wrote:
Hi,
I don't have a copy of the standard on hand and a collegue is claiming
that there must be a from clause in a select query (he is an oracle
guy). This doesn't seem to be the case for postgres... does anyone
know?
Dunno, but I know
Shaun Thomas [EMAIL PROTECTED] writes:
Simply changing:
now() - interval '2 days'
to
'2006-06-11 15:30:00'
generated a much more accurate set of estimates.
Yeah, 7.4 won't risk basing estimates on the results of non-immutable
functions. We relaxed that in 8.0 I believe.
On Tue, Jun 13, 2006 at 12:44:17PM -0700, Joshua D. Drake wrote:
You can get extremely competitive quotes from IBM or HP as long as you
say, You are competing against Dell.
Possibly even more competitive from Sun...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive
On Tue, Jun 13, 2006 at 10:14:44PM +0200, PFC wrote:
Uhm... stick with commodity CPUs?
Hehe, does this include Opterons ?
Absolutely. Heck, it wouldn't surprise me if a single model # of Opteron
sold more than all Power CPUs put together...
Still, I looked on the customize
On Tue, Jun 13, 2006 at 03:54:44PM -0500, Shaun Thomas wrote:
On 6/13/2006 at 1:09 PM, Tom Lane [EMAIL PROTECTED] wrote:
I missed the part where you explain why you think this plan is
terrible?
412ms for what seems a rather expensive query doesn't sound so
awful.
Sorry, I based that
On 6/13/2006 at 4:13 PM, Jim C. Nasby [EMAIL PROTECTED]
wrote:
Is there some compelling reason to stick with 7.4? In my experience
you'll see around double (+100%) the performance going to 8.1...
Not really. We *really* want to upgrade, but we're in the middle of
buying the new machine
Maybe from a postgresql perspective the cpus may be useless but the memory on the pSeries can't be beat. We've been looking at running our warehouse (PGSQL) in a LoP lpar but I wasn't able to find a LoP build of 8.1.
We've been thrilled with the performance of our DB2 systems that run on
On Tue, Jun 13, 2006 at 04:35:41PM -0500, Shaun Thomas wrote:
On 6/13/2006 at 4:13 PM, Jim C. Nasby [EMAIL PROTECTED]
wrote:
Is there some compelling reason to stick with 7.4? In my experience
you'll see around double (+100%) the performance going to 8.1...
Not really. We *really*
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Gavin Hamill wrote:
On Tue, 13 Jun 2006 14:28:49 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
Search the performance archives for the last 4 or 5 months for PPC /
pseries machines.
You'll find a very long thread about the disappointing
Jim C. Nasby [EMAIL PROTECTED] writes:
Also, I'm not sure that the behavior is entirely changed, either. On a
8.1.4 database I'm still seeing a difference between now() - interval
and a hard-coded date.
It'd depend on the context, possibly, but it's easy to show that the
current planner does
On Tue, Jun 13, 2006 at 05:40:58PM -0400, John Vincent wrote:
Maybe from a postgresql perspective the cpus may be useless but the memory
on the pSeries can't be beat. We've been looking at running our warehouse
(PGSQL) in a LoP lpar but I wasn't able to find a LoP build of 8.1.
Probably just
Jim C. Nasby wrote:
...
Actually, in 8.1.x I've seen some big wins from greatly increasing the
amount of shared_buffers, even as high as 50% of memory, thanks to the
changes made to the buffer management code. ...
Anyone else run into a gotcha that one of our customers ran into?
PG 7.4.8
Mischa Sandberg [EMAIL PROTECTED] writes:
vmstat showed that it was swapping like crazy.
Dropped shared_buffers back down again.
Swapping stopped.
Does Solaris have any call that allows locking a shmem segment in RAM?
regards, tom lane
---(end
On Tue, Jun 13, 2006 at 06:22:07PM -0400, Tom Lane wrote:
Mischa Sandberg [EMAIL PROTECTED] writes:
vmstat showed that it was swapping like crazy.
Dropped shared_buffers back down again.
Swapping stopped.
Does Solaris have any call that allows locking a shmem segment in RAM?
The
On Tue, Jun 13, 2006 at 06:04:42PM -0400, Tom Lane wrote:
Jim C. Nasby [EMAIL PROTECTED] writes:
Also, I'm not sure that the behavior is entirely changed, either. On a
8.1.4 database I'm still seeing a difference between now() - interval
and a hard-coded date.
It'd depend on the context,
On 6/13/2006 at 4:54 PM, Jim C. Nasby [EMAIL PROTECTED]
wrote:
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid='table_name'::regclass AND attnum = 0;
-1 for all values.
SHOW default_statistics_target;
10.
Here's something slightly annoying: I tried precalculating
On Tue, Jun 13, 2006 at 06:21:21PM -0400, John Vincent wrote:
On 6/13/06, Jim C. Nasby [EMAIL PROTECTED] wrote:
On Tue, Jun 13, 2006 at 05:40:58PM -0400, John Vincent wrote:
Maybe from a postgresql perspective the cpus may be useless but the
memory
on the pSeries can't be beat. We've been
On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
Jim C. Nasby wrote:
...
Actually, in 8.1.x I've seen some big wins from greatly increasing the
amount of shared_buffers, even as high as 50% of memory, thanks to the
changes made to the buffer management code. ...
Anyone else
On Tue, Jun 13, 2006 at 05:41:06PM -0500, Shaun Thomas wrote:
On 6/13/2006 at 4:54 PM, Jim C. Nasby [EMAIL PROTECTED]
wrote:
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid='table_name'::regclass AND attnum = 0;
-1 for all values.
SHOW
Initially shared_buffers were set to 1000 (8MB).
Then, we moved all apps but the database server off the box.
Raised shared_buffers to 2000 (16MB).
Modest improvement in some frequent repeated queries.
Raised shared_buffers to 16000 (128MB).
DB server dropped to a CRAWL.
Versions below 8.1
Tom Lane wrote:
Mischa Sandberg [EMAIL PROTECTED] writes:
vmstat showed that it was swapping like crazy.
Dropped shared_buffers back down again.
Swapping stopped.
Does Solaris have any call that allows locking a shmem segment in RAM?
Yes, mlock(). But want to understand what's going on
Jim C. Nasby wrote:
On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
Raised shared_buffers to 16000 (128MB).
DB server dropped to a CRAWL.
vmstat showed that it was swapping like crazy.
Dropped shared_buffers back down again.
Swapping stopped.
What's sort_mem set to? I
On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
Jim C. Nasby wrote:
On Tue, Jun 13, 2006 at 03:21:34PM -0700, Mischa Sandberg wrote:
Raised shared_buffers to 16000 (128MB).
DB server dropped to a CRAWL.
vmstat showed that it was swapping like crazy.
Dropped shared_buffers
Jim C. Nasby wrote:
On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
Jim C. Nasby wrote:
What's sort_mem set to? I suspect you simply ran the machine out of
memory.
8192 (8MB). No issue when shared_buffers was 2000; same apps always.
So if all 50 backends were running a
Mischa Sandberg wrote:
Jim C. Nasby wrote:
...
Actually, in 8.1.x I've seen some big wins from greatly increasing the
amount of shared_buffers, even as high as 50% of memory, thanks to the
changes made to the buffer management code. ...
Anyone else run into a gotcha that one of our customers
Well, pre-compiled isn't going to make much of a difference
stability-wise. What you will run into is that very few people arerunning PostgreSQL on your hardware, so it's possible you'd run intosome odd corner cases. I think it's pretty unlikely you'd lose data, but
you could end up with
Jim C. Nasby [EMAIL PROTECTED] writes:
On Tue, Jun 13, 2006 at 06:04:42PM -0400, Tom Lane wrote:
It'd depend on the context, possibly, but it's easy to show that the
current planner does fold now() - interval_constant when making
estimates. Simple example:
Turns out the difference is
Mischa Sandberg [EMAIL PROTECTED] writes:
Tom Lane wrote:
Does Solaris have any call that allows locking a shmem segment in RAM?
Yes, mlock(). But want to understand what's going on before patching.
Sure, but testing it with mlock() might help you understand what's going
on, by eliminating
Shaun Thomas [EMAIL PROTECTED] writes:
I can see the planner not liking the results of a function,
but a variable? That's a static value!
Read what you wrote, and rethink...
If you're desperate you can construct a query string with the variable
value embedded as a literal, and then EXECUTE
Jim C. Nasby [EMAIL PROTECTED] writes:
On Tue, Jun 13, 2006 at 06:21:21PM -0400, John Vincent wrote:
Actually it's on my radar. I was looking for a precompiled build first (we
actually checked the Pervasive and Bizgres sites first since we're
considering a support contract) before going the
Jim C. Nasby [EMAIL PROTECTED] writes:
On Tue, Jun 13, 2006 at 02:43:45PM +0200, Antoine wrote:
I don't have a copy of the standard on hand and a collegue is claiming
that there must be a from clause in a select query (he is an oracle
guy). This doesn't seem to be the case for postgres... does
On Tue, Jun 13, 2006 at 05:01:34PM -0700, Mischa Sandberg wrote:
Jim C. Nasby wrote:
On Tue, Jun 13, 2006 at 04:20:34PM -0700, Mischa Sandberg wrote:
Jim C. Nasby wrote:
What's sort_mem set to? I suspect you simply ran the machine out of
memory.
8192 (8MB). No issue when shared_buffers was
Tom Lane [EMAIL PROTECTED] writes:
The spec does require a FROM clause in SELECT (at least as of SQL99, did
not check SQL2003). However, it's clearly mighty useful to allow FROM
to be omitted for simple compute-this-scalar-result problems. You
should respond to the Oracle guy that SELECT
75 matches
Mail list logo
