asn't 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 t
> 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
> pla
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
> 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 broadcast)--
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 migh
'm going to reindex 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
> 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 random
> 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.
> 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
h better with a partitioned table setup.
Also, since I usually delete old data one day at a time, I could simply
drop the old day's partition. 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 give
> I still keep wondering if this table is bloated with dead tuples. Even
> if you vacuum often if there's a connection with an idle transaction,
> the tuples can't be reclaimed and the table would continue to grow.
I used to vacuum once an hour, although I've switched it to autovacuum
now. It de
"comment";0.0052;29;7885;0.0219167
"archived";0;1;2;0.84623
"response_code";0.9942;4;3;0.905409
"transaction_source";0;4;2;0.983851
"location_dim_id";0;4;86;0.985384
"success";0;4;2;0.981072
Just curious - what does that tell us?
Jeremy Haile
worth 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 i
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, alth
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 +01
do that usually to lower the scale factors? Is it
ever a good approach 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
endly syntax 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 PROTEC
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
On
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),
PARTITIO
than 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 Pos
like 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
ased on an unrelated join 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&quo
27;2007-01-09
09:30:00'::timestamp without time
zone))
Total runtime: 675.638 ms
On Wed, 10 Jan 2007 12:15:44 -0500, "Tom Lane" <[EMAIL PROTECTED]> said:
> "Jeremy Haile" <[EM
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 onl
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
&
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)
-regu
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.
J
s of connections is
not a huge 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"
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,
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
> 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 da
iety of factors
- but I'd love 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 +0000, "Richard Huxton"
sai
:19 +, "Richard Huxton"
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_buf
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.
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
> > 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 no
(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
3000ED
12/18/2006 08:52 PM 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 Hail
. 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 s
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 re
different plan?
On Tue, 19 Dec 2006 20:02:35 +, "Richard Huxton"
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
Makes sense. It is NOT executing the subquery more than once is it?
On Tue, 19 Dec 2006 20:02:35 +, "Richard Huxton"
said:
> Jeremy Haile wrote:
> > Here's the query and explain analyze using the result of the sub-query
> > substituted:
> >
> > Q
t the best at
interpreting the explains. Why is this explain so much simpler than the
other query plan (with the subquery)?
On Tue, 19 Dec 2006 18:23:06 +, "Richard Huxton"
said:
> Jeremy Haile wrote:
> > Here is the explain analyze output:
>
> Well, the row estimate
NOT NULL) AND (nlogid > $1))
Total runtime: 156589.605 ms
On Tue, 19 Dec 2006 16:31:41 +, "Richard Huxton"
said:
> Jeremy Haile wrote:
> > I have the following query which performs extremely slow:
> > select min(nlogid) as start_nlogid,
> >
easiest to read and wish
it performed well.
Jeremy Haile
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
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 dat
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)
wise would be
appreciated!
Thanks for all of the responses!
On Wed, 15 Feb 2006 14:53:28 -0500, "Ron" <[EMAIL PROTECTED]> said:
> At 11:21 AM 2/15/2006, Jeremy Haile wrote:
> >We are a small company looking to put together the most cost effective
> >solution for our pr
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)
4
> 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 ins
ow to
interpret 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--
--
51 matches
Mail list logo
