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
sequentialscan 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
randomrecords 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
usingindex scan for 5
 
min  - 8 threads.         

Test case -5:    Check original pgbench performance & vacuum operation    1. For  select only and tcp_b  performance
suiteswith 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;
Vacuumthe table                                        Checkpoint;                3. Record the IO statistics & time
takenfor 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
allthe 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.