Обсуждение: 7.0.3(nofsync) vs 7.1
I've run tests (with 50 .. 250 simult users) for some PG project of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near 3 times slower than 7.0.3 (nofsync). It was not the best day in my life - WAL looked like big bottleneck -:( But finally I've realized that this test makes ~3 FK insertions ... and FK insert means SELECT FOR UPDATE ... and this could reduce # of commits per fsync. So, I've run simple test (below) to check this. Seems that 7.1 is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI triggers is quite bad for performance. Please take this into account when comparing 7.1 with 7.0.3. Also, we should add new TODO item: implement dirty reads and use them in RI triggers. Vadim ================================================================== Tables: PK (p int primary key), p in 1 .. 1000. FK (f int, foreign key(f) references pk(p)). First column below - # of users; second - # of FK inserts per user; next - what values were used in each insert: either unique (ie there was no users inserting same value - no waiters on SELECT FOR UPDATE on PK) or some random value from range. 7.1 50 1000 unique: 250 tps 100 1000 unique: 243 tps50 1000 rand(1 .. 10): 178 tps50 1000 rand(1 .. 5): 108 tps 7.0.3 (nofsync) 50 1000 unique: 157 tps50 1000 rand(1 .. 10): 154 tps50 1000 rand(1 .. 5): 154 tps
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> So, I've run simple test (below) to check this. Seems that 7.1
> is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI
> triggers is quite bad for performance.
> Also, we should add new TODO item: implement dirty reads
> and use them in RI triggers.
That would fix RI triggers, I guess, but what about plain SELECT FOR
UPDATE being used by applications?
Why exactly is SELECT FOR UPDATE such a performance problem for 7.1,
anyway? I wouldn't have thought it'd be a big deal...
regards, tom lane
> > So, I've run simple test (below) to check this. Seems that 7.1 > > is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI > > triggers is quite bad for performance. > > Also, we should add new TODO item: implement dirty reads > > and use them in RI triggers. > > That would fix RI triggers, I guess, but what about plain SELECT FOR > UPDATE being used by applications? What about it? Application normally uses exclusive row locks only when it's really required by application logic. Exclusive PK locks are not required for FK inserts by RI logic, we just have no other means to ensure PK existence currently. Keeping in mind that RI is used near in every application I would like to see this fixed. And ppl already complained about it. > Why exactly is SELECT FOR UPDATE such a performance problem for 7.1, > anyway? I wouldn't have thought it'd be a big deal... I have only one explanation: it reduces number of transactions ready to commit (because of the same FK writers will wait till first one committed - ie log fsynced) and WAL commit performance greatly depends on how many commits were done by single log fsync. 7.0.3+nofsync commit performance doesn't depend on this factor. Vadim
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> I have only one explanation: it reduces number of transactions ready
> to commit (because of the same FK writers will wait till first one
> committed - ie log fsynced) and WAL commit performance greatly depends
> on how many commits were done by single log fsync.
> 7.0.3+nofsync commit performance doesn't depend on this factor.
Sure, but that's not exactly a fair comparison is it? 7.0.3+nofsync
should be compared against 7.1+nofsync. I put the pg_fsync routine
back in a little while ago, so nofsync should work again.
It occurs to me though that disabling fsync should probably also reduce
the WAL commit delay to zero, no? What is the default commit delay now?
regards, tom lane
> > I have only one explanation: it reduces number of transactions ready > > to commit (because of the same FK writers will wait till first one > > committed - ie log fsynced) and WAL commit performance > > greatly depends on how many commits were done by single log fsync. > > 7.0.3+nofsync commit performance doesn't depend on this factor. > > Sure, but that's not exactly a fair comparison is it? 7.0.3+nofsync > should be compared against 7.1+nofsync. I put the pg_fsync routine > back in a little while ago, so nofsync should work again. But I tested old 7.1 (fsync) version -:) And we always will have to enable fsync when comparing our performance with other DBes. > It occurs to me though that disabling fsync should probably > also reduce the WAL commit delay to zero, no? What is the default I think so. > commit delay now? As before 5 * 10^(-6) sec - pretty the same as sleep(0) -:) Seems CommitDelay is not very useful parameter now - XLogFlush logic and fsync time add some delay. Vadim
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> And we always will have to enable fsync when comparing our
> performance with other DBes.
Of course, but when people say "it's slower than 7.0.3+nofsync"
I think that turning off fsync makes a fairer comparison there.
>> also reduce the WAL commit delay to zero, no? What is the default
> I think so.
>> commit delay now?
> As before 5 * 10^(-6) sec - pretty the same as sleep(0) -:)
> Seems CommitDelay is not very useful parameter now - XLogFlush
> logic and fsync time add some delay.
There was a thread recently about smarter ways to handle shared fsync
of the log --- IIRC, we talked about self-tuning commit delay, releasing
waiting processes as soon as someone else had fsync'd, etc. Looks like
none of those ideas are in the code now. Did you not like any of those
ideas, or just no time to work on it yet?
regards, tom lane
> I've run tests (with 50 .. 250 simult users) for some PG project > of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near > 3 times slower than 7.0.3 (nofsync). It was not the best day in > my life - WAL looked like big bottleneck -:( > > But finally I've realized that this test makes ~3 FK insertions > ... and FK insert means SELECT FOR UPDATE ... and this could > reduce # of commits per fsync. > > So, I've run simple test (below) to check this. Seems that 7.1 > is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI > triggers is quite bad for performance. > > Please take this into account when comparing 7.1 with 7.0.3. > Also, we should add new TODO item: implement dirty reads > and use them in RI triggers. > > Vadim I did some testings using contrib/pgbench (100k tuples, 32 concurrent users) with 7.1 and 7.0.3. It seems 7.1 is 5 times faster than 7.0.3 with fsync, but 1.5 times slower than 7.0.3 without fsync. So I modified access/transam/xlog.c to disable fsync() call at all. Now I get nearly equal performance as 7.0.3 without fsync. It seems the bottle neck is logging with fsync(). It might be interesting moving data/pg_xlog to a separate disk drive and see how it performs better. BTW pgbench does PK insertions and updates, but does no FK things. -- Tatsuo Ishii
At 16:27 9/12/00 +0900, Tatsuo Ishii wrote: >It might be interesting >moving data/pg_xlog to a separate disk drive and see how it performs >better. Good idea. One of the fundamental rules with Dec/RDB is to put the XLOG-equivant on a different drive from *any* database-related file. Another setting you might consider is 'fsync-every-n-commits'. The different drive provides enhanced recoverability as well as enhanced performance. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
[ Charset ISO-8859-1 unsupported, converting... ] > I've run tests (with 50 .. 250 simult users) for some PG project > of my company. 7.1 was 3 times faster than 7.0.3 (fsync) but near > 3 times slower than 7.0.3 (nofsync). It was not the best day in > my life - WAL looked like big bottleneck -:( > > But finally I've realized that this test makes ~3 FK insertions > ... and FK insert means SELECT FOR UPDATE ... and this could > reduce # of commits per fsync. > > So, I've run simple test (below) to check this. Seems that 7.1 > is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI > triggers is quite bad for performance. > > Please take this into account when comparing 7.1 with 7.0.3. > Also, we should add new TODO item: implement dirty reads > and use them in RI triggers. Added: * Implement dirty reads and use them in RI triggers -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> >> What is the default commit delay now? > > > As before 5 * 10^(-6) sec - pretty the same as sleep(0) -:) > > Seems CommitDelay is not very useful parameter now - XLogFlush > > logic and fsync time add some delay. > > There was a thread recently about smarter ways to handle shared fsync > of the log --- IIRC, we talked about self-tuning commit delay, > releasing waiting processes as soon as someone else had fsync'd, etc. > Looks like none of those ideas are in the code now. Did you not like > any of those ideas, or just no time to work on it yet? We're in beta - it's better to test WAL to find/fix bugs than make further improvements. Also, I've run test with 100 clients inserting records into 100 tables (to minimize contentions) - 915 tps with fsync and 1190 tps without fsync. So, we do ~ 18 commits per fsync now and probably we'll be able to increase commit performance by ~ 30%, no more. Vadim
Mikheev, Vadim wrote: > > > So, I've run simple test (below) to check this. Seems that 7.1 > > > is faster than 7.0.3 (nofsync), and that SELECT FOR UPDATE in RI > > > triggers is quite bad for performance. > > > Also, we should add new TODO item: implement dirty reads > > > and use them in RI triggers. > > > > That would fix RI triggers, I guess, but what about plain SELECT FOR > > UPDATE being used by applications? > > What about it? Application normally uses exclusive row locks only when > it's really required by application logic. > > Exclusive PK locks are not required for FK inserts by RI logic, > we just have no other means to ensure PK existence currently. > > Keeping in mind that RI is used near in every application I would > like to see this fixed. And ppl already complained about it. I still don't see how dirty reads can solve the RI problems. If Xact A deletes a PK while Xact B inserts an FK, one of them will either see the new reference or the PK gone. But from a transactional POV it depends on if the opposite Xact finally commits or not to tell if that really happened. With dirty read, you only get "maybe my PK is gone" or "maybe there is a reference". Jan > > > Why exactly is SELECT FOR UPDATE such a performance problem for 7.1, > > anyway? I wouldn't have thought it'd be a big deal... > > I have only one explanation: it reduces number of transactions ready > to commit (because of the same FK writers will wait till first one > committed - ie log fsynced) and WAL commit performance greatly depends > on how many commits were done by single log fsync. > 7.0.3+nofsync commit performance doesn't depend on this factor. > > Vadim > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> I still don't see how dirty reads can solve the RI problems. > If Xact A deletes a PK while Xact B inserts an FK, one of > them will either see the new reference or the PK gone. But > from a transactional POV it depends on if the opposite Xact > finally commits or not to tell if that really happened. > > With dirty read, you only get "maybe my PK is gone" or "maybe > there is a reference". Yes, and so we'll write special function(s) to check was PK really gone/FK inserted or not. This funcs will call XactLockTableWait(t_xmin|t_xmax) for questionable tuple to wait for concurrent transaction commit/rollback. It will work as long as we call RI triggers *after* INSERT/UPDATE/DELETE op, so triggers can see concurrent changes with dirty reads. Vadim