Re: WIP patch for hint bit i/o mitigation

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.

Suggestions/Feedback

With Regards,
Amit Kapila.