Hi all
I've have a strange problem with my Windows postgresql-9.0 service
stopping after any transaction which manipulates tables in any database
(Deleting records, Inserting records, bulk importing via \copy, etc). This
problem occurs regardless whether I'm accessing the database server via
On 24 September 2010 05:39, adrian.kitching...@dse.vic.gov.au wrote:
Hi all
I've have a strange problem with my Windows postgresql-9.0 service stopping
after any transaction which manipulates tables in any database (Deleting
records, Inserting records, bulk importing via \copy, etc). This
Tobias Brox tobi...@gmail.com wrote:
Sorry for all the stupid questions ;-)
I'm with Mark -- I didn't see nay stupid questions there.
Where I would start, though, is by checking the level of bloat. One
long-running query under load, or one query which updates or deletes
a large number of
Tobias,
Consult pg_statio_user_indexes to see which indexes have been used and how
much. Indexes with comparitively low usages rates aren't helping you much and
are candidates for elimination. Also, partitioning large tables can help,
since the indexes on each partition are smaller than one
On 24 September 2010 18:23, Bob Lunney bob_lun...@yahoo.com wrote:
Consult pg_statio_user_indexes to see which indexes have been used
and how much.
What is the main differences between pg_statio_user_indexes and
pg_stat_user_indexes?
Indexes with comparitively low usages rates aren't
On 24 September 2010 00:12, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote:
All good questions! Before (or maybe as well as) looking at index sizes vs
memory I'd check to see if any of your commonly run queries have suddenly
started to use different plans due to data growth, e.g:
- index
On 10-09-24 12:46 PM, Tobias Brox wrote:
On 24 September 2010 18:23, Bob Lunneybob_lun...@yahoo.com wrote:
Consult pg_statio_user_indexes to see which indexes have been used
and how much.
What is the main differences between pg_statio_user_indexes and
pg_stat_user_indexes?
The pg_stat_*
On Sep 22, 2010, at 6:36 AM, Ogden wrote:
On Sep 21, 2010, at 2:34 PM, Ogden wrote:
On Sep 21, 2010, at 2:16 PM, Greg Smith wrote:
Joshua D. Drake wrote:
PostgreSQL's defaults are based on extremely small and some would say
(non production) size databases. As a matter of course I
On 24 September 2010 19:16, Brad Nicholson bnich...@ca.afilias.info wrote:
[Brad Nicholson]
Why is the vacuum dragging out over time? Is the size of your data
increasing, are you doing more writes that leave dead tuples, or are your
tables and/or indexes getting bloated?
Digressing a bit here
Tobias Brox tobi...@gmail.com wrote:
If it was to me, we would have had autovacuum turned on. We've
had one bad experience when the autovacuumer decided to start
vacuuming one of the biggest table at the worst possible moment -
and someone figured autovacuum was a bad idea. I think we
Tobias Brox wrote:
1) Are there any good ways to verify my hypothesis?
You can confim easily whether the contents of the PostgreSQL buffer
cache contain when you think they do by installing pg_buffercache. My
paper and sample samples at
http://www.pgcon.org/2010/schedule/events/218.en.html
Tobias Brox wrote:
We do have some bloat-problems as well - every now and then we decide
to shut down the operation, use pg_dump to dump the entire database to
an sql file and restore it. The benefits are dramatic, the space
requirement goes down a lot, and often some of our
On 10-09-24 01:41 PM, Tobias Brox wrote:
What do you mean, that you could run regular vacuum less frequently,
or that the regular vacuum would go faster?
It means that vacuums ran less frequently. With cron triggered vacuums,
we estimated when tables needed to be vacuumed, and vacuumed them
Tobias,
First off, what version of PostgreSQL are you running? If you have 8.4,
nightly vacuuming shouldn't be necessary with properly tuned autovacuum jobs.
The pertinent difference between pg_stat_user_indexes and
pg_statio_user_indexes is the latter shows the number of blocks read from
On 10-09-24 03:06 PM, Bob Lunney wrote:
The pertinent difference between pg_stat_user_indexes and
pg_statio_user_indexes is the latter shows the number of blocks read from disk
or found in the cache.
I have a minor, but very important correction involving this point. The
pg_statio tables
On 24 September 2010 21:06, Bob Lunney bob_lun...@yahoo.com wrote:
First off, what version of PostgreSQL are you running? If you have 8.4,
nightly vacuuming shouldn't be necessary with properly tuned autovacuum jobs.
8.3. We'll upgrade to 9.0 during the December holidays fwiw. But
point
On 24 September 2010 00:12, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote:
Re index size, you could try indexes like:
some_table(a)
some_table(b)
which may occupy less space, and the optimizer can bitmap and/or them to
work like the compound index some_table(a,b).
Hm ... never
On 24 September 2010 21:24, Brad Nicholson bnich...@ca.afilias.info wrote:
The pertinent difference between pg_stat_user_indexes and
pg_statio_user_indexes is the latter shows the number of blocks read from
disk or found in the cache.
I have a minor, but very important correction involving
Thanks for spending your time on this ... amidst all the useful
feedback I've received, I'd rate your post as the most useful post.
1) Are there any good ways to verify my hypothesis?
You can confim easily whether the contents of the PostgreSQL buffer cache
contain when you think they do by
Tobias Brox wrote:
I have easily measurable improvements on client systems increasing
shared_buffers into the 4GB - 8GB range. Popular indexes move into there,
stay there, and only get written out at checkpoint time rather than all the
time.
Ours is at 12 GB, out of 70 GB total RAM.
20 matches
Mail list logo
