Re: [Maria-developers] doubt about read uncommited
Hi Jocelyn! 2014-11-25 5:27 GMT-02:00 jocelyn fournier jocelyn.fourn...@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! ___ 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
[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 long 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 ___ 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
Re: [Maria-developers] doubt about read uncommited
- 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 long 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. ___ 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
Re: [Maria-developers] doubt about read uncommited
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 long 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
Re: [Maria-developers] doubt about read uncommited
hi Daniel! 2014-11-25 1:03 GMT-02:00 Daniel Black daniel.bl...@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 long 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
Re: [Maria-developers] doubt about read uncommited
- Original Message - hi Daniel! 2014-11-25 1:03 GMT-02:00 Daniel Black daniel.bl...@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 long 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
Re: [Maria-developers] doubt about read uncommited
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 ___ 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
Re: [Maria-developers] doubt about read uncommited
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 long 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 ___ 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
