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

Re: [agileDatabases] Table growth/refacrtoring

Expand Messages
  • Pramod Sadalage
    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
    Message 1 of 9 , Sep 27, 2012
    • 0 Attachment
      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]
    • ayo.akinyemi
      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
      Message 2 of 9 , Sep 27, 2012
      • 0 Attachment
        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]
        >
      • Berke Sokhan
        Plus, you should consider dividing one big table to seperated tables, that each one is a shadow of the original table with additional column like change action
        Message 3 of 9 , Sep 28, 2012
        • 0 Attachment
          Plus, you should consider dividing one big table to seperated tables, that
          each one is a shadow of the original table with additional column like
          change action (inserted, updated, deleted), changed by whom, when...

          2012/9/27 ayo.akinyemi <sayhello2ay@...>

          > **
          >
          >
          > 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]
          > >
          >
          >
          >



          --
          Berke SOKHAN.

          http://twitter.com/berkesokhan
          http://blog.berkesokhan.com
          http://www.birliktegelistir.com/editors.aspx


          [Non-text portions of this message have been removed]
        • BW
          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
          Message 4 of 9 , Oct 2, 2012
          • 0 Attachment
            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]
            > >
            >
          • 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 5 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 6 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 7 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.