pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedCSStorm occurred again by postgreSQL8.2. (Re: poor performance with Context Switch Storm at TPC-W.)
6+ messages / 4 participants
[nested] [flat]
* CSStorm occurred again by postgreSQL8.2. (Re: poor performance with Context Switch Storm at TPC-W.)
@ 2006-07-18 07:50 Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
0 siblings, 1 reply; 6+ messages in thread
From: Katsuhiko Okano @ 2006-07-18 07:50 UTC (permalink / raw)
To: pgsql-hackers
Katsuhiko Okano wrote:
> By PostgreSQL8.2, NUM_SUBTRANS_BUFFERS was changed into 128
> and recompile and measured again.
> NOT occurrence of CSStorm. The value of WIPS was about 400.
measured again.
not occurrence when measured for 30 minutes.
but occurrence when measured for 3 hours, and 1 hour and 10 minutes passed.
It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
The problem was only postponed.
> If the number of SLRU buffers is too low,
> also in PostgreSQL8.1.4, if the number of buffers is increased
> I think that the same result is brought.
> (Although the buffer of CLOG or a multi-transaction also increases,
> I think that effect is small)
>
> Now, NUM_SLRU_BUFFERS is changed into 128 in PostgreSQL8.1.4
> and is under measurement.
Occurrence CSStorm when the version 8.1.4 passed similarly for
1 hour and 10 minutes.
A strange point,
The number of times of a LWLock lock for LRU buffers is 0 times
until CSStorm occurs.
After CSStorm occurs, the share lock and the exclusion lock are required and
most locks are kept waiting.
(exclusion lock for SubtransControlLock is increased rapidly after CSStorm start.)
Is different processing done by whether CSStrom has occurred or not occurred?
regards,
--------
Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CSStorm occurred again by postgreSQL8.2. (Re: poor performance with Context Switch Storm at TPC-W.)
@ 2006-07-18 16:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-07-18 16:51 UTC (permalink / raw)
To: Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>; +Cc: pgsql-hackers
Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp> writes:
> It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
> The problem was only postponed.
Can you provide a reproducible test case for this?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CSStorm occurred again by postgreSQL8.2. (Re: poor performance with Context Switch Storm at TPC-W.)
@ 2006-07-19 10:37 Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Katsuhiko Okano @ 2006-07-19 10:37 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; pgsql-hackers; +Cc: okano.katsuhiko@oss.ntt.co.jp
"Tom Lane <tgl@sss.pgh.pa.us>" wrote:
> Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp> writes:
> > It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
> > The problem was only postponed.
>
> Can you provide a reproducible test case for this?
Seven machines are required in order to perform measurement.
(DB*1,AP*2,CLient*4)
Enough work load was not able to be given in two machines.
(DB*1,{AP+CL}*1)
It was not able to reappear to a multiplex run of pgbench
or a simple SELECT query.
TPC-W of a work load tool used this time is a full scratch.
Regrettably it cannot open to the public.
If there is a work load tool of a free license, I would like to try.
I will show if there is information required for others.
The patch which outputs the number of times of LWLock was used this time.
The following is old example output. FYI.
# SELECT * FROM pg_stat_lwlocks;
kind | pg_stat_get_lwlock_name | sh_call | sh_wait | ex_call | ex_wait | sleep
------+----------------------------+------------+-----------+-----------+-----------+-------
0 | BufMappingLock | 559375542 | 33542 | 320092 | 24025 | 0
1 | BufFreelistLock | 0 | 0 | 370709 | 47 | 0
2 | LockMgrLock | 0 | 0 | 41718885 | 734502 | 0
3 | OidGenLock | 33 | 0 | 0 | 0 | 0
4 | XidGenLock | 12572279 | 10095 | 11299469 | 20089 | 0
5 | ProcArrayLock | 8371330 | 72052 | 16965667 | 603294 | 0
6 | SInvalLock | 38822428 | 435 | 25917 | 128 | 0
7 | FreeSpaceLock | 0 | 0 | 16787 | 4 | 0
8 | WALInsertLock | 0 | 0 | 1239911 | 885 | 0
9 | WALWriteLock | 0 | 0 | 69907 | 5589 | 0
10 | ControlFileLock | 0 | 0 | 16686 | 1 | 0
11 | CheckpointLock | 0 | 0 | 34 | 0 | 0
12 | CheckpointStartLock | 69509 | 0 | 34 | 1 | 0
13 | CLogControlLock | 0 | 0 | 236763 | 183 | 0
14 | SubtransControlLock | 0 | 0 | 753773945 | 205273395 | 0
15 | MultiXactGenLock | 66 | 0 | 0 | 0 | 0
16 | MultiXactOffsetControlLock | 0 | 0 | 35 | 0 | 0
17 | MultiXactMemberControlLock | 0 | 0 | 34 | 0 | 0
18 | RelCacheInitLock | 0 | 0 | 0 | 0 | 0
19 | BgWriterCommLock | 0 | 0 | 61457 | 1 | 0
20 | TwoPhaseStateLock | 33 | 0 | 0 | 0 | 0
21 | TablespaceCreateLock | 0 | 0 | 0 | 0 | 0
22 | BufferIO | 0 | 0 | 695627 | 16 | 0
23 | BufferContent | 3568231805 | 1897 | 1361394 | 829 | 0
24 | CLog | 0 | 0 | 0 | 0 | 0
25 | SubTrans | 138571621 | 143208883 | 8122181 | 8132646 | 0
26 | MultiXactOffset | 0 | 0 | 0 | 0 | 0
27 | MultiXactMember | 0 | 0 | 0 | 0 | 0
(28 rows)
I am pleased if interested.
regards,
--------
Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CSStorm occurred again by postgreSQL8.2. (Re: poor
@ 2006-07-19 10:53 Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
parent: Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
0 siblings, 1 reply; 6+ messages in thread
From: Stefan Kaltenbrunner @ 2006-07-19 10:53 UTC (permalink / raw)
To: Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>; +Cc: tgl@sss.pgh.pa.us; pgsql-hackers
Katsuhiko Okano wrote:
> "Tom Lane <tgl@sss.pgh.pa.us>" wrote:
>> Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp> writes:
>>> It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
>>> The problem was only postponed.
>> Can you provide a reproducible test case for this?
>
> Seven machines are required in order to perform measurement.
> (DB*1,AP*2,CLient*4)
> Enough work load was not able to be given in two machines.
> (DB*1,{AP+CL}*1)
>
>
> It was not able to reappear to a multiplex run of pgbench
> or a simple SELECT query.
> TPC-W of a work load tool used this time is a full scratch.
> Regrettably it cannot open to the public.
> If there is a work load tool of a free license, I would like to try.
FYI: there is a free tpc-w implementation done by Jan available at:
http://pgfoundry.org/projects/tpc-w-php/
Stefan
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CSStorm occurred again by postgreSQL8.2. (Re: poor
@ 2006-07-19 11:11 Masanori ITOH <ito.masanori@oss.ntt.co.jp>
parent: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
0 siblings, 1 reply; 6+ messages in thread
From: Masanori ITOH @ 2006-07-19 11:11 UTC (permalink / raw)
To: stefan@kaltenbrunner.cc; +Cc: okano.katsuhiko@oss.ntt.co.jp; tgl@sss.pgh.pa.us; pgsql-hackers; ito.masanori@oss.ntt.co.jp
Hi folks,
From: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
Subject: Re: CSStorm occurred again by postgreSQL8.2. (Re: [HACKERS] poor
Date: Wed, 19 Jul 2006 12:53:53 +0200
> Katsuhiko Okano wrote:
> > "Tom Lane <tgl@sss.pgh.pa.us>" wrote:
> >> Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp> writes:
> >>> It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
> >>> The problem was only postponed.
> >> Can you provide a reproducible test case for this?
> >
> > Seven machines are required in order to perform measurement.
> > (DB*1,AP*2,CLient*4)
> > Enough work load was not able to be given in two machines.
> > (DB*1,{AP+CL}*1)
> >
> >
> > It was not able to reappear to a multiplex run of pgbench
> > or a simple SELECT query.
> > TPC-W of a work load tool used this time is a full scratch.
> > Regrettably it cannot open to the public.
> > If there is a work load tool of a free license, I would like to try.
>
>
> FYI: there is a free tpc-w implementation done by Jan available at:
> http://pgfoundry.org/projects/tpc-w-php/
FYI(2):
There is one more (pseudo) TPC-W implementation by OSDL.
http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/
One more comment is that Katsuhiko't team is using their own version of
TPC-W like benchmark suite, and he cannot make it public.
Also, his point is that he tried to reproduce the CSS phenomena using
pgbench and a proguram issuing heavily multiple SELECT queries
on a single table but they didn't work well reproducing CSS.
Regards,
Masanori
> Stefan
---
Masanori ITOH NTT OSS Center, Nippon Telegraph and Telephone Corporation
e-mail: ito.masanori@oss.ntt.co.jp
phone : +81-3-5860-5015
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CSStorm occurred again by postgreSQL8.2. (Re: poor
@ 2006-07-20 10:02 Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
parent: Masanori ITOH <ito.masanori@oss.ntt.co.jp>
0 siblings, 0 replies; 6+ messages in thread
From: Katsuhiko Okano @ 2006-07-20 10:02 UTC (permalink / raw)
To: pgsql-hackers
> > > If there is a work load tool of a free license, I would like to try.
> >
> >
> > FYI: there is a free tpc-w implementation done by Jan available at:
> > http://pgfoundry.org/projects/tpc-w-php/
>
> FYI(2):
>
> There is one more (pseudo) TPC-W implementation by OSDL.
>
> http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/
Thank you for the information.
I'll try it.
Regards,
--------
Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-07-20 10:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-18 07:50 CSStorm occurred again by postgreSQL8.2. (Re: poor performance with Context Switch Storm at TPC-W.) Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
2006-07-18 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-19 10:37 ` Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
2006-07-19 10:53 ` Re: CSStorm occurred again by postgreSQL8.2. (Re: poor Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2006-07-19 11:11 ` Re: CSStorm occurred again by postgreSQL8.2. (Re: poor Masanori ITOH <ito.masanori@oss.ntt.co.jp>
2006-07-20 10:02 ` Re: CSStorm occurred again by postgreSQL8.2. (Re: poor Katsuhiko Okano <okano.katsuhiko@oss.ntt.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox