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

Re: Table growth/refacrtoring

Expand Messages
  • 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 1 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 2 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 3 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 4 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 5 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 6 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.