Re: MultiXact\SLRU buffers configuration

On Sat, Jan 15, 2022 at 12:16:59PM +0500, Andrey Borodin wrote:

> > I was planning on running a set of stress tests on these patches. Could
> > we confirm which ones we plan to include in the commitfest?
> 
> Many thanks for your interest. Here's the  latest version.

Here are the results of the multixact perf test I ran on the patch that splits
the linear SLRU caches into banks.  With my test setup, the binaries
with the patch applied performed slower marginally across the test
matrix against unpatched binaries. Here are the results:

+-------------------------------+---------------------+-----------------------+------------+
|           workload            | patched average tps | unpatched average tps | difference |
+-------------------------------+---------------------+-----------------------+------------+
| create only                   |         10250.54396 |           10349.67487 | -1.0%      |
| create and select             |         9677.711286 |           9991.065037 | -3.2%      |
| large cache create only       |         10310.96646 |           10337.16455 | -0.3%      |
| large cache create and select |          9654.24077 |           9924.270242 | -2.8%      |
+-------------------------------+---------------------+-----------------------+------------+

The test was configured in the following manner:
- AWS EC2 c5d.24xlarge instances, located in the same AZ, were used as
  the database host and the test driver. These systems have 96 vcpus and
  184 GB memory. NVMe drives were configured as RAID5.
- GUCs were changed from defaults to be the following:
    max_connections = 5000
    shared_buffers = 96GB
    max_wal_size = 2GB
    min_wal_size = 192MB
- pgbench runs were done with -c 1000 -j 1000 and a scale of 10,000
- Two multixact workloads were tested, first [0] was a create only
  script which selected 100 pgbench_account rows for share. Second
  workload [1] added a select statement to visit rows touched in the
  past which had multixacts generated for them. pgbench test script [2]
  wraps the call to the functions inside an explicit transaction.
- Large cache tests are multixact offsets cache size hard coded to 128
  and members cache size hard coded to 256.
- Row selection is based on time based approach that lets all client
  connections coordinate which rows to work with based on the
  millisecond they start executing. To allow for more multixacts to be
  generated and reduce contention, the workload uses offsets ahead of
  the start id based on a random number.
- The one bummer about these runs were that they only ran for 600
  seconds for insert only and 400 seconds for insert and select. I
  consistently ran into checkpointer getting oom-killed on this instance
  after that timeframe. Will dig into this separately. But the TPS was 
  consistent.
- Each test was repeated at least 3 times and the average of those runs
  were used.
- I am using the master branch and changes were applied on commit
  f47ed79cc8a0cfa154dc7f01faaf59822552363f


I think patch 1 is a must-have. Regarding patch 2, I would propose we 
avoid introducing more complexity into SimpleLRU cache and instead focus 
on making the SLRU to buffer cache effort [3] a reality. I would also 
add that we have a few customers in our fleet who have been successfully 
running the large cache configuration on the regular SLRU without any 
issues. With cache sizes this small, the linear searches are still quite 
efficient.

If my test workload can be made better, please let me know. Happy to 
re-run tests as needed.


[0] https://gist.github.com/sdebnath/e015561811adf721dd40dd6638969c69
[1] https://gist.github.com/sdebnath/2f3802e1fe288594b6661a7a59a7ca07
[2] https://gist.github.com/sdebnath/6bbfd5f87945a7d819e30a9a1701bc97
[3] https://www.postgresql.org/message-id/CA%2BhUKGKAYze99B-jk9NoMp-2BDqAgiRC4oJv%2BbFxghNgdieq8Q%40mail.gmail.com



--
Shawn Debnath
Amazon Web Services (AWS)