On Thu, 2007-02-22 at 22:53, Mark Stosberg wrote:
Thanks to everyone for the feedback about vacuuming. It's been very
useful. The pointers to the pgstattuple and Pgfouine tools were also
helpful.
I'm now considering the following plan for trying Autovacuuming again
with 8.1. I'd like any
Hi all,
I'm using Postgresql 8.2.3 on a Windows XP system.
I need to
write and retrieve bytea data from a table.
The problem is that, while
data insertion is quite fast, bytea extraction is very slow.
I'm trying
to store a 250KB image into the bytea field.
A simple select query on a
36-row
[EMAIL PROTECTED] wrote:
Hi all,
I'm using Postgresql 8.2.3 on a Windows XP system.
I need to
write and retrieve bytea data from a table.
The problem is that, while
data insertion is quite fast, bytea extraction is very slow.
I'm trying
to store a 250KB image into the bytea field.
A simple
Thanks for your reply,
Is it in executing the query (what does
EXPLAIN ANALYSE show)?
Here is the output of explain analyze SELECT *
FROM FILE
Seq Scan on FILE (cost=0.00..1.36 rows=36 width=235)
(actual time=0.023..0.107 rows=36 loops=1)
How are you accessing
the database:
[EMAIL PROTECTED] wrote:
Thanks for your reply,
Is it in executing the query (what does
EXPLAIN ANALYSE show)?
Here is the output of explain analyze SELECT *
FROM FILE
Seq Scan on FILE (cost=0.00..1.36 rows=36 width=235)
(actual time=0.023..0.107 rows=36 loops=1)
If you look at the
I would like to get someone's recommendations on the best initial
settings for a dedicated PostgreSQL server. I do realize that there are
a lot of factors that influence how one should configure a database. I
am just looking for a good starting point. Ideally I would like the
database to reside
Campbell, Lance wrote:
I would like to get someone's recommendations on the best initial
settings for a dedicated PostgreSQL server. I do realize that there are
a lot of factors that influence how one should configure a database. I
am just looking for a good starting point. Ideally I would
Richard,
Thanks for your reply.
You said:
Your operating-system should be doing the caching for you.
My understanding is that as long as Linux has memory available it will
cache files. Then from your comment I get the impression that since
Linux would be caching the data files for the
Campbell, Lance wrote:
Richard,
Thanks for your reply.
You said:
Your operating-system should be doing the caching for you.
My understanding is that as long as Linux has memory available it will
cache files. Then from your comment I get the impression that since
Linux would be caching the
On Fri, Feb 23, 2007 at 10:13:31AM +0100, Csaba Nagy wrote:
You likely don't need the nightly full vacuum run... we also do here a
nightly vacuum beside autovacuum, but not a full one, only for tables
which are big enough that we don't want autovacuum to touch them in high
business time but
If you're doing much updating at all you'll also want to bump up
checkpoint_segments. I like setting checkpoint_warning just a bit under
checkpoint_timeout as a way to monitor how often you're checkpointing
due to running out of segments.
With a large shared_buffers you'll likely need to make the
If you look at the actual time it's completing very quickly indeed.
So
- it must be something to do with either:
1. Fetching/formatting
the data
2. Transferring the data to the client.
I do agree.
What
happens if you only select half the rows? Does the time to run the
select halve?
Yes,
are you getting the data from the local box or from a remote site?
Everything is on the local box.
also explain analyze is showing
nothing slow but you did not post the
enitre output. also, try the
\timing switch in psql.
Actually a line was missing: Total runtime:
0.337 ms.
Massimo
In response to Campbell, Lance [EMAIL PROTECTED]:
Richard,
Thanks for your reply.
You said:
Your operating-system should be doing the caching for you.
My understanding is that as long as Linux has memory available it will
cache files. Then from your comment I get the impression that
The postgresql.conf says that the maximum checkpoint_timeout is 1 hour.
However, the following messages seem to suggest that it may be useful to
set the value significantly higher to reduce unnecessary WAL volume:
http://archives.postgresql.org/pgsql-hackers/2006-10/msg00527.php
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
In searching the archives, I can't find any specific info indentifying
which Xeon processors don't have this problem.
Anyone point me to a reference?
Is
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
In searching the archives, I can't find any specific info indentifying
which Xeon processors don't have this problem.
Anyone point me to a reference?
We
On Fri, Feb 23, 2007 at 02:05:57PM -0500, Geoffrey wrote:
In searching the archives, I can't find any specific info indentifying
which Xeon processors don't have this problem.
AFAIK the cut-off point is at the Woodcrests. They are overall much better
suited to PostgreSQL than the older Xeons
Steinar H. Gunderson wrote:
On Fri, Feb 23, 2007 at 02:05:57PM -0500, Geoffrey wrote:
In searching the archives, I can't find any specific info indentifying
which Xeon processors don't have this problem.
AFAIK the cut-off point is at the Woodcrests. They are overall much better
suited to
On Fri, Feb 23, 2007 at 04:53:18PM -0300, Alvaro Herrera wrote:
It's slightly unfortunate that AMD and Intel cling to the Opteron and Xeon
names even though they're making significant architecture changes, but that's
life, I guess.
AFAIR Intel has been calling their server processors Xeon
On Fri, Feb 23, 2007 at 10:14:29AM -0800, Jeff Davis wrote:
The postgresql.conf says that the maximum checkpoint_timeout is 1 hour.
However, the following messages seem to suggest that it may be useful to
set the value significantly higher to reduce unnecessary WAL volume:
On Fri, 2007-02-23 at 14:02 -0600, Jim C. Nasby wrote:
say that checkpoints cause extra disk I/O. Is there a good way to
measure how much extra I/O (and WAL volume) is caused by the
checkpoints? Also, it would be good to know how much total I/O is caused
by a checkpoint so that I know if
Geoffrey,
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
Just to be clear, it's a software problem which affects all architectures,
including AMD and Sparc. It's just *worse* on the PIII and P4
Josh Berkus wrote:
Geoffrey,
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
Just to be clear, it's a software problem which affects all architectures,
including AMD and Sparc. It's just *worse* on
Josh Berkus wrote:
Geoffrey,
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
Just to be clear, it's a software problem which affects all architectures,
including AMD and Sparc. It's just *worse*
Joshua D. Drake wrote:
Josh Berkus wrote:
Geoffrey,
I recall a reference on the list indicating that newer Xeon processors
don't suffer from the context switching problem reported last year.
Just to be clear, it's a software problem which affects all architectures,
including AMD and Sparc.
On 2/23/07, Joshua D. Drake [EMAIL PROTECTED] wrote:
Also isn't it pretty much *not* a problem with current versions of
PostgreSQL?
We had a really *big* scalability problem with a quad Xeon MP 2.2 and
PostgreSQL 7.4. The problem is mostly gone since we upgraded to 8.1 a
year ago.
Woodcrest
On 2/23/07, Geoffrey [EMAIL PROTECTED] wrote:
As I've heard. We're headed for 8 as soon as possible, but until we get
our code ready, we're on 7.4.16.
You should move to at least 8.1 and possibly 8.2. It's not a good idea
to upgrade only to 8 IMHO.
--
Guillaume
On Fri, Feb 23, 2007 at 12:23:08PM -0800, Jeff Davis wrote:
On Fri, 2007-02-23 at 14:02 -0600, Jim C. Nasby wrote:
say that checkpoints cause extra disk I/O. Is there a good way to
measure how much extra I/O (and WAL volume) is caused by the
checkpoints? Also, it would be good to know
Florian, Steinar,
Could you try to see if the GIN implementation of pg_trgm is faster in
your cases?
Florian, instead of using WHERE similarity(...) 0.4, you should use
set_limit (SELECT set_limit(0.4);).
I posted it on -patches and it is available here:
On Sat, Feb 24, 2007 at 12:09:41AM +0100, Guillaume Smet wrote:
Could you try to see if the GIN implementation of pg_trgm is faster in
your cases?
I'm sorry, I can no longer remember where I needed pg_trgm. Simple testing of
your patch seems to indicate that the GiN version is about 65%
Alvaro Herrera wrote:
Steinar H. Gunderson wrote:
On Fri, Feb 23, 2007 at 02:05:57PM -0500, Geoffrey wrote:
In searching the archives, I can't find any specific info indentifying
which Xeon processors don't have this problem.
AFAIK the cut-off point is at the Woodcrests. They are overall
Hi Steinar,
On 2/24/07, Steinar H. Gunderson [EMAIL PROTECTED] wrote:
I'm sorry, I can no longer remember where I needed pg_trgm. Simple testing of
your patch seems to indicate that the GiN version is about 65% _slower_ (18ms
vs. 30ms) for a test data set I found lying around, but I remember
Guillaume Smet wrote:
On 2/23/07, Geoffrey [EMAIL PROTECTED] wrote:
As I've heard. We're headed for 8 as soon as possible, but until we get
our code ready, we're on 7.4.16.
You should move to at least 8.1 and possibly 8.2. It's not a good idea
to upgrade only to 8 IMHO.
When I said 8, I
34 matches
Mail list logo