Re: Database design for monitoring tool
Posted by: Rick James
Date: May 19, 2012 07:08PM

> 1 for each function/server. There are around 120-140 functions and 4 servers.
That's about 2 INSERTs per second. Yawn.

> 120 tables a year.
Rarely a good idea. A single, big, table is probably better.

> 4.3 million rows and 12 tables a year.
Or 50M rows/year in a single table. At one year, that would be about 95 percentile of the tables discussed on these forums.

> would you consider 120 tables a year also too much?
Well... Is this reasonable:
1 table per function, but that table contains all the data for all years? That would mean a total of 120-140 tables? Those tables would not need a "function" column.

The answer may depend on what the queries are like. If you need to SELECT based on a user-provided "function", then it would be better to have function as a column, rather than constructing the SELECT by patching in the table (function) name.

Please mock up the SELECTs (based on any of these table designs). That will give us something more concrete to talk about. And it may lead to other benefits/flaws of one or more potential schema designs.

> and no indexing.
Almost always a bad idea.

> I'm not sure how indexing can help me as they have to be unique and I'll need multiple rows.
No, an index does not have to be UNIQUE.

> serverID_functionID_date_time into the primary key
CREATE TABLE foo (
serverID ...,
functionID ...,
date_time ...,
PRIMARY KEY (serverID, functionID, date_time)
);

Recommend TIMESTAMP instead of DATETIME -- 4 bytes vs 8 bytes.

> primary key should be small in size since it is loaded into memory.
False and False. (We will discuss that further as the rest unfolds.)

Keep in mind that 50M rows will be about 1GB, which will take several minutes to scan (especially unindexed).

Options: ReplyQuote


Subject
Written By
Posted
Re: Database design for monitoring tool
May 19, 2012 07:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.