New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Consider setting transaction_persistent=1 by default. #793

Closed
grypyrg opened this Issue Nov 9, 2016 · 3 comments

Comments

Projects
None yet
3 participants
@grypyrg
Contributor

grypyrg commented Nov 9, 2016

I've seen comments which explains why transaction_persistent=0 by default .(#653)

This issue here is to discuss this, after reading the comment, I still think transaction_persistent=1 should be default.

why 0 is good

Yes, I understand that it is possible that some connectors, applications, frameworks and many other tools disable autocommit=0 by default or wrap everything inside a transaction.

so yes, having transaction_persistent=1 by default could indeed lead the query routing behaviour to not work and feel not flexible as an unknowing inexperienced human would expect when (s)he's just adding some users or just copy pastes something from a blogpost found on the internet and then figures out that that proxysql tool did not send those darn selects to a replica.

why 1 is good

However, that exact same inexperienced person who is a dba/sysadmin might actually create some major problems in an application with the current default behaviour of transaction_persistent=0. Suddenly after setting up proxysql, select statements within the transaction go to a slave and that inexperienced person might become very happy after checking his statistics seeing that the query went to the slave.
But in turn, several of these selects actually start to show different results as it would have when executed within the transaction, as maybe the select read some data that is being changed in that transaction, and it might make wrong decisions for the application to see what to perform as insert as next query.

This problem is similar to sending all/ a lot of reads to asynchronous slaves.

Even in this case, I am very careful before I tell my customers in for example healthcare and financial sectors to just send those reads to slaves.

plea for 1 default!

To summarise, I think that, because we are dealing with data, big and small, we should aim at keeping safety/expected behavior first, only when we really want it, we can have that flexibility and set transaction_persistent=0 when it is really required.

Let's make ProxySQL proper-expected-transactional-behavior Default (and Great) Again!!

@y-trudeau

This comment has been minimized.

Show comment
Hide comment
@y-trudeau

y-trudeau Nov 10, 2016

I completely agree with Kenny, to have transaction_persistent=0 by default is dangerous for data integrity and it should be an option someone knowing the implications set if more more performance is needed. Neither Maxscale nor ScaleArc have a similar dangerous default.

I completely agree with Kenny, to have transaction_persistent=0 by default is dangerous for data integrity and it should be an option someone knowing the implications set if more more performance is needed. Neither Maxscale nor ScaleArc have a similar dangerous default.

@renecannao renecannao self-assigned this Nov 10, 2016

@renecannao

This comment has been minimized.

Show comment
Hide comment
@renecannao

renecannao Nov 10, 2016

Contributor

In several blog posts out there the there is the simple configuration "all reads to to slaves", and I think it should be clear that these are just examples and that DBA should be very selective in what should go to slaves.
Although, I often saw this as a source of confusion. The last, in issue #785 .

To draw a line, maybe it is indeed better to change the default for transaction_persistent, and allow users to change it to 0 once they become more familiar with the routing flexibility of mysql_query_rules .

Contributor

renecannao commented Nov 10, 2016

In several blog posts out there the there is the simple configuration "all reads to to slaves", and I think it should be clear that these are just examples and that DBA should be very selective in what should go to slaves.
Although, I often saw this as a source of confusion. The last, in issue #785 .

To draw a line, maybe it is indeed better to change the default for transaction_persistent, and allow users to change it to 0 once they become more familiar with the routing flexibility of mysql_query_rules .

@grypyrg

This comment has been minimized.

Show comment
Hide comment
@grypyrg

grypyrg Nov 10, 2016

Contributor

@renecannao: Thank you for your consideration.

I've myself have been working on getting some recent blogposts we wrote adjusted. I want to avoid people just copy/pasting these simplistic but non-practical examples.

Contributor

grypyrg commented Nov 10, 2016

@renecannao: Thank you for your consideration.

I've myself have been working on getting some recent blogposts we wrote adjusted. I want to avoid people just copy/pasting these simplistic but non-practical examples.

renecannao added a commit that referenced this issue Nov 11, 2016

Changed transaction_persistent=1 by default #793
Supports upgrade from previous versions

@grypyrg grypyrg closed this Nov 16, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment