/create_mat_view.txt
or maybe like this:
0 6 * * * C:\Program Files\PostgreSQL\8.0\psql.exe dbname
C:\create_mat_view.txt
I hope this helps,
--
Matthew Nuzum
www.bearfruit.org
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please
.
--
Matthew Nuzum
www.bearfruit.org
---(end of broadcast)---
TIP 8: explain analyze is your friend
Mindaugas Riauba [EMAIL PROTECTED] writes:
And what in vacuum verbose output suggests that vacuum is not done
often enough? Current output (table is 100MB already) is below.
The output shows vacuum cleaning up about a third of the table. Usually
people like to keep the overhead down to
Mindaugas Riauba wrote:
Might e aggressive enough, but might not. I have seen some people run
-V 0.1. Also you probably don't need -A that low. This could an issue
where analyze results in an inaccurate reltuples value which is
preventing autovacuum from doing it's job. Could you please run
Mindaugas Riauba wrote:
AFAICT the vacuum is doing what it is supposed to, and the problem has
to be just that it's not being done often enough. Which suggests either
an autovacuum bug or your autovacuum settings aren't aggressive enough.
-D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10
Mindaugas Riauba wrote:
The vacuum cost parameters can be adjusted to make vacuums fired
by pg_autovacuum less of a burden. I haven't got any specific numbers
to suggest, but perhaps someone else does.
It looks like that not only vacuum causes our problems. vacuum_cost
seems to lower vacuum
On 4/26/05, Steinar H. Gunderson [EMAIL PROTECTED] wrote:
On Tue, Apr 26, 2005 at 03:16:57PM -0500, Matthew Nuzum wrote:
Seq Scan on usage_access (cost=0.00..1183396.40 rows=12713851
width=116) (actual time=481796.22..481839.43 rows=3343 loops=1)
That's a gross misestimation -- four
On 4/26/05, Steinar H. Gunderson [EMAIL PROTECTED] wrote:
On Tue, Apr 26, 2005 at 03:16:57PM -0500, Matthew Nuzum wrote:
Seq Scan on usage_access (cost=0.00..1183396.40 rows=12713851
width=116) (actual time=481796.22..481839.43 rows=3343 loops=1)
That's a gross misestimation -- four
spindle speed of drive A?
--
Matthew Nuzum
www.bearfruit.org
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
of people as these on the
performance list.
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of Elite Content Management System
View samples of Elite CMS in action by visiting
http://www.followers.net/portfolio/
---(end of broadcast
spindle speed of drive A?
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of “Elite Content Management System”
View samples of Elite CMS in action by visiting
http://www.followers.net/portfolio/
---(end of broadcast)---
TIP 4: Don't 'kill
currently using
7.3.
I'm eager to hear your thoughts and experiences,
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of Elite Content Management System
Earn a commission of $100 - $750 by recommending Elite CMS. Visit
http://www.elitecms.com/Contact_Us.partner for details
and will contact you off list for more
details soon.
Out of curiosity, does batch mode produce a lighter load? Live updating will
provide maximum data security, and I'm most interested in how it affects the
server.
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of Elite Content
numbers before and see if they
are keeping up with the actual number if I/U/D's that you are
performing. If they are, then it's a pg_autovacuum problem that I will
look into further, if they are not, then it's a stats system problem
that I can't really help with.
Good luck,
Matthew
Otto
= 113082 correct right after the vacuum?
Matthew
Otto Blomqvist wrote:
It looks like the reltuples-values are screwed up. Even though rows are
constantly being removed from the table the reltuples keep going up. If I
understand correctly that also makes the Vacuum threshold go up and we end
up
Tom Lane wrote:
Matthew T. O'Connor matthew@zeut.net writes:
hmm the value in reltuples should be accurate after a vacuum (or
vacuum analyze) if it's not it's a vacuum bug or something is going on
that isn't understood. If you or pg_autovacuum are running plain
analyze commands
Tom Lane wrote:
I wrote:
One thing that is possibly relevant here is that in 8.0 a plain VACUUM
doesn't set reltuples to the exactly correct number, but to an
interpolated value that reflects our estimate of the steady state
average between vacuums. I wonder if that code is wrong, or if it's
.
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of Elite Content Management System
View samples of Elite CMS in action by visiting
http://www.elitecms.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go
. PostgreSQL version is 7.3.2 and the sort_mem is at the
default setting.
(I know that's an old version. We've been testing with 7.4 now and are
nearly ready to upgrade.)
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of Elite Content Management System
View samples of Elite CMS in action
)
Sort Key: accountid, sessionid
- Seq Scan on usage_access (cost=0.00..1018901.84
rows=35678384 width=28) (actual time=8.13..416580.35 rows=35678383 loops=1)
Total runtime: 12625498.84 msec
(7 rows)
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net
to unacceptable levels.
FWIW, the explain was run from psql running on the db server, the test query
the other day was run from one of the webservers. Should I run this on the
db server to minimize load?
--
Matthew Nuzum [EMAIL PROTECTED]
www.followers.net - Makers of Elite Content Management System
View
The version that shipped with 8.0 should be fine. The only version that
had the problem Tom referred to are in the early 7.4.x releases.
Did you get my other message about information from the stats system
(I'm not sure why my other post has yet to show up on the performance
list).
Matthew
be
something new.
Thanks,
Matthew O'Connor
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Gaetano Mendola wrote:
pg_class after the vacuum full for that table
relfilenode | relname | relpages | reltuples
-+--+--+-
18376 | messages |63307 | 1.60644e+06
pg_class before the vacuum full for that table
relfilenode | relname | relpages |
Josh Berkus wrote:
Matt,
I had one comment on the pg_autovacuum section. Near the bottom it
lists some of it's limitations, and I want to clarify the 1st one: Does
not reset the transaction counter. I assume this is talking about the
xid wraparound problem? If so, then that bullet can be
Josh, Steve:
I have also been looking at non-dell server vendors due to
recent concerns about the PERC RAID Controllers. That said,
I believe IBM just shoots itself in the foot via its sales/pricing
practices
Price out a PE2850 w/ 8GB RAM and 6 18GB Drives on the
Dell website and you'll
David Parker wrote:
We're using postgresql 7.4.5. I've only recently put pg_autovacuum in
place as part of our installation, and I'm basically taking the
defaults. I doubt it's a problem with autovacuum itself, but rather with
my configuration of it. I have some reading to do, so any pointers to
to be an autovacuum problem. I'm not sure about the missing
pg_statistic entries anyone else care to field that one?
Matthew
David Parker wrote:
Thanks. The tables I'm concerned with are named: 'schema', 'usage',
'usageparameter', and 'flow'. It looks like autovacuum is performing
analyzes:
% grep
. There are people who use proxying in apache to
redirect expensive tasks to other servers that are dedicated to just one
heavy challenge. In that case you likely do have 99% dynamic content.
Matthew Nuzum | Makers of Elite Content Management System
www.followers.net | View samples of Elite
Matt Nuzum wrote:
To me, these three queries seem identical... why doesn't the first one
(simplest to understand and write) go the same speed as the third one?
If you look at the explain output, you will notice that only the 3rd
query is using an Index Scan, where as the 1st and 2nd are doing
Andrew Sullivan wrote:
Probably the most severe objection to doing things this way is that the
selected plan could change unexpectedly as a result of the physical
table size changing. Right now the DBA can keep tight rein on actions
that might affect plan selection (ie, VACUUM and ANALYZE), but
the screen.
--
Matthew Nuzum + Man was born free, and everywhere
www.bearfruit.org : he is in chains, Rousseau
+~~+ Then you will know the truth, and
the TRUTH will set you free, Jesus Christ (John 8:32 NIV)
-Original Message-
From: [EMAIL PROTECTED]
[mailto
be interesting.
Also how do these numbers compare to 7.4? They may not be what you
expected, but they might still be an improvment.
Matthew
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister
in.
--
Matthew Nuzum + Man was born free, and everywhere
www.bearfruit.org : he is in chains, Rousseau
+~~+ Then you will know the truth, and
the TRUTH will set you free, Jesus Christ (John 8:32 NIV)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf
specific tables.
Matthew
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Stephane Tessier wrote:
I think with your help guys I'll do it!
I'm working on it!
I'll work on theses issues:
we have space for more ram(we use 2 gigs on possibility of 3 gigs)
iowait is very high 98% -- look like postgresql wait for io access
raid5 --raid0 if i'm right raid5 use 4
VACUUM FULL ANALYZE every 3 hours seems a little severe. You will
probably be be served just as well by VACUUM ANALYZE. But you probably
don't need the VACUUM part most of the time. You might try doing an
ANALYZE on the specific tables you are having issues with. Since
ANALYZE should be
in the future.
You can however set the VACUUM and ANALYZE thresholds independently.
So perhpaps it will help you if you set your ANALYZE setting to be very
aggressive and your VACUUM settings to something more standard.
Matthew
---(end of broadcast
Gaetano Mendola wrote:
Well I think pg_autovacuum as is in 7.4 can not help me for this particular
table.
The table have 4.8 milions rows and I have for that table almost 10252 new
entries for day.
I'm using pg_autovacuum with -a 200 -A 0.8 this means a threashold for
that table equal to: 3849008
.
Matthew
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
with people often suggesting things you'd never considered.
I hope this helps and I hope that you achieve your goals of a well
performing application.
Matthew Nuzum | Makers of Elite Content Management System
www.followers.net | View samples of Elite CMS in action
[EMAIL
office at 4:15 EDT and will not return
until Friday, although I can do another test on my home computer Thursday.
Matthew Nuzum | Makers of Elite Content Management System
www.followers.net | View samples of Elite CMS in action
[EMAIL PROTECTED] | http://www.followers.net
On Wed, 2004-06-02 at 17:39, Greg Stark wrote:
Matthew Nuzum [EMAIL PROTECTED] writes:
I have colinux running on a Fedora Core 1 image. I have the rhdb 3 (or
PostgreSQL RedHat Edition 3) on it running. Here are tests with fsync on
and off:
FSYNC OFF FSYNC
it produces such a low load on the system. If you are going to give it a
try, the one trick I used to get things going was to download the newest
beta of winpcap and then the networking came up easily. Everything else was
a piece of cake.
Matthew Nuzum | Makers of Elite Content Management
would probably help as it monitors activity and vacuumus
tables accordingly. It is not included with 7.3.x but if you download it
and compile yourself it will work against a 7.3.x server.
Good luck,
Matthew
---(end of broadcast)---
TIP 4: Don't
doesn't cause pg_autovacuum to stop vacuuming but rather to vacuum to
often. So perhaps this is a different issue? Please let me know what
you find.
Thanks,
Matthew O'Connor
---(end of broadcast)---
TIP 9: the planner will ignore your desire
of it's I/O capacity because it's using PIO
mode or some other non-optimal method of accessing the disk.
Just a suggestion, I hope it helps,
Matthew Nuzum | ISPs: Make $200 - $5,000 per referral by
www.followers.net | recomending Elite CMS to your customers!
[EMAIL PROTECTED
Paul Thomas wrote:
Looks like he's using the default postgresql.conf settings in which
case I'm not suprised at pg looking so slow. His stated use of foreign
keys invalidates the tests anyway as MyISAM tables don't support FKs
so we're probably seeing FK check overheads in pg that are simply
Heiko Kehlenbrink wrote:
i use suse 8.1
postgresql 7.2 compiled from the rpms for using postgis, but that is
Try v7.4, there are many performance improvements. It may not make up
all the differences but it should help.
---(end of
for swap.
I haven't heard from Joe how things are going with the fixed
pg_autovacuum but that in combination with the vacuum delay stuff should
work well.
Matthew
---(end of broadcast)---
TIP 6: Have you searched our list archives
in Jan's later patch was the
ability to specify how many pages to work on before sleeping, rather
than how long to sleep inbetween every 1 page. You might be able to do
a quick hack and have it do 10 pages or so before sleeping.
Matthew
---(end of broadcast
is a good default value for the scaling factor
but I erred on the side of not vacuuming too often.
Second, Matthew requested pg_autovacuum run with -d2; I found that
with -d2 set, pg_autovacuum would immediately exit on start. -d0 and
-d1 work fine however.
That's unfortunate
Joe Conway wrote:
Yeah, I'm sure. Snippets from the log:
[...lots-o-tables...]
[2004-03-14 12:44:48 PM] added table: specdb.public.parametric_states
[2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE
public.transaction_data
[2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE
while the server is
slow? If so, have you played with the pg_autovacuum default vacuum and
analyze thresholds? If it appears that it is related to pg_autovacuum
please send me the command options used to run it and a logfile of it's
output running at at a debug level of -d2
Matthew
MTO == Matthew T O'Connor [EMAIL PROTECTED] writes:
MTO I don't run FreeBSD, so I haven't tested with FreeBSD. Recently
Craig MTO Boston reported and submitted a patch for a crash on FreeBSD,
but that
some more debugging data:
(gdb) print now
$2 = {tv_sec = 1070565077, tv_usec = 216477
Josh Berkus wrote:
Matthew,
For small tables, you don't need to vacuum too often. In the testing I
did a small table ~100 rows, didn't really show significant performance
degredation until it had close to 1000 updates.
This is accounted for by using the threshold value. That way
pg_autovacuum was
given knobs so that the vacuum and analyze thresholds can be set
independently.
Matthew
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Josh Berkus wrote:
Matthew,
True, but I think it would be one hour once, rather than 30 minutes 4
times.
Well, generally it would be about 6-8 times at 2-4 minutes each.
Are you saying that you can vacuum a 1 million row table in 2-4
minutes? While a vacuum of the same table
Shridhar Daithankar wrote:
Matthew T. O'Connor wrote:
But we track tuples because we can compare against the count given by
the stats system. I don't know of a way (other than looking at the
FSM, or contrib/pgstattuple ) to see how many dead pages exist.
I think making pg_autovacuum dependent
Josh Berkus wrote:
Matthew,
But we could create a config file that would store stuff in a flatfile table,
OR we could add our own system table that would be created when one
initializes pg_avd.
I don't want to add tables to existing databases, as I consider that
clutter and I never like
Josh Berkus wrote:
Matthew,
I don't see how a seperate database is better than a table in the databases.,
except that it means scanning only one table and not one per database. For
one thing, making it a seperate database could make it hard to back up and
move your database+pg_avd config
Josh Berkus wrote:
Matthew,
I certainly agree that less than 10% would be excessive, I still feel
that 10% may not be high enough though. That's why I kinda liked the
sliding scale I mentioned earlier, because I agree that for very large
tables, something as low as 10% might be useful
From: Tom Lane [EMAIL PROTECTED]
Matthew T. O'Connor [EMAIL PROTECTED] writes:
So, now is precisely the time to be experimenting to find out what works
well and what features are needed.
Another quick question while I have your attention :-)
Since pg_autovaccum is a contrib module does
are implying here. Please expand on this if
you deem it worthy.
I guess I'll start coding again. I'll send an email to the hackers list
tomorrow evening with as much info / design as I can think of.
Matthew
---(end of broadcast)---
TIP 7: don't
I currently have a J2EE app that allows our users to upload files. The
actual file gets stored on the same disk as the webserver is running
on, while the information they entered about the file gets stored in
the database. We now need to move the database to a different machine
and I'm
on.
Additionally, I have an increasingly large production database that I would
be willing to do some test-cases on. I don't really know how to do it
though... If someone where able to give instructions I could run tests on
three different platforms.
Matthew Nuzum | Makers of Elite Content
On Wednesday, July 2, 2003, at 01:10 PM, Rod Taylor wrote:
We have also done little to no performance tuning of Postgres'
configuration. We do have indexes on all of the important columns and
we have reindexed. Any pointers would be greatly appreciated.
Tuning will often double (if not more)
501 - 567 of 567 matches
Mail list logo
