On 09/09/16 07:09, Jeff Janes wrote:

On Wed, Sep 7, 2016 at 3:29 AM, Ashutosh Sharma <ashu.coe...@gmail.com <mailto:ashu.coe...@gmail.com>> wrote:

    > Thanks to Ashutosh Sharma for doing the testing of the patch and
    > helping me in analyzing some of the above issues.

    Hi All,

    I would like to summarize the test-cases that i have executed for
    validating WAL logging in hash index feature.

    1) I have mainly ran the pgbench test with read-write workload at the
    scale factor of 1000 and various client counts like 16, 64 and 128 for
    time duration of 30 mins, 1 hr and 24 hrs. I have executed this test
    on highly configured power2 machine with 128 cores and 512GB of RAM. I
    ran the test-case both with and without the replication setup.

    Please note that i have changed the schema of pgbench tables created
    during initialisation phase.

    The new schema of pgbench tables looks as shown below on both master
    and standby:

    postgres=# \d pgbench_accounts
       Table "public.pgbench_accounts"
      Column  |     Type      | Modifiers
    ----------+---------------+-----------
     aid      | integer       | not null
     bid      | integer       |
     abalance | integer       |
     filler   | character(84) |
    Indexes:
        "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
        "pgbench_accounts_bid" hash (bid)

    postgres=# \d pgbench_history
              Table "public.pgbench_history"
     Column |            Type             | Modifiers
    --------+-----------------------------+-----------
     tid    | integer                     |
     bid    | integer                     |
     aid    | integer                     |
     delta  | integer                     |
     mtime  | timestamp without time zone |
     filler | character(22)               |
    Indexes:
        "pgbench_history_bid" hash (bid)


Hi Ashutosh,

This schema will test the maintenance of hash indexes, but it will never use hash indexes for searching, so it limits the amount of test coverage you will get. While searching shouldn't generate novel types of WAL records (that I know of), it will generate locking and timing issues that might uncover bugs (if there are any left to uncover, of course).

I would drop the primary key on pgbench_accounts and replace it with a hash index and test it that way (except I don't have a 128 core machine at my disposal, so really I am suggesting that you do this...)

The lack of primary key and the non-uniqueness of the hash index should not be an operational problem, because the built in pgbench runs never attempt to violate the constraints anyway.

In fact, I'd replace all of the indexes on the rest of the pgbench tables with hash indexes, too, just for additional testing.

I plan to do testing using my own testing harness after changing it to insert a lot of dummy tuples (ones with negative values in the pseudo-pk column, which are never queried by the core part of the harness) and deleting them at random intervals. I think that none of pgbench's built in tests are likely to give the bucket splitting and squeezing code very much exercise.

Is there a way to gather statistics on how many of each type of WAL record are actually getting sent over the replication link? The only way I can think of is to turn on wal archving as well as replication, then using pg_xlogdump to gather the stats.

I've run my original test for a while now and have not seen any problems. But I realized I forgot to compile with enable-casserts, to I will have to redo it to make sure the assertion failures have been fixed. In my original testing I did very rarely get a deadlock (or some kind of hang), and I haven't seen that again so far. It was probably the same source as the one Mark observed, and so the same fix.

Cheers,

Jeff

Yeah, good suggestion about replacing (essentially) all the indexes with hash ones and testing. I did some short runs with this type of schema yesterday (actually to get a feel for if hash performance vs btree was compareable - does seem tp be) - but probably longer ones with higher concurrency (as high as I can manage on a single socket i7 anyway) is a good plan. If Ashutosh has access to seriously large numbers of cores then that is even better :-)

Cheers

Mark


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to