Re: WIP patch for hint bit i/o mitigation - Mailing list pgsql-hackers

On Thu, Dec 6, 2012 at 3:59 AM, Amit Kapila <amit.kapila@huawei.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