Subject Sweep question causing cpu spikes
Author javakqj
My application which is running at the customer site which uses Firebird classic 2.0.3 produces a cpu spike every 10 mins or so. I had already posted this issue on the forum . Here is some more information regarding this . Can anyone please help and correct my understanding (if wrong).?

I captured the gstat snapshots at the beginning of a 10 min interval and the next one at the time the spike occurs and verified whether the sweep ran or not. I am suspecting the sweep is causing the spike .
But here is the problem:
The sweep interval is the default (20,000 transaction).But comparing the two gstat snapshots (mentioned above which I have posted at the end of this post) I see a change in the total versions(3624 & 66) for a particular table although the no. of transactions (60147977 - 60140136 ) is just 7841 (I am using the generation nos). If there is a change in the total versions means to my understanding the sweep did run although it did not hit the limit of 20,000. Is my understanding correct? Why does the sweep run at 7841 and not wait for the 20,000 mark? Am i missing something?

Below are the snapshots:
First Snapshot:(taken at the beginning of 10 interval mark)
==========================================================

Database header page information:
Flags 0
Checksum 12345
Generation 60140136
Page size 8192
ODS version 11.0
Oldest transaction 60140110
Oldest active 60140111
Oldest snapshot 60140111
Next transaction 60140116
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 Oct 7, 2008 22:05:07
Attributes force write

Variable header data:
*END*


TASKINFO (129)
Primary pointer page: 149, Index root page: 150
Average record length: 160.97, total records: 281379
Average version length: 30.79, total versions: 3624, max versions: 6
Data pages: 7195, data page slots: 7195, average fill: 88%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 128
80 - 99% = 7067

Index ENDTIME_IDX (3)
Depth: 2, leaf buckets: 373, nodes: 284969
Average data length: 2.15, total dup: 4486, max dup: 3787
Fill distribution:
0 - 19% = 32
20 - 39% = 0
40 - 59% = 114
60 - 79% = 11
80 - 99% = 216

Index RDB$PRIMARY2 (0)
Depth: 2, leaf buckets: 424, nodes: 281379
Average data length: 5.83, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 31
60 - 79% = 0
80 - 99% = 391

Index TASKINFO_TASKID_IDX_DESC (4)
Depth: 3, leaf buckets: 810, nodes: 281379
Average data length: 5.83, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 2
40 - 59% = 807
60 - 79% = 0
80 - 99% = 1

Index TASK_HOST_IDX (2)
Depth: 2, leaf buckets: 307, nodes: 281379
Average data length: 0.03, total dup: 281368, max dup: 47951
Fill distribution:
0 - 19% = 3
20 - 39% = 15
40 - 59% = 232
60 - 79% = 40
80 - 99% = 17

Index TASK_STATUS_IDX (1)
Depth: 2, leaf buckets: 300, nodes: 284969
Average data length: 0.01, total dup: 284964, max dup: 280601
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 236
60 - 79% = 53
80 - 99% = 10
======================================================================
Second snapshot:(taken at the time the cpu spike is seen caused by one of the fb_inet_server process)
----------------
Database header page information:
Flags 0
Checksum 12345
Generation 60147977
Page size 8192
ODS version 11.0
Oldest transaction 60147955
Oldest active 60147956
Oldest snapshot 60147956
Next transaction 60147957
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 Oct 7, 2008 22:05:07
Attributes force write

Variable header data:
*END*

TASK_INFO (129)
Primary pointer page: 149, Index root page: 150
Average record length: 160.98, total records: 281679
Average version length: 18.52, total versions: 66, max versions: 6
Data pages: 7204, data page slots: 7204, average fill: 87%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 157
80 - 99% = 7047

Index ENDTIME_IDX (3)
Depth: 2, leaf buckets: 371, nodes: 281708
Average data length: 2.18, total dup: 930, max dup: 229
Fill distribution:
0 - 19% = 33
20 - 39% = 0
40 - 59% = 111
60 - 79% = 12
80 - 99% = 215

Index RDB$PRIMARY2 (0)
Depth: 2, leaf buckets: 424, nodes: 281679
Average data length: 5.83, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 30
60 - 79% = 0
80 - 99% = 392

Index TASKINFO_TASKID_IDX_DESC (4)
Depth: 3, leaf buckets: 811, nodes: 281679
Average data length: 5.83, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 2
40 - 59% = 808
60 - 79% = 0
80 - 99% = 1

Index TASK_HOST_IDX (2)
Depth: 2, leaf buckets: 309, nodes: 281679
Average data length: 0.03, total dup: 281668, max dup: 47951
Fill distribution:
0 - 19% = 3
20 - 39% = 15
40 - 59% = 235
60 - 79% = 41
80 - 99% = 15

Index TASK_STATUS_IDX (1)
Depth: 2, leaf buckets: 298, nodes: 281709
Average data length: 0.01, total dup: 281704, max dup: 280899
Fill distribution:
0 - 19% = 1
20 - 39% = 1
40 - 59% = 235
60 - 79% = 52
80 - 99% = 9

Thanks in advance.