Audit table from hell
-
Just whipped up a little SQL procedure to show us the largest tables in our production database.
Allow me to summarize:
Order system auditing table, 3.25 million rows, 13 GB, zero indexes. Entire database is barely 30 GB.Took almost three minutes to search the table by date. Ha ha kill me.
-
@db2 said:
Just whipped up a little SQL procedure to show us the largest tables in our production database.
Allow me to summarize:
Order system auditing table, 3.25 million rows, 13 GB, zero indexes. Entire database is barely 30 GB.Took almost three minutes to search the table by date. Ha ha kill me.
How often do you have to search in this table? Sometimes, there are tables which are mostly write-only; they exist because some stuff must be logged (because of QA requirements), but it is infrequently read and when it is read, it's hard to tell by which columns it is searched.
-
Yes some things do need to be logged, and some things have to have those logs kept for seven years (in the case of the health industry) or more.
But keeping this table in it's current form is a WTF. This table should be archived off, possibly segmenting the archive by either month or year, whatever is deemed more appropriate based off of system usage.
You may ask why, the reason is simple. The data isn't looked at often but when it is it may will eventually become so large that it has become useless unless steps are taken to prevent that.
-
@ammoQ said:
How often do you have to search in this table? Sometimes, there are tables which are mostly write-only; they exist because some stuff must be logged (because of QA requirements), but it is infrequently read and when it is read, it's hard to tell by which columns it is searched.
So Write-only devices are acceptable? I thought that was just for the BOFH.
-
@ammoQ said:
How often do you have to search in this table?
Never. We didn't even know it was being kept until today, and it's been there for at least a couple years. Logic suggests I just delete/archive everything older than a month, but we're waiting to hear from the application vendor to make sure that won't completely break the app. That would be a pretty big WTF if it did, but after finding a 13 GB "write-only" table, I'd believe anything at this point.
Oh, and I found out it's also got a 3GB+ cousin that audits another table. These are the two largest tables in the whole database. Ugh.
-
@db2 said:
Never. We didn't even know it was being kept until today, and it's been there for at least a couple years. Logic suggests I just delete/archive everything older than a month
Eep! I'd worry about that a little until you figure out what the purpose of the table is...usually 'audit' tables are there so you have an objective record of the changes that have taken place over time...by definition they aren't there for day-to-day use, they're there in case you need to go back and figure out what/when/how something changed, you don't want to just throw that information away -- it might even involve legal liability in some cases.
Moving it out into another db or even on a whole different server on a regular basis might not be a bad idea (if the size of the db is causing you problems and if it doesn't break the app), but I'd be wary of just dumping the data until you know why it's there in the first place :)
-cw
-
@db2 said:
@ammoQ said:
How often do you have to search in this table?
Never. We didn't even know it was being kept until today, and it's been there for at least a couple years. Logic suggests I just delete/archive everything older than a month, but we're waiting to hear from the application vendor to make sure that won't completely break the app. That would be a pretty big WTF if it did, but after finding a 13 GB "write-only" table, I'd believe anything at this point.
Oh, and I found out it's also got a 3GB+ cousin that audits another table. These are the two largest tables in the whole database. Ugh.
Unless you are legally required to keep the audit trail, you might want to look for a "debug level" setting in your application. It's not uncommon to set this one to a high level in the beginning, and forget about it.
-
I'd wager good sums of money that these two particular tables aren't under any sort of legal audit requirement, based on what they do. This will probably end with deleting most of the table, and setting up a regular job to do so, or killing the triggers that do the auditing in the first place. Either way, happy ending. Heh.
