Re: Proposal: GetOldestXminExtend for ignoring arbitrary vacuum flags

From: "Seki, Eiji" <seki(dot)eiji(at)jp(dot)fujitsu(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, 'Michael Paquier' <michael(dot)paquier(at)gmail(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, 'Alvaro Herrera' <alvherre(at)2ndquadrant(dot)com>
Subject: Re: Proposal: GetOldestXminExtend for ignoring arbitrary vacuum flags
Date: 2017-02-16 05:24:25
Message-ID: A11BD0E1A40FAC479D740CEFA373E203396A5CF7@g01jpexmbkw05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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] -
[2] - (Sorry, there is Japanese document only)

Eiji Seki

Attachment Content-Type Size
image/png 26.3 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2017-02-16 05:33:46 Re: increasing the default WAL segment size
Previous Message Peter Eisentraut 2017-02-16 05:17:53 Re: ICU integration