[Maria-developers] RFC: Pausing a query thread
Hi guys, I work with hosting companies and all of my clients have the same irritating problem, one query taking a lot of time to finish and consuming a lot of resources. So I have solved the same issue but with processes on the machine by simply pausing(kill -STOP) these processes when the load is high and continuing(kill -CONT) them when the server load goes down. I want to implement the same logic within MariaDB, so I can STOP/CONT the thread that is executing this query. My idea is to implement this in the 'KILL query_id' function, so it will accept one more argument, which will be either STOP or CONT. If the parameter is not present, the default behavior should remain. What do you think about my approach? Marian
hum, it's a innodb table? a myisam? what are you talking about high resource? locked tables? high write/read ? 2013/6/24 Marian Marinov <mm@yuhu.biz>
Hi guys,
I work with hosting companies and all of my clients have the same irritating problem, one query taking a lot of time to finish and consuming a lot of resources.
So I have solved the same issue but with processes on the machine by simply pausing(kill -STOP) these processes when the load is high and continuing(kill -CONT) them when the server load goes down.
I want to implement the same logic within MariaDB, so I can STOP/CONT the thread that is executing this query.
My idea is to implement this in the 'KILL query_id' function, so it will accept one more argument, which will be either STOP or CONT. If the parameter is not present, the default behavior should remain.
What do you think about my approach?
Marian
______________________________**_________________ Mailing list: https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> Post to : maria-developers@lists.**launchpad.net<maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> More help : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
-- Roberto Spadim SPAEmpresarial
I have seen the problems with both MyISAM, InnoDB and even with memory storage. I want to stop the executing thread instead of implementing the change in the storage engines. Unfortunately hosting companies can not change the software of their clients and also don't have the manpower to do that. So a solution for them would only be if it is possible to pause the executing thread until the server calms down. It is a unique situation where general replies as "Fix your database design", "Optimize your query" or "Upgrade to the next version" are not possible. It is impossible to fix the enormous amount of software that they are hosting. Marian On 06/24/2013 03:48 PM, Roberto Spadim wrote:
hum, it's a innodb table? a myisam? what are you talking about high resource? locked tables? high write/read ?
2013/6/24 Marian Marinov <mm@yuhu.biz <mailto:mm@yuhu.biz>>
Hi guys,
I work with hosting companies and all of my clients have the same irritating problem, one query taking a lot of time to finish and consuming a lot of resources.
So I have solved the same issue but with processes on the machine by simply pausing(kill -STOP) these processes when the load is high and continuing(kill -CONT) them when the server load goes down.
I want to implement the same logic within MariaDB, so I can STOP/CONT the thread that is executing this query.
My idea is to implement this in the 'KILL query_id' function, so it will accept one more argument, which will be either STOP or CONT. If the parameter is not present, the default behavior should remain.
What do you think about my approach?
Marian
_________________________________________________ Mailing list: https://launchpad.net/~maria-__developers <https://launchpad.net/~maria-developers> Post to : maria-developers@lists.__launchpad.net <mailto:maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-__developers <https://launchpad.net/~maria-developers> More help : https://help.launchpad.net/__ListHelp <https://help.launchpad.net/ListHelp>
-- Roberto Spadim SPAEmpresarial
I think it is a known issue wiht 'hsared hosting' . Any user can fire a query killing the server (for example causing excessive memory load ude to a cartesian join on huge tables, or CPU due a a routine entering an infinite loop. It can be both due to user's lack of skills og even be a malicious attack/teeny experiment. I have always wondered how hosting companies handled that. MySQL does not provide any mechanism for 'isolating server resources per user or per database or whatever'. Still MySQL has been the most popular choice of database for such hosting companies for a decade. I think that in extreme cases most 'shared hosting will kill such query and sometimes warn users if it repeats frequently. But cases can alos be valid. Some queries require lot of memory or CPU or whatever. I think Marian's request for an admin option to pause a specific query is a reasonable workaround. But only that. A priority setting by the server for queries|users|databases and automatic allocation of resources accordingly would be a better option.
From one that used 'shared hosting' quite a lot many years ago - but who did not for 6-7 years at least.
Peter Webyog On Mon, Jun 24, 2013 at 5:31 PM, Marian Marinov <mm@yuhu.biz> wrote:
I have seen the problems with both MyISAM, InnoDB and even with memory storage.
I want to stop the executing thread instead of implementing the change in the storage engines.
Unfortunately hosting companies can not change the software of their clients and also don't have the manpower to do that.
So a solution for them would only be if it is possible to pause the executing thread until the server calms down. It is a unique situation where general replies as "Fix your database design", "Optimize your query" or "Upgrade to the next version" are not possible. It is impossible to fix the enormous amount of software that they are hosting.
Marian
On 06/24/2013 03:48 PM, Roberto Spadim wrote:
hum, it's a innodb table? a myisam? what are you talking about high resource? locked tables? high write/read ?
2013/6/24 Marian Marinov <mm@yuhu.biz <mailto:mm@yuhu.biz>>
Hi guys,
I work with hosting companies and all of my clients have the same irritating problem, one query taking a lot of time to finish and consuming a lot of resources.
So I have solved the same issue but with processes on the machine by simply pausing(kill -STOP) these processes when the load is high and continuing(kill -CONT) them when the server load goes down.
I want to implement the same logic within MariaDB, so I can STOP/CONT the thread that is executing this query.
My idea is to implement this in the 'KILL query_id' function, so it will accept one more argument, which will be either STOP or CONT. If the parameter is not present, the default behavior should remain.
What do you think about my approach?
Marian
______________________________**___________________ Mailing list: https://launchpad.net/~maria-_**_developers<https://launchpad.net/~maria-__developers><; https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
Post to : maria-developers@lists.__launc**hpad.net<http://launchpad.net><mailto: maria-developers@**lists.launchpad.net<maria-developers@lists.launchpad.net>
Unsubscribe : https://launchpad.net/~maria-_**_developers<https://launchpad.net/~maria-__developers><; https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers>
More help : https://help.launchpad.net/__**ListHelp<https://help.launchpad.net/__ListHelp><; https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
-- Roberto Spadim SPAEmpresarial
______________________________**_________________ Mailing list: https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> Post to : maria-developers@lists.**launchpad.net<maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> More help : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
Hi, Marian! On Jun 24, Marian Marinov wrote:
Hi guys,
I work with hosting companies and all of my clients have the same irritating problem, one query taking a lot of time to finish and consuming a lot of resources.
So I have solved the same issue but with processes on the machine by simply pausing(kill -STOP) these processes when the load is high and continuing(kill -CONT) them when the server load goes down.
I want to implement the same logic within MariaDB, so I can STOP/CONT the thread that is executing this query.
My idea is to implement this in the 'KILL query_id' function, so it will accept one more argument, which will be either STOP or CONT. If the parameter is not present, the default behavior should remain.
What do you think about my approach?
That's surely possible. And it can be done with a relatively small patch. But note that if you "pause" a query that's holding locks, be it pthread mutexes, row locks, table locks, metadata locks, whatever, all other connections that might need them will "pause" too. They'll wait for the lock, and may eventually time out. Regards, Sergei
could you explain better what is very consuming a lot of resources? maybe a processlist output consuming != locked like sergei wrote, a lock may cause problems when you 'pause' a query 2013/6/24 Sergei Golubchik <serg@mariadb.org>
Hi, Marian!
On Jun 24, Marian Marinov wrote:
Hi guys,
I work with hosting companies and all of my clients have the same irritating problem, one query taking a lot of time to finish and consuming a lot of resources.
So I have solved the same issue but with processes on the machine by simply pausing(kill -STOP) these processes when the load is high and continuing(kill -CONT) them when the server load goes down.
I want to implement the same logic within MariaDB, so I can STOP/CONT the thread that is executing this query.
My idea is to implement this in the 'KILL query_id' function, so it will accept one more argument, which will be either STOP or CONT. If the parameter is not present, the default behavior should remain.
What do you think about my approach?
That's surely possible. And it can be done with a relatively small patch.
But note that if you "pause" a query that's holding locks, be it pthread mutexes, row locks, table locks, metadata locks, whatever, all other connections that might need them will "pause" too. They'll wait for the lock, and may eventually time out.
Regards, Sergei
_______________________________________________ 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 SPAEmpresarial
i reread your emails, are you thinking something like this: 1)select * from table .... (very big) 2)update table (lock reads, very time consuming) 3)select * from table2 .... (very small, but i/o is very high and it will be very time consuming) questions: 1)you want to pause 1 or 2 or 3? 2)maybe you want to pause 1 and 2, to make 3 faster since i/o? 3)after pausing a select, we can't execute update since select lock for read, and update for write, (maybe a row level lock allow it, but i think sometimes it can't do and update (2) will wait (1) end to start it job) ------- there's some MDEV maybe it's what you need? MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623> - lock/unlock connection (maybe this one) MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427> - twitter like patch
I have a processlist to show you, but it will be only one of many different cases. And I do not want to discuss everything case by case. This will not solve the problem. Consuming a lot of resources can mean one of the following: - locked query is forcing other queries to stack - one query is using a lot of I/O and in doing so is generating situations where other queries may stay in locked state for longer times and adding the first issue to the problems - single query is taking a lot of CPU/Memory resources to produce its output, in doing so it starves all other services on the machine I understand Sergei's point, that is why I started this discussion. Stopping a single thread may lead to more problems then it will solve, but I'm sure we can come up with a solution. I already have a plugin, that looks for queries that take long time to finish and while they are executing it prevents new queries for the same table to be executed. But this is not a good solution :( Marian On 06/24/2013 06:37 PM, Roberto Spadim wrote:
could you explain better what is very consuming a lot of resources? maybe a processlist output consuming != locked like sergei wrote, a lock may cause problems when you 'pause' a query
2013/6/24 Sergei Golubchik <serg@mariadb.org <mailto:serg@mariadb.org>>
Hi, Marian!
On Jun 24, Marian Marinov wrote: > Hi guys, > > I work with hosting companies and all of my clients have the same > irritating problem, one query taking a lot of time to finish and > consuming a lot of resources. > > So I have solved the same issue but with processes on the machine by > simply pausing(kill -STOP) these processes when the load is high and > continuing(kill -CONT) them when the server load goes down. > > I want to implement the same logic within MariaDB, so I can STOP/CONT > the thread that is executing this query. > > My idea is to implement this in the 'KILL query_id' function, so it > will accept one more argument, which will be either STOP or CONT. If > the parameter is not present, the default behavior should remain. > > What do you think about my approach?
That's surely possible. And it can be done with a relatively small patch.
But note that if you "pause" a query that's holding locks, be it pthread mutexes, row locks, table locks, metadata locks, whatever, all other connections that might need them will "pause" too. They'll wait for the lock, and may eventually time out.
Regards, Sergei
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net <mailto:maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial
hi marian, could you see MDEV-4623 and MDEV-4427, and tell me if that could help you? MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623> - https://mariadb.atlassian.net/browse/MDEV-4623 - lock/unlock connection MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427> - https://mariadb.atlassian.net/browse/MDEV-4427 - twitter like patch (this one is to kill a query, but after MDEV-4623 we could use lock connection)
Hi Roberto, MDEV-4623 is does exactly what I want in a different manner. I have a similar thing as MDEV-4427 written as MySQL plugin. Unfortunately this have one very big problem. It sometimes breaks tables. I have seen even InnoDB tables broken by this type of intervention :( I can't reproduce it very easy for InnoDB, but for MyISAM it should be pretty easy. Also killing query, even one that takes a lot of resources may result in a loss of data, which in turn says, that the service is bad. My opinion is, we should either accept a query and try to execute it while preventing resource starvation or we should deny it. It is very bad to accept it and then kill it in the middle of its work. This is a DataBase, it should either accept data or not. Marian On 06/24/2013 08:15 PM, Roberto Spadim wrote:
hi marian, could you see MDEV-4623 and MDEV-4427, and tell me if that could help you?
MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623> - https://mariadb.atlassian.net/browse/MDEV-4623 - lock/unlock connection MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427> - https://mariadb.atlassian.net/browse/MDEV-4427 - twitter like patch (this one is to kill a query, but after MDEV-4623 we could use lock connection)
hi marian, as a beginner mysql developer, could i ask some questions? your plugin is based in daemon? information schema? other kind of plugin? just to help me how could expand mariadb
Why not take a more daring approach - implement 'priorities' in processlist? "SET PRIORITY = high|medium|low [or 1..9] FOR processlist.ID=xx;" I realize that this is not a trivial task, and obviously it requires so much change in server architecture that it is not a solution for Marian here-and-now. @Marian BTW (as you probably know the answer): what do hosting companies offering 2.95 $-per-month 'web hotel' solutions do where hundreds or thousands of users/customers share a MySQL server and one user repeatedly executes idiotic queres from home-grown applications resulting in starving of the server and thus effectively causing denial-of-service for other users/customers? I was always surprised that they could manage it with MySQL, actually! -- Peter On Mon, Jun 24, 2013 at 9:50 PM, Marian Marinov <mm@yuhu.biz> wrote:
Hi Roberto, MDEV-4623 is does exactly what I want in a different manner.
I have a similar thing as MDEV-4427 written as MySQL plugin. Unfortunately this have one very big problem. It sometimes breaks tables. I have seen even InnoDB tables broken by this type of intervention :( I can't reproduce it very easy for InnoDB, but for MyISAM it should be pretty easy. Also killing query, even one that takes a lot of resources may result in a loss of data, which in turn says, that the service is bad.
My opinion is, we should either accept a query and try to execute it while preventing resource starvation or we should deny it. It is very bad to accept it and then kill it in the middle of its work. This is a DataBase, it should either accept data or not.
Marian
On 06/24/2013 08:15 PM, Roberto Spadim wrote:
hi marian, could you see MDEV-4623 and MDEV-4427, and tell me if that could help you?
MDEV-4623 <https://mariadb.atlassian.**net/browse/MDEV-4623<https://mariadb.atlassian.net/browse/MDEV-4623>> - https://mariadb.atlassian.net/**browse/MDEV-4623<https://mariadb.atlassian.net/browse/MDEV-4623>- lock/unlock connection MDEV-4427 <https://mariadb.atlassian.**net/browse/MDEV-4427<https://mariadb.atlassian.net/browse/MDEV-4427>> - https://mariadb.atlassian.net/**browse/MDEV-4427<https://mariadb.atlassian.net/browse/MDEV-4427>- twitter like patch (this one is to kill a query, but after MDEV-4623 we could use lock connection)
______________________________**_________________ Mailing list: https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> Post to : maria-developers@lists.**launchpad.net<maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-**developers<https://launchpad.net/~maria-developers> More help : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
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.
"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.
about the priority column i will create a MDEV but i don't know if it's a good solution (maybe a performace tune, not feature that every body will use) the MDEV is: MDEV-4705 <https://mariadb.atlassian.net/browse/MDEV-4705> but again, i don't know if i will do it and if anyone will do it too, but i think it's a nice feature, at least create a global variable that change server nice value instead of using renice commands
My understanding is that Rackspace Cloud MySQL uses Virtuozzo to control IO per user. James. -------------------------------------------- On Mon, 6/24/13, Roberto Spadim <roberto@spadim.com.br> wrote: Subject: Re: [Maria-developers] RFC: Pausing a query thread To: "Peter Laursen" <peter_laursen@webyog.com> Cc: "Sergei Golubchik" <serg@mariadb.org>, "maria-developers@lists.launchpad.net" <maria-developers@lists.launchpad.net> Date: Monday, June 24, 2013, 1:56 PM 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 - vertical partitioning, 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) 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. -----Inline Attachment Follows----- _______________________________________________ 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
On 06/24/2013 11:21 PM, Peter Laursen wrote:
Why not take a more daring approach - implement 'priorities' in processlist? "SET PRIORITY = high|medium|low [or 1..9] FOR processlist.ID=xx;"
I realize that this is not a trivial task, and obviously it requires so much change in server architecture that it is not a solution for Marian here-and-now.
Priorities actually require a new logic for handling the queries. This is something that I don't think is feasible in SQL servers as a whole. The problem is that the queries are generally short lived, so implementing a priority based scheduling will only slow down the server. It will be nice for long lived queries, but then the next problem is, what would be the priority criteria and what would be the actions? We will be going again back to the problem of pausing a query or stacking many queries because of one that took a lot of resources.
@Marian BTW (as you probably know the answer): what do hosting companies offering 2.95 $-per-month 'web hotel' solutions do where hundreds or thousands of users/customers share a MySQL server and one user repeatedly executes idiotic queres from home-grown applications resulting in starving of the server and thus effectively causing denial-of-service for other users/customers? I was always surprised that they could manage it with MySQL, actually!
Most hosting companies do not monitor mysql processes and in fact don't care much until one user abuses the system. After a few strikes the user is usually persuaded either to upgrade or go to another host. Marian
-- Peter
On Mon, Jun 24, 2013 at 9:50 PM, Marian Marinov <mm@yuhu.biz <mailto:mm@yuhu.biz>> wrote:
Hi Roberto, MDEV-4623 is does exactly what I want in a different manner.
I have a similar thing as MDEV-4427 written as MySQL plugin. Unfortunately this have one very big problem. It sometimes breaks tables. I have seen even InnoDB tables broken by this type of intervention :( I can't reproduce it very easy for InnoDB, but for MyISAM it should be pretty easy. Also killing query, even one that takes a lot of resources may result in a loss of data, which in turn says, that the service is bad.
My opinion is, we should either accept a query and try to execute it while preventing resource starvation or we should deny it. It is very bad to accept it and then kill it in the middle of its work. This is a DataBase, it should either accept data or not.
Marian
On 06/24/2013 08:15 PM, Roberto Spadim wrote:
hi marian, could you see MDEV-4623 and MDEV-4427, and tell me if that could help you?
MDEV-4623 <https://mariadb.atlassian.__net/browse/MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623>> - https://mariadb.atlassian.net/__browse/MDEV-4623 <https://mariadb.atlassian.net/browse/MDEV-4623> - lock/unlock connection MDEV-4427 <https://mariadb.atlassian.__net/browse/MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427>> - https://mariadb.atlassian.net/__browse/MDEV-4427 <https://mariadb.atlassian.net/browse/MDEV-4427> - twitter like patch (this one is to kill a query, but after MDEV-4623 we could use lock connection)
_________________________________________________ Mailing list: https://launchpad.net/~maria-__developers <https://launchpad.net/~maria-developers> Post to : maria-developers@lists.__launchpad.net <mailto:maria-developers@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-__developers <https://launchpad.net/~maria-developers> More help : https://help.launchpad.net/__ListHelp <https://help.launchpad.net/ListHelp>
hi marian, you told what i'm think, the main problem is 'pause' query and 'continue' query without disconnect the user, not the priority level i think that MDEV-4623 is the solution to our problems, plus some others tunes and automatic scripts that check that a query is blocking the system and pausing / unpausing it (that what twitter patch do) the automatic script need information about query since we can't pause a query 100000 times, or the query will not be executed and process will hang for a long time (maybe with many memory allocated at client side) i think that, the main point is MDEV-4623, a interface to control mariadb connections the twitter patch MDEV-4427 is interesting since it will kill the query, but it is a problem sometimes since some applications just stop or give bad results to a killed query. A pause/unlock is a nice feature the nice part of twitter patch is the background thread to monitor queries, instead of doing a external script, they implement it inside server (maybe a daemon plugin should help us here, instead of a server patch in main line) any other point that i lost? and we should add to mdev, and after develop? i don't know if GSoC (google summer of code) will help us, but maybe someone can help developing this MDEVs mariadb team is very nice, but we can't force them to do everything we want, i send some patchs and they accept them with some conditions about code quality and community use, example display a strange value to priority column in processlist, like: "HIGH_PRIORITY_CLASS" instead of "HIGH PRIORITY" i'm not from mariadb team, but i can help writing patchs when possible and writing MDEV feature request to don't lost usefull/interesting ideas
Le 25/06/2013 00:07, Marian Marinov a écrit :
On 06/24/2013 11:21 PM, Peter Laursen wrote:
Why not take a more daring approach - implement 'priorities' in processlist? "SET PRIORITY = high|medium|low [or 1..9] FOR processlist.ID=xx;"
I realize that this is not a trivial task, and obviously it requires so much change in server architecture that it is not a solution for Marian here-and-now.
Priorities actually require a new logic for handling the queries. This is something that I don't think is feasible in SQL servers as a whole. The problem is that the queries are generally short lived, so implementing a priority based scheduling will only slow down the server.
It will be nice for long lived queries, but then the next problem is, what would be the priority criteria and what would be the actions? We will be going again back to the problem of pausing a query or stacking many queries because of one that took a lot of resources.
@Marian BTW (as you probably know the answer): what do hosting companies offering 2.95 $-per-month 'web hotel' solutions do where hundreds or thousands of users/customers share a MySQL server and one user repeatedly executes idiotic queres from home-grown applications resulting in starving of the server and thus effectively causing denial-of-service for other users/customers? I was always surprised that they could manage it with MySQL, actually!
Most hosting companies do not monitor mysql processes and in fact don't care much until one user abuses the system. After a few strikes the user is usually persuaded either to upgrade or go to another host.
In the case of a shared hosting environnement, this priority idea might not be effective in all cases. For example, if you would use a crontab script that reads the processlist to SET PRIORITY on queries, if the user has a forum or a guestbook that allows the posting of messages or the registration by bots, you can end up with many small insert/select queries that would take less than a sec but that would use resources. Another problem is that lowering priority wont be good if the server is already slow and having trouble executing the already priorized queries. Usually on shared hosting server, you limit the number of concurrent connections per user depending on the hosting plan and kill the queries running longer than X sec (you can use different settings depending on the query type and the user), you can also use a crontab script that would read the processlist periodically to count how much long queries are ran by each users then send an automated mail to the abusing customer saying that if it still happens on the next few days its database will be read-locked or that he needs to take a better hosting plan. Its not always easy to identify the origin of the problem if the admin is not watching the processlist at the moment of the problem and not always due ton long running queries but you can set a crontab that wil save the processlist on a file if the number of running queries (or the slow queries) gets abnormal (i think that one of the Percona Toolkit tools can do that) and set your monitoring to send an alert in these cases. Having on its backoffice an "overquota/abnormal ressource usage/hacked db" database disabler button is also quite handy when you encounter these issues often. Another useful practice could be to monitor if a DB has grown more than x% between two DB size calculation, if the growth seems abnormal, the admins are notified and got to check (it can send a mail/put in the back-office a recap of the biggest tables of the DB so you can directly see when its the usual WP/Prestashop or phpBB tables that can be problematic). ps: you can also check the tables called %wordlist and %wordmatch, the ones contains much datas are holding "old-style" search indexes and datas for forums and CMS and can grow very fast and using this kind of search rather than fulltext could use more resources, the use of these can be usually disabled really simply either by modifying a search type setting from the forum/CMS backoffice either by modifying it on the config file or the table config ; after that, these tables can be truncated safely.
participants (6)
-
James Briggs -
Jean Weisbuch -
Marian Marinov -
Peter Laursen -
Roberto Spadim -
Sergei Golubchik