"what this should do?". Processlist would then have one more column ('PRIORITY*). And the value of that column is the priority with what the OS executes this paricular thread (forever in the lifetime of the thread or for the time of current query only - could be one more parameter). But my system knowledge is not deep enough to decide if the idea is feasible wtit reasonable effort. I actually don't know if priorities are only available for processes and not threads in various OS'es. If the control should be implemented inside the server without calling OS routines, I realize it is a huge project. Also my proposed syntax should more be seen as 'pseudo code' than an actual implementation. But SET PRIORITY [FOR THIS] could make sense if it could also be executed from clients/scripts running with sufficient privileges. You may want to SET PRIORITY *low* ("don't let this thread have any impact on other threads") - but also maybe sometimes to set it *high* in some cases ("this is more important than anything else and it does not matter if other threads suffer"). If you can afford a server instance (virtual or not) per customer there are many posibilities. But that is not Marian's environment I think. If it was, she would not have found the solution inside MySQL. I understand that this is about management of traditional 'shared hosting', where users/customers that are completely unrelated and have no knowledge of each others share access to a MySQL server - only managed by different user accounts and their privileges. -- Peter On Mon, Jun 24, 2013 at 10:56 PM, Roberto Spadim <roberto@spadim.com.br>wrote:
hi Peter, i was helping some guys in a datacenter here in brazil and they implement server hosting via virtual machines, in other words, they have a very big linux cluster and many virtual machines with memory/cpu restrictions, maybe that's the solution they give... in each virtual machine they run a 'small server', it's a very 'expensive' solution, but works... a cheaper one could be a per process restriction via variables tunes / os restrictions
there's some others mysql patchs (github lauchpad others) that limit mysql resources, but i didn't used
--- getting back to your idea: "SET PRIOTITY = xxxx FOR processlist.id=xxxx" what this should do? change 'nice' value of linux pid? it can help, but i tryed this some times and when problem is disk i/o limit it don't 'solve' the problem, just help
i see a better solution using the MDEV-4623 that i told, and this others MDEVs (MDEV-4615 <https://mariadb.atlassian.net/browse/MDEV-4615> - vertical partitioning, MDEV-3932<https://mariadb.atlassian.net/browse/MDEV-3932> - mysql 5.6 merge) with vetical/horizontal partitioning and partition lock prunes
with vertical/horizontal partitioning + partition lock prunes, we can "change" a myisam table with table lock level, to a myisam table with 'partition lock' level, and implement a good partitioning that help us to minimize the lock contention, and after we can change query cache to prune partitions too, giving a better query cache hit rate (MDEV-4676<https://mariadb.atlassian.net/browse/MDEV-4676> )
any other idea? maybe after this mdevs an script could 'optimize' server 'online' reading query cache queries removing some queries, reading processlist and locking/unlocking some process and any other feature
my ideas was added in mariadb JIRA but i don't know if this solve your problem and when someone will start development, i don't know many things about mariadb source code, and i can't develop it (not yet =], but maybe in future i can), if you want to vote that mdev we can have a better rank position in JIRA and maybe someone give more priority to this and help us developing
any other ideas? i have the same problem for many queries, but in my case i can change source code of some projects, others projects i just tell developers to rewrite queries and optimize to better performace
sorry my english errors that's not my main language.