Re: WIP patch for hint bit i/o mitigation

On Thu, Dec 6, 2012 at 3:59 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com> wrote:
> On Thursday, November 22, 2012 3:00 AM Greg Smith wrote:
>> On 11/16/12 9:03 AM, Merlin Moncure wrote:
>> > Atri ran some quick n dirty tests to see if there
>> > were any regressions. He benched a large scan followed by vacuum. So
>> > far, results are inconclusive so better testing methodologies will
>> > definitely be greatly appreciated. One of the challenges with working
>> > in this part of the code is that it's quite difficult to make changes
>> > without impacting at least some workloads.
>>
>> I'm adding something to pgbench-tools to test for some types of vacuum
>> regressions that I've seen before. And the checksum benchmarking I've
>> already signed up for overlaps with this one quite a bit. I'd suggest
>> reviewers here focus on code quality, correctness, and targeted
>> optimization testing. I'm working heavily on a larger benchmarking
>> framework that both this and checksums will fit into right now.
>
> We are planning below performance tests for hint-bit I/O mitigation patch:
>
> Test case -1: Select performance in sequential scan and vacuum operation
> with I/O statistics
> Bulk operations are happened in multiple transactions.
> 1. Stop the auto-vacuum.
> 2. Create table
> 3. Insert 10000 records in one transaction for 1000 times.
> 4A. Use pgbench to select all the records using sequential scan for 5
> min - 8 threads.
> 4B. Record the IO statistics.
> 5. After completion of test-case check VACUUM performance.
>
> Test case -2:
> Select performance in index scan and vacuum operation with I/O
> statistics
> Same as testcase - 1 change the 4A as follows
> 4A. Use pgbench with -F option to select random records using
> index scan for 5 min - 8 threads.
>
> Test case -3:
> Select performance in sequential scan and vacuum operation with I/O
> statistics
> When bulk operations are happened in one transaction.
> 1. Stop the auto-vacuum.
> 2. Create table
> 3. Insert 10,000,000 times.
> 4A. Use pgbench to select all the records using sequential scan for 5
> min - 8 threads.
> 4B. Record the IO statistics.
> 5. After completion of test-case check VACUUM performance.
>
> Test case -4:
> Select performance in index scan and vacuum operation with I/O
> statistics
> Same as testcase - 3 change the 4A as follows
> 4A. Use pgbench to select random records using index scan for 5
> min - 8 threads.
>
> Test case -5:
> Check original pgbench performance & vacuum operation
> 1. For select only and tcp_b performance suites with scale factor of
> 75 & 150, threads 8 &16
>
> Test case -6:(Vacuum I/O may increase if vacuum need to make the page dirty
> only for setting the hit bits. )
> 1. Session - 1 : Open a some long transaction
>
> 2. Session - 2 : Insert some records & commit
> Do the select - all the tuples.
> Checkpoint;
> Vacuum the table
> Checkpoint;
> 3. Record the IO statistics & time taken for Vacuum & 2nd
> Checkpoint.
>
> Test case -7: (This is also to check Vacuum I/O)
> 1. Have replication setup.
> 2. Insert some records & commit
> 3. Vacuum the table
> 4. Upgrade the standby.
> 5. Select the all the tuples on new master
> 6. Vacuum the tbl on new master.
> 6B. Record the IO statistics & time taken for Vacuum on new
> master.

Thanks for that -- that's fairly comprehensive I'd say. I'm quite
interested in that benchmarking framework as well. Do you need help
setting up the scripts?

merlin