I am attempting to debug some strange behavior on a large table that exhibits
the same problems on both FB 1.5 Classic and FB 2.5 Classic. This is on a
CentOS 7 server, with a ton of RAM and disk, etc.
The database has about 150 tables in it. One table is the culprit. All other
tables, stored procedures, etc. work flawlessly and fast. But this one table,
which is used to load about 1 million rows each day for processing is
problematic. The table is large in terms of the number of columns, yet only
has about 6 indexes on it. Since it is only used as a "kill & fill" table to
get data into the database, before that data is then processed into its
destination tables, I would consider it temporary. But unfortunately the data
provider is unable to give me the delta of what has changed each night, so I'm
forced to delete all rows in this table, then load them from this file.
My code that loads the data commits the transactions after the DELETE
statement, and then at each 5,000 rows loaded. I have checked this by watching
the load and using IBExpert to count records, so I can confirm that the commits
are happening.
After the loading has completed, what then occurs is strange. Any attempt to
interact with that table (and only that table) forces some form of sweep or
some delay that shows up as a process on the server running at near 100% CPU,
for about 4-5 hours. Then it calms down and access to the table is pretty much
instantaneous. I thought that this might be a sweep that is occurring after
the load, so I set the database to not auto sweep. I have force dropped the
table, and rebuilt it. I have done a backup with sweep and a restore to
refresh the database. Nothing seems to make any difference.
This is what database statistics are showing for the database:
Database header page information:
Flags 0
Checksum 12345
Generation 995313
Page size 16384
ODS version 10.1
Oldest transaction 93188
Oldest active 990357
Oldest snapshot 990354
Next transaction 995298
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 19
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date May 8, 2019 23:37:46
Attributes force write
Variable header data:
Sweep interval: 0
*END*
This is what statistics show for that one table:
CLAIM_LOAD (276)
Primary pointer page: 711, Index root page: 712
Average record length: 1475.73, total records: 962662
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 93152, data page slots: 116597, average fill: 94%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 93151
Can anyone see any issues with these statistics, or have any suggestions as to
what may be going on? Is there anything that can be seen in logs for this
mysterious "after load" behavior I'm seeing?
Thanks in advance for any suggestions.
Myles
