JezK

Edit File: z-server-tuning.cnf

# MariaDB tuning - pre-migration hardening # # Filename intentionally starts with 'z' so it loads last and wins over the # other files in /etc/my.cnf.d/. Kept OUT of /etc/my.cnf because cPanel # manages that file and will rewrite it. # # Rollback: delete this file and restart mariadb. # Baseline before this file: see /root/server-config-snapshot-*/mysql/ [mysqld] # --------------------------------------------------------------------------- # InnoDB - was at stock 128M buffer pool on a 250G host # --------------------------------------------------------------------------- innodb_buffer_pool_size = 32G innodb_log_file_size = 2G innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # Durability deliberately left at the default 1 (flush+sync every commit). # innodb_flush_log_at_trx_commit = 1 # --------------------------------------------------------------------------- # Connection handling # # max_connections 151 -> 2000. Safe here ONLY in combination with the thread # pool and the raised fd ceiling below - see the notes on each. # # Worst-case per-connection buffer allocation is ~3MB (sort 2M + join 256K + # read 128K + read_rnd 256K + stack 292K), so 2000 conns ~= 6G. The five # per-connection buffers are deliberately NOT raised anywhere in this file; # with a high max_connections they must stay small. # --------------------------------------------------------------------------- max_connections = 2000 # CRITICAL companion to max_connections=2000. one-thread-per-connection would # mean up to 2000 OS threads contending on 80 cores. pool-of-threads bounds # concurrent execution to the pool while still accepting all 2000 connections. thread_handling = pool-of-threads thread_pool_size = 80 thread_pool_max_threads = 2000 # TCP accept queue - migration cutovers produce exactly this burst shape. back_log = 1000 # --------------------------------------------------------------------------- # File descriptors - the REAL ceiling at this connection count, not memory. # # Requirement ~= max_connections + (table_open_cache * 2) + table_definition_cache # ~= 2000 + 16000 + 4000 = 22000, BEFORE per-table descriptors. # innodb_file_per_table=1 means every InnoDB table is its own fd, and a large # migration means many thousands of tables. # # NOTE: this cannot exceed the systemd LimitNOFILE. The drop-in at # /etc/systemd/system/mariadb.service.d/99-nofile.conf raises that to match. # Without it MariaDB silently clamps back to 40000 and this looks like a no-op. # --------------------------------------------------------------------------- open_files_limit = 200000 table_open_cache = 8000 table_definition_cache = 4000 # Default of 100 starts blocking legitimate hosts at this scale - there were # already 101 Aborted_connects at only 9 accounts. max_connect_errors = 100000 # --------------------------------------------------------------------------- # Per-account protection - native controls, replacing CloudLinux MySQL # Governor (deliberately not installed: its unmanaged RPM targets block # native cPanel upgrades and it drifts on MariaDB version changes). # --------------------------------------------------------------------------- # Hard per-account ceiling. Deliberate trade-off, chosen with eyes open: # LVE EP=20 permits 20 concurrent PHP workers and a WordPress request holds # its DB connection for essentially the whole request, so a legitimate burst # above 10 concurrent DB requests WILL return # "User already has more than 'max_user_connections' active connections" # surfacing as "Error establishing a database connection". # This favours blast-radius containment over burst tolerance. Monitor for # these rejections; if they appear across many healthy accounts rather than # one or two, revise upward to 20-25. max_user_connections = 10 # Idle connection reclaim. Was 28800 (8h) - harmless at 151 connections, a # slow leak against a 2000-slot pool. # # Safe for interactive users: this governs the MySQL connection, NOT the # application login session. WordPress auth is a cookie, and WP uses # non-persistent connections that open and close within a single request. # An admin idling in Elementor is never affected. Only persistent connections # (WP core does not use them) and long-running CLI/cron jobs are in scope - # 600s rather than 300s is deliberate headroom for exactly those. wait_timeout = 600 interactive_timeout = 600 # Release locks held by abandoned transactions. idle_transaction_timeout = 300 # DEFERRED UNTIL AFTER MIGRATION - intentionally left commented. # This is the native equivalent of Governor's query throttling, but it will # kill long-running imports and ALTER TABLEs, which is precisely what a bulk # migration generates. Enable once migration completes, or run restores with # SET SESSION max_statement_time=0. # max_statement_time = 120 # --------------------------------------------------------------------------- # Temp tables - was 16M, forcing small sorts to spill to disk # --------------------------------------------------------------------------- tmp_table_size = 256M max_heap_table_size = 256M # --------------------------------------------------------------------------- # Visibility - slow query log was off entirely # --------------------------------------------------------------------------- slow_query_log = 1 slow_query_log_file = /var/lib/mysql/slow-query.log long_query_time = 2 # --------------------------------------------------------------------------- # OPTIONAL SAFETY NET - not enabled, documented for consideration. # # With pool-of-threads, a saturated pool can lock out even root. extra_port # gives a separate listener with its own small connection allowance so an # admin can always get in and diagnose. Bind is local; CSF already blocks # 3307 externally. Enable if you want that escape hatch. # # extra_port = 3307 # extra_max_connections = 10