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.
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
On 24 September 2010 21:24, Brad Nicholson 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
>
On 24 September 2010 00:12, Mark Kirkwood 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 considered that ... but is it c
On 24 September 2010 21:06, Bob Lunney 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 taken, I will continue
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 s
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 di
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 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
performance-problems
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:
> 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
> probably still w
On 24 September 2010 19:16, Brad Nicholson 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 ... but the biggest r
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 ma
On 10-09-24 12:46 PM, Tobias Brox wrote:
On 24 September 2010 18:23, Bob Lunney 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_* views give you usage i
On 24 September 2010 00:12, Mark Kirkwood 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 scan to seq scan (perhaps
On 24 September 2010 18:23, Bob Lunney 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 helping you much and are
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 h
Tobias Brox 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 rows, can put you
On 24 September 2010 05:39, 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 problem
> occurs regardless whe
19 matches
Mail list logo
