Re: Sequence Question DOH!

Oscar Tuscon <obtuse@bmwe30.net> writes:

I found the average select nextval() call was taking 2ms, which seems
a bit slow to me. Throw in the fsync I suppose and that'd explain it.

Interestingly, in the tests I ran the minimum select nextval() was
400us, and the max was 35ms, with an average of 2ms.

I believe that nextval() only actually writes to disk once every 32
calls. This probably explains the variability.

regards, tom lane

Oscar Tuscon wrote:

Not sure how I managed to interchange Greg's reply and my response,
but here's mine again:

Oscar - if you're still interested in grabbing variable ranges of
sequence-id's then I had an idea. Just multiply nextval() by 1000 (or
whatever) and use however many you need. You'll want to set the maximum
for the sequence correspondingly lower.

--
Richard Huxton
Archonet Ltd

Oscar - if you're still interested in grabbing variable ranges of
sequence-id's then I had an idea. Just multiply nextval() by 1000 (or
whatever) and use however many you need. You'll want to set the maximum
for the sequence correspondingly lower.
--
Richard Huxton
Archonet Ltd

Thanks Richard,
I'd considered using an approach as you suggest, but I need long term high volume scalability and don't want to waste sequence values. Not that 2**64 will happen anytime soon...

What I did was to use my existing shm subsystem, which controls accesses with semaphores, and allocated a lock obj. I just modified my sequence accessor (which was already abstracted to keep db independence) to lock/unlock around sequence nextval/setval. Since the majority of my sequence requirements are for batches I wound up with about a 30% net app performance improvement (and about 500x in the sequence accessing!)

Oscar

_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net