> On Feb 11, 2016, at 6:10 PM, Dan Langille <d...@langille.org> wrote: > >> On Jan 11, 2016, at 3:03 PM, Dan Langille <d...@langille.org >> <mailto:d...@langille.org>> wrote: >> >> >>> On Jan 11, 2016, at 8:35 AM, Eric Bollengier <e...@baculasystems.com >>> <mailto:e...@baculasystems.com>> wrote: >>> >>> Hello Dan, >>> >>>> >>>> I have a test job with 15,764,45 rows in the Batch table. >>>> >>>> With a change in SQL, I went from 45 minutes to 15 minutes. >>>> >>>> Old SQL: >>>> >>>> SELECT batch_testing.FileIndex, batch_testing.JobId, Path.PathId, >>>> Filename.FilenameId, batch_testing.LStat, >>>> batch_testing.MD5, batch_testing.DeltaSeq >>>> FROM batch_testing JOIN Path ON (batch_testing.Path = Path.Path) >>>> JOIN Filename ON (batch_testing.Name = >>>> Filename.Name); >>>> >>>> >>>> New SQL: >>>> >>>> explain analyze >>>> INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, >>>> DeltaSeq) >>>> SELECT BT.FileIndex, >>>> BT.JobId, >>>> (SELECT Path.PathId FROM Path WHERE Path.path >>>> = BT.Path), >>>> (SELECT Filename.FilenameId FROM Filename WHERE Filename.name >>>> = BT.Name) , >>>> BT.LStat, >>>> BT.MD5, >>>> BT.DeltaSeq >>>> FROM batch_testing BT; >>>> >>>> NOTE: I have used the table Batch_Testing... because I was testing. :) >>>> >>> >>> Thanks for your feedback. >>> >>> The difference makes me think that it might be a cache issue. Can you >>> give us the detailed execution plan for both queries ? explain (analyze >>> and buffers) and your server settings? That might be probably done with >>> pg_autoexplain I think. >> >> The explain analyze output is in the URL: >> https://gist.github.com/dlangille/58a414a260f4c6706d7e >> <https://gist.github.com/dlangille/58a414a260f4c6706d7e> >> >> I also just added postgresql.conf > > I suggest we ignore what I was trying above. > > I am still looking at this, mostly because of this issue: > > 07-Feb 18:01 crey-sd JobId 230447: Sending spooled attrs to the Director. > Despooling 422,031,119 bytes ... > 08-Feb 01:31 bacula-dir JobId 230447: Bacula bacula-dir 7.4.0 (16Jan16): > > FD Files Written: 1,365,074 > FD Bytes Written: 332,924,576,236 (332.9 GB) > > The above is the original job. The following is the copy to tape: > > 09-Feb 00:24 crey-sd JobId 230519: Sending spooled attrs to the Director. > Despooling 422,031,119 bytes ... > 09-Feb 06:57 bacula-dir JobId 230516: Bacula bacula-dir 7.4.0 (16Jan16): > > SD Files Written: 1,365,074 > SD Bytes Written: 333,216,925,325 (333.2 GB) > > The first job spends about 5.5 hours getting attributes into the database. > The second job spends about 6.5 hours.
I ran this job again, this time to the LTO-4 tape library (see bacula-users post http://marc.info/?t=145497169500003&r=1&w=2 <http://marc.info/?t=145497169500003&r=1&w=2>). 16-Feb 00:12 tape01-sd JobId 231188: Sending spooled attrs to the Director. Despooling 422,031,119 bytes ... 16-Feb 00:15 bacula-dir JobId 231187: Bacula bacula-dir 7.4.0 (16Jan16): Three minutes. Yes, that's thee minutes to spool it all into the table. That's fantastic. Thank you. -- Dan Langille - BSDCan / PGCon d...@langille.org
signature.asc
Description: Message signed with OpenPGP using GPGMail
------------------------------------------------------------------------------ Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
_______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
