mysql running over 200% CPU according to atop – pt-query-digest suggests slow running query is a lookup in wp_options for ‘autoload’ = yes

So the issue was actually a DDOS attack on our site. I used urlsnarf to see what requests were being placed and there were basically hundreds of POST request from about 20-30 or so IP addresses. Seems quite small scale for what I understood is a typical DDOS attack. It also explains why it took a few days before the site ground to a halt. We have quite a few cores running on this host and it was maxing out 7 of them. I think in a larger scale attack it would have taken out the site a lot faster… We’re behind cloudflare so maybe it was setup at that level to avoid their automatic detection…

As soon as I turned on the ‘We’re under attack mode’ the POST requests stopped coming in and the server returned to normal.

Now I just need to figure out an early detection mechanism and automatic switch of the cloudflare under attack functionality.

Related Posts:

  1. How can I find the source of slow queries in WordPress?
  2. WordPress MySQL & PHP Performance
  3. Is it useful to turn on MySQL query cache for performance
  4. Universal problem: first request after ~25 second inactivity always slower (~1 second) than subsequent requests (~1/10sec)
  5. Concurrent / simultaneous MySQL connections
  6. Slow Mysql Queries
  7. Best settings for MySQL to speed up site? [closed]
  8. How do I join a $wpdb query with user meta using default value if not exist?
  9. Alter wp_post table vs. post_meta performance/speed
  10. Slow wp-admin due to “SELECT DISTINCT post_mime_type FROM wp_posts WHERE post_type = ‘attachment'”
  11. WordPress plugin overloading with tmp tables
  12. How to simulate web traffic to test performance of a website
  13. Regular XML-RPC timeouts
  14. Cannot add or update a child row: a foreign key constraint fails
  15. Cannot delete or update a parent row: a foreign key constraint fails
  16. ERROR 2003 (HY000): Can’t connect to MySQL server on localhost (10061)
  17. Rename a column in MySQL
  18. Not unique table/alias
  19. Install mysql-python (Windows)
  20. #1055 – Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by
  21. Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
  22. Disable ONLY_FULL_GROUP_BY
  23. MySQL WHERE IN ()
  24. SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
  25. Host ‘xxx.xx.xxx.xxx’ is not allowed to connect to this MySQL server
  26. difference between primary key and unique key
  27. How to export a mysql database using Command Prompt?
  28. MySQL AS keyword
  29. Should I use the datetime or timestamp data type in MySQL?
  30. MySQL Error #1133 – Can’t find any matching row in the user table
  31. How can I prevent SQL injection in PHP?
  32. Best way to store an array in MySQL database?
  33. MySQL date formats – difficulty Inserting a date
  34. How to grant remote access permissions to mysql server for user?
  35. How to check if mysql database exists
  36. #1062 – Duplicate entry for key ‘PRIMARY’
  37. How can I convert a string to a float in mysql?
  38. Is there a Profiler equivalent for MySql?
  39. How to use greater than operator with date?
  40. How to replace the domain name in a WordPress database?
  41. Mysqldump add drop table?
  42. Optimize WordPress Query that take 5 seconds to execute
  43. MySQL Replication Latency Issues in wp-admin pages
  44. Increment value (value = value+1) of $wpdb->update
  45. #1115 – Unknown character set: ‘utf8mb4’
  46. How can I debug my database connection for unit testing?
  47. Extending the database
  48. SQL query not working in alphabetical post title/content search
  49. Changing BIGINT to INT
  50. Importing MariaDB into Mysql in WordPress
  51. How to get a Google maps SQL->XML->map marker tutorial to work in WordPress
  52. Converting mysql to $wpdb
  53. Update a WordPress post or page takes 60+ seconds
  54. Looking for most performant way to execute several similar WP queries within shortcodes
  55. add_post_meta does not respect the content
  56. Modify MySQL Query Based on Dropdown Menu
  57. Select multiple wp_postmeta keys with single select
  58. Backing up and restoring multiple sites efficiently
  59. How to prevent/delay MySQL connections when an object cache is used?
  60. Many slow queries post_type = ‘attachment’;
  61. Are these WordPress tables safe to add indexes to?
  62. SQL syntax error when getting data for a url
  63. Import CSV to MySQL, with custom registration field information
  64. Locally restoring a ManageWP full site backup
  65. WordPress doesn’t save changes some times
  66. Match tag names with form titles
  67. Update Custom Post Type Taxonomies with SQL
  68. Getting Error Trying to Create Table
  69. Custom SELECT Query not returning the_title and the_permalink
  70. Will reducing mysql permissions hinder WP function? [duplicate]
  71. Php Mysql Terms
  72. Export SQL query based on custom field?
  73. Lock wait timeout exceeded; try restarting transaction
  74. How to install WordPress from hosting service with mysql database from Compute Engine GCP?
  75. 504 Gateway Time-out after custom plugin inserts data into mysql database
  76. WordPress fails to install with “Can’t Select Database” Error (WAMPServer)
  77. What is this? MySQL array?
  78. What is the MYSQL Query to bulk update the canonical URL of specific categories of posts in WordPress?
  79. Why is this function so slow?
  80. Why should be SQL keywords, like CREATE TABLE and UPDATE, must be uppercase in WordPress?
  81. Add row to SQL array
  82. SELECT statement wrong?
  83. $wpdb->get_var not returning count
  84. mysql query on wp_user and user_meta problem
  85. $wpdb post type and term query only works when there are no dashes or spaces in the term slug and title
  86. Custom user query – orderby meta_key (that may not exist)
  87. Use custom query if main search query returns zero results in wordpress
  88. get_results() query not working only when entering strings
  89. Geographical proximity query using post_meta
  90. Can I use mysql queries to replace query_posts()?
  91. Add value to array in database
  92. How to Import Multiple Values in custom field
  93. Making CURDATE() recognize current_time()
  94. How should I troubleshoot a database serialization issue with a WordPress migration
  95. How WP decide to show or not to show in admin panel the pop-up window with hint? Need a fix
  96. SQL – Pull products of particular category from a remote WordPress woocommerce db
  97. Efficiently loop over huge number of posts
  98. Using queries in and i see 7000+queries?
  99. How can I show mysql locks?
  100. $wpdb->get_results() query empty, but same query in phpmyadmin has results
tech