On Wed, Jul 5, 2017 at 11:03 AM, AP <ap@zip.com.au> wrote:
> On Wed, Jul 05, 2017 at 10:29:09AM +0530, Amit Kapila wrote:
>> >> bitmappages. Can you try to use pgstattuple extension and get us the
>> >> results of Select * from pgstathashindex('index_name');? If the
>> >> number of bitmappages is 128 and total overflow pages are 128 * 4096,
>> >> then that would mean that all the pages are used. Then maybe we can
>> >
>> > Hmm. Unless I misunderstood that'd mean that overflow_pages/4096 should
>> > result in a number <= 128 at the moment, right?
>>
>> No, sorry, I think my calculation above has something missing. It
>> should be 128 * 4096 * 8. How we can compute this number is
>> no_bitmap_pages * no_bits_used_to_represent_overflow_pages.
>
> AHA! Ok. Then that appears to match. I get 65.041.
>
>> >If so then something is
>> > amiss:
>> >
>> > # select * from pgstathashindex('link_datum_id_hash_idx');
>> > version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent
>> >
---------+--------------+----------------+--------------+--------------+------------+------------+------------------
>> > 3 | 10485760 | 2131192 | 66 | 0 | 2975444240 | 0 |
1065.19942179026
>> > (1 row)
>> >
>> > oldmdstash=# select 2131192/4096;
>> > ?column?
>> > ----------
>> > 520
>> > (1 row)
>>
>> You need to divide 520 by 8 to get the bitmap page. Is this the index
>> in which you get the error or is this the one on which you have done
>> REINDEX?
>
> Post REINDEX.
>
>> > And I do appear to have an odd percentage of free space. :)
Are you worried about "unused_pages"? If so, then this is not a major
reason to worry, because these are probably freed overflow pages which
can be used in future. In the hash index, when we free the overflow
pages, they are not returned back to OS, rather they are tracked in
the index as unused pages which will get used when required in future.
>> >
>>
>> It looks like Vacuum hasn't been triggered.
>>
Vacuum won't be triggered on insert load. I think that is one of the
reasons why in your initial copy, you might have got the error. We
had some discussion in the past to trigger Vacuum on insert heavy
workloads [1], but the patch still didn't get committed. I think if
that patch or some other form of that patch gets committed, it will
help the workload what you are trying here.
[1] - https://www.postgresql.org/message-id/b970f20f-f096-2d3a-6c6d-ee887bd30cfb%402ndquadrant.fr
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com