In the last few days I've been collaborating with Sergio Lopez at
Nologin in Spain (http://www.nologin.es/portal/en/about+nologin) on an
informal benchmark using Ingres. Sergio is primarily interested in
PostgreSQL and Oracle, but very knowledgeable about Ingres too.

To cut a long story short, in his benchmark I have been able to
increase the performance of Ingres by a factor of 10 by making a few
simple configuration changes and structuring the tables appropriately.

The benchmark is very loosely based on the (infamous) TPC-C benchmark,
and involves running a number of concurrent clients executing a mix of 5
different OLTP-type transactions.

Three of the most effective changes I made were: relaxing the system
isolation level to READ COMMITTED; increasing system_maxlocks to 100,
and making all but two of the tables ISAM. (The remaining two I made
into hash tables.)

This highlights something that I've wondered about for years. Why on
earth is the MAXLOCKS threshold still at 10 after almost 25 years?!

And why, when almost every other DBMS in the world defaults to an
isolation level of READ COMMITTED, does Ingres default to SERIALIZABLE?
(Only Sybase AFAIK comes close, defaulting to REPEATABLE READ.)

Is there any good reason I shouldn't suggest an enhancement to these
seemingly very silly defaults? (I know the SQL standard calls for
SERIALIZABLE isolation by default, but the standard calls for a number
of silly things that Ingres doesn't do.)

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Roy,

system_maxlocks has had a default of 50 since 9.x (so we are half way there
). Avoiding escalation is vital as it consumes a lot of resources and
table level locking effectively serializes processing.
The problem with tinkering with the default isolation level is that the
implications of not using SERIALIZABLE need to be understood. READ COMMITTED
allows non-repeatable reads and phantom rows, both of which could have
implications on the quality and accuracy of data being handled by
applications. I routinely tell students in training and customers how bad
READ UNCOMMITTED (READLOCK=NOLOCK) could be for the quality of their data.

So whilst I am in favour of improving the out-of-the-box defaults, and all
sugestions are welcome, we must make sure the appropriate warnings are in
place as well.

Peter

2009/2/26 Roy Hann <specially (AT) processed (DOT) almost.meat>



--
Peter Gale
pgale61 (AT) gmail (DOT) com

Peter Gale wrote:

You and I have both been banging on about that for as long as I can
remember. Much joy it has brought us! :-)

I have no problem with warnings, but to repeat, Ingres is the only DBMS
(AFAIK) that defaults to SERIALIZABLE, so therefore it behaves contrary
to the expectations of virtually everyone who tries it out for the
first time, and by doing so it takes a ~10-fold performance penalty in
simple-minded tests. SERIALIZABLE certainly doesn't make sense as the
default. Evidently no one (Oracle, IBM, Microsoft, Sun, etc.) thinks
even REPEATABLE READ is a good default either. I think they're
wrong, but it's what they do.

Sadly this seems to be one of those classic Ingres decisions to do it
right that gets it "wrong" by being right.

Perhaps SERIALIZABLE isolation should be made one of the optional
strict SQL92-compliance features, which is where it really belongs since
it has adverse consequences even though the standard does call for it,
or it could be a separate question at installation time with the same
justification as the strict SQL92-compliance question.

There's a lot more to making the right decision about which DBMS to
choose than just speed, but unfortunately speed is easy to test so
people do.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

I vote for a separate question at installation time - changing the
default silently could be dangerous for existing applications.

Kristoff

Kristoff wrote:

I take your point, but the number of existing Ingres applications that
don't already defeat SERIALIZABLE isolation by blithely doing "SET
LOCKMODE WHERE READLOCK=NOLOCK" is tiny. And no application
ported to Ingres from some other DBMS would ever expect default
SERIALIZABLE isolation.

But no matter; if a separate question is feasible then it would do the
job just fine, so it gets my vote too.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.