Log table with millions of rows. Now, what?

views:

answers:

+3 Q:

Log table with millions of rows. Now, what?

I have a Log table with millions of rows. I'm thinking about separating the data into multiple tables (i.e. LoginHistory, ExceptionHistroy, PaymentProcessingHistory, etc.) What is the term used when taking a large table with many rows (not Columns) and creating multiple tables?

My current Log table schema resembles: LogID, LogMessage, LogReason, LoggedBy, LoggedOn, etc.

The problem is I'm putting too many things in one table, I think? Perhaps the table is too generic?

Thanks

+3 A:

It is called data partitioning.

RedFilter 2010-10-06 16:13:02

+2 A:

Sharding is the term de jour. From the link:

Horizontal partitioning is a database design principle whereby rows of a database table are held separately, rather than splitting by columns (as for normalization). Each partition forms part of a shard, which may in turn be located on a separate database server or physical location.

T.J. Crowder 2010-10-06 16:13:15

Very Interesting! This might be the solution; however, I feel that my table is too generic? Any more clever ideas?

Moderator71 2010-10-06 16:21:03

+1 The concept of sharding is new to me, I knew about the idea of horizontal partitioning, but I didn't know the actual name of "sharding". Something interesting from the Wikipedia entry to keep in mind, is that you should try to create your shards in such a way that each shard has a meaning or context (in the article the given example is to have a shard for american customers and other shard for european customers).

Abel Morelos 2010-10-06 16:47:27

Thanks for clarifying Abel. With sharding, am I able to select only from a specified partition? For example, the ExceptionHistory partition or the LoginHistory partition of the Log table.

Moderator71 2010-10-06 17:55:15

I think you should be looking at horizontal partitioning. Horizontal partitioning is more or less a subset of sharding.

For more details on horizontal partitioning, here's the wikipedia link:
http://en.wikipedia.org/wiki/Partition_%28database%29

You didn't mention which DB technology you use, but here are some technical links that can get you going with partitioning your data:

IBM DB2 partitioning
MySQL partitioning
Oracle partitioning
SQL Server partitions
PostgreSQL partitioning
Sybase ASE 15.0 partitioning

code4life 2010-10-06 16:39:35

I would prefer not to split my table into partitions. I'm actually looking into splitting one table into many tables. I'm sorry if I didn't articulate my question correctly. I'm wondering what that is called when you go from one table to many tables? Thanks

Moderator71 2010-10-06 17:49:50

FYI, I am using SQL Server

Moderator71 2010-10-06 18:07:03

ansaurus

tags:

views:

answers:

Log table with millions of rows. Now, what?

related questions