[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [OSSTEST PATCH v2 07/41] schema: Provide indices for sg-report-flight

  • To: Ian Jackson <Ian.Jackson@xxxxxxxxxx>
  • From: George Dunlap <George.Dunlap@xxxxxxxxxx>
  • Date: Fri, 31 Jul 2020 14:21:23 +0000
  • Accept-language: en-GB, en-US
  • Authentication-results: esa4.hc3370-68.iphmx.com; dkim=none (message not signed) header.i=none
  • Cc: "xen-devel@xxxxxxxxxxxxxxxxxxxx" <xen-devel@xxxxxxxxxxxxxxxxxxxx>
  • Delivery-date: Fri, 31 Jul 2020 14:21:37 +0000
  • Ironport-sdr: rsUxuyQPNnXVk7MgZUD3od0Dw/CdEDO2LVUhHiV7bWM5rSRasc2Dla3iAP83zsmiJcYV259hyb Yxm9HB0H1g0+n3f7n+YsGtshAJzzF8aVWFjAvz8CSjO2VK5+uhoJ26jQ+EcrZotbloX96zmemH /x+EKBEsb3qa3gcsiZtf+BEzkIf8eOL5CVVVG8jX9/XoTJurU+HKxCYfdJSNgR5jbrSj8cu5vc aDdWJEE3QYgbe/kG1TDAdH8npKlUbuR3CXwIwqpJ0bTMbRSreEHnGUebeYgi+5mlb+ijJ5bAxH My8=
  • List-id: Xen developer discussion <xen-devel.lists.xenproject.org>
  • Thread-index: AQHWZy8fK8s8vZ3fmUmSnUsHUP7xn6khm9aA
  • Thread-topic: [OSSTEST PATCH v2 07/41] schema: Provide indices for sg-report-flight

> On Jul 31, 2020, at 12:37 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> These indexes allow very fast lookup of "relevant" flights eg when
> trying to justify failures.
> In my ad-hoc test case, these indices (along with the subsequent
> changes to sg-report-flight and Executive.pm, reduce the runtime of
> sg-report-flight from 2-3ks (unacceptably long!) to as little as
> 5-7s seconds - a speedup of about 500x.
> (Getting the database snapshot may take a while first, but deploying
> this code should help with that too by reducing long-running
> transactions.  Quoted perf timings are from snapshot acquisition.)
> Without these new indexes there may be a performance change from the
> query changes.  I haven't benchmarked this so I am setting the schema
> updates to be Preparatory/Needed (ie, "Schema first" as
> schema/README.updates has it), to say that the index should be created
> before the new code is deployed.
> Testing: I have tested this series by creating experimental indices
> "trial_..." in the actual production instance.  (Transactional DDL was
> very helpful with this.)  I have verified with \d that schema update
> instructions in this commit generate indexes which are equivalent to
> the trial indices.
> Deployment: AFter these schema updates are applied, the trial indices
> are redundant duplicates and should be deleted.
> CC: George Dunlap <George.Dunlap@xxxxxxxxxx>
> Signed-off-by: Ian Jackson <ian.jackson@xxxxxxxxxxxxx>

I have no idea if building an index on a LIKE is a good idea or not, but it 
certainly seems to be useful, so:

Reviewed-by: George Dunlap <george.dunlap@xxxxxxxxxx>



Lists.xenproject.org is hosted with RackSpace, monitoring our
servers 24x7x365 and backed by RackSpace's Fanatical Support®.