I once had a query which would operate on a recordlist and
see whether there were any gaps larger than 1 between consecutive
primary keys.
Would you mind sharing the query you described? I am attempting to do
something similar now.
---(end of
, the tables have been updated with tens of thousands of inserts
and the table has still not been analyzed (according to
pg_stat_user_tables). Does a scale factor of 0 cause the table to never
be analyzed? What am I doing wrong?
I'm using PG 8.2.1.
Thanks,
Jeremy Haile
Unless it's just a bug, my only guess is that autovacuum may be getting
busy at times (vacuuming large tables for example) and hasn't had a
chance to even look at that table for a while, and by the time it gets
to it, there have been tens of thousands of inserts. Does that sounds
watching it *all* the time)
So - as far as I could tell it wasn't running.
On Thu, 18 Jan 2007 16:30:17 -0500, Tom Lane [EMAIL PROTECTED] said:
Jeremy Haile [EMAIL PROTECTED] writes:
No tables have been vacuumed or analyzed today. I had thought that this
problem was due to my pg_autovacuum
. This would make vacuuming much less of an
issue.
But I won't be making any changes immediately, so I'll continue to run
tests given your advice.
Thanks again,
Jeremy Haile
---(end of broadcast)---
TIP 5: don't forget to increase your free space
How much memory does the box have
2GB
Yes, it takes up space
Well, I upped max_fsm_pages to 200 because it vacuums were failing
with it set to 150. However, I'm now autovacuuming, which might be
keeping my fsm lower. I didn't realize that setting it too high had
negative effects, so
That's about 32% dead rows. Might be worth scheduling a vacuum full,
but it's not like I was afraid it might be. It looks to me like you
could probably use a faster I/O subsystem in that machine though.
I'll try to schedule a full vacuum tonight. As far as I/O - it's using
SAN over fiber.
It would be nice if the database could
learn to estimate these values, as newer versions of Oracle does.
That would be really nice since it would take some of the guess work out
of it.
Yes, cluster would rebuild the table for you. I wouldn't do anything too
intrusive, run with the
the the table tonight.
Jeremy Haile
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Interesting - I haven't seen that tool before. I'll have to check it
out when I get a chance. Thanks!
On Wed, 17 Jan 2007 20:32:37 +0100, Tomas Vondra [EMAIL PROTECTED] said:
That's about 32% dead rows. Might be worth scheduling a vacuum full,
but it's not like I was afraid it might be.
Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq
scan over index scan even though index scan is faster (as shown by
disabling seqscan). Table is recently analyzed and row count estimates
seem to be in the ballpark.
Another tidbit - I haven't done a vacuum full ever,
trying to defragment the drive on a regular basis
in Windows?
Jeremy Haile
On Tue, 16 Jan 2007 16:39:07 -0500, Tom Lane [EMAIL PROTECTED] said:
Jeremy Haile [EMAIL PROTECTED] writes:
Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq
scan over index scan even though index
Well - whether or not MySQL's implementation of partitioning has some
deficiency, it sure is a lot easier to set up than PostgreSQL. And I
don't think there is any technical reason that setting up partitioning
on Postgres couldn't be very easy and still be robust.
On Thu, 11 Jan 2007 13:59:20
I have a query made by joining two subqueries where the outer query
performing the join takes significantly longer to run than the two
subqueries.
The first subquery runs in 600ms. The seconds subquery runs in 700ms.
But the outer query takes 240 seconds to run! Both of the two
subqueries
On Wed, 10 Jan 2007 12:15:44 -0500, Tom Lane [EMAIL PROTECTED] said:
Jeremy Haile [EMAIL PROTECTED] writes:
I have a query made by joining two subqueries where the outer query
performing the join takes significantly longer to run than the two
subqueries.
Please show EXPLAIN ANALYZE results
condition.
If I ever get it to happen again, I'll be more careful and repost if it
is a real issue. Thanks for pointing me in the right direction!
On Wed, 10 Jan 2007 13:38:15 -0500, Tom Lane [EMAIL PROTECTED] said:
Jeremy Haile [EMAIL PROTECTED] writes:
I still don't understand why
that. And of
course, if PostgreSQL doesn't cache query plans - this idea is bogus =)
On Wed, 10 Jan 2007 13:38:24 -0500, Jeremy Haile [EMAIL PROTECTED]
said:
I'm pretty sure it didn't analyze in between - autovac is turned off
and I ran the test multiple times before posting.
But since I
once) Anyways - I'll let you know if something
similar happens again.
Thanks,
Jeremy Haile
On Wed, 10 Jan 2007 14:22:35 -0500, Tom Lane [EMAIL PROTECTED] said:
Jeremy Haile [EMAIL PROTECTED] writes:
Another random idea - does PostgreSQL do any caching of query plans?
Only if the client
I really wish that PostgreSQL supported a nice partitioning syntax
like MySQL has.
Here is an example:
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION
Hey Jim -
Thanks for the feedback. The server has dual Xeons with HyperThreading
enabled - so perhaps I should try disabling it. How much performance
boost have you seen by disabling it? Of course, the bottleneck in my
case is more on the I/O or RAM side, not the CPU side.
Jeremy Haile
in the future similar
to MySQL partitioning support. Having first-class citizen support of
partitions would also allow some nice administrative GUIs and views to
be built for managing them.
Jeremy Haile
On Wed, 10 Jan 2007 15:09:31 -0600, Jim C. Nasby [EMAIL PROTECTED] said:
On Wed, Jan 10, 2007
to lower the scale factor to zero and just set the
thresholds to a pure number of rows? (when setting it for a specific
table)
Thanks,
Jeremy Haile
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
I am developing an application that has very predictable database
operations:
-inserts several thousand rows into 3 tables every 5 minutes. (table
contain around 10 million rows each)
-truncates and rebuilds aggregate tables of this data every 5 minutes.
(several thousand rows each)
back on?
On Tue, 09 Jan 2007 19:02:25 +0100, Florian Weimer [EMAIL PROTECTED]
said:
* Jeremy Haile:
I'd like any performance advice, but my main concern is the amount of
time vacuum/analyze runs and its possible impact on the overall DB
performance. Thanks!
You could partition your
Thanks for the recommendations. I wasn't familiar with those packages!
On Thu, 4 Jan 2007 00:46:32 +0100, Dimitri Fontaine [EMAIL PROTECTED]
said:
Le jeudi 4 janvier 2007 00:18, Magnus Hagander a écrit :
But to get a good answer on if the difference is
significant enough to matter, you
issue. However - it does have very large tables and
regularly queries and inserts into these tables. I insert several
million rows into 3 tables every day - and also delete about the same
amount.
On Thu, 04 Jan 2007 00:18:23 +0100, Magnus Hagander
[EMAIL PROTECTED] said:
Jeremy Haile wrote:
I
I'm using 8.2. I don't know when I'll get a chance to run my own
benchmarks. (I don't currently have access to a Windows and Linux
server with similar hardware/configuration) But when/if I get a chance
to run them, I will post the results here.
Thanks for the feedback.
Jeremy Haile
On Thu
I am sure that this has been discussed before, but I can't seem to find
any recent posts. (I am running PostgreSQL 8.2)
I have always ran PostgreSQL on Linux in the past, but the company I am
currently working for uses Windows on all of their servers. I don't
have the luxury right now of running
More specifically, you should set the noatime,data=writeback options in
fstab on ext3 partitions for best performance. Correct?
it doesn't really belong here but ext3 has
data journaled (data and meta data)
ordered (meta data journald but data written before meta data (default))
journald
I'm curious what parameters you guys typically *always* adjust on new
PostgreSQL installs.
I am working with a database that contains several large tables (10-20
million) and many smaller tables (hundreds of rows). My system has 2 GB
of RAM currently, although I will be upping it to 4GB soon.
:19 +, Richard Huxton dev@archonet.com
said:
Jeremy Haile wrote:
I'm curious what parameters you guys typically *always* adjust on new
PostgreSQL installs.
The parameters that I almost always change when installing a new system
is shared_buffers, max_fsm_pages, checkpoint_segments
some more advice on what good rule-of-thumb starting
points are for experimentation and how to evaluate whether the values
are set correctly. (in the case of temp_buffers and work_mem especially)
On Tue, 02 Jan 2007 18:49:54 +, Richard Huxton dev@archonet.com
said:
Jeremy Haile wrote:
What
So, on a 4 Gig machine you could divide 1G (25%) by the total possible
connections, then again by the average number of sorts you'd expect per
query / connection to get an idea.
Thanks for the advice. I'll experiment with higher work_mem settings,
as I am regularly doing sorts on large
Once you free some space on the data partition and restart, you should
be good to go --- there will be no loss of committed transactions, since
all the operations are in pg_xlog. Might take a little while to replay
all that log though :-(
Amazing that all works. What I did not see is
I created a 10GB partition for pg_xlog and ran out of disk space today
during a long running update. My checkpoint_segments is set to 12, but
there are 622 files in pg_xlog. What size should the pg_xlog partition
be?
Postmaster is currently not starting up (critical for my organization)
and
. It seems like it can vary considerably depending on
how intensive your current transactions are. Is there a way to
determine a maximum?
On Fri, 22 Dec 2006 11:06:46 -0500, Jeremy Haile [EMAIL PROTECTED]
said:
I created a 10GB partition for pg_xlog and ran out of disk space today
during a long
12/18/2006 08:52 PMDIR archive_status
28 File(s)469,762,048 bytes
3 Dir(s) 10,206,756,864 bytes free
On Fri, 22 Dec 2006 17:02:43 +, Simon Riggs
[EMAIL PROTECTED] said:
On Fri, 2006-12-22 at 11:52 -0500, Jeremy Haile wrote:
I would still
(roughly 80% of the rows) The transaction ran for a long
time and I assume is what caused the pg_xlog to fill up.
On Fri, 22 Dec 2006 17:36:39 +, Simon Riggs
[EMAIL PROTECTED] said:
On Fri, 2006-12-22 at 12:30 -0500, Jeremy Haile wrote:
The archive_status directory is empty. I've never
8.2. Why is that this the case? Shouldn't the query
planner be smart enough to know that the first query is the same as the
second and third? The inner query does not refer to any columns outside
of itself. I personally find the first query easiest to read and wish
it performed well.
Jeremy
@archonet.com
said:
Jeremy Haile wrote:
I have the following query which performs extremely slow:
select min(nlogid) as start_nlogid,
max(nlogid) as end_nlogid,
min(dtCreateDate) as start_transaction_timestamp,
max(dtCreateDate) as end_transaction_timestamp
from
is this explain so much simpler than the
other query plan (with the subquery)?
On Tue, 19 Dec 2006 18:23:06 +, Richard Huxton dev@archonet.com
said:
Jeremy Haile wrote:
Here is the explain analyze output:
Well, the row estimates are about as far out as you can get:
- Index Scan
Makes sense. It is NOT executing the subquery more than once is it?
On Tue, 19 Dec 2006 20:02:35 +, Richard Huxton dev@archonet.com
said:
Jeremy Haile wrote:
Here's the query and explain analyze using the result of the sub-query
substituted:
QUERY
explain analyze select min
@archonet.com
said:
Jeremy Haile wrote:
Here's the query and explain analyze using the result of the sub-query
substituted:
QUERY
explain analyze select min(nlogid) as start_nlogid,
max(nlogid) as end_nlogid,
min(dtCreateDate) as start_transaction_timestamp
Clustering solutions for PostgreSQL are currently pretty limited. Slony
could be a good option in the future, but it currently only supports
Master-Slave replication (not true clustering) and in my experience is a
pain to set up and administer. Bizgres MPP has a lot of promise,
especially for
I am running a query that joins against several large tables (~5 million
rows each). The query takes an exteremely long time to run, and the
explain output is a bit beyond my level of understanding. It is an
auto-generated query, so the aliases are fairly ugly. I can clean them
up (rename them)
We are a small company looking to put together the most cost effective
solution for our production database environment. Currently in
production Postgres 8.1 is running on this machine:
Dell 2850
2 x 3.0 Ghz Xeon 800Mhz FSB 2MB Cache
4 GB DDR2 400 Mhz
2 x 73 GB 10K SCSI RAID 1 (for xlog and OS)
one other note, you probably don't want to use all the disks in a raid10
array, you probably want to split a pair of them off into a seperate
raid1 array and put your WAL on it.
Is a RAID 1 array of two disks sufficient for WAL? What's a typical
setup for a high performance PostgreSQL
it in this case.
3) Any other advice, other than the things I listed above (I am aware of
using copy, ext3 tuning, multiple disks, tuning postgresql.conf
settings)?
Thanks in advance,
Jeremy Haile
#vmstat 2 20
procs ---memory-- ---swap-- -io --system--
cpu
r b
48 matches
Mail list logo
