lp times out trying to enable a non-virtualized PPA for test rebuilds

[Matthias Klose]

https://launchpad.net/ubuntu/+archive/test-rebuild-20150929/+edit

Error ID: OOPS-1f186c43b4445213d2776be3cf812f4e

already tried several times, always times out

[Colin Watson (cjwatson)]

I think we incur a large cost here from rewriting many rows unnecessarily:

launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) UPDATE BinaryPackageBuild SET virtualized=NOT Processor.supports_nonvirtualized FROM Processor WHERE BinaryPackageBuild.archive=63108 AND BinaryPackageBuild.status=0 AND BinaryPackageBuild.processor=Processor.id;
                                                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on binarypackagebuild (cost=49.47..5659.90 rows=1528 width=108) (actual time=1667.053..1667.053 rows=0 loops=1) Buffers: shared hit=846845 read=1607 dirtied=4576atus=0 AND BinaryPackageBuil -> Hash Join (cost=49.47..5659.90 rows=1528 width=108) (actual time=27.692..74.754 rows=19640 loops=1)
         Hash Cond: (binarypackagebuild.processor = processor.id)
         Buffers: shared hit=8647
         -> Bitmap Heap Scan on binarypackagebuild (cost=48.22..5637.65 rows=1528 width=101) (actual time=27.659..48.549 rows=19640 loops=1)
               Recheck Cond: ((archive = 63108) AND (status = 0))
               Buffers: shared hit=8646
               -> Bitmap Index Scan on binarypackagebuild__archive__status__date_finished__id__idx (cost=0.00..47.84 rows=1528 width=0) (actual time=13.563..13.563 rows=58032 loops=1)
                     Index Cond: ((archive = 63108) AND (status = 0))
                     Buffers: shared hit=388
         -> Hash (cost=1.11..1.11 rows=11 width=11) (actual time=0.017..0.017 rows=11 loops=1)
               Buckets: 1024 Batches: 1 Memory Usage: 1kB
               Buffers: shared hit=1
               -> Seq Scan on processor (cost=0.00..1.11 rows=11 width=11) (actual time=0.006..0.010 rows=11 loops=1)
                     Buffers: shared hit=1
 Trigger for constraint binarypackagebuild_archive_fkey: time=357.263 calls=19640
 Trigger for constraint binarypackagebuild__distro_arch_series__fk: time=346.675 calls=19640
 Trigger for constraint binarypackagebuild_distro_series_fkey: time=347.908 calls=19640
 Trigger for constraint binarypackagebuild_distribution_fkey: time=376.901 calls=19640
 Trigger for constraint binarypackagebuild_build_farm_job_fkey: time=375.608 calls=19640
 Trigger for constraint binarypackagebuild_processor_fkey: time=298.695 calls=19640
 Trigger for constraint binarypackagebuild_source_package_name_fkey: time=360.448 calls=19640
 Trigger for constraint binarypackagebuild__source_package_release__fk: time=382.187 calls=19640
 Total runtime: 4545.115 ms
(25 rows)

launchpad_dogfood=# EXPLAIN (ANALYZE ON, BUFFERS ON) UPDATE BinaryPackageBuild SET virtualized=NOT Processor.supports_nonvirtualized FROM Processor WHERE BinaryPackageBuild.archive=63108 AND BinaryPackageBuild.status=0 AND Binar...

[Launchpad QA Bot (lpqabot)]

Fixed in stable r17777 (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/17777) by a commit, but not testable.