Re: [openstack-dev] [octavia] Optimize the query of the octavia database

[Michael Johnson]

Hi All,

I have created a patch that resolves this regression:
https://review.openstack.org/#/c/603242/

Please take a look. Locally it showed dramatic improvements. List load
balancers went from two and half minutes down to seconds when I had a
thousand Act/Stdby LBs.

The patch may need some touch ups around testing, but the
functionality should be good.

We also have some team members working on Rally support for Octavia,
so hopefully we will be able to catch a regression like this
immediately in the future. Please support those efforts if you can
contribute some time.

Michael
On Fri, Sep 14, 2018 at 6:01 PM Jeff Yang  wrote:
>
> Ok, Thank you very much for your work.
>
> Adam Harwell  于2018年9月15日周六 上午8:26写道：
>>
>> It's high priority for me as well, so we should be able to get something 
>> done very soon, I think. Look for something early next week maybe?
>>
>> Thanks,
>> --Adam
>>
>> On Thu, Sep 13, 2018, 21:18 Jeff Yang  wrote:
>>>
>>> Thanks:
>>> I found the correlative patch in neutron-lbaas: 
>>> https://review.openstack.org/#/c/568361/
>>>
>>> The bug was marked high level by our QA team. I need to fix it as soon 
>>> as possible.
>>>  Does Michael Johnson have any good suggestion? I am willing to 
>>> complete the
>>>  repair work of this bug. If your patch still takes a while to prepare.
>>>
>>> Michael Johnson  于2018年9月14日周五 上午7:56写道：

 This is a known regression in the Octavia API performance. It has an
 existing story[0] that is under development. You are correct, that
 star join is the root of the problem.
 Look for a patch soon.

 [0] https://storyboard.openstack.org/#!/story/2002933

 Michael
 On Thu, Sep 13, 2018 at 10:32 AM Erik Olof Gunnar Andersson
  wrote:
 >
 > This was solved in neutron-lbaas recently, maybe we could adopt the same 
 > method for Octavia?
 >
 > Sent from my iPhone
 >
 > On Sep 13, 2018, at 4:54 AM, Jeff Yang  wrote:
 >
 > Hi, All
 >
 > As octavia resources increase, I found that running the "openstack 
 > loadbalancer list" command takes longer and longer. Sometimes a 504 
 > error is reported.
 >
 > By reading the code, I found that octavia will performs complex left 
 > outer join queries when acquiring resources such as loadbalancer, 
 > listener, pool, etc. in order to only make one trip to the database.
 > Reference code: http://paste.openstack.org/show/730022 Line 133
 > Generated SQL statements: http://paste.openstack.org/show/730021
 >
 > So, I suggest that adjust the query strategy to provide different join 
 > queries for different resources.
 >
 > https://storyboard.openstack.org/#!/story/2003751
 >
 > __
 > OpenStack Development Mailing List (not for usage questions)
 > Unsubscribe: 
 > openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
 > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
 >
 > __
 > OpenStack Development Mailing List (not for usage questions)
 > Unsubscribe: 
 > openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
 > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

 __
 OpenStack Development Mailing List (not for usage questions)
 Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
 http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>>
>>> __
>>> OpenStack Development Mailing List (not for usage questions)
>>> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
>> __
>> OpenStack Development Mailing List (not for usage questions)
>> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [octavia] Optimize the query of the octavia database

[Jeff Yang]

Ok, Thank you very much for your work.

Adam Harwell  于2018年9月15日周六 上午8:26写道：

> It's high priority for me as well, so we should be able to get something
> done very soon, I think. Look for something early next week maybe?
>
> Thanks,
> --Adam
>
> On Thu, Sep 13, 2018, 21:18 Jeff Yang  wrote:
>
>> Thanks:
>> I found the correlative patch in neutron-lbaas:
>> https://review.openstack.org/#/c/568361/
>>
>> The bug was marked high level by our QA team. I need to fix it as
>> soon as possible.
>>  Does Michael Johnson have any good suggestion? I am willing to
>> complete the
>>  repair work of this bug. If your patch still takes a while to
>> prepare.
>>
>> Michael Johnson  于2018年9月14日周五 上午7:56写道：
>>
>>> This is a known regression in the Octavia API performance. It has an
>>> existing story[0] that is under development. You are correct, that
>>> star join is the root of the problem.
>>> Look for a patch soon.
>>>
>>> [0] https://storyboard.openstack.org/#!/story/2002933
>>>
>>> Michael
>>> On Thu, Sep 13, 2018 at 10:32 AM Erik Olof Gunnar Andersson
>>>  wrote:
>>> >
>>> > This was solved in neutron-lbaas recently, maybe we could adopt the
>>> same method for Octavia?
>>> >
>>> > Sent from my iPhone
>>> >
>>> > On Sep 13, 2018, at 4:54 AM, Jeff Yang 
>>> wrote:
>>> >
>>> > Hi, All
>>> >
>>> > As octavia resources increase, I found that running the "openstack
>>> loadbalancer list" command takes longer and longer. Sometimes a 504 error
>>> is reported.
>>> >
>>> > By reading the code, I found that octavia will performs complex left
>>> outer join queries when acquiring resources such as loadbalancer, listener,
>>> pool, etc. in order to only make one trip to the database.
>>> > Reference code: http://paste.openstack.org/show/730022 Line 133
>>> > Generated SQL statements: http://paste.openstack.org/show/730021
>>> >
>>> > So, I suggest that adjust the query strategy to provide different join
>>> queries for different resources.
>>> >
>>> > https://storyboard.openstack.org/#!/story/2003751
>>> >
>>> >
>>> __
>>> > OpenStack Development Mailing List (not for usage questions)
>>> > Unsubscribe:
>>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>>> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>> >
>>> >
>>> __
>>> > OpenStack Development Mailing List (not for usage questions)
>>> > Unsubscribe:
>>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>>> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>>
>>>
>>> __
>>> OpenStack Development Mailing List (not for usage questions)
>>> Unsubscribe:
>>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>>
>> __
>> OpenStack Development Mailing List (not for usage questions)
>> Unsubscribe:
>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [octavia] Optimize the query of the octavia database

[Adam Harwell]

It's high priority for me as well, so we should be able to get something
done very soon, I think. Look for something early next week maybe?

Thanks,
--Adam

On Thu, Sep 13, 2018, 21:18 Jeff Yang  wrote:

> Thanks:
> I found the correlative patch in neutron-lbaas:
> https://review.openstack.org/#/c/568361/
>
> The bug was marked high level by our QA team. I need to fix it as soon
> as possible.
>  Does Michael Johnson have any good suggestion? I am willing to
> complete the
>  repair work of this bug. If your patch still takes a while to prepare.
>
> Michael Johnson  于2018年9月14日周五 上午7:56写道：
>
>> This is a known regression in the Octavia API performance. It has an
>> existing story[0] that is under development. You are correct, that
>> star join is the root of the problem.
>> Look for a patch soon.
>>
>> [0] https://storyboard.openstack.org/#!/story/2002933
>>
>> Michael
>> On Thu, Sep 13, 2018 at 10:32 AM Erik Olof Gunnar Andersson
>>  wrote:
>> >
>> > This was solved in neutron-lbaas recently, maybe we could adopt the
>> same method for Octavia?
>> >
>> > Sent from my iPhone
>> >
>> > On Sep 13, 2018, at 4:54 AM, Jeff Yang  wrote:
>> >
>> > Hi, All
>> >
>> > As octavia resources increase, I found that running the "openstack
>> loadbalancer list" command takes longer and longer. Sometimes a 504 error
>> is reported.
>> >
>> > By reading the code, I found that octavia will performs complex left
>> outer join queries when acquiring resources such as loadbalancer, listener,
>> pool, etc. in order to only make one trip to the database.
>> > Reference code: http://paste.openstack.org/show/730022 Line 133
>> > Generated SQL statements: http://paste.openstack.org/show/730021
>> >
>> > So, I suggest that adjust the query strategy to provide different join
>> queries for different resources.
>> >
>> > https://storyboard.openstack.org/#!/story/2003751
>> >
>> >
>> __
>> > OpenStack Development Mailing List (not for usage questions)
>> > Unsubscribe:
>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>> >
>> >
>> __
>> > OpenStack Development Mailing List (not for usage questions)
>> > Unsubscribe:
>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
>> __
>> OpenStack Development Mailing List (not for usage questions)
>> Unsubscribe:
>> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [octavia] Optimize the query of the octavia database

[Jeff Yang]

Thanks:
I found the correlative patch in neutron-lbaas:
https://review.openstack.org/#/c/568361/

The bug was marked high level by our QA team. I need to fix it as soon
as possible.
 Does Michael Johnson have any good suggestion? I am willing to
complete the
 repair work of this bug. If your patch still takes a while to prepare.

Michael Johnson  于2018年9月14日周五 上午7:56写道：

> This is a known regression in the Octavia API performance. It has an
> existing story[0] that is under development. You are correct, that
> star join is the root of the problem.
> Look for a patch soon.
>
> [0] https://storyboard.openstack.org/#!/story/2002933
>
> Michael
> On Thu, Sep 13, 2018 at 10:32 AM Erik Olof Gunnar Andersson
>  wrote:
> >
> > This was solved in neutron-lbaas recently, maybe we could adopt the same
> method for Octavia?
> >
> > Sent from my iPhone
> >
> > On Sep 13, 2018, at 4:54 AM, Jeff Yang  wrote:
> >
> > Hi, All
> >
> > As octavia resources increase, I found that running the "openstack
> loadbalancer list" command takes longer and longer. Sometimes a 504 error
> is reported.
> >
> > By reading the code, I found that octavia will performs complex left
> outer join queries when acquiring resources such as loadbalancer, listener,
> pool, etc. in order to only make one trip to the database.
> > Reference code: http://paste.openstack.org/show/730022 Line 133
> > Generated SQL statements: http://paste.openstack.org/show/730021
> >
> > So, I suggest that adjust the query strategy to provide different join
> queries for different resources.
> >
> > https://storyboard.openstack.org/#!/story/2003751
> >
> >
> __
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe:
> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >
> >
> __
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe:
> openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [octavia] Optimize the query of the octavia database

[Michael Johnson]

This is a known regression in the Octavia API performance. It has an
existing story[0] that is under development. You are correct, that
star join is the root of the problem.
Look for a patch soon.

[0] https://storyboard.openstack.org/#!/story/2002933

Michael
On Thu, Sep 13, 2018 at 10:32 AM Erik Olof Gunnar Andersson
 wrote:
>
> This was solved in neutron-lbaas recently, maybe we could adopt the same 
> method for Octavia?
>
> Sent from my iPhone
>
> On Sep 13, 2018, at 4:54 AM, Jeff Yang  wrote:
>
> Hi, All
>
> As octavia resources increase, I found that running the "openstack 
> loadbalancer list" command takes longer and longer. Sometimes a 504 error is 
> reported.
>
> By reading the code, I found that octavia will performs complex left outer 
> join queries when acquiring resources such as loadbalancer, listener, pool, 
> etc. in order to only make one trip to the database.
> Reference code: http://paste.openstack.org/show/730022 Line 133
> Generated SQL statements: http://paste.openstack.org/show/730021
>
> So, I suggest that adjust the query strategy to provide different join 
> queries for different resources.
>
> https://storyboard.openstack.org/#!/story/2003751
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
> __
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [octavia] Optimize the query of the octavia database

[Erik Olof Gunnar Andersson]

This was solved in neutron-lbaas recently, maybe we could adopt the same method 
for Octavia?

Sent from my iPhone

On Sep 13, 2018, at 4:54 AM, Jeff Yang 
mailto:yjf1970231...@gmail.com>> wrote:


Hi, All

As octavia resources increase, I found that running the "openstack loadbalancer 
list" command takes longer and longer. Sometimes a 504 error is reported.

By reading the code, I found that octavia will performs complex left outer join 
queries when acquiring resources such as loadbalancer, listener, pool, etc. in 
order to only make one trip to the database.
Reference code: http://paste.openstack.org/show/730022 Line 133
Generated SQL statements: http://paste.openstack.org/show/730021

So, I suggest that adjust the query strategy to provide different join queries 
for different resources.

https://storyboard.openstack.org/#!/story/2003751

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: 
openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

[openstack-dev] [octavia] Optimize the query of the octavia database

[Jeff Yang]

Hi, All

As octavia resources increase, I found that running the "openstack
loadbalancer list" command takes longer and longer. Sometimes a 504 error
is reported.

By reading the code, I found that octavia will performs complex left outer
join queries when acquiring resources such as loadbalancer, listener, pool,
etc. in order to only make one trip to the database.
Reference code: http://paste.openstack.org/show/730022 Line 133
Generated SQL statements: http://paste.openstack.org/show/730021

So, I suggest that adjust the query strategy to provide different join
queries for different resources.

https://storyboard.openstack.org/#!/story/2003751
__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev