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

Re: Table growth/refacrtoring

Expand Messages
  • 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 1 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]
      > > >
      > >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.