Learn about Centmin Mod LEMP Stack today
Register Now

MySQL Runs at 100% during attack

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Oxide, Jul 10, 2020.

  1. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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
     
  2. Rake-GH

    Rake-GH Active Member

    147
    73
    28
    Jul 29, 2019
    USA
    Ratings:
    +111
    Local Time:
    2:10 AM
    default
    default
    This was happening to me also, before Cloudflare kicked in, enough traffic got through to peg my CPU at 100% for a few minutes. I rate limit with nginx now and it is no longer a problem. I also automate cloudflare so it automatically turns on under attack mode when under high load. Maybe one of those things will help you.

    It is also important to know what requests they're making, are they hitting the search feature? Captcha and rate limit anything that uses alot of resources

    as mySQL optimizations go, I just use default centminmod settings
     
  3. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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.
     
  4. eva2000

    eva2000 Administrator Staff Member

    45,404
    10,301
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,979
    Local Time:
    4:10 PM
    Nginx 1.19.x
    MariaDB 5.5/10.x
    +1 understanding and figuring out what requests are bad is key and then use Cloudflare's layer 7 tools to mitigate i.e. CF WAF/Rate Limit/Firewall Rules + Centmin Mod Nginx end ratelimiting and maybe fail2ban that is configured to talk with Cloudflare Firewall.
     
  5. Rake-GH

    Rake-GH Active Member

    147
    73
    28
    Jul 29, 2019
    USA
    Ratings:
    +111
    Local Time:
    2:10 AM
    default
    default
    For me the script kiddies only hit / so I only need to rate limit that one get request and then it doesn't affect any other pages, and people don't sit there refreshing the homepage all day so it doesn't affect anyone except the attackers. For me this was 2 lines of code and instantly stopped an attack that was happening for several months, so it was just a simple no brainer for me. I am def looking forward to setting up the CF firewall integration in the future but I have my hands full right now
     
  6. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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.
     
  7. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    Implement:
    OVH (the only hosting with real DDOS protection)
    Nginx limit_req and limit_conn
    Nginx Full page Caching via fastcgi_cache
    CSF ct_limit and regex.custom.pm auto blocking

    I don't even use Cloudflare here, and I'm constantly being attacked since 2013.
     
  8. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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.
     
  9. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    This helps a lot with that problem.
     
  10. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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.
     
  11. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    Weird that it's MYSQL affected much during the attack, mostly it is PHP-FPM will get overwhelmed.

    it's possible, like I do with XenForo 2.x

    Using CF Business or Pro plan?
     
  12. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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.
     
  13. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    I think that is IPS bad design hmm.
    Already implemented Data caching for IPS?
    Redis or Memcached?
     
  14. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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
    
     
  15. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    Only @eva2000 I would think of capable doing the right way.

    This doesn't help much with Mysql/MariaDB as I think it's more on single thread.
    Higher clock speed would be good.
     
  16. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    I have had higher, but it's always the same issue.
     
  17. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    What is the attack pattern of URL?
    99% random URL?
    Or just hitting few URI?
     
  18. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    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.
     
  19. rdan

    rdan Well-Known Member

    4,987
    1,186
    113
    May 25, 2014
    Ratings:
    +1,808
    Local Time:
    2:10 PM
    Mainline
    10.2
    What is your current Disk setup on Hetzner?
    Fast Disk also helps a lot with MYSQL.

    I have this on my biggest server:
    upload_2020-7-28_1-59-32.png
     
  20. Oxide

    Oxide Active Member

    529
    29
    28
    Mar 19, 2015
    Ratings:
    +58
    Local Time:
    4:10 PM
    2 x 1.92 TB NVMe SSD Datacenter Edition