[Maria-developers] doubt about read uncommited
hi guys, i use read uncommited sometimes with innodb, that's nice and work but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine? i tested and table stay 'waiting table lock' while a looooong update occurs i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
----- Original Message -----
hi guys, i use read uncommited sometimes with innodb, that's nice and work
too scared to ask why. Its nice until the one day it shoots you.
but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine?
There is never any uncommitted as a table lock is held when writing occurs. Reads wait until that finishes.
i tested and table stay 'waiting table lock' while a looooong update occurs
Right (+write) - as above.
i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria?
It is possible to change to easily change to innodb, an actually transactional storage engine, for the transaction features you want to use? -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
hi Daniel! 2014-11-25 1:03 GMT-02:00 Daniel Black <daniel.black@openquery.com>:
----- Original Message -----
hi guys, i use read uncommited sometimes with innodb, that's nice and work
too scared to ask why. Its nice until the one day it shoots you.
no problem, i use with data that don't need fully consistency but this problem isn't my design, that's why i'm searching some solution just some points to avoid thinking that i'm a newbie trying to use myisam as innodb cause it have better count(*) speed than innodb or anything like it... i like the innodb and myisam when right used, and yes innodb can do this job with transactions, i know isolation level "read uncommited" is for some transactional engine and not all engine can use it, that's engine specific i don't remember what engine don't allow this, but i read something about it myisam and aria don't execute as transactional engine, they have a non transacitonal model, ok i know this, i'm testing what it can really do i know that's something that maybe the solution don't exists today with standard mysql/mariadb engines maybe there's another engine "plug and play", i only don't know what engine and how complex is implement this if no one solve, yes i don't know how complex is change this applicatoin since i don't have the source code... i will execute query rewrite forgetting the "solution" of 'use innodb instead of myisam'... (that's valid solution) there's space to implement some new feature to aria/myisam? i think aria is better in this case cause it have jornalling, or maybe any other solution? the table don't have many update/delete and writes are concurrent inserts, it's like a historical table i don't have access to source code of application sending the queries, i will implement something between client and mariadb to rewrite the queries, i know exactly what query must execute in this kind of "feature" since app send comments telling what 'module' is executing the query, something like "/* module=1242 */ SELECT ...."
but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine?
There is never any uncommitted as a table lock is held when writing occurs. Reads wait until that finishes.
nice, confirmed with you too :) that was a doubt, since transactional level is realated to transactional engine, i never tested if it could change something of non trasactional engines, a idiot doubt i know... but only testing to really know 100% if this change something, reading the docs or reading the myisam/aria source code, the easier method was testing... =] that's probably expected from what i know about myisam/aria, a table write lock block the table read lock (i can check this with meta data lock plugin), and read only execute after release of write lock, some writes can execute "without" "big" locks, like concurrent inserts, right? but instead of a read lock waiting write locks, i'm thinking about a one new "level of lock" inside myisam (or aria), i don't know how to name this "level of lock" using the myisam/aria words, with transactional engines that's a transactional isolation level no problem of slowing down the update query or the select query or the whole table read/write, and no problem about reading garbage (uncommited data) by select (that's expected to result "old" data) i don't know 100% how write works with myisam/aria, but at a high level i think the only problem is reading one row while write is being executed, probably this need a row level locking or a 'range level lock' or a jornal method (like aria), but i'm thinking if some kind of non transactional model could be used to read uncommited data, i will try to run 2 or more mysqld with same mysql table and check if any problem occur, probably yes... check that i don't care about transaction in this case, transaction solve the problem of course, but i'm thinking if there's a non transactional solution, and if exists how complex could be implement it, cause i didn't checked yet how complex is rewriting the whole queries from app to run with transactions, ideas and experiences with this are wellcome
i tested and table stay 'waiting table lock' while a looooong update occurs
Right (+write) - as above.
yeap, the normal "problem" (solution) of write lock what i didn't tested before is the transactional level with non transactional tables... the "dumb doubt"
i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria?
It is possible to change to easily change to innodb
i don't have the source code of app, probably i could execute a "proxy" between client and server to rewrite and implement begin/commit, and execute single statistics that myisam have (max/min/count) with triggers and statistics tables, rewrite some queries, etc... but this is a bit "complex", i don't know how complex it is yet, i didn't tested and didn't tried to convert a whole system without changing source code i know that i'm with a design problem, probably the 'easily' solution is contacting the developer , an actually transactional storage engine, for the transaction features
you want to use?
well... i don't know if this need all transactional features of innodb, i didn't checked yet if the row length could break innodb engine, and i should partition this table and create a view or a vertical partition table with spider i was thinking about ACID: * Atomicity - i don't need and myisam/aria don't have (aria "partially atomicity") * Consistency - no need... only unique keys check * Isolation - that's my doubt today myisam serialize writes that's a lock method, not a isolation method with read it do a 'uncommited' (execute reads with locks), it's a "lock oriented" engine what i'm thinking is write execute normally as myisam/aria do, only select with read uncommited could read while writing, others selects should wait table write lock as normal i don't know how complex, but maybe the simple solutions is a read that change writes to wait or something like this, i really don't know yet what's the solution, just trying to check if any idea could be possible this will increase some lock contention or anyother contention (or maybe reduce it)... anything better than long write lock is ok * Durability - execute as myisam/aria do today i think aria have better support to this since aria have jornal log and some kind of multi versioning aria write to jornal, and after write to file, the file have the 'uncommited data' and the jornal+file have the "commited" data what i'm thinking with aria: 1) when executing this selects (uncommited) we could ensure that jornal be write to file before starting select 2) writing from jornal to file is blocked 3) when select end, the write from jornal to file is released well i don't know if this is possible, just an idea... maybe no other engine do this in this way (non transactional), i didn't checked yet nosql storages/plugins solutions, any idea? sorry many doubts and questions, i'm thinking about possible solutions -- Roberto Spadim
----- Original Message -----
hi Daniel!
2014-11-25 1:03 GMT-02:00 Daniel Black <daniel.black@openquery.com>:
----- Original Message -----
hi guys, i use read uncommited sometimes with innodb, that's nice and work
too scared to ask why. Its nice until the one day it shoots you.
no problem, i use with data that don't need fully consistency but this problem isn't my design, that's why i'm searching some solution
just some points to avoid thinking that i'm a newbie trying to use myisam as innodb cause it have better count(*) speed than innodb or anything like it...
You define a solution with so man doubts and questions rather than describing a problem. This will always make you look like a newbie.
i like the innodb and myisam when right used, and yes innodb can do this job with transactions, i know isolation level "read uncommited" is for some transactional engine and not all engine can use it, that's engine specific i don't remember what engine don't allow this, but i read something about it
SQL spec - DB implementations can escalate the isolation level if they so choose.
myisam and aria don't execute as transactional engine, they have a non transacitonal model, ok i know this, i'm testing what it can really do
i know that's something that maybe the solution don't exists today with standard mysql/mariadb engines maybe there's another engine "plug and play", i only don't know what engine and how complex is implement this if no one solve, yes i don't know how complex is change this applicatoin since i don't have the source code... i will execute query rewrite
forgetting the "solution" of 'use innodb instead of myisam'... (that's valid solution) there's space to implement some new feature to aria/myisam? i think aria is better in this case cause it have jornalling, or maybe any other solution?
a solution to what? you haven't defined the problem.
the table don't have many update/delete and writes are concurrent inserts, it's like a historical table i don't have access to source code of application sending the queries, i will implement something between client and mariadb to rewrite the queries, i know exactly what query must execute in this kind of "feature" since app send comments telling what 'module' is executing the query, something like "/* module=1242 */ SELECT ...."
So you want MariaDB devs to rewrite the database because you cant' improve the application?
but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine?
There is never any uncommitted as a table lock is held when writing occurs. Reads wait until that finishes.
nice, confirmed with you too :) that was a doubt, since transactional level is realated to transactional engine
This of it as always transactional isolation level with no rollback or transactions.
, i never tested if it could change something of non trasactional engines, a idiot doubt i know... but only testing to really know 100% if this change something, reading the docs or reading the myisam/aria source code, the easier method was testing... =] that's probably expected
from what i know about myisam/aria, a table write lock block the table read lock (i can check this with meta data lock plugin), and read only execute after release of write lock, some writes can execute "without" "big" locks, like concurrent inserts, right?
but instead of a read lock waiting write locks, i'm thinking about a one new "level of lock" inside myisam (or aria), i don't know how to name this "level of lock" using the myisam/aria words, with transactional engines that's a transactional isolation level no problem of slowing down the update query or the select query or the whole table read/write, and no problem about reading garbage (uncommited data) by select (that's expected to result "old" data)
i don't know 100% how write works with myisam/aria, but at a high level i think the only problem is reading one row while write is being executed, probably this need a row level locking or a 'range level lock' or a jornal method (like aria), but i'm thinking if some kind of non transactional model could be used to read uncommited data, i will try to run 2 or more mysqld with same mysql table and check if any problem occur, probably yes...
check that i don't care about transaction in this case, transaction solve the problem of course, but i'm thinking if there's a non transactional solution, and if exists how complex could be implement it, cause i didn't checked yet how complex is rewriting the whole queries from app to run with transactions, ideas and experiences with this are wellcome
i tested and table stay 'waiting table lock' while a looooong update occurs
Right (+write) - as above.
yeap, the normal "problem" (solution) of write lock what i didn't tested before is the transactional level with non transactional tables... the "dumb doubt"
i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria?
It is possible to change to easily change to innodb
i don't have the source code of app, probably i could execute a "proxy" between client and server to rewrite and implement begin/commit, and execute single statistics that myisam have (max/min/count) with triggers and statistics tables, rewrite some queries, etc... but this is a bit "complex", i don't know how complex it is yet, i didn't tested and didn't tried to convert a whole system without changing source code i know that i'm with a design problem, probably the 'easily' solution is contacting the developer
Given you can't describe the problem to us contacting the developer won't help either. Apart from some vague notions of too much time waiting in locks and needing quicker counts I'm still clueless about what you need. Be simple, concise and avoid elaboration on aspects untested/unknown.
, an actually transactional storage engine, for the transaction features
you want to use?
well... i don't know if this need all transactional features of innodb, i didn't checked yet if the row length could break innodb engine, and i should partition this table and create a view or a vertical partition table with spider
i was thinking about ACID:
* Atomicity - i don't need and myisam/aria don't have (aria "partially atomicity")
* Consistency - no need... only unique keys check
* Isolation - that's my doubt today myisam serialize writes that's a lock method, not a isolat*ion method with read it do a 'uncommited' (execute reads with locks), it's a "lock oriented" engine
what i'm thinking is write execute normally as myisam/aria do, only select with read uncommited could read while writing, others selects should wait table write lock as normal i don't know how complex, but maybe the simple solutions is a read that change writes to wait or something like this, i really don't know yet what's the solution, just trying to check if any idea could be possible this will increase some lock contention or anyother contention (or maybe reduce it)... anything better than long write lock is ok
* Durability - execute as myisam/aria do today
i think aria have better support to this since aria have jornal log and some kind of multi versioning
You're making assumptions. Stop it.
aria write to jornal, and after write to file, the file have the 'uncommited data' and the jornal+file have the "commited" data what i'm thinking with aria: 1) when executing this selects (uncommited) we could ensure that jornal be write to file before starting select 2) writing from jornal to file is blocked 3) when select end, the write from jornal to file is released
well i don't know if this is possible, just an idea...
maybe no other engine do this in this way (non transactional), i didn't checked yet nosql storages/plugins solutions, any idea? sorry many doubts and questions, i'm thinking about possible solutions
Think about the problem more than the solutions. -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
hi Daniel the problems: 1) i have a application/database design problem, i don't have access to source code, the application is running in a server, and database at other, i have only access to database 2) at database the problem is with myisam locking contention when running slow updated and selects, update get a write lock and select wait it, no problems with insert (yet), no deletes in this table (yet) i tried to partition this table, but this don't reduce the write lock time, the where part of update normally get rows from all partitions 3) users report long times waiting data (this happen with long updates, i confirm this with slow query log and processlist) what i'm thinking as possible solutions, but i don't know if it's really possible, or how complex it is 0) leave developer solve the problem rewriting code the 'best' one, but i need to contact he, i'm not finding he, trying to call, sending email and nothing... 1) convert myisam to innodb and check what happen i'm creating another server to reproduce the load, this take sometime (>100gb myisam tables) it's executing from the first email i will execute some logged queries to reproduce load, and check what problems i will have at query log there're queries without "where" part (possible slow queries to innodb) and queries using count/min/max without "where" too 2) try to improve or create a new lock method at storage engine (myisam or aria) to allow "dirty" read possible? sounds interesting? is relevant or usefull? i was reading jira about mvcc with aria, but it's from 2011, a bit old and no news 3) try other non standard mariadb engine? not sure if relevant, non standard engine = non standard problems, must discover new storages, but don't know where to start 4) use faster storage to reduce write/read time, reducing lock time i think that's not a good solution, but the last one i have, with a high cost --- do you see any other solution to this problems? any other experience like this? sorry i was reading the 'to' part of email, i selected the maria-developers instead maria-discuss
----- Original Message -----
hi Daniel
the problems:
1) i have a application/database design problem, i don't have access to source code, the application is running in a server, and database at other, i have only access to database 2) at database the problem is with myisam locking contention when running slow updated and selects, update get a write lock and select wait it, no problems with insert (yet), no deletes in this table (yet)
have you used explain {query} on all of these to verify if there is an missing index problem?
i tried to partition this table, but this don't reduce the write lock time, the where part of update normally get rows from all partitions
Don't consider partitioning yet. It probably won't help.
3) users report long times waiting data (this happen with long updates, i confirm this with slow query log and processlist)
what i'm thinking as possible solutions, but i don't know if it's really possible, or how complex it is
0) leave developer solve the problem rewriting code the 'best' one, but i need to contact he, i'm not finding he, trying to call, sending email and nothing...
When you have indexes that need adding feed this back. When you see bad queries feed those back with details and a substitute query that does the same thing.
1) convert myisam to innodb and check what happen i'm creating another server to reproduce the load, this take sometime (>100gb myisam tables) it's executing from the first email i will execute some logged queries to reproduce load, and check what problems i will have at query log there're queries without "where" part (possible slow queries to innodb) and queries using count/min/max without "where" too.
Don't remove the where part. It changes the query significantly and you don't have a comparison point. If your app doesn't do the queries without the wheres then why are you? If you get two servers replicating of each other. Have one server with myisam tables, another with innodb and at some point switch the application to a different server making sure replication has caught up before writes are enabled. Make sure the innodb server is tuned for innodb with decent buffer pool size etc. Use mariadb-10 on both. log_slow_verbosity=query_plan,explain (on both) will help a lot. mysqldumpslow to assist in summarising all similar queries. Compare the results of the slow query log on a query by query basis. See if the problem is the query, the indexes or indeed the engine. Work out what's better overall by fixing indexes, suggesting query fixes to the developer and if something is really bad and shouldn't be give devs here details.
3) try other non standard mariadb engine?
You could try tokudb the same way.
4) use faster storage to reduce write/read time, reducing lock time i think that's not a good solution, but the last one i have, with a high cost
Lock time isn't related to storage. After fixing queries and indexes you still might have a slow hardware problem. Sometimes good solutions aren't free.
do you see any other solution to this problems? any other experience like this?
Get details to identify the cause of the problems. New Relic can help breakdown web requests and information as to which DB queries occur in them. Might still be useful with closed source applications.
sorry i was reading the 'to' part of email, i selected the maria-developers instead maria-discuss
yes. -- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
Hi Daniel! 2014-11-25 5:54 GMT-02:00 Daniel Black <daniel.black@openquery.com>:
----- Original Message -----
hi Daniel
the problems:
1) i have a application/database design problem, i don't have access to source code, the application is running in a server, and database at other, i have only access to database 2) at database the problem is with myisam locking contention when running slow updated and selects, update get a write lock and select wait it, no problems with insert (yet), no deletes in this table (yet)
have you used explain {query} on all of these to verify if there is an missing index problem?
yes, that's considered and very relevant to optimizer i check profiling too, i will get a 10.1 version to check analyze too (the real execution "explain") i get slow queries mainly with slow log, and i consider creating a index, and don't create too many index, sometimes i check if histograms could help, i don't remember others options to 'tune' the optimizer, but that's what i normally do when i found a slow query or users report about contention
i tried to partition this table, but this don't reduce the write lock time, the where part of update normally get rows from all partitions
Don't consider partitioning yet. It probably won't help.
yeap :/ didn't work, i didn't tested shard with spider yet
3) users report long times waiting data (this happen with long updates, i confirm this with slow query log and processlist)
what i'm thinking as possible solutions, but i don't know if it's really possible, or how complex it is
0) leave developer solve the problem rewriting code the 'best' one, but i need to contact he, i'm not finding he, trying to call, sending email and nothing...
When you have indexes that need adding feed this back. When you see bad queries feed those back with details and a substitute query that does the same thing.
nice, i'm logging them to send to developer sorry i don't have many contact with he to solve this easily, many thanks helping me here
1) convert myisam to innodb and check what happen i'm creating another server to reproduce the load, this take sometime (>100gb myisam tables) it's executing from the first email i will execute some logged queries to reproduce load, and check what problems i will have at query log there're queries without "where" part (possible slow queries to innodb) and queries using count/min/max without "where" too.
Don't remove the where part. It changes the query significantly and you don't have a comparison point. If your app doesn't do the queries without the wheres then why are you?
yeap, i think developer use the table as a statistic table, myisam sometimes do a good job with this design, but innodb sometimes execute full table scan when it don't have a where part i was thinking about a way to rewrite the table without changing engine, something like... if i found a query without where, rewrite to read from a statistic table, create some triggers to update this statistic table and 'solve' part of the problem, i don't know if this work, again just an idea
If you get two servers replicating of each other.
yeap, i forgot the option to create replicas... i will check this, maybe just a good balance solve the main problem
Have one server with myisam tables, another with innodb and at some point switch the application to a different server making sure replication has caught up before writes are enabled.
Make sure the innodb server is tuned for innodb with decent buffer pool size etc.
yeap, what i can do today without problem is get all queries from one database log and test the same load at the other, ok this don't test the application, i'm with doubts about BLOB inside innodb table, probably the application test will get some feeling about this
Use mariadb-10 on both. log_slow_verbosity=query_plan,explain (on both) will help a lot.
hum, nice, i only record the query and the time expent, i will record the plain and explain now
mysqldumpslow to assist in summarising all similar queries.
Compare the results of the slow query log on a query by query basis.
nice, there's some percona tools that help here, i'm using it and raw log
See if the problem is the query, the indexes or indeed the engine.
Work out what's better overall by fixing indexes, suggesting query fixes to the developer and if something is really bad and shouldn't be give devs here details.
nice
3) try other non standard mariadb engine?
You could try tokudb the same way.
right, considering this too what about replication with toku? i didn't tested yet, is something different that i should pay attention?
4) use faster storage to reduce write/read time, reducing lock time i think that's not a good solution, but the last one i have, with a high cost
Lock time isn't related to storage. After fixing queries and indexes you still might have a slow hardware problem.
Sometimes good solutions aren't free.
yes
do you see any other solution to this problems? any other experience like this?
Get details to identify the cause of the problems.
New Relic can help breakdown web requests and information as to which DB queries occur in them. Might still be useful with closed source applications.
sorry i was reading the 'to' part of email, i selected the maria-developers instead maria-discuss
yes.
-- -- Daniel Black, Engineer @ Open Query (http://openquery.com.au) Remote expertise & maintenance for MySQL/MariaDB server environments.
thanks Daniel -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
Transaction level only has effect on transactional engines (and note that read uncommited is treated as read commited on TokuDB if i recall correctly) thus the variable has no effect on MyISAM and Aria. Le 25/11/2014 03:48, Roberto Spadim a écrit :
hi guys, i use read uncommited sometimes with innodb, that's nice and work but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine? i tested and table stay 'waiting table lock' while a looooong update occurs i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria?
Hi jean! 2014-11-25 1:04 GMT-02:00 Jean Weisbuch <jean@phpnet.org>:
Transaction level only has effect on transactional engines
yeap :) i tested with non transactional just to confirm the "dumb doubt"
(and note that read uncommited is treated as read commited on TokuDB if i recall correctly)
hummmm maybe i read this from tokudb... i think you are right, thanks!
thus the variable has no effect on MyISAM and Aria.
yeap... but aria have a jornal file... maybe we could do something... i was searching jira and found a delete/update concurrent with aria (MDEV-23), maybe that's the 'solution' anyone know about this MDEV?
Le 25/11/2014 03:48, Roberto Spadim a écrit :
hi guys, i use read uncommited sometimes with innodb, that's nice and work but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine? i tested and table stay 'waiting table lock' while a looooong update occurs i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria?
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim
Hi Jean, Read uncommited is definitly treated differently on TokuDB (no snapshot read). You have a description of the kind of lock taken on TokuDB depending on the isolation level here : https://github.com/Tokutek/tokudb-engine/wiki/Transactions-and-Concurrency Jocelyn Le 25/11/2014 04:04, Jean Weisbuch a écrit :
Transaction level only has effect on transactional engines (and note that read uncommited is treated as read commited on TokuDB if i recall correctly) thus the variable has no effect on MyISAM and Aria.
Le 25/11/2014 03:48, Roberto Spadim a écrit :
hi guys, i use read uncommited sometimes with innodb, that's nice and work but now, i'm using a myisam table, and a aria table.... does read uncommited work with this kind of engine? i tested and table stay 'waiting table lock' while a looooong update occurs i don't know what's the internal diference but is possible to "easily" implement read uncommited to myisam/aria?
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
Hi Jocelyn! 2014-11-25 5:27 GMT-02:00 jocelyn fournier <jocelyn.fournier@gmail.com>:
Hi Jean,
Read uncommited is definitly treated differently on TokuDB (no snapshot read). You have a description of the kind of lock taken on TokuDB depending on the isolation level here : https://github.com/Tokutek/tokudb-engine/wiki/Transactions-and-Concurrency
wow, a very nice wiki about isolation levels, should be nice this at mariadb kb, and maybe with innodb and others engines, just to better explain how it works, very nice wiki
Jocelyn
thanks Jocelyn!
participants (4)
-
Daniel Black -
Jean Weisbuch -
jocelyn fournier -
Roberto Spadim