Hi,
I have been studying the basic limitation that the number of committed
transactions per second possible in a relational databases. Since
each transaction requires at least write-ahead log data to be flushed
to disk the upper bound of transactions per second is equal to the
number of independent disk writes possible per second. Most of what I
know is from performance docs of PostgreSQL and MySQL.
Its often possible to increase the total transaction processing speed
by turning off the compulsory disc syncing at each commit, which means
that in the case of system failure some transactions may be lost *but*
the database would still be consistent if we are careful to make sure
the log is always written first.
I observed that in in many applications there are some transactions
that are more critical than others. I may have the same database
instance managing website visitor accounting and financial
transactions. I could tolerate the loss of a few transactions whose
only job is to tell me a user has clicked a page on my website but
would not dare risk this for any of the "real" financials work my
web-based app is doing.
In the case of bulk inserts, also, or in some special cases I might be
able to code around the need for guaranteed *durability* on
transaction commit as long as the database is consistent.
So I want to ask, "what is databases have a 'COMMIT NOSYNC;' option?"
Then we can really improve "transaction-per-second" performance for a
database that has lots of non-critical transactions while not
jeopardising the durability of critical transactions in the
(relatively unlikely) case of system failure. Primarily through
combining the log updates for several non-critical transactions.
COMMIT; --> COMMIT SYNC; (guarantees atomic, consistent, durable
write)
COMMIT NOSYNC; --> (sacrifice durability of non-critical transaction
for overall speed). So, the question is what people, especially those
who have done DBMS work, think about this!
Seun Osewa.