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
sorry i was reading the 'to' part of email, i selected the maria-developers instead maria-discuss