maria-developers team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

hi Daniel!

2014-11-25 1:03 GMT-02:00 Daniel Black <daniel.black@xxxxxxxxxxxxx>:

>
>
> ----- 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