Alternative databases to use when putting IIS Logs into a database using LogParser

Question

We have run some scripts that use LogParser to dump our IIS logs into a SQL Server database.

We can then query this to get simple stats on hits, usage etc. It's also good when linking it to error log databases and performance counter database to compare usage with errors, etc.

Having implemented this for just one system and for the last 2-3 weeks we already have a 5GB database with around 10 million records.

This is making any queries to this database quite slow and will no doubt cause storage issues if we continue to log as we are.

Can anyone suggest any alternative databases that we could use for this data that would be more efficient for such logs? I'd be particularly interested in any experience of Google's BigTable or Amazon's SimbleDB.

Are either of these suitable for reporting queries? COUNTs, GROUP BYs, PIVOTs?

Answer 1

How often have you updated your indexes? What sort of queries on the data are you performing?

Perhaps you can perform routine collation of data at the end of every day to speed up other queries? (create new tables with this collated information)

Like a page hits table might have a record for each day on how many times that page was hit - that way you don't have to do a full table-scan on every query, you just hit the page-hits table.

A unique hosts table might have records for linger time, how many pages they hit, number of files downloaded, total bandwidth, session abandonement, unique cookies (different users, perhaps behind proxies or firewalls).

What sort of purge schedule are you planning, if any?

While it's nice to keep all that data around forever, especially for things you haven't thought of yet, the vast majority of what you want is in the collated data - so build your reports around that, and keep the raw data for those cases you really need something unique.

This is all stuff you'd have to build with a key-value store (like simpledb or bigtable) anyway.

Answer 2

I think storage costs are going to be your biggest concern. Even if you go the cloud route I doubt you will be able to manage costs for that amount of data. My suggestion would be to move the data to ultra-cheap storage and deploy a solution that can operate on that data in an efficient manner.

For example, you could move log files from your server(s) to a local machine with a huge hard drive (and appropriate backup solution) and then run tools locally that can analyze the data. Log parser is effective if you can operate against a small subset of that data. You can run a database locally but even optimized queries might run slowly.

You might consider purchasing a log analysis tool like WebLog Expert to operate against these files.

Answer 3

I also faced similar problem before. Since the log file was growing so fast, I started to think if it is suitable to use database for IIS log. There are two points you may need to consider about:

1. In most case we the IIS log can not provide useful information directly, we need to parse it to obtain statistic information.
2. Also, in most case, IIS logs are not necessary to be ready in database for query.

It is recommended to keep all logs in files as they used to, but store weekly or monthly statistic information (processed periodically) in database, so that you have those essential data ready-to-serve.