Re: [Maria-developers] doubt about read uncommited
Hi Jocelyn! 2014-11-25 5:27 GMT-02:00 jocelyn fournier : > 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
Re: [Maria-developers] doubt about read uncommited
Hi Daniel!
2014-11-25 5:54 GMT-02:00 Daniel Black :
>
>
> - 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
Re: [Maria-developers] doubt about read uncommited
- 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.
___
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
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
- Original Message - > hi Daniel! > > 2014-11-25 1:03 GMT-02:00 Daniel Black : > > > > > > > - 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 loc
Re: [Maria-developers] doubt about read uncommited
Hi jean! 2014-11-25 1:04 GMT-02:00 Jean Weisbuch : > 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) hu 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 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 > -- Roberto Spadim ___ 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 : > > > - 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 s
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
- 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
[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
