>>>>> Is there a way to completely regenerate all indices in a database
>>>>> without running through the full backup/restore cycle? It's been my
>>>>> experience that over months or years, the database keeps getting slower
>>>>> and slower, but a backup and immediate restore fix it to run at
>>>>> full-speed again. I would like to achieve the same result without having
>>>>> to stop all other work. What I tried so far:
>>>>>
>>>> Are you sure that the index stuff is your problem and not transaction
>>>> related? What's the output of gstat -h at the time you think the
>>>> database is getting slow?
>>>
>>> No, I am not sure. I will try to get gstat -h for the slow database (I
>>> can't do it right away as I am only getting backups from my users, not
>>> the production databases themselves). What should I be looking for?
>>
>> Simply post the output here and someone gets a hold on that and
>> discusses the result.
>
> Here goes. Server is Firebird 1.5 in this case. Apparently it's been 10
> months since the last backup/restore.
>
> Database header page information:
> Flags 0
> Checksum 12345
> Generation 12609262
> Page size 4096
> ODS version 10.1
> Oldest transaction 179370
> Oldest active 12607073
> Oldest snapshot 12561377
> Next transaction 12609255
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 0
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Jul 28, 2010 6:25:25
> Attributes force write
>
> Variable header data:
> Sweep interval: 0
> *END*

The oldest transaction is a transaction in a state other than committed.
Can be rolled back and the engine wasn't able to undo stuff and do a
commit or it's in limbo in case of a distributed transaction across two
ore more databases.

Then there is also a gap of ~ 2200 between the oldest active and the
next transaction, thus you have a transaction, which is still active
resp. not hard committed or rolled back.

A sweep, if the oldest transaction didn't get stuck due to a failed two
phase commit, should make the oldest transaction move forward.

As you have set the sweep interval to 0, automatic sweep is disabled,
thus you should run a scheduled sweep at low load, e.g. in the night.

With Firebird 1.5, there is no way to identify the oldest active
transaction. With Firebird 2.1, there are monitoring tables, which are
helpful here. So, if it is a client transaction management issue, and if
you are also using Firebird 2.1 or up, then you might be able to spot a
transaction issue easily. With Firebird 2.5, there is also the Trace API
for getting a server-side continuous stream of executed statements.

With your gstat output, I'm pretty certain your primarily performance
problem is not index related, although there is still room for
improvement to create proper indices for faster statement execution.

Hope this helps.



--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!