Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    Unanswered: high performance schema design

    I'm trying to make an app which will collect the list of Windows services every minute and their state, and store the results over say a week.

    My dilemma is on the schema. I can have single table like

    service_id , service_name, state (running, stopped), timestamp

    but fear it will grow pretty fast (figure 50 services x 60/hr x 24hr = 72,000/day)

    or I could try to reduce the number of rows dramatically (60 x 24=1440) and assume there are no more than 50 services ever and use

    service_id, state, timestamp, service1, service2, service3, ... service50
    ================================================== ======

    and another table for mapping

    service_map_id, service_name, column_name
    1 Bonjour Service service1

    any thoughts on either approach or an even better solution?

  2. #2
    Join Date
    Aug 2009
    Olympia, WA
    Probably what I would do is model the valid time for a given service state.

    CREATE TABLE service_monitor (
      service_id INT NOT NULL,
      service_name  VARCHAR(30),
      state VARCHAR(30),
      beginning TIMESTAMPTZ(0)  NOT NULL,
      ending     TIMESTAMPTZ(0),
      CONSTRAINT service_monitor_pk PRIMARY KEY (service_id, beginning)
    That way you only have to perform a db operation when you find the state has changed, and it will be MUCH easier to query later on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts