You are here

10+ Ways to Crash or Overload MySQL

MySQL Performance Blog - Tue, 13/11/2007 - 3:04pm

People are sometimes contacting me and asking about bugs like this which provide a trivial way to crash MySQL to the user with basic privileges and asking me what to do.

My answer to them is - there is nothing new to it and they just sit should back and relax :)

Really - there are many ways to crash or otherwise made unavailable server with any MySQL version if you have access to it with normal privileges. We’re constantly helping people to fix mistakes in the applications which make MySQL Server useless (though few of them cause crashes to be honest) so obviously it is even easier if you have intent.

In my opinion MySQL Security should be treated the following way - if you do not allow any access to MySQL Server you are reasonably secure. There were few attacks which did not require valid MySQL account and they have been normally treated very quickly by MySQL. The moment you give someone access to MySQL Server, all MySQL Security guarantees is you can stop one from seeing data you do not want one to see (or change data) but it does ensure you can stop one from overtaking the server.

This will not really change until MySQL Server implements Global Resource Management as otherwise you can’t really control how much resources user can takes.

You would say resource hog is not a crash ? Indeed but it still can make server unavailable plus in many cases you can use result overload to make MySQL to consume so much memory so it will swap badly until it runs out of space and gets killed. In 32bit systems it was even easier as all you had to do is to get enough memory allocated to get to address space limit and MySQL will crash when one of internal memory allocations would unexpectedly fail.

To give you couple of hints:

Temporary Tables You can build query (with derived tables) which uses as many temporary tables as you like and you can size them so they would be still be created in memory.

Memory Tables If you can create memory tables you can create any number of them and even though there is max_heap_table_size to restrict size of each table total size is unrestricted. Note you can create tables as TEMPORARY so they would not be easily visible on file system.

MyISAM Sort Buffer - This one is typically set large as it is assumed only couple of tables would be repaired at the same time. What if user uses all 100 of his allowed connections to ALTER 100 different tables ? This can be offset by keeping myisam_sort_buffer_size low, but then performance would suffer.

Prepared Statements Number - Happily now there is a limit on total number of prepared statements (max_stmt_count) which can be created per server, so it is better than it was before when application which forgot to close prepared statements could easily make server to take up all memory. However there is no per user limit so one user can consume all prepared breaking other applications which need prepared statements. Moreover not all prepared statements consume same amount of memory and by preparing complex prepared statements you can eat a lot of memory. The workaround for this issue is to avoid use of prepared statements and keep max_prepared_stmt_count very low.

Prepared Statements and Blob Data If you’re want to get memory consumed by single prepared statement you can create statement with thousands of placeholders and send data for each of them using mysql_stmt_send_long_data call - Server buffers such data until you have executed prepared statement.

Innodb Table Cache Leak - Innodb never shrinks its internal table cache (data dictionary) so by creating and accessing large amount of Innodb tables you can allocate large amount of memory on server. The size is typically 4-8K per table though complex tables can require larger sizes, so this is mainly the problem for smaller servers.

Table Cache Merge Tables - Table Cache is allocated in entries and it is normally assumed each entry will use no more than couple of file descriptors. This is not the case with Merge tables for example - creating and accessing few merge tables with 1000 of subtables will likely cause your MySQL server to run out of file descriptors. The same is true for Partitioned tables in MySQL 5.1

Disk Space For MyISAM tables hosting providers used to use disk quotas for MyISAM tables. You can also use similar technique with innodb_file_per_table. However you can’t control growth of Innodb system tablespace which is used to hold undo data and which you can grow over roof by opening transaction and doing a lot of updates, or simply keeping transaction open and allowing other users to do updates - Innodb only can purge data after oldest transactions needing snapshot commits. You can kind of workaround this issue by killing transactions which are too old though proper solution would be implementing some form of limit on undo segment size. Another possibility is to use queries which use large temporary tables or sort files which can take up all space and even if they can be placed on separate partition filling it up will cause other users being unable to run their queries.

Stored Procedures - How much memory can stored procedure allocate ? say can you create 1000 variables in stored procedure and set 1M result set to each of them ? I have not experimented with other stored procedure language constructions but I do not think tight memory allocation policy is enforced.

Stored Procedures Cursors - Cursors inside of stored procedures are implemented as temporary tables, so by opening large amount of cursors which are handled as in-memory temporary tables you can consume unbound amount of memory.

Stored Procedures Recursion - It does not have to be recursion per say - just different stored procedures calling each other. Calls require memory allocation and especially stack memory allocations. There are some protections to ensure you would not run out of stack but they might not cover all cases.

Server Side Variables - Each server side can hold value up to max_allowed_packet in size (1M by default) but there does not seems to be any limit for server side variables one can create.

Parse Tree The query is internally presented using parse tree inside MySQL which of course depends on query size which is controlled by max_allowed_packet. However some MySQL optimizations such as equity propagation and range expansion can cause Parse Tree to blow up in size. For most trivial case it was fixed it was fixed though I’m not sure if all possibilities were validated.

Session variables There is no restriction on how large you can set per connection variables for unprivileged user which allows to run queries with uncontrolled resource usage.

Host Block You can have given client host blocked from accessing the server by simulating number of failed connections. This can be avoided by having high max_connect_errors variables but this will obviously disable password brute force protection.

Mutex Saturation Both Innodb and MyISAM have hotspots and having few connections which use appropriate operations heavily you can reduce system performance beyond being functional.

General Overload As MySQL does not have much of resource utilization control you can simply run heavy queries to get MySQL Server barely functional. The limits which exist are not really helpful as they do not define query complexity and resource consumption allowed for user. Heavy Disk IO queries can be one of the worse because they would both overload IO system and wipe off both MySQL and OS caches which can cause other users queries to perform order of magnitude slower than in normal case.

Some of these comes from real experiences others are just my guesses of what could break things.

As you can see from these points it does not looks like MySQL tries to make server bullet proof if somebody tries to break it intensionally - most of the limits, such as max_heap_table_size or max_prepared_stmt_count are designed to protect from typical application mistakes not from someone intentionally trying to bring MySQL Server down.

Note: I explored only some of server side objects - generally one would need to ensure for each and every object there is some global quotas so it can’t consume too much memory and also you can’t get all of it consumed by the single user/single connection.

P.S You would say how this all could be true if there are thousands of virtual hosting companies offering MySQL access. Sure they do and many of them are lucky having users using MySQL lightly and not trying to crash/overtake it. Others constantly have to detect and restrict abusers. This is not to mention many Virtual Hosting companies use old MySQL versions which typically have more issues.

Nothing I write here is security hole which is unknown to MySQL Team. What I’m trying to do is pretty much explain the issue so there is no misunderstandings on how secure is MySQL.

Entry posted by peter | 2 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks