> Hope this helps,
> Ann

It has helped tremendously in furthering my understanding. You are an
extremely valuable resource, and I just hope you are getting some reward
for it.


So, considering that this:

> > For instance, why might I see the following?
> > Oldest transaction 2896976
> > Oldest active 2896977
> > Oldest snapshot 2853644
> > Next transaction 2897181
> I don't understand how the oldest snapshot could be more than forty
> thousand older than the oldest active

Is the same database as this:

> > Average version length: 14.99, total versions: 4327, max
> > versions: 51
> A max version of 51 is breathtaking.

I guess I have a problem here. My guess is that this has been
introduced as the result of an abnormal termination. We have 2 database
servers each with about 12 actively used databases, and several dozen
that are only occasionally used. Over the last several years, we have
periodically seen 100% processor spikes on either of these servers.
Maybe about once per 4-12 weeks? We have tried shuffling around
databases between the 2 servers to try and figure out through trial and
error which application might be responsible for the processor spikes,
but we never knew enough about the database's internals to really
diagnose the cause.

If I had to guess, I'd say 15% of the time the server was allowed to
come back from 100% CPU itself, 30-40% of the time it was restarted
'nicely' using ibmgr -shut (at least, I'm assuming this is considered a
"safe" way to shutdown), and 45-55% of the time it was restarted poorly
via kill -9. I'm not condoning that, and I'm not entirely responsible
for it, but that is the facts.

So, I'm assuming one of those kill -9s could have caused the very old
limbo snapshot, yes? And those 51 versions are waiting around for that
40,000 transaction-old snapshot. If so, what do I do now? Should a
backup and restore set things right? Any special flags I will want
during the backup/restore?

Now that I know what the numbers mean, I can setup some scripts to
monitor them over time and we will eventually root out the cause of our
processor spikes.

-Kevin

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Thursday, June 16, 2005 12:09
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support]

Kevin Hamilton wrote:

>
> 1) In a gstat -h, what is the difference between "Oldest Active" and
> "Oldest snapshot"?

The oldest active is the oldest transaction that was active when any
currently active transaction started. That's important because versions

created by that transaction and newer transactions are visible to some
active transaction and can not be garbage collected.


The oldest snapshot is a a problem. We' spent about two weeks tracing

it through the code on the devel list, and it appears that when the
oldest active is updated, the previous value for the oldest active
becomes the oldest snapshot. I don't know why it's kept or why it's
used instead of the oldest-active to limit garbage collection. Normally

it lags the oldest active by two or three transactions and is not a
problem. The stats you provided below, however, are really troubling...

> For instance, why might I see the following?
> Oldest transaction 2896976
> Oldest active 2896977
> Oldest snapshot 2853644
> Next transaction 2897181

I don't understand how the oldest snapshot could be more than forty
thousand older than the oldest active. Sometimes that sort of thing is
a bug in the reporting program... (she says, clutching at straws).

> 2) Oldest transaction means "oldest interesting transaction"

Yes.

> and is used for auto-sweeping, right?

No. The oldest interesting transaction is the oldest transaction that
did not commit. Every running transaction must look for data created by

uncommitted transactions - if those transactions rolled back, the
current transaction will remove their changes. If the transaction that
created the data is active, the current transaction will read a back
version.

> a) I don't need to be concerned if the Oldest Transaction is very
> old, do I? Like the following?
> Oldest transaction 38879
> Oldest active 293593
> Oldest snapshot 293585
> Next transaction 293606

That's not a serious problem - on old machines with tiny memory, keeping

an array of two bits per transaction for three hundred thousand
transactions was a problem. Sweeping from time to time will move the
oldest interesting transaction along. As long as all the changes made
by a transaction are removed, it's state can be reset to committed.
That's what sweep does.

> 3) In a gstat -r -a, what is the difference between "total versions"

and

> "max versions"?

Easy. The total versions is the total number of back versions of
records in that table. The max versions is the largest number of back
versions of any single record. When you seen one record with 30 back
versions, you know you've got a hot-spot or seriously delayed garbage
collection, or probably both.

> a) Does the average version length tell me anything useful?

It tells you how much space is being used by back versions - and is an
indication of the type of changes made. Firebird stores back versions
as deltas when possible. A delta isn't a full record, but instructions
that tell the system how to transform the current record into its
previous state. For example, start at byte 23 and replace the next 12
bytes with zeros. There are three cases where deltas aren't used.

1) when the size of the delta is longer than the size of the full record
2) when the delta would be longer than 255 bytes (that's a mistake and
ought to be changed at some point - the size was picked in 1985 and
things have changed.
3) when a transaction modifies a single record twice. That's a long
story.

> b) I was under the impression that having an old "Oldest Snapshot"
> would mean I would also have a high max versions, but the database

cited

> in (1) doesn't seem to.

It depends on the activity level. If your database is all insert/read
and never has a rollback, you'll never have any back versions.

> Here's the only interesting lines from that
> database:
> TABLE_Q
> Average version length: 14.99, total versions: 4327, max
> versions: 51

A max version of 51 is breathtaking. Fifty-one transactions have
modified a single record, and there's still some transaction around that

needs the oldest version. Is it a gateway record?

> TABLE_X
> Average version length: 42.16, total versions: 650, max

versions:

> 1

no problem.

>
> 4) What is the difference between total dup, and max dup for indexes?

Same as above, total is the aggregate for the index, max is the max for
any single key value.

> I had a high max dup on the tables cited in (3b), so I did a compound
> index with the primary key. But I still have a high "total dup"?

Gstat will report duplicates on unique indexes - that's one of the
oddities of multi-generational indexes. No one transaction can see more

than one instance of each value, but different transactions can see
different instances. I wouldn't expect a lot of duplicates, but quite
possibly a record is being deleted, then another with the same key value

is created over and over ....

> INDEX_Q
> Average data length: 0.00, total dup: 2379, max dup: 28
> INDEX_X
> Average data length: 0.00, total dup: 29593, max dup: 32

Average data length shows that prefix compression is working well, and
that there's a high percentage of duplicate values. Neither of the max
dup values are in the critical range.

Hope this helps,


Ann



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links








CONFIDENTIALITY NOTICE:
This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information.
Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.