MonitorWare Knowledge Base

Not sure how many of you have dealt with with large volumes of data, but a couple million entries per hour in my database is no unheard of (I average over 1million entries per hour during peak). I need to keep 2 weeks of data in-line.

This left me with a big problem of how to archive old data. Deleting 12 Million+ records overnight was starting to run very long (and blocking the database so that Winsyslog couldn't insert new data). Deleting on the weekend had the same issues since this would run 48+ hours.

So, exporting the data (for longer term archival) before deletion was just out of the question - instead, I write events both to DB and to log file and just zip/archive the log file nightly.

Thanks to a suggestion from a co-worker, I created a stored procedure on MSSQL 2000 server that runs every hour and only deletes the data in 10,000 record increments - with a pause between each incremental delete to ensure WinSyslog can insert new data that it has queued up.

The SP deletes data that is 2 weeks old. So, I'm only deleting 1 hour of data each time since the SP runs hourly.

Some additional improvements to this that I've done are:
1. "Skew" the deletes by 12 hours (so I'm actually deleting data that's 14.5 days old). This way, when I'm getting peak number of events coming into the database at 9:00am, I'm deleting off-peak data from 9:00pm - less to delete means that it finishes faster.

2. Added the "WITH (PAGLOCK)" table hint so that (though the delete takes a bit longer) SQL doesn't lock the whole table - so INSERTs can still occur while I'm deleting the old data. NOTE: in truth, SQL will still escalate the pagelock to a tablelock after some threshold is hit. If anyone knows how to control this threshold, please let me know

The code for this is below. There are 3 values you'll want to look at before just running the code:
1. The number of events being deleted (10,000) - you need to change both the "IF @currentrows >" statement and the "SET @delcount" values to the same value (these are in adjacent rows).
2. The number of seconds to wait between batch deletes - the "WAITFOR DELAY" statement at the end.
3. You'll probably need to change the "SET @log_datetime" value. This value dictates the data you want to delete (e.g. how old the data is). In my instance I want 14.5 days as explained above. Since I'm also storing everything in GMT time (but my server clock is GMT-5), I also add 5 hours to the time to ensure I'm deleting the expected data.

You shouldn't need to change both #1 and #2, the only one you should NEED to change is the seconds to wait (#2) - either increasing or decreasing this from 10 seconds.

If you read my other posting, you'll know that I have a CLUSTERED index on the ReceivedAt field. This is CRITICAL since performing inequality operators (e.g. less-than-or-equal-to), such as in the DELETE statement, will perform several magnitudes better since the DELETE can seek the index. Not having an index, or even having a NON-CLUSTERED index, will cause a table scan - in other words, SQL will read every record in the table. Table Scans are the worst things for performance and will certainly cause Table Locks.

Anyway, here's the code - I've commented it pretty heavily. I think all of the code except for the table hint are portable to MySQL if anyone wants to try it.

Let me know if you find it useful and/or can improve on it...

Code: Select all


CREATE PROCEDURE sp_Delete14DayOldData AS
   DECLARE @log_datetime DATETIME,
      @rowcount INT,
      @currentrows INT,
      @delcount INT

   -- Get the current datetime minus 14 days -- we want to delete anything older than this value
   -- Actually offsetting this by 1/2 day (12 hours) to ensure that during peak periods (for example, 9am)
   -- a lower number of events are being deleted (9pm). This is to distribute load a bit.
   -- Lastly, since I'm storing everything in GMT time and my server is sitting in EST(GMT-5),
   -- I need to ADD 5 hours to ensure I'm deleting data that was properly offset by 1/2 day
   SET @log_datetime = DateAdd(hh,5,GETDATE() - 14.5)

   -- get the total number of rows older than the above value
   SET @rowcount =
      (SELECT COUNT(*)
       FROM SystemEvents
       WHERE receivedat <= @log_datetime)

   -- set the current number of rows left to delete "currentrows" equal to the total number of rows to delete
   set @currentrows = @rowcount

   -- as long as there are still more rows to delete "currentrows > 0", then we can continue to loop
   WHILE (@currentrows > 0)
   BEGIN

      -- if there are more than 10,000 rows available to delete, we want to delete a maximum of 10,000 rows
      -- else there are less than 10,000 and we want to delete all of them
      IF (@currentrows > 10000)
         SET @delcount = 10000
      ELSE
         SET @delcount = @currentrows

      -- here's the MAGIC command. This limits the number of rows that the DELETE statement will impact
      SET rowcount @delcount

      DELETE FROM SystemEvents WITH (PAGLOCK) WHERE receivedat <= @log_datetime

      -- now that we've deleted @delcount number of rows, we need to decrement the number of rows left to be deleted
      set @currentrows = @currentrows - @delcount

      -- if there are more rows left to delete, we want to wait 10 seconds for other processes to be able to complete
      -- before locking the table with another delete command
      -- otherwise, the number of rows left to delete is zero so the sp will just exit at the end of this loop
      IF (@currentrows >0)
         waitfor delay '00:00:10'

   END

GO



Awesome post! This looks very interesting. When I get the time in the next days, I will try this on one of our testing environments.

Btw the next major WinSyslog Version might be very interesting for you as we introduced Database logging via OLEDB. In our testing environments, we had like 20-30% performance advantage compared to ODBC. You can download the Rolling Beta Version (See Stickies in the WinSyslog Forum) if you would like to test this new OLEDB Database Action.

--
best regards,
Andre Lorbach
Adiscon

GordT. wrote:You shouldn't need to change both #1 and #2, the only one you should NEED to change is the seconds to wait (#2)

I hate to quote and contradict myself, but I'm going to anyway. I've found that there are distinct advantages to increasing #2 (number of records to delete) rather than #1 time between deletes. Both have their advantages/disadvantages:

#1 Increasing the # of rows to delete per pass
Pros: - The entire delete process will finish faster since it needs to calculate fewer deletes. Believe it or not, in my testing, it took about 30 seconds to delete 100,000 records once, but almost 2 minutes to delete 10,000 records 10 times (this excludes any "wait" time between deletes); more than 3 times longer which I'm guessing is due to the "setup" work being done for each of the 10 delete commands.
Cos: - Higher probability of table locks
- Longer wait until another process can query/insert the data, which can cause timeouts or backlogs.

#2 Increasing time between deletes
Pros: - Allows other actions to occur (queries, inserts) between deletes. Cons: - Making this too short can cause long running queries or insert backlogs during peak times.
- Setting it too long can cause the process to run way too long (e.g. since I have this running as an hourly job, a run time of more than 60 minutes is not beneficial).


So, some fine-tuning is needed for this, and it really depends heavily on the speed of your disk, CPU time/memory, and database recovery model.

Since I am writing to database and file, I don't need to worry too much about database recovery since I can always rebuild everything from logfile. If you're doing the same, I strongly suggest the SIMPLE recovery model - INSERTs and DELETEs will both run much faster.

We deal with the same problem of old data by putting a datetime field in our databases called entered and setting the default to getdate(). In our application which is ASP based under IIS we simply do a sql execute in the global.asa with a directive to delete any records where the entered field indicates the record is beyond a preset number of days old. This results in only a few records being automatically culled whenever a users web session is first started. The result is not needing to manually cull data. If you use indexes on the entered field, even deleting thousands of records to automatically cull the data as part of the data entrty should take no time as sql server simply marks records the delete in memory and schedules the delete to occur in an orderly fashion, if you access the record that has been deleted sql server simply checks to see if that record is delete pending.

Just want drop a line that we have also a tool for archiving / deleting available in our monitoring solution. The tool is called LogDeleter [1]. It is able to delete records in a database older then the specified days and it also can be used for backup the data first. It runs either in GUI mode or on command line, e.g. scheduled via Task Manager. LogDeleter runs with lower system priority as normal application and use resource friendly archiving / deleting mechanism. Therefore it is ideal to run it in the background all days without impact the database performance to much.

For larger databases we recommend to indexing the timestamp column (ReceivedAt or DeviceReportedTime column in Adiscon's database schemata). Note that it is important to update the database statistics frequently. Otherwise you may run into performance issues. Update statistics can be activated by enable "Update the statistics used by query optimizer" in your Database Maintenance Plan. It is also possible to run a query directly (UPDATE STATISTICS tablename) but is highly recommended to schedule this task.

[1] http://www.monitorware.com/en/logdeleter/index.php