MySQL – How to Throttle Requests to Mitigate DoS Traffic

connectionsMySQL

I've a server with ~200 wordpress websites hosted. When Google Favicon bot pays us a visit it searches all the sites indexed on our IP, that means it's requesting ~200 favicons. Most of the sites doesn't have one, so the request is passed to wordpress which has to render a 404 page. This means in a matter of seconds dozens of php processes and mysql connections (with different users), which causes a server overload.

I mitigated this by limiting the number of MySQL connection and it partially works, because the server is not crashing anymore, but when this happens it still gets overloaded and unresponsive for some time.

Given that I could either

create an empty favicon.ico file for every site
add a custom htaccess rule for favicon.ico
enable some caching plugin on wordpress

is there a better way of controlling this issue, which doesn't involve touching websites?

UPDATE

Indeed I understand I didn't explain very well why I posted here and not on stackexchange.

I believe the MySQL server can be more fine-tuned in order to avoid overloading the server. As said I already limited the maximum number of connections, but I was wondering what can be done on the databases side, if there's some way to queue or semaphore them, so that when there are 200 users connected and doing basic queries the server can somehow sustain them.

The server is a quad core with 8G of ram, running Ubuntu 12.04. My custom MySQL config:

[mysqld]
table_cache=1024
query_cache_size=64M
join_buffer_size=512K
tmp_table_size=64M
max_heap_table_size=64M
open_files_limit=3200
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
innodb_buffer_pool_size=600M
innodb_thread_concurrency=8
thread_concurrency=8
max_user_connections = 15
max_connections = 80

Best Answer

I usually consider things like this best to be caught as high in the stack as possible on Nginx or Apache level. Wordpess is doing it's job of serving a 404 page for a url that doesn't exist. The lower you get in the stack the harder it gets to distinguish between usual requests and this behaviour. From MySQL it's just a normal legit request from php.

I have this in many of my sites Nginx config:

location /favicon.ico {
    expires 365d;
    return 200;
}

For apache with htaccess you can put a shared favicon.ico (an empty image or something) and this should work (I didn't test this):

RewriteCond %{REQUEST_FILENAME} !-f 
RewriteCond favicon.ico 
RewriteRule .* path/to/shared/favicon.ico [L]

Executive Summary

For each recommendation your optimiser script has generated, check out the appropriate MySQL documentation to see if it will impact your setup. Then tweak your my.conf little by little.

Your overall objective is for MySQL to load as much as it can into memory and not hit the disk. So increasing various caches will probably help. As long as you don't exceed the physical memory in your server (you need to take into account other processes running on the server). Then, make sure your tables are indexed appropriately.

Your Specific Issues

But to address each of the items you've listed:

Query cache is supported but not enabled; Perhaps you should set the query_cache_size

The query cache is like a big hash table of Select-Statement -> Result-Table. MySQL checks to see if the same query exists in the cache and returns the cached result without re-running the query. If you enable it, run SHOW VARIABLES to see how well utilised it is (having a huge cache which isn't used is just a waste of memory). My experience of the query cache is that it seems really good in theory, but didn't provide much help in practice.

You have had 11025 queries where a join could not use an index properly; You should enable "log-queries-not-using-indexes" then look for non indexed joins in the slow query log.

Indexes are what makes or breaks a database. Enable the slow query log and use EXPLAIN to see what queries are not using indexes. Fix the slow ones first.

You have a total of 834 tables; You have 528 open tables; Current table_cache hit rate is 8%, while 132% of your table cache is in use; You should probably increase your table_cache

MySQL ISAM databases exist as pairs (I think) of files on disk, but MySQL doesn't keep them open all the time, only ones used recently. The table_cache setting controls how many files it will keep open. This appears to be related to the number of connections, so be careful setting this too high, but it seems you have stacks of memory available so increase this until all tables are cached.

Current Lock Wait ratio = 1 : 529; You may benefit from selective use of InnoDB.

MyISAM tables are great for reading, but whenever you UPDATE, INSERT or DELETE from them MySQL locks the whole table. So if, for example, you have a Page table with a HitCount field which is incremented whenever the page is loaded, the entire Page table is locked and no other connections can read from it. I've seen some particular nasty combinations of read / write queries which would lock tables for minutes or even hours! Effectively killing the site.

InnoDB isn't as fast at reading, but supports more granular write operations (only locking the one record being updated). So is a better fit for tables which are written to more frequently. Converting tables with large numbers of UPDATE, INSERT and DELETE operations to InnoDB may decrease locking and increase performance. Many apps which use MySQL default to InnoDB across the board for this very reason.

~~I think you need to drop the old MyISAM tables and re-create them as InnoDB, so there will be downtime involved.~~

Apparently an ALTER TABLE statement is all you require to change the engine type. Although full table locks will be required, so you'll have some time when you can't run queries.

I don't know if WordPress assumes InnoDB or MyISAM tables. Please check WordPress before altering the tables' engine.

Current max_heap_table_size = 16 M; Current tmp_table_size = 16 M; Of 107071 temp tables, 25% were created on disk; Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables

MySQL requires memory to do sorting (ORDER BY), JOINs and other operations involving large chunks of data, but only up to a certain limit, after that limit the operation spills out onto disk (so that one giant ORDER BY doesn't use up gigabytes of memory which could better be spent doing other things). Increasing max_heap_table_size and tmp_table_size means less operations run on the slow disk and more in fast memory (a discussion about these variables). 64M should be large enough for most cases, and making these too big means you're just wasting memory.

You have 1290 out of 1145245 that take longer than 2.000000 sec. to complete

Use the slow query log to figure out what these slow queries are. Although that ratio (0.11%) is pretty low, there may be a few really slow queries which are causing bigger problems.

Current max_connections = 500; Current threads_connected = 501; You should raise max_connections

Once you have more threads_connected than max_connections new connections are rejected. Increase max_connections (as you already have done).

As you can see, its not always obvious what to do without knowing what sorts of queries WordPress is generating.

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.