Mariadb crashing under spike in traffic/ddos

Community Support Web
1

Hello,

my clients site is struggling with DDOS attacks. As of now their setup looks like this:

Visitor → Cloudflare → Sucuri → FIrewal 7G WAF on HestiaCP → Server

Looks decent for me, but attackers still manage to take down this server. They’re crashing mariadb, and we have to reboot entire server because systemctl restart mariadb is not starting it back.

I’ll put my config and logs below. Maybe someone can offer paid help?

tail -50 /var/log/mysql/error.log 

https://pastebin.com/thTcQHyp

Example of erros in site.errors.log

https://pastebin.com/ehGZrQ48

cat my.cnf

[client]
port=3306
socket=/run/mysqld/mysqld.sock
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld_safe]
socket=/run/mysqld/mysqld.sock

[mysqld]
user=mysql
pid-file=/run/mysqld/mysqld.pid
socket=/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
general_log=OFF
log_error=/var/log/mysql/error.log
collation-server = utf8mb4_unicode_520_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

symbolic-links=0
local-infile=0

skip-external-locking
key_buffer_size = 48M
max_allowed_packet = 500M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 64M
innodb_buffer_pool_size = 20G
innodb_log_file_size = 3G
#innodb_use_native_aio = 0
innodb_file_per_table

max_connections=8000
max_user_connections=0
wait_timeout=150
interactive_timeout=60
long_query_time=10

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

!includedir /etc/mysql/releem.conf.d

perl mysqltuner

Hope someone can point me in right direction.

EDIT:

PHP-8_3.tpl

root@hello:/usr/local/hestia/data/templates/web/php-fpm# cat tuned-PHP-8_3.tpl 
; origin-src: deb/php-fpm/multiphp.tpl
;#=========================================================================#
;# Default Web Domain Template                                             #
;# DO NOT MODIFY THIS FILE! CHANGES WILL BE LOST WHEN REBUILDING DOMAINS   #
;# https://hestiacp.com/docs/server-administration/web-templates.html      #
;#=========================================================================#

[%domain%]
listen = /run/php/php%backend_version%-fpm-%domain%.sock
listen.owner = %user%
listen.group = www-data
listen.mode = 0660

user = %user%
group = %user%

pm = ondemand
pm.max_children = 600
pm.start_servers = 100
pm.max_requests = 1000

slowlog = /var/log/php8.3-fpm.log.slow
request_slowlog_timeout = 3s
request_slowlog_trace_depth = 50


pm.process_idle_timeout = 10s
pm.status_path = /status

php_admin_value[upload_tmp_dir] = /home/%user%/tmp
php_admin_value[session.save_path] = /home/%user%/tmp
php_admin_value[open_basedir] = /home/%user%/.composer:/home/%user%/web/%domain%/public_html:/home/%user%/web/%domain%/private:/home/%user%/web/%domain%/public_shtml:/home/%user%/tmp:/tmp:/var/www/html:/bin:/usr/bin:/usr/local/bin:/usr/share:/opt
php_admin_value[sendmail_path] = /usr/sbin/sendmail -t -i -f admin@%domain%

env[PATH] = /usr/local/bin:/usr/bin:/bin
env[TMP] = /home/%user%/tmp
env[TMPDIR] = /home/%user%/tmp
env[TEMP] = /home/%user%/tmp
2

I should mention that this is bare metal server.

CPU: Intel Xeon Gold 6140 (72) @ 3.700GHz

Memory: 8797MiB / 96312MiB

3

MySQL configuration has some good settings but it may require adjustments for high traffic scenarios.

  1. you should increase innodb_buffer_pool_size = 64G #Adjust based on available RAM

  2. 8000 connections is too high, Rather than setting a high max_connections, consider using ProxySQL.

Also, instead of solely increasing MySQL configuration, consider optimizing your software architecture by implementing a caching layer, such as Redis.

4

You’re complicating the setup unnecessarily. You don’t need to proxy through Sucuri and then add a firewall on HestiaCP. You can manage everything through Cloudflare and effectively filter the traffic there.

1 Like
5

Is your server’s IP public? Cloudflare does a great job of hiding the origin IP address.

6

Changed:

 innodb_buffer_pool_size = 64G

and

max_connections=500

Using proxy sql is cool, but it’s just one wordpress site.

Yes ip’s public. But only traffic allowed is from cloudfalre and sucuri.

7

Cloudflare even with setting to ,under attack mode" was letting through lots of traffic.

Attackers are using residential proxys (smart proxy is the name if I’m correct). And the attack is originating from Android phones user agents and new versions of chrome. Attack is coming from UK and USA ( that’s where this site needs to work).

8

I’m not sure if you’re using a multisite WordPress setup or have many dynamic pages, but the server configuration you mentioned can easily handle 10k to 15k/RPS

9

Consider enabling FastCGI cache or Proxy cache.

Also use RedisDB or Memcached it saves to most used Mysql queries in Memory instead

10

It’s 1 wordpress page with microcache - 30 sec. Only thing that was dying is PHP-FPM or mariadb. PHP is no longer an issue since I tuned template to:

pm = ondemand
pm.max_children = 600
pm.start_servers = 100
pm.max_requests = 1000
11

did you proxy the server via cloudflare or not?

12

FastCGI is enabled : 30 sec

Redis is in place.
Opcache is working as well.

13

Yes it’s 100% behind Cloudflare.
When I was issuing SSL certificate if was off for few minutes and censys scraped my IP. That’s why I said it’s public, but it’s accessible only via Cloudflare.

14

FastCGI caching in Nginx enabled?
On what traffic load is the currently?
Please DM me the domain name?

I never had any issues with +10k active users with smaller server then yours currently…

1 Like
15

If you’re managing a serious business, I suggest using Datadog or New Relic to profile performance and identify bottlenecks. Alternatively, consider using Docker for horizontal scaling.

16

In that case, you might want to change your IP address, lol.

17

FastCGI enabled.

Current load 1.35.

This mariadb instance is crashing only under short spikes in load.
I’ll send you DM soon.

I’m using New Relic to profile as well.

In that case, you might want to change your IP address, lol.

Will do it soon, but the traffic is coming from Cloudfalre —> Sucuri.

Any other access is blocked.

18

disable outside traffic on port 3306 and only allow localhost.

also in MySQL Configuration.

bind-address = 127.0.0.1
19

There doesn’t exists a firewall rule for Mysql so port is closed by default

20

Yes, from my understanding it should be closed.