Jump to content


Checkpoint replay to PostgreSQL database for Insight

Insights

  • Please log in to reply
6 replies to this topic

#1 Punith

Punith

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 24 November 2013 - 08:57 AM

Hi,


We have a p4d checkpoint of size 40Gig, while replaying it to PostgresSQL database, its taking days to complete. With 30% of progress it consumed 500Gig of space.

Do you guys recommend any configuration for PostgresSQL to improve the checkpoint replay time?
Also wondering when will Insight start supporting database other than PostgresSQL?


Thanks!
Punith

#2 P4Nick

P4Nick

    Advanced Member

  • Staff
  • 50 posts
  • LocationReading, UK

Posted 26 November 2013 - 09:45 AM

Hi Punith,

We don't have any specific recommendations for tuning PostgreSQL; however, you might want to exclude the db.have and db.label tables from the import. This will save some space and reduce the time it would take to process those records.

Neither of these tables are used by any of the metrics that come with Insights, so excluding them will not affect the application's behaviour.

To exclude these tables, just add their names to the the excludeTables option in the dbimport-*.cfg files. This would change the lines from:
excludeTables=db.archive,db.boddate,db.changex,db.fixrev,db.haverp,db.integ,db.ixdate,db.ixtext,db.jobdesc,db.jobpend,db.locks,db.logger,db.message,db.monitor,db.resolve,db.resolvex,db.revcx,db.revpx,db.revsh,db.user.rp,db.view.rp,db.working,db.workingx

To:
excludeTables=db.archive,db.boddate,db.changex,db.fixrev,db.haverp,db.integ,db.ixdate,db.ixtext,db.jobdesc,db.jobpend,db.locks,db.logger,db.message,db.monitor,db.resolve,db.resolvex,db.revcx,db.revpx,db.revsh,db.user.rp,db.view.rp,db.working,db.workingx,db.have,db.label


We don't currently plan to support any databases other than PostgreSQL at this time, but if you have a database you'd like us to consider, please contact our support team at support@perforce.com who will open an enhancement request for you.

Thanks,

Nick

#3 Punith

Punith

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 26 November 2013 - 10:06 AM

Thanks! Nick.

Let me try this...

--
Punith

#4 Bruce Mc

Bruce Mc

    Advanced Member

  • Members
  • PipPipPip
  • 84 posts
  • LocationSeattle Area

Posted 05 May 2014 - 08:38 PM

I went looking for the dbimport-*.cfg files and I could not find them.

I'm assuming that the implementation for importing a checkpoint has changed since this thread was last active. How do we exclude specific tables from import currently?

Bruce

#5 P4Matt

P4Matt

    Advanced Member

  • Members
  • PipPipPip
  • 1383 posts

Posted 06 May 2014 - 06:59 PM

I'm not sure; I've reached out to the team for details.

#6 Bruce Mc

Bruce Mc

    Advanced Member

  • Members
  • PipPipPip
  • 84 posts
  • LocationSeattle Area

Posted 12 May 2014 - 05:40 PM

Matt,

Have you heard back?

#7 P4Matt

P4Matt

    Advanced Member

  • Members
  • PipPipPip
  • 1383 posts

Posted 12 May 2014 - 08:45 PM

Grr, yes. I'm sorry I forgot to post the answer! (thanks to P4Nick for the help)

The flags are undocumented but there are two configuration options (one for the initial checkpoint import and one for the ongoing replication):

insights.journal.action.SQLLoader.excludeTables
insights.journal.action.SQLUpdater.excludeTables

They both default to: db.archive,db.boddate,db.changex,db.fixrev,db.haverp,db.integ,db.ixdate,db.ixtext,db.jobdesc,db.jobpend,db.locks,db.logger,db.message,db.monitor,db.resolve,db.resolvex,db.revcx,db.revpx,db.revsh,db.user.rp,db.view.rp,db.working,db.workingx,db.sendq,db.archmap,db.have,db.label,db.uxtext





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users