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
Re: [Maria-developers] [Commits] Rev 4369: MDEV-7148 - Recurring: InnoDB: Failing assertion: !lock->recursive in lp:maria/5.5
Sergey Vojtovich writes: > revno: 4369 > revision-id: s...@mariadb.org-20141120094823-fozdsrm1kzv46kxi > parent: jplin...@mariadb.org-20141119182734-cwbaed0ka90ocj5e > committer: Sergey Vojtovich > branch nick: 5.5 > timestamp: Thu 2014-11-20 13:48:23 +0400 > message: > MDEV-7148 - Recurring: InnoDB: Failing assertion: !lock->recursive > > On PPC64 high-loaded server may crash due to assertion failure in InnoDB > rwlocks code. > > This happened because load order between "recursive" and "writer_thread" > wasn't properly enforced. Looks ok. I failed to understand why the patch would fix the assertion mentioned in the bug report. But the patch itself looks correct. - Kristian. ___ 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] [Commits] Rev 4507: MDEV-7157 plugins.server_audit fails sporadically in buildbot. in file:///home/hf/wmar/mdev-7157/
writes: > > revno: 4507 > revision-id: holyf...@askmonty.org-20141123225345-p2xd7yrklzee10u1 > parent: ser...@pisem.net-20141121192039-d0lv6cj96kg5pw02 > committer: Alexey Botchkov > branch nick: mdev-7157 > timestamp: Mon 2014-11-24 02:53:45 +0400 > message: > MDEV-7157 plugins.server_audit fails sporadically in buildbot. > Records can get to the different place in the log when multiple thread > are logged. So the delay added to let the record be saved on the same > place. > === modified file 'mysql-test/suite/plugins/t/server_audit.test' > --- a/mysql-test/suite/plugins/t/server_audit.test2014-03-26 19:58:27 > + > +++ b/mysql-test/suite/plugins/t/server_audit.test2014-11-23 22:53:45 > + > @@ -43,6 +43,7 @@ show variables like 'server_audit%'; > set global server_audit_mode=1; > set global server_audit_events=''; > create database sa_db; > +--sleep 2 The correct way to do this is to use DEBUG_SYNC... - Kristian. ___ 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] [Commits] Rev 3879: MDEV-6593 : domain_id based replication filters in lp:~maria-captains/maria/maria-10.0-galera
Nirbhay Choubey writes: >> Suppose we have this transaction: >> >> BEGIN GTID 2-1-100 >> INSERT INTO t1 VALUES (1); >> INSERT INTO t1 VALUES (2); >> COMMIT; >> >> What happens in the following scenario? >> >> CHANGE MASTER TO master_use_gtid=current_pos, ignore_domain_ids=(2); >> START SLAVE; >> # slave IO thread connects to master; >> # slave receives: BEGIN GTID 2-1-100; INSERT INTO t1 VALUES (1); >> # slave IO thread is disconnected from master >> STOP SLAVE; >> # slave mysqld process is stopped and restarted. >> CHANGE MASTER TO master_use_gtid=no, ignore_domain_ids=(); >> START SLAVE; >> # slave IO thread connects to master; >> # slave IO thread receives: INSERT INTO t1 VALUES (2); COMMIT; Ah, now I see. CHANGE MASTER deletes the relay logs. So it's not a problem if the ignore_domain_ids changes in the middle of receiving an event group, we will discard that partial event group anyway. (I missed the fact that (almost) any CHANGE MASTER deletes the relay logs, but it's actually documented). Ok that's good, so there is no bug here. > Following your suggestion, I have now added 2 more cases (4 and 5) using > DBUG_EXECUTE_IF(+d,"kill_slave_io_after_2_events") to kill I/O after reading > first INSERT in a transaction. The outcome is expected. Cool, thanks. Looks good now, I think. - Kristian. ___ 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
