maria-developers team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

"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@xxxxxxxxxxxxx>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.
>