MySQL Runs at 100% during attack

Hello.

During a Layer-7 attack, despite the attack was fully blocked by CloudFlare. It seems like MySQL runs at 100% for a few minutes after the attack was blocked. I checked the requests per second to confirm this. However, the CPU is still at 100% CPU.

I run a forum software (Invision Power) that is constantly being attacked.

---

Any advice on what I could do to possibly improve MySQL performance? It seems to have really bad performance.

If anyone could provide some assistance it would be greatly appreciated, I will happily post/do whatever you have to do and provide the logs here.

I also run another site that uses the database really frequently for lookups, all that is fully optimized by indexing etc. It only seems like my forum software is really crashing so easily and maxing the entire CPU at 100% for several minutes even after the attack is blocked.



Available ram: 56GB DDR4
CPU: Intel(R) Xeon(R) E-2288G CPU @ 3.70GHz
Nginx Version: nginx version: nginx/1.19.0 (010620-074715-centos7-kvm-eb01721-br-25f86f0)
PHP-FPM: PHP 7.3.17 (cli) (built: Jun 1 2020 07:51:24) ( NTS )
Centmin version: Centmin Mod Menu 123.09beta01
MySQL Version: mysql Ver 15.1 Distrib 10.1.45-MariaDB, for Linux (x86_64) using readline 5.1


my.cnf

Code:

[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
local-infile=0
ignore-db-dir=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# centminmod.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 1024
max_connections = 2500
key_buffer_size = 768M
myisam_sort_buffer_size = 768M
myisam_max_sort_file_size = 8192M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_definition_cache = 10240
table_open_cache = 10240
thread_cache_size = 384
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 1024M
max_heap_table_size = 1024M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1536K
query_cache_size = 0
query_cache_type = 0
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads=1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 10000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 12G

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=12

innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 600
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 2
innodb_flush_neighbors = 1

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 128M
aria_sort_buffer_size = 128M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 1024M
sort_buffer = 256M
read_buffer = 256M
write_buffer = 256M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

 

They're just hitting the front end of the website, which is also cached using memcache etc. Just seems like something is letting MySQL to use 100% CPU Usage for a long time. Affecting all other vhosts as well.

Just trying to figure out if there is any solution without rate limiting Nginx, rather perhaps some config in my.cnf that could help it to not choke out for 5 minutes after the attack stopped.

 

The issue is that there is no DDoS Protection that is fast enough.

If I rate-limit the front page to 20 requests a second per IP Address and it is being attacked by 2000 IP's that allows 40,000 requests to slide through before they get blacklisted or detected.

I am trying to optimize my database config so it can handle that small leak while it happens, and not run at 100% CPU for several minutes before I restart MySQL.

 

As I said previously, I can mitigate the attacks fine. But there will always be a small leak, and that leaks seems to "rape" the entire dedi (AMD EPYC 64 threads).

The leak can happen for 2-3 seconds.

I cannot cache an entire forum.

 

Yes if it's a static website, Wordpress etc. However for forums that's not possible.

Rate limiting is, and I am already doing it at CloudFlare's edge. It works perfect, but there will always be requests coming through... enough to affect Invision Power, seems like it's really badly optimized.

 

Yeah it is very weird, if you have an invasion power forum you can reload the page 10 times quickly and see CPU increase a lot.

PRO Plan, however I also pay for custom rate limiting. I rate limit 10 requests per 10 seconds. Then cache all css/other so it only rate limit actual PHP Requests.

I use CloudFlare firewall rules to captcha bad ASN and countries, it works perfect.

It blocks 100% of the attack within 3-4 seconds, but the Database still shakes for a good minute or two after.. or till i restart.

My database is 100% misconfigured though, MySQL tuner estimated it would use 400GB of ram when i tried to tune it to 64.

 

I was using Memcached, but changed to Redis yesterday to see if it would have any difference.

If anyone are able to help me out with my my.cnf and optimize it, i'd happily donate or pay.



I have 192 GB DDR4 Available & AMD EPYC 7502P 32-Core Processor.

I moved to Hetzner yesterday to help it, but the issue remains despite how many resources I've gotten.


Current my.cnf

Code:

[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
local-infile=0
ignore-db-dir=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# centminmod.com

tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 1024
max_connections = 2500
key_buffer_size = 768M
myisam_sort_buffer_size = 1024M
myisam_max_sort_file_size = 10240M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
table_definition_cache = 10240
table_open_cache = 10240
thread_cache_size = 384
wait_timeout = 30
interactive_timeout=30
connect_timeout = 10
tmp_table_size = 1536M
max_heap_table_size = 1536M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1536K
query_cache_size = 0
query_cache_type = 0
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads=1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 10000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 104G

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
innodb_buffer_pool_instances=64

innodb_log_files_in_group = 2
innodb_log_file_size = 13G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 800
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 2
innodb_flush_neighbors = 1

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 128M
aria_sort_buffer_size = 128M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 1536M
sort_buffer = 384M
read_buffer = 384M
write_buffer = 384M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=ON
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

mysqltuner

Code:

>>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.45-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(0B)
[OK] Log file /var/log/mysqld.log is readable.
[!!] Log file /var/log/mysqld.log is empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] /var/log/mysqld.log doesn't contain any warning.
[OK] /var/log/mysqld.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysqld.log
[--] 0 shutdown(s) detected in /var/log/mysqld.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 7.1M (Tables: 10)
[--] Data in InnoDB tables: 13.5G (Tables: 981)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 23h 13m 32s (32M q [394.058 qps], 2M conn, TX: 100G, RX: 8G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 188.7G
[--] Max MySQL memory    : 435.5G
[--] Other process memory: 0B
[--] Total buffers: 106.4G global + 134.8M per thread (2500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 133.7G (70.83% of installed RAM)
[!!] Maximum possible memory usage: 435.5G (230.74% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (50/32M)
[OK] Highest usage of available connections: 8% (207/2500)
[OK] Aborted connections: 0.05%  (1148/2271646)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (350 temp sorts / 5M sorts)
[!!] Joins performed without indexes: 1889570
[!!] Temporary tables created on disk: 51% (2M on disk / 4M total)
[OK] Thread cache hit rate: 99% (207 created / 2M connections)
[OK] Table cache hit rate: 93% (2K open / 2K opened)
[OK] table_definition_cache(10240) is upper than number of tables(1151)
[OK] Open file limit used: 0% (114/23K)
[OK] Table locks acquired immediately: 99% (36M immediate / 36M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 64 thread(s).
[--] Using default value is good enough for your version (10.1.45-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (150M used / 805M cache)
[OK] Key buffer size / total MyISAM indexes: 768.0M/4.1M
[!!] Read Key buffer hit rate: 92.9% (366 cached / 26 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 104.0G/13.5G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 13.0G * 2/104.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 64
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (125796100340 hits/ 125796409751 total)
[OK] InnoDB Write log efficiency: 94.43% (20416417 hits/ 21621335 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1204918 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 90.5% (19M cached / 1M reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    performance_schema = ON enable PFS

 

I have had higher, but it's always the same issue.

 

I receive 50+ unique attacks a day, it's honestly always the front page. I may need to look into Nginx caching based on cookie. So when they are not logged in, it's cached.

 

2 x 1.92 TB NVMe SSD Datacenter Edition