Multi-generational architecture (MGA) and record versioning

<< Request handles | Database technology articles | Multi-version concurrency control >>

tutorial available

Multi-generational architecture
(MGA) and record versioning

Database statistics
Analyzing transactions

Multi-generational architecture (MGA) and record versioning

InterBase^Ž introduced multi-generational architecture (MGA) as the term for its implementation of multiversion concurrency control.

Multiversion concurrency control (abbreviated MCC or MVCC) is the method used to prevent two or more users changing a single data set at the same time. It provides each user connected to the database with a "snapshot" of the database for that person to work with. Any changes made will not be seen by other users of the database until the transaction has been committed.

Firebird and InterBase^Ž implement this architecture using record versions. For example in dBase when a data set is altered, dBase overwrites the old version of the data set with the new in the database file. The old version of the data set is lost for ever. The Firebird server processes the data manipulation differently: when a data set is updated, Firebird creates a new data set, recording the differences between the original data set in its original state and the new updated content. And when a data set is deleted, Firebird also creates a new data set (flagged as deleted)! For the simple reason, if a mistake has been made and the transaction needs to be rolled back, the data set fully recovered.

These record versions are maintained by Firebird - parallel to the original data sets - until a COMMIT or ROLLBACK has been executed or until the server is restarted (when Firebird restarts it rolls back all active transactions).

But not just the active transactions are stored. For example: User A checks the bank balance ($1,500) makes a bank account withdrawal of $1,000. Just then the great-looking guy from the office next door rings and asks if she's free for lunch. User A drops everything and rushes out to lunch, forgetting to commit her transaction, thus leaving it open. In the meantime User B checks the bank balance (still $1,500) and withdraws $800, not forgetting to commit his transaction before he goes to lunch. User C likes to work through lunch, and whilst User A and B are out, he withdraws (bank balance now $700) respectively, $100, $200 and $300.

Not only is the record version for User A's active transaction stored. The 4 transactions made by Users B and C also have to be stored, because they were made after User A's transaction. In fact, all transactions which follow User A's cannot be completed and garbage collected until she has committed or rolled back her transaction. What if she and the "good-looker" fall so madly in love, they spontaneously decide to elope and never return to the office? It quite simply means that all record versions from this date on will remain on the database file as record versions, which will obviously soon start to slow performance considerably, unless someone finds her active transaction and rolls it back, or the server is restarted.

Database statistics

Poor or degrading database performance is practically always to do with poor programming and/or poor transaction handling.

Database statistics are an invaluable insight to what is actually happening on the server. Firebird statistics should be evaluated regularly and kept, because when things do go wrong, it's immensely helpful to be able to see what they looked like when thing went right.

The IBExpert Database Statistics display the following information for all tables in the database, both as a log script and in tabular form: table name, location, pages, size (bytes), slots, fill (%), DP usage (%) and fill distribution (an optimal page fill is around 80%). For each table the indices statistics include: depth, leaf buckets, nodes, average data length and fill distribution. Further information regarding these statistics can be found in the IBExpert Services menu item, Database Statistics.

Analyzing transactions

Under the oldest transaction we can see the oldest transaction number that cannot yet be garbage collected. To ensure efficient performance, the difference between this number and the next transaction number should be kept as small as possible. This depends of course on the number of users and database activity. For example, if you have 160 users working on one database, a difference of 3,000-5,000 is probably perfectly acceptable. However if there are only 2 users working on the database, you should be concerned if the difference between the oldest and next transaction is in the range of 3,000-5,000.

The fault can usually be found in the programming. For example a select query that's never committed or rolled back. One secure way of ensuring active transactions are rolled back is to temporarily disconnect any user, that has not actively used the application for the last half hour. There are great components on the market for this, e.g. FIBPlus and IBObjects.

By the way: the next transaction value may not exceed 1.4 billion. At the very latest at this stage you will need to do a backup and restore, as the restore sets all transactions back to zero. However, at an average rate of one transaction per second, it would take 130 years to reach this number, and even if 10 transactions a second are performed, it will take 13 years!

It's important to observe the degradation when things slow down. For example, running a select every second, and watching the prepare and execute time can be a good indicator. When this begins to slow, it's a premptive that something is wrong, and you will find within a few hours that the database will begin to slow, unless you find the source of the problem quick.

In daily usage, the oldest active transaction should not stay on a specific value for a long time, when the next transaction is constantly increasing.

If the oldest transaction is lower that the oldest active, use GFIX or any other tool for that matter, to sweep the database.