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 archi
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 sol
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 bot
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 -
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 e
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 ef
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 manu
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 that
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
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 p
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 w
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 effe
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
---(
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 y
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 "opt
* 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 = 'activ
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 D
"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 altern
> 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 broadc
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 perfo
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 wel
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
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.
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
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 hand-wav
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 shared_b
> -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?
>
> T
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
>>> 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 que
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 kn
"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]
Pervasi
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 "custom
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,
>>> 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 mach
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 AIX/Power
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
-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 disappoin
"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
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 jus
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 runnin
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 looking at running our warehouse
> (PGSQL) in a LoP lpar but I wasn
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
---
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?
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
>>> 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 preca
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 be
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. ...
>
> An
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 nor
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 be
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 suspec
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.
> >>Droppe
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 sort,
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 r
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 performanc
"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 differe
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 eliminati
"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 EXECU
"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 go
"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 postgr
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 shar
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 "SE
> 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 whatever FROM dual" is not
> in t
73 matches
Mail list logo
