pgstat wait timeout (RE: contrib/cache_scan) - Mailing list pgsql-hackers

It is another topic from the main thread,

I noticed the following message under the test cases that
takes heavy INSERT workload; provided by Haribabu.

[kaigai@iwashi ~]$ createdb mytest
[kaigai@iwashi ~]$ psql -af ~/cache_scan.sql mytest
\timing
Timing is on.
--cache scan select 5 million
create table test(f1 int, f2 char(70), f3 float, f4 char(100));
CREATE TABLE
Time: 22.373 ms
truncate table test;
TRUNCATE TABLE
Time: 17.705 ms
insert into test values (generate_series(1,5000000), 'fujitsu', 1.1, 'Australia software tech pvt ltd');
WARNING:  pgstat wait timeout
WARNING:  pgstat wait timeout
WARNING:  pgstat wait timeout
WARNING:  pgstat wait timeout  :

Once I got above messages, write performance is dramatically
degraded, even though I didn't take detailed investigation.

I could reproduce it on the latest master branch without my
enhancement, so I guess it is not a problem something special
to me.
One other strangeness is, right now, this problem is only
happen on my virtual machine environment - VMware ESXi 5.5.0.
I couldn't reproduce the problem on my physical environment
(Fedora20, core i5-4570S).
Any ideas?

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>


> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Kouhei Kaigai
> Sent: Wednesday, March 12, 2014 3:26 PM
> To: Haribabu Kommi; Kohei KaiGai
> Cc: Tom Lane; PgHacker; Robert Haas
> Subject: Re: contrib/cache_scan (Re: [HACKERS] What's needed for cache-only
> table scan?)
>
> Thanks for your efforts!
> >                                          Head          patched
> > Diff
> > Select -  500K                772ms        2659ms        -200%
> > Insert - 400K                   3429ms     1948ms          43% (I am
> > not sure how it improved in this case)
> > delete - 200K                 2066ms     3978ms        -92%
> > update - 200K                3915ms      5899ms        -50%
> >
> > This patch shown how the custom scan can be used very well but coming
> > to patch as It is having some performance problem which needs to be
> > investigated.
> >
> > I attached the test script file used for the performance test.
> >
> First of all, it seems to me your test case has too small data set that
> allows to hold all the data in memory - briefly 500K of 200bytes record
> will consume about 100MB. Your configuration allocates 512MB of
> shared_buffer, and about 3GB of OS-level page cache is available.
> (Note that Linux uses free memory as disk cache adaptively.)
>
> This cache is designed to hide latency of disk accesses, so this test case
> does not fit its intention.
> (Also, the primary purpose of this module is a demonstration for
> heap_page_prune_hook to hook vacuuming, so simple code was preferred than
> complicated implementation but better performance.)
>
> I could reproduce the overall trend, no cache scan is faster than cached
> scan if buffer is in memory. Probably, it comes from the cost to walk down
> T-tree index using ctid per reference.
> Performance penalty around UPDATE and DELETE likely come from trigger
> invocation per row.
> I could observe performance gain on INSERT a little bit.
> It's strange for me, also. :-(
>
> On the other hand, the discussion around custom-plan interface effects this
> module because it uses this API as foundation.
> Please wait for a few days to rebase the cache_scan module onto the newer
> custom-plan interface; that I submitted just a moment before.
>
> Also, is it really necessary to tune the performance stuff in this example
> module of the heap_page_prune_hook?
> Even though I have a few ideas to improve the cache performance, like
> insertion of multiple rows at once or local chunk copy instead of t-tree
> walk down, I'm not sure whether it is productive in the current v9.4
> timeframe. ;-(
>
> Thanks,
> --
> NEC OSS Promotion Center / PG-Strom Project KaiGai Kohei
> <kaigai@ak.jp.nec.com>
>
>
> > -----Original Message-----
> > From: pgsql-hackers-owner@postgresql.org
> > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Haribabu
> > Kommi
> > Sent: Wednesday, March 12, 2014 1:14 PM
> > To: Kohei KaiGai
> > Cc: Kaigai Kouhei(海外 浩平); Tom Lane; PgHacker; Robert Haas
> > Subject: Re: contrib/cache_scan (Re: [HACKERS] What's needed for
> > cache-only table scan?)
> >
> > On Thu, Mar 6, 2014 at 10:15 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
> > > 2014-03-06 18:17 GMT+09:00 Haribabu Kommi <kommi.haribabu@gmail.com>:
> > >> I will update you later regarding the performance test results.
> > >>
> >
> > I ran the performance test on the cache scan patch and below are the
> readings.
> >
> > Configuration:
> >
> > Shared_buffers - 512MB
> > cache_scan.num_blocks - 600
> > checkpoint_segments - 255
> >
> > Machine:
> > OS - centos - 6.4
> > CPU - 4 core 2.5 GHZ
> > Memory - 4GB
> >
> >                                          Head          patched
> > Diff
> > Select -  500K                772ms        2659ms        -200%
> > Insert - 400K                   3429ms     1948ms          43% (I am
> > not sure how it improved in this case)
> > delete - 200K                 2066ms     3978ms        -92%
> > update - 200K                3915ms      5899ms        -50%
> >
> > This patch shown how the custom scan can be used very well but coming
> > to patch as It is having some performance problem which needs to be
> > investigated.
> >
> > I attached the test script file used for the performance test.
> >
> > Regards,
> > Hari Babu
> > Fujitsu Australia
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers