Re: [HACKERS] pgsql 10: hash indexes testing - Mailing list pgsql-hackers

On Thu, Jul 6, 2017 at 5:04 PM, AP <ap@zip.com.au> wrote:
> On Thu, Jul 06, 2017 at 12:38:38PM +0530, Amit Kapila wrote:
>> On Thu, Jul 6, 2017 at 9:32 AM, AP <ap@zip.com.au> wrote:
>> > On Thu, Jul 06, 2017 at 08:52:03AM +0530, Amit Kapila wrote:
>> >> On Thu, Jul 6, 2017 at 2:40 AM, AP <ap@zip.com.au> wrote:
>> >> > On Wed, Jul 05, 2017 at 05:52:32PM +0530, Amit Kapila wrote:
>> >> >> >> >  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)
>> >> > ...
>> >> >> >> > 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
>> >> >
>> >> > Nope. "free_percent" Just a bit weird that I have it at over 1000% free. :)
>> >> > Shouldn't that number be < 100?
>> >>
>> >> Yes, there seems to be some gotcha in free percent calculation.  Is it
>> >> possible for you to debug or in some way share the test?
>> >
>> > I can try to debug but I need to know what to look for and how.
>>
>> Okay,  you need to debug function pgstathashindex and have your
>> breakpoint at free_percent calculation, then try to get the values of
>> nblocks, all the values in stats struct and total_space.  I think
>> after getting this info we can further decide what to look for.
>
> Ok. I'll try and get to this tomorrow amidst fun with NFS. Hopefully
> there'll be time.
>

Cool.

> So... I'll need
>
> postgresql-10-dbg - debug symbols for postgresql-10
>
> Then given https://doxygen.postgresql.org/pgstatindex_8c.html#af86e3b4c40779d4f30a73b0bfe06316f
> set a breakpoint at pgstatindex.c:710 via gdb and then have fun with
> print?
>

If I am reading it correctly it should be line 706 as below:
if (get_call_result_type(fcinfo, NULL, &tupleDesc) != TYPEFUNC_COMPOSITE)

>> > As for sharing the test, that'd mean sharing the data. If it helps I can
>> > provide the content of that column but you're looking at an sql dump that
>> > is roughly (2*64+1)*2.3 billion (give or take a (few) billion) in size. :)
>>
>> This is tricky, will Ibe able to import that column values by creating
>> table, if so, then probably it is worth.
>
> Should do. Thinking about it a little more, I can shrink the file down by
> roughly half if I don't do a pg_dump or similar. Doing
>
> COPY link (datum_id) TO '/tmp/moocow.copy' WITH (FORMAT BINARY)
>
> should allow you to use COPY FROM to restore the file and produce something
> a lot smaller than a dump, right?
>

I think so.  You can share it. I will try.

> The table is simple:
>
> CREATE TABLE link (datum_id BYTEA);
>
> I can't give you the rest of the table (one other column) as the stuff hidden
> in there is private.
>

No issues.

> The only thing that wont give you is the manner in which the column is filled
> (ie: the transactions, their size, how long they run, their concurrency etc).
> Don't know if that's important.
>
>> >> So at this stage, there are two possibilities for you (a) run manual
>> >> Vacuum in-between (b) create the index after bulk load.  In general,
>> >> whatever I have mentioned in (b) is a better way for bulk loading.
>> >> Note here again the free_percent seems to be wrong.
>> >
>> > If you didn't mean VACUUM FULL then (a) does not appear to work and (b)
>> > would kill usability of the db during import, which would happen daily
>> > (though with a vastly reduced data size).
>>
>> If the data size in subsequent import is very less, then you only need
>> to Create the index after first import and then let it continue like
>> that.
>
> Managing this has suddenly gotten a lot more complex. :)
>
>> > It also messes with the
>> > permission model that has been set up for the least-trusted section of
>> > the project (at the moment that section can only INSERT).
>>
>> As per your permission model Vacuum Full is allowed, but not Create index?
>
> Well, at the moment it's all in development (though time for that to end
> is coming up). As such I can do things with enhanced permissions manually.
>

I think if you are under development, it is always advisable to create
indexes after initial bulk load.  That way it will be faster and will
take lesser space atleast in case of hash index.

> When it hits production, that rather stops.
>
>> As mentioned above REINDEX might be a better option.  I think for such
>> situation we should have some provision to allow squeeze functionality
>> of hash exposed to the user, this will be less costly than REINDEX and
>> might serve the purpose for the user.  Hey, can you try some hack in
>
> Assuming it does help, would this be something one would need to guess
> at? "I did a whole bunch of concurrent INSERT heavy transactions so I
> guess I should do a squeeze now"?
>
> Or could it be figured out programmatically?
>

I think one can refer free_percent and number of overflow pages to
perform such a command.  It won't be 100% correct, but we can make a
guess.  We can even check free space in overflow pages with page
inspect to make it more accurate.

>> the code which can at least tell us whether squeezing hash can give us
>> any benefit or is the index in such a situation that only REINDEX will
>> help.  Basically, while doing Vacuum (non-full), you need to somehow
>> bypass the below line in lazy_scan_heap
>> lazy_scan_heap
>> {
>> ..
>> if (vacrelstats->num_dead_tuples > 0)
>> ..
>> }
>>
>> I am not sure it will work, but we can try once if you are okay.
>
> So this would be at L1284 of https://doxygen.postgresql.org/vacuumlazy_8c.html#a59a677fb19b0aae6a57c87ae073e081b ?
>
> Would turning it into if (1) and recompiling be the way to go?
>

Yes.

> Not sure if I'd be able to do this before or after the weekend.
>

No problem.


-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com