Loading ...
Sorry, an error occurred while loading the content.

Re: [agileDatabases] Re: Table growth/refacrtoring

Expand Messages
  • Scott Ambler
    Sounds like a fair bit of continuing maintenance to me.  I tend to avoid stuff like that because a few years down the road these sorts of strategies tend to
    Message 1 of 9 , Oct 4, 2012
    • 0 Attachment
      Sounds like a fair bit of continuing maintenance to me.  I tend to avoid stuff like that because a few years down the road these sorts of strategies tend to fall apart as the team evolves (or simply when the person doing this goes on a vacation).
       
      Another strategy might be simply to use rotating logs.  When you rotate out the oldest stuff it goes to the external DB as described earlier.  This puts you in a position where your code, views, ... can access a set of stable logs.
       
      - Scott 
      Scott W. Ambler
      Principal, Scott W. Ambler + Associates, http://www.scottwambler.com
      http://www.DisciplinedAgileDelivery.com
      http://www.ambysoft.com
      Follow me on Twitter: http://twitter.com/scottwambler


      ________________________________
      From: BW <bretweinraub@...>
      To: agileDatabases@yahoogroups.com
      Sent: Tuesday, October 2, 2012 9:33:08 AM
      Subject: [agileDatabases] Re: Table growth/refacrtoring

      why not write to a table called

      activity_log_[week of year] ?

      like

      activity_log_2012_23 # the 23rd week of 2012

      you can then create a view that joined up the last months data:

      create table last_months_activity
      as
      select * from activity_log_2012_23
      union all
      select * from activity_log_2012_22
      ...

      and rebuild that view once a week.

      Or so on.

      --- In agileDatabases@yahoogroups.com, "ayo.akinyemi" <sayhello2ay@...> wrote:
      >
      > Yes, the ActivityLog table is being used by the application.
      > We do archive the table, and truncate data that are 3 weeks old. Now, we want to be able to provide activity logs up to 6-12 months.
      > Thanks for the NoSql suggestion. I will look into that.
      > If we partition the tables by date, we would still have so many tables eventually, right ? That seem to me like raising the water level to cover the challenges.
      >
      >
      > --- In agileDatabases@yahoogroups.com, Pramod Sadalage <pramodsadalage@> wrote:
      > >
      > > is the ActivityLog data needed by the application, if its just stored and
      > > not used by the application can you write to disk with ASync logging, in
      > > java you can use the AsyncAppender. The log file can then be imported into
      > > any database for further analysis.
      > >
      > > If it is needed by the application, can you also archive old data or
      > > partition your table for dates (assuming you store activity date), you
      > > could even write the activitylogs to a NoSQL type database.
      > >
      > > Many options, depends on how you plan to use the ActivityLog data?
      > >
      > > *Pramod Sadalage
      > > @pramodsadalage
      > > <http://www.twitter.com/pramodsadalage><http://www.sadalage.com/>
      > > *
      > > *www.sadalage.com
      > > www.databaserefactoring.com*
      > >
      > >
      > >
      > > On Thu, Sep 27, 2012 at 10:16 AM, ayo.akinyemi <sayhello2ay@>wrote:
      > >
      > > > **
      > > >
      > > >
      > > > My team maintains a MySql database that have about 120 tables housed
      > > > within 1 database. There is an Activity Logs table that records every
      > > > action all users take within a ERP software.
      > > >
      > > > The challenge we have right now is that the Activity Logs table is growing
      > > > so big, so fast , and my team is contemplating on moving this table to
      > > > another database.
      > > > So, all logs goes into this new database, while other records are stored
      > > > in the old database.
      > > >
      > > > This we believe, will make access to the old database faster, and we will
      > > > be able to transfer data from the old database into testing servers for the
      > > > users to test on quickly.
      > > >
      > > > Right now, because of the large data in the Activity Logs table, it takes
      > > > a while to get the live data transferred into test servers.
      > > >
      > > > Do you think it is a shrewd move to separate the Activity Logs table into
      > > > its own database ?
      > > >
      > > > Do you have any recommendations ?
      > > >
      > > > Please let me know if you need further clarification.
      > > >
      > > > 
      > > >
      > >
      > >
      > > [Non-text portions of this message have been removed]
      > >
      >




      ------------------------------------

      Yahoo! Groups Links



      [Non-text portions of this message have been removed]
    • ayo.akinyemi
      We are trying to move away from views, the idea of activity_log_[week of year] sounds tempting, but that would just be creating so many activity logs tables in
      Message 2 of 9 , Oct 4, 2012
      • 0 Attachment
        We are trying to move away from views, the idea of activity_log_[week of year] sounds tempting, but that would just be creating so many activity logs tables in the DB and complex logic to join 2 or more tables to pull data. Considering your suggestion though.. thanks.

        --- In agileDatabases@yahoogroups.com, "BW" <bretweinraub@...> wrote:
        >
        > why not write to a table called
        >
        > activity_log_[week of year] ?
        >
        > like
        >
        > activity_log_2012_23 # the 23rd week of 2012
        >
        > you can then create a view that joined up the last months data:
        >
        > create table last_months_activity
        > as
        > select * from activity_log_2012_23
        > union all
        > select * from activity_log_2012_22
        > ...
        >
        > and rebuild that view once a week.
        >
        > Or so on.
        >
        > --- In agileDatabases@yahoogroups.com, "ayo.akinyemi" <sayhello2ay@> wrote:
        > >
        > > Yes, the ActivityLog table is being used by the application.
        > > We do archive the table, and truncate data that are 3 weeks old. Now, we want to be able to provide activity logs up to 6-12 months.
        > > Thanks for the NoSql suggestion. I will look into that.
        > > If we partition the tables by date, we would still have so many tables eventually, right ? That seem to me like raising the water level to cover the challenges.
        > >
        > >
        > > --- In agileDatabases@yahoogroups.com, Pramod Sadalage <pramodsadalage@> wrote:
        > > >
        > > > is the ActivityLog data needed by the application, if its just stored and
        > > > not used by the application can you write to disk with ASync logging, in
        > > > java you can use the AsyncAppender. The log file can then be imported into
        > > > any database for further analysis.
        > > >
        > > > If it is needed by the application, can you also archive old data or
        > > > partition your table for dates (assuming you store activity date), you
        > > > could even write the activitylogs to a NoSQL type database.
        > > >
        > > > Many options, depends on how you plan to use the ActivityLog data?
        > > >
        > > > *Pramod Sadalage
        > > > @pramodsadalage
        > > > <http://www.twitter.com/pramodsadalage><http://www.sadalage.com/>
        > > > *
        > > > *www.sadalage.com
        > > > www.databaserefactoring.com*
        > > >
        > > >
        > > >
        > > > On Thu, Sep 27, 2012 at 10:16 AM, ayo.akinyemi <sayhello2ay@>wrote:
        > > >
        > > > > **
        > > > >
        > > > >
        > > > > My team maintains a MySql database that have about 120 tables housed
        > > > > within 1 database. There is an Activity Logs table that records every
        > > > > action all users take within a ERP software.
        > > > >
        > > > > The challenge we have right now is that the Activity Logs table is growing
        > > > > so big, so fast , and my team is contemplating on moving this table to
        > > > > another database.
        > > > > So, all logs goes into this new database, while other records are stored
        > > > > in the old database.
        > > > >
        > > > > This we believe, will make access to the old database faster, and we will
        > > > > be able to transfer data from the old database into testing servers for the
        > > > > users to test on quickly.
        > > > >
        > > > > Right now, because of the large data in the Activity Logs table, it takes
        > > > > a while to get the live data transferred into test servers.
        > > > >
        > > > > Do you think it is a shrewd move to separate the Activity Logs table into
        > > > > its own database ?
        > > > >
        > > > > Do you have any recommendations ?
        > > > >
        > > > > Please let me know if you need further clarification.
        > > > >
        > > > >
        > > > >
        > > >
        > > >
        > > > [Non-text portions of this message have been removed]
        > > >
        > >
        >
      • bretweinraub
        Did I miss something? If we are rotating logs, then we are writing to flat files. Last time I checked the amount of maintenance to recreate a view does not
        Message 3 of 9 , Oct 4, 2012
        • 0 Attachment
          Did I miss something? If we are "rotating" logs, then we are writing to flat files.

          Last time I checked the amount of maintenance to recreate a view does not significantly differ from the maintenance to rotate a log.

          Both are objects in a "database". (A filesystem is just a database; think about it).

          You have to have a "job" to manipulate the object in your database. You must manage that job.

          If you are extra clever, you can trick the object into managing itself. Bonus points for that.

          -bret
          --- In agileDatabases@yahoogroups.com, Scott Ambler <scottwambler@...> wrote:
          >
          > Sounds like a fair bit of continuing maintenance to me.  I tend to avoid stuff like that because a few years down the road these sorts of strategies tend to fall apart as the team evolves (or simply when the person doing this goes on a vacation).
          >  
          > Another strategy might be simply to use rotating logs.  When you rotate out the oldest stuff it goes to the external DB as described earlier.  This puts you in a position where your code, views, ... can access a set of stable logs.
          >  
          > - Scott 
          > Scott W. Ambler
          > Principal, Scott W. Ambler + Associates, http://www.scottwambler.com
          > http://www.DisciplinedAgileDelivery.com
          > http://www.ambysoft.com
          > Follow me on Twitter: http://twitter.com/scottwambler
          >
          >
          > ________________________________
          > From: BW <bretweinraub@...>
          > To: agileDatabases@yahoogroups.com
          > Sent: Tuesday, October 2, 2012 9:33:08 AM
          > Subject: [agileDatabases] Re: Table growth/refacrtoring
          >
          > why not write to a table called
          >
          > activity_log_[week of year] ?
          >
          > like
          >
          > activity_log_2012_23 # the 23rd week of 2012
          >
          > you can then create a view that joined up the last months data:
          >
          > create table last_months_activity
          > as
          > select * from activity_log_2012_23
          > union all
          > select * from activity_log_2012_22
          > ...
          >
          > and rebuild that view once a week.
          >
          > Or so on.
          >
          > --- In agileDatabases@yahoogroups.com, "ayo.akinyemi" <sayhello2ay@> wrote:
          > >
          > > Yes, the ActivityLog table is being used by the application.
          > > We do archive the table, and truncate data that are 3 weeks old. Now, we want to be able to provide activity logs up to 6-12 months.
          > > Thanks for the NoSql suggestion. I will look into that.
          > > If we partition the tables by date, we would still have so many tables eventually, right ? That seem to me like raising the water level to cover the challenges.
          > >
          > >
          > > --- In agileDatabases@yahoogroups.com, Pramod Sadalage <pramodsadalage@> wrote:
          > > >
          > > > is the ActivityLog data needed by the application, if its just stored and
          > > > not used by the application can you write to disk with ASync logging, in
          > > > java you can use the AsyncAppender. The log file can then be imported into
          > > > any database for further analysis.
          > > >
          > > > If it is needed by the application, can you also archive old data or
          > > > partition your table for dates (assuming you store activity date), you
          > > > could even write the activitylogs to a NoSQL type database.
          > > >
          > > > Many options, depends on how you plan to use the ActivityLog data?
          > > >
          > > > *Pramod Sadalage
          > > > @pramodsadalage
          > > > <http://www.twitter.com/pramodsadalage><http://www.sadalage.com/>
          > > > *
          > > > *www.sadalage.com
          > > > www.databaserefactoring.com*
          > > >
          > > >
          > > >
          > > > On Thu, Sep 27, 2012 at 10:16 AM, ayo.akinyemi <sayhello2ay@>wrote:
          > > >
          > > > > **
          > > > >
          > > > >
          > > > > My team maintains a MySql database that have about 120 tables housed
          > > > > within 1 database. There is an Activity Logs table that records every
          > > > > action all users take within a ERP software.
          > > > >
          > > > > The challenge we have right now is that the Activity Logs table is growing
          > > > > so big, so fast , and my team is contemplating on moving this table to
          > > > > another database.
          > > > > So, all logs goes into this new database, while other records are stored
          > > > > in the old database.
          > > > >
          > > > > This we believe, will make access to the old database faster, and we will
          > > > > be able to transfer data from the old database into testing servers for the
          > > > > users to test on quickly.
          > > > >
          > > > > Right now, because of the large data in the Activity Logs table, it takes
          > > > > a while to get the live data transferred into test servers.
          > > > >
          > > > > Do you think it is a shrewd move to separate the Activity Logs table into
          > > > > its own database ?
          > > > >
          > > > > Do you have any recommendations ?
          > > > >
          > > > > Please let me know if you need further clarification.
          > > > >
          > > > > 
          > > > >
          > > >
          > > >
          > > > [Non-text portions of this message have been removed]
          > > >
          > >
          >
          >
          >
          >
          > ------------------------------------
          >
          > Yahoo! Groups Links
          >
          >
          >
          > [Non-text portions of this message have been removed]
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.