On Feb 12, 2016, at 11:25 AM, Martin Simmons <mar...@lispworks.com> wrote: > >>>>>> On Thu, 11 Feb 2016 18:10:52 -0500, Dan Langille said: >> >> 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. > > Unless I've misunderstood something, your speed is very much slower than min. > For me, inserting a similar sized job takes less than 2 minutes: > > 07-Feb 23:28 backupserver1-sd JobId 58993: Sending spooled attrs to the > Director. Despooling 445,307,500 bytes ... > 07-Feb 23:29 lwfs1-cam-dir JobId 58993: Bacula lwfs1-cam-dir 7.0.5 (28Jul14): > Build OS: x86_64-unknown-freebsd10.1 freebsd 10.1-RELEASE-p24 > FD Files Written: 1,201,502 > FD Bytes Written: 38,280,979,374 (38.28 GB) > > The db is PostgreSQL 9.4.5_1 running on the Director, a 2.40GHz Xeon E5620 > with 4GB of RAM and ZFS. The postgresql.conf matches postgresql.conf.sample > except for the following (many of which seem to be the default anyway): > > max_connections = 100 > shared_buffers = 128MB > dynamic_shared_memory_type = posix > synchronous_commit = off > log_timezone = 'GB' > datestyle = 'iso, mdy' > timezone = 'GB' > lc_messages = 'C' > lc_monetary = 'C' > lc_numeric = 'C' > lc_time = 'C' > default_text_search_config = 'pg_catalog.english' > > If you are running ZFS, then synchronous_commit=off may be the key thing.
I am. The differences between your configuration and mine:
shared_buffers = 4GB
shared_buffers = 128MB
#synchronous_commit = on
synchronous_commit = off
I am running ZFS and I tried with 'synchronous_commit = off'.
My zpool has lots of space (11TB free).
This command took 233 minutes:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq)
SELECT BT.FileIndex, BT.JobId, P.PathId, FN.FilenameId, BT.LStat, BT.MD5,
BT.DeltaSeq
FROM batch_testing BT JOIN Path P ON (BT.Path = P.Path)
JOIN Filename FN ON (BT.Name = FN.Name);
This is a Xeon E3-1230 V2 @ 3.30GHz with 32GB of RAM.
Martin: Is your database schema standard Bacula with no local modifications?
Wait, don't answer that. As I typed that question, I wondered how my
production database differs from stock vanilla Bacula.
I loaded a new Bacula database. These are the indexes on file:
Indexes:
"file_pkey" PRIMARY KEY, btree (fileid)
"file_jobid_idx" btree (jobid)
"file_jpfid_idx" btree (jobid, pathid, filenameid)
On my database:
Indexes:
"file_pkey" PRIMARY KEY, btree (fileid)
"file_fileindex" btree (fileindex)
"file_filenameid_idx" btree (filenameid)
"file_jobid_fileindex" btree (jobid, fileindex)
"file_jobid_idx" btree (jobid)
"file_jpfid_idx" btree (jobid, pathid, filenameid)
"file_pathid_idx" btree (pathid)
That's a lot of extra stuff. When I posted to the PostgreSQL performance
mailing list
(http://www.postgresql.org/message-id/6cb3cc7f-859f-4550-983c-074f3617a...@langille.org)
before I posted here, it was suggested I had too many indexes. My tests on
another server were not a dramatic as what I'm about to show you.
Before removing the indexes: 223 minutes
After: 4.5 minutes.
Wow. The query plans are here:
https://twitter.com/DLangille/status/698528182383804416
Thanks everyone.
--
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
