Re: Proposal: GetOldestXminExtend for ignoring arbitrary vacuum flags

Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Please persuade us with measurements that allowing this impact on
> ANALYZE would really improve performance at least in your case, and
> also examine the effect of this on the accuracy and usefulness of the
> gathered statistics.

I explain results of the test that Haribabu mentioned in [1].

The purpose of this test is the followings.

- Check the effect of VCI to OLTP workload
- Check whether VCI can be used in OLAP query
even if there is OLTP workload at a same table

The test is done as the followings.

- Use the Tiny TPC-C [2] benchmark as OLTP workload
- Scale factor: 100
- Create VCI on the 'stock' table before starting benchmark
- Planner doesn't select VCI for queries of the Tiny TPC-C.

I attach the result graph.

This graph indicates the transition of the number of rows in WOS. In our environment, when the WOS size exceeds about 700,000, VCI is no longer used as such the following query.

select count(*) from stock where s_order_cnt > 4;

While in low load ("Number of clients = 10" line, the throughput was about 1,000) the WOS size didn't exceed about 500,000, in high load ("Number of clients = 30 (Without patch)" line, the throughput was about 1,400) the WOS size frequently exceeded 700,000.

While the WOS size continued to increase, ANALYZE only (without VACUUM) process created by autovacuum daemon always ran and conversion process from WOS to ROS didn't run. Then, after changing to ignore ANALYZE only processes using my patch, the WOS size no longer exceeded about 500,000 ("Number of clients = 30 (With patch)" line, the throughput was about 1,400).

Please let me know if you need any further information.

[1] - https://www.postgresql.org/message-id/CAJrrPGen1bJYRHu7VFp13QZUyaLdX5N4AH1cqQdiNd8uLVZWow%40mail.gmail.com
[2] - http://hp.vector.co.jp/authors/VA052413/jdbcrunner/manual_ja/tpc-c.html (Sorry, there is Japanese document only)

--
Regards,
Eiji Seki
Fujitsu