Re: [openstack-dev] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-10 Thread Renat Akhmerov 

> On 08 Dec 2015, at 21:10, Mike Bayer  wrote:
> 
> 
> 
> On 12/08/2015 07:28 AM, Renat Akhmerov wrote:
>> Hi,
>> 
>> Moshe, thanks a lot for bringing this up. I remember I tried to find a
>> way to change isolation level per connection but also was unable to do that.
> 
> Current SQLAlchemy has a lot of isolation level options.There is a
> complete guide to doing this from an ORM perspective at
> http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#setting-transaction-isolation-levels.
> 
> 
> On a per-connection basis, you would use the isolation_level execution
> option.  An example of how to integrate this into Session usage is in
> this section at
> http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#setting-isolation-for-individual-sessions.
> 
> In oslo.db, there's no engine-wide isolation level setting available,
> however when the engine is returned from the oslo.db version of
> create_engine, you can immediately get a copy of that engine with the
> isolation_level option set by calling:
> 
> engine = oslo_db.create_engine(...)
> engine = engine.execution_options(isolation_level='READ_COMMITTED’)

This is very helpful, thanks Mike. We’ll try to use this way.

> The future of oslo.db is oriented around the upgraded "enginefacade"
> API.  Adding isolation hooks to this new API is a TODO, but is
> straightforward; if you're using the new enginefacade API, I can
> expedite having the appropriate hooks added in.

Yes, I think this is a too important property to just ignore it.

Renat Akhmerov
@ Mirantis Inc.


__
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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-08 Thread ELISHA, Moshe (Moshe) 
Thank you, Gordon for your reply.

In your patch I see that you are setting the isolation_level to 
REPEATABLE_READ. We are trying to change the isolation level to READ_COMMITTED.
I like to refer you to this performance article[1] that determines that 
READ_COMMITTED is usually better than REPEATABLE_READ.

In addition, maybe an additional reason for your slowness is that you do it 
when you get the connection - so you do an extra DB command that sets the 
isolation level.
My patch is changing the default isolation level for all connections upfront.

[1] 
https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/



From: gord chung [mailto:g...@live.ca]
Sent: Tuesday, December 08, 2015 2:50 PM
To: openstack-dev@lists.openstack.org
Subject: Re: [openstack-dev] [oslo.db][sqlalchemy][mistral] Configuring default 
transaction isolation level

we had this in ceilometer events. you can see it here: 
https://github.com/openstack/ceilometer/commit/898cd3d036c4358aa16f7b3e2028365dc9829213

note, that patch is removing it because it slowed everything way down because 
of locking. if you can avoid it, avoid it.
On 08/12/2015 7:28 AM, Renat Akhmerov wrote:
Hi,

Moshe, thanks a lot for bringing this up. I remember I tried to find a way to 
change isolation level per connection but also was unable to do that.

An advice from oslo team would be very helpful.

Renat Akhmerov
@ Mirantis Inc.



On 08 Dec 2015, at 13:41, ELISHA, Moshe (Moshe) 
<moshe.eli...@alcatel-lucent.com<mailto:moshe.eli...@alcatel-lucent.com>> wrote:

Hi,

We at Mistral want to move from the default transaction isolation level of 
REPEATABLE READ to READ COMMITTED as part of a bugfix[1].

I did not find a way to pass the isolation level to sqlachemy using oslo.db and 
the current solution is to use monkey-patching[2] that adds the 
"isolation_level" property.

Is there currently a better way to set the default isolation level?
If not - I will create a BP for it.

Thanks.

[1] https://review.openstack.org/#/c/253819
[2] https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: 
openstack-dev-requ...@lists.openstack.org<mailto: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<mailto:openstack-dev-requ...@lists.openstack.org?subject:unsubscribe>

http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



--

gord
__
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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-08 Thread Renat Akhmerov 
Hi,

Moshe, thanks a lot for bringing this up. I remember I tried to find a way to 
change isolation level per connection but also was unable to do that.

An advice from oslo team would be very helpful.

Renat Akhmerov
@ Mirantis Inc.



> On 08 Dec 2015, at 13:41, ELISHA, Moshe (Moshe) 
>  wrote:
> 
> Hi,
>  
> We at Mistral want to move from the default transaction isolation level of 
> REPEATABLE READ to READ COMMITTED as part of a bugfix[1].
>  
> I did not find a way to pass the isolation level to sqlachemy using oslo.db 
> and the current solution is to use monkey-patching[2] that adds the 
> “isolation_level” property.
>  
> Is there currently a better way to set the default isolation level?
> If not – I will create a BP for it.
>  
> Thanks.
>  
> [1] https://review.openstack.org/#/c/253819 
> 
> [2] https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py 
> 
>  
> __
> 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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-08 Thread gord chung 
we had this in ceilometer events. you can see it here: 
https://github.com/openstack/ceilometer/commit/898cd3d036c4358aa16f7b3e2028365dc9829213


note, that patch is removing it because it slowed everything way down 
because of locking. if you can avoid it, avoid it.


On 08/12/2015 7:28 AM, Renat Akhmerov wrote:

Hi,

Moshe, thanks a lot for bringing this up. I remember I tried to find a 
way to change isolation level per connection but also was unable to do 
that.


An advice from oslo team would be very helpful.

Renat Akhmerov
@ Mirantis Inc.



On 08 Dec 2015, at 13:41, ELISHA, Moshe (Moshe) 
> wrote:


Hi,
We at Mistral want to move from the default transaction isolation 
level of REPEATABLE READ to READ COMMITTED as part of a bugfix[1].
I did not find a way to pass the isolation level to sqlachemy using 
oslo.db and the current solution is to use monkey-patching[2] that 
adds the “isolation_level” property.

Is there currently a better way to set the default isolation level?
If not – I will create a BP for it.
Thanks.
[1]https://review.openstack.org/#/c/253819
[2]https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py
__
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


--
gord

__
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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-08 Thread Roman Podoliaka 
Hi Moshe,

Feel free to submit a patch! This seems to be something we want to be
able to configure.

Thanks,
Roman

On Tue, Dec 8, 2015 at 9:41 AM, ELISHA, Moshe (Moshe)
 wrote:
> Hi,
>
>
>
> We at Mistral want to move from the default transaction isolation level of
> REPEATABLE READ to READ COMMITTED as part of a bugfix[1].
>
>
>
> I did not find a way to pass the isolation level to sqlachemy using oslo.db
> and the current solution is to use monkey-patching[2] that adds the
> “isolation_level” property.
>
>
>
> Is there currently a better way to set the default isolation level?
>
> If not – I will create a BP for it.
>
>
>
> Thanks.
>
>
>
> [1] https://review.openstack.org/#/c/253819
>
> [2] https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py
>
>
>
>
> __
> 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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-08 Thread Mike Bayer 


On 12/08/2015 07:28 AM, Renat Akhmerov wrote:
> Hi,
> 
> Moshe, thanks a lot for bringing this up. I remember I tried to find a
> way to change isolation level per connection but also was unable to do that.

Current SQLAlchemy has a lot of isolation level options.There is a
complete guide to doing this from an ORM perspective at
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#setting-transaction-isolation-levels.


On a per-connection basis, you would use the isolation_level execution
option.  An example of how to integrate this into Session usage is in
this section at
http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#setting-isolation-for-individual-sessions.

In oslo.db, there's no engine-wide isolation level setting available,
however when the engine is returned from the oslo.db version of
create_engine, you can immediately get a copy of that engine with the
isolation_level option set by calling:

engine = oslo_db.create_engine(...)
engine = engine.execution_options(isolation_level='READ_COMMITTED')

The future of oslo.db is oriented around the upgraded "enginefacade"
API.  Adding isolation hooks to this new API is a TODO, but is
straightforward; if you're using the new enginefacade API, I can
expedite having the appropriate hooks added in.

Please pull me in directly on any launchpads / gerrits you have where
isolation level is not doing what you'd like, thanks!






> 
> An advice from oslo team would be very helpful.
> 
> Renat Akhmerov
> @ Mirantis Inc.
> 
> 
> 
>> On 08 Dec 2015, at 13:41, ELISHA, Moshe (Moshe)
>> > > wrote:
>>
>> Hi,
>>  
>> We at Mistral want to move from the default transaction isolation
>> level of REPEATABLE READ to READ COMMITTED as part of a bugfix[1].
>>  
>> I did not find a way to pass the isolation level to sqlachemy using
>> oslo.db and the current solution is to use monkey-patching[2] that
>> adds the “isolation_level” property.
>>  
>> Is there currently a better way to set the default isolation level?
>> If not – I will create a BP for it.
>>  
>> Thanks.
>>  
>> [1] https://review.openstack.org/#/c/253819
>> [2] https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py
>>  
>> __
>> 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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-08 Thread Yanyan Hu 
Hi, Moshe,

I think you can try the following way to change the isolation_level of a
specific transaction when using oslo.db. We apply it to solve the
concurrency issue happen in Senlin project. Hope that will be helpful.

1272 session = _session(context)
1273 session.begin()
1274 session.connection(
1275 execution_options={'isolation_level': 'SERIALIZABLE'})


https://review.openstack.org/#/c/252231/11/senlin/db/sqlalchemy/api.py
#Line1214



2015-12-08 21:13 GMT+08:00 ELISHA, Moshe (Moshe) <
moshe.eli...@alcatel-lucent.com>:

> Thank you, Gordon for your reply.
>
>
>
> In your patch I see that you are setting the isolation_level to
> REPEATABLE_READ. We are trying to change the isolation level to
> READ_COMMITTED.
>
> I like to refer you to this performance article[1] that determines that
> READ_COMMITTED is usually better than REPEATABLE_READ.
>
>
>
> In addition, maybe an additional reason for your slowness is that you do
> it when you get the connection – so you do an extra DB command that sets
> the isolation level.
>
> My patch is changing the default isolation level for all connections
> upfront.
>
>
>
> [1]
> https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/
>
>
>
>
>
>
>
> *From:* gord chung [mailto:g...@live.ca]
> *Sent:* Tuesday, December 08, 2015 2:50 PM
> *To:* openstack-dev@lists.openstack.org
> *Subject:* Re: [openstack-dev] [oslo.db][sqlalchemy][mistral] Configuring
> default transaction isolation level
>
>
>
> we had this in ceilometer events. you can see it here:
> https://github.com/openstack/ceilometer/commit/898cd3d036c4358aa16f7b3e2028365dc9829213
>
> note, that patch is removing it because it slowed everything way down
> because of locking. if you can avoid it, avoid it.
>
> On 08/12/2015 7:28 AM, Renat Akhmerov wrote:
>
> Hi,
>
>
>
> Moshe, thanks a lot for bringing this up. I remember I tried to find a way
> to change isolation level per connection but also was unable to do that.
>
>
>
> An advice from oslo team would be very helpful.
>
>
>
> Renat Akhmerov
>
> @ Mirantis Inc.
>
>
>
>
>
>
>
> On 08 Dec 2015, at 13:41, ELISHA, Moshe (Moshe) <
> moshe.eli...@alcatel-lucent.com> wrote:
>
>
>
> Hi,
>
>
>
> We at Mistral want to move from the default transaction isolation level of
> REPEATABLE READ to READ COMMITTED as part of a bugfix[1].
>
>
>
> I did not find a way to pass the isolation level to sqlachemy using
> oslo.db and the current solution is to use monkey-patching[2] that adds the
> “isolation_level” property.
>
>
>
> Is there currently a better way to set the default isolation level?
>
> If not – I will create a BP for it.
>
>
>
> Thanks.
>
>
>
> [1] https://review.openstack.org/#/c/253819
>
> [2]
> https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py
>
>
>
> __
> 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
>
>
>
> --
>
> gord
>
>
> __
> 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
>
>


-- 
Best regards,

Yanyan
__
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] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level

2015-12-07 Thread ELISHA, Moshe (Moshe) 
Hi,

We at Mistral want to move from the default transaction isolation level of 
REPEATABLE READ to READ COMMITTED as part of a bugfix[1].

I did not find a way to pass the isolation level to sqlachemy using oslo.db and 
the current solution is to use monkey-patching[2] that adds the 
"isolation_level" property.

Is there currently a better way to set the default isolation level?
If not - I will create a BP for it.

Thanks.

[1] https://review.openstack.org/#/c/253819
[2] https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py

__
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