| Subject | Re: Strange behavior on very large table |
|---|---|
| Author | |
| Post date | 2019-02-22T04:33:53Z |
Thanks Helen. Just a few clarifications, that might help whittle this down a bit...
900,000 rows each day before you load up the latest batch of 900,000?
>Given that this table is "temporary" storage, one supposes that you
are deleting rows from it regularly. Do you happen to be deleting900,000 rows each day before you load up the latest batch of 900,000?
Yes, that is correct. Each morning it does a "kill & fill" on this table. All 900,000 rows are deleted, then a new set of that data is loaded from a CSV file via an external application. This happens about 2 hours after the system has completed its backup procedures.
>If you're not doing any particular housework on it (restoring from
backup and/or resetting the indexes periodically), then it would be
normal to expect degrading performance until the next time that
housekeeping is done.
For about the last 6 months we have put the database through a regular gfix mend process once a week, following with a backup of the database and then a restore. This doesn't seem to have changed the behavior, however.
The behavior is quite unpredictable. It seems that any attempt to access the table after the Kill & Fill has been done, or within a few hours of it, sends the server off into a 100% CPU processor load for hours. But if this is done 6 hours after the kill & fill, then it seems to work just fine. I have also seen the same issues if I attempt to access that table after a database server is rebooted. Not sure if that makes any difference here at all, or signifies anything else that could be going on?
Myles