Working with number of hosting providers I always wonder how do they manage to keep things up given MySQL gives you so little ways to really restrict how much resources single user can consume. I have written over a year ago about 10+ ways to crash or overload MySQL and since that people have come to me and suggested more ways to do the same.
This is huge hole in MySQL design, thinking little about users isolations and resource quotas and interesting enough I have not seen significant changes in fresh our MySQL 5.1 GA or even something major on the roadmap for future MySQL versions. May be Drizzle will give it a thought ? This surely would help adoption by (especially low end) Hosting Providers and remember this exactly where a lot of kids start to develop their first sites and play with web technologies.
So how do the hosting providers manage to host hundreds of users on single server with single MySQL server ? Well people just seems to be nice and not looking to crash MySQL/DOS server on purpose but rather cause most of the issues unintentionally by running bad queries or installing bad software.
It is good people are nice but it may not be comforting to know you stay up just because nobody wants bring you down rather than because your systems are solid and designed to prevent such abuse.
The systems which I see people implementing are typically focused on the load eliminating – using Google UserStats patches (included in Percona releases) or Log analyzes as well as PROCESSLIST monitoring. This allows you to crack down on users which cause a lot of load which causes a lot of unintentional abusers, but to get MySQL in trouble you do not need a lot of load. You can do this by very light queries which would not show up in the PROCESSLIST or will not take too much combined time in the logs to attract your attention;
For example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SET @a1:=repeat("a",1000000); select sleep(1); SET @a2:=repeat("a",1000000); select sleep(1); SET @a3:=repeat("a",1000000); select sleep(1); SET @a4:=repeat("a",1000000); select sleep(1); SET @a5:=repeat("a",1000000); select sleep(1); SET @a6:=repeat("a",1000000); select sleep(1); SET @a7:=repeat("a",1000000); select sleep(1); SET @a8:=repeat("a",1000000); select sleep(1); SET @a9:=repeat("a",1000000); select sleep(1); SET @a10:=repeat("a",1000000); select sleep(1); SET @a11:=repeat("a",1000000); select sleep(1); SET @a12:=repeat("a",1000000); select sleep(1); SET @a13:=repeat("a",1000000); select sleep(1); ... |
Causes MySQL to “leak” 1MB of memory per second with no apparent good reason – no slow queries or queries in the PROCESS LIST and you can’t really track how much memory was allocated for given session (or restrict this number)
Now remove sleep(1) and you will get mysqld quickly running out of memory and being killed by OOM killer or being unusable for any queries. In my test I got OOM though it kept the box stalled for few minutes before that:
Out of memory: Killed process 1081 (mysqld).
automount invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0
Out of memory: Killed process 1081 (mysqld).
automount invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0Call Trace:
[] out_of_memory+0x8e/0x2f5
[] __alloc_pages+0x245/0x2ce
[] __do_page_cache_readahead+0x95/0x1d9
[] :dm_mod:dm_any_congested+0x38/0x3f
[] filemap_nopage+0x148/0x322
[] __handle_mm_fault+0x1f8/0xe23
[] do_page_fault+0x4cb/0x830
[] error_exit+0x0/0x84
Do I need any particular privileges for this to happen ? Not really – you do not even need to be able to select from the table. Any user with permission to connect to MySQL Server can crash it.
If anybody has a clever solution to this I’d love to hear it since this is a problem I deal with near daily.
One customer on a box will spin it hard enough to impact everybody else, and I’ll have to track down the culprit.
In our particular case its rarely memory or cpu that’s the problem, but one user can thrash the disk pretty hard with a big query and then everything pretty much grinds to a halt until the query completes.
Yeah,
This also can often be the problem. One thing is obviously to kill queries which run too long, which can be done from ProcessList pretty trivial way. The other thing we’ve been thinking for Percona Patches is to implement IO/CPU throttling so you can ease off users if they cause too much IOs or use too much CPU.
Usually we just track down whoever is causing problems and suspend the account. I really do wish that MySQL had support for CPU limits though.
Michael,
Sure you do. The question is how and why you can track them down. Unintentional is easy to catch, intentional is much harder.
Try SHOW full processlist, followed by show processlist 2 seconds later, and grep out any matches, follow it again 2 seconds later and grep out the matches. If it has been 4 seconds and they are running the same query same connection ID. Kill that connection.
That should clear out the deadbeats. If you want to be kinder you can send an email if they surpass a certain amount.
mysql> show full processlist;
+—–+——+———–+——+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+——+———+——+——-+———————–+
| 281 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 282 | root | localhost | NULL | Query | 3 | init | select sleep(10) |
+—–+——+———–+——+———+——+——-+———————–+
2 rows in set (0.00 sec)
mysql> show full processlist;
+—–+——+———–+——+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+——+———+——+——-+———————–+
| 281 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 282 | root | localhost | NULL | Query | 3 | init | select sleep(10) |
+—–+——+———–+——+———+——+——-+———————–+
mysql> show full processlist;
+—–+——+———–+——+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+—–+——+———–+——+———+——+——-+———————–+
| 281 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 282 | root | localhost | NULL | Query | 3 | init | select sleep(10) |
+—–+——+———–+——+———+——+——-+———————–+
2 rows in set (0.00 sec)
mysql> kill 282
You could also run a script that checks for large tables with no indexes ? And emails the account holder ?
Slow query log is not a real solution because you want to stop the queries taking resources from other users right away.
Is something this crude going to work for everyone ? No, but if you are trying to share a MySQL instance among hundreds of users without any training or knowledge of what they are trying to do, then this is the only solution you have and it is probably satisfactory in many cases.
Shared hosting providers give newbies, shell access, PHP access, sometimes more, they already have plenty of ways to make a mess of things. The usual mistakes are caused by ignorance not malice.
I have not purchased shared hosting for many years, but I thought they had moved to virtual machines to give them control and flexibility ?
—
Tom
Tom,
This was example. Sure you can see SLEEP in processlist but What if you would do the sleep in the application instead ? Try it and try to catch things in the processlist 🙂 Also note it does not have to be pure SET assignment, you can create user variable as a side effect of the query to obfuscate things.
As I mentioned before dealing with complex individual queries is easy… And this is most typical unintentional way to overload MySQL 🙂
The Shell Access/PHP Access etc is solved on the different level – the process accounting, the MySQL is however single process shared by many users which makes it hard.
Regarding Shared Hosting… it has its set and the price point. If you’re selling hosting for $5-10 a month you can’t really get VPS running as it will take too much overhead.
I actually work for a hosting company and I’m not sure exactly how our monitoring team catches things but mytop is a very useful application along with the process list and slow query logs, etc.
Michael,
mytop etc is good interactive tool You can sit and watch it but it is not productive use of your time especially if you have 100+ servers. I would say you can do the following
1) Have a script which will catch kill and report bad queries (taking too much). When you can aggregate them to see if particular users are guilty. This is often done together with watching loadavg or other hosting metric. During low load you can just let queries run but in case server is overloaded you’ve got to find a victim.
2) Slow Query log (w up to microsecond resolution) are great to find out slow queries and queries causing the load… post factum. Really helps if your goal to help users to reduce the load rather to punish them.
3) Google User Statistics found in Percona Extensions are very good way to monitor the load on per user and per table basics.
Nothing of this deals with memory resource usage though.
One web hosting company that I host with (NearlyFreeSpeech.NET) gives a private instance of MySQL server to each customers (listening on standard port on a private IP address). It provides much better isolation than everyone shares the same MySQL process.
I agree,
Private MySQL, Private apache etc is better. It is just question of resources. Can you run few hundreds or thousands of such instances per box ? And this is low cost market we look at in this case.
Peter Zaitsev of the MySQL Performance Blog wondered how do MySQL hosting providers manage, when MySQL (including the 5.1 release) offers so little by way of resource limiting of users. — Log Buffer #126
Could that example be used in a fashion for a sql injection?
Steven,
What do you mean ? With SQL injection you can do all kind of nasty stuff.
I was with host monster but they kicked us off because our mysql queries were too long. Do you know of a web hosting place where we can have longer queries
Alex,
If you produce high load the best is to get dedicated server or VPS (where resource control is enforced).