Mariadb
無法降低/計算每個連接 mariadb 記憶體使用情況
我正在執行一個小型 mariadb 數據庫來服務一些網站和監控工具（nexctloud、wordpress 和 icinga2）。作為數據庫伺服器工作的機器也執行一個 elasticsearch + graylog 實例，所以我想降低 mariadb 可以達到的最大記憶體佔用。執行 mysqltuner 會給我以下記憶體結果：
[--] Binary logging is disabled [--] Physical Memory : 7.4G [--] Max MySQL memory : 4.7G [--] Other process memory: 0B [--] Total buffers: 2.7G global + 19.7M per thread (100 max threads) [--] P_S Max memory usage: 95M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 2.9G (38.83% of installed RAM) [OK] Maximum possible memory usage: 4.7G (63.35% of installed RAM)我無法弄清楚每個連接的 19.7 M 是如何計算為
read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack
遠低於此。
這是我的 my.cnf 文件
[client] default_character_set = utf8mb4 port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] log_error = /var/log/mysql/mysql_error.log nice = 0 socket = /var/run/mysqld/mysqld.sock [mariadb] ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem tls_version = TLSv1.2,TLSv1.3 [mysqld] basedir = /usr #bind_address = 0.0.0.0 datadir = /var/lib/mysql max_allowed_packet = 16M pid_file = /var/run/mysqld/mysqld.pid port = 3306 skip_external_locking skip_name_resolve socket = /var/run/mysqld/mysqld.sock tmpdir = /dev/shm user = mysql ##tmpdir = /tmp character-set-server = utf8mb4 collation-server = utf8mb4_general_ci concurrent_insert = 2 connect_timeout = 5 interactive_timeout = 600 wait_timeout = 600 lc_messages_dir = /usr/share/mysql lc_messages = en_US transaction_isolation = READ-COMMITTED ###temp tables tmp_table_size = 64M max_heap_table_size = 64M ###innodb settings default_storage_engine = InnoDB innodb_buffer_pool_size = 2500M innodb_buffer_pool_instances = 2 innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 16M innodb_log_file_size = 384M innodb_max_dirty_pages_pct = 90 innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 4000 innodb_flush_method = O_DIRECT innodb_stats_on_metadata = 0 ###connections max_connections = 100 ###buffer settings read_buffer_size = 1M read_rnd_buffer_size = 1M sort_buffer_size = 1M join_buffer_size = 512K key_buffer_size = 128K ###log expire_logs_days = 7 general_log_file = /var/log/mysql/mysql.log general_log = 0 skip-log-bin log_error=/var/log/mysql/mysql_error.log log_slow_verbosity = query_plan log_warnings = 2 slow_query_log_file = /var/log/mysql/mysql_slow.log slow_query_log = 1 ###query cache query_cache_type = 0 query_cache_size = 0 table_definition_cache = 8000 # UPD table_open_cache = 40000 # UPD open_files_limit = 60000 thread_stack = 192K thread_cache_size = 100 back_log = 512 myisam_recover_options = BACKUP [mysqldump] host = localhost port = 3306 max_allowed_packet = 16M quick quote_names [isamchk] !include /etc/mysql/mariadb.cnf !includedir /etc/mysql/conf.d/ key_buffer = 10M非常感謝！
這是我的 mysqltuner 輸出，不幸的是，由於核心更新，mysql 實例僅在 14 小時後才執行，但通常這些是我在正常執行數週後得到的通常結果（就警告和建議而言）
-------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 14h 7m 30s (482K q [9.485 qps], 9K conn, TX: 1G, RX: 64M) [--] Reads / Writes: 90% / 10% [--] Binary logging is disabled [--] Physical Memory : 7.4G [--] Max MySQL memory : 4.7G [--] Other process memory: 0B [--] Total buffers: 2.7G global + 19.7M per thread (100 max threads) [--] P_S Max memory usage: 95M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 3.0G (40.12% of installed RAM) [OK] Maximum possible memory usage: 4.7G (63.35% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/482K) [OK] Highest usage of available connections: 10% (10/100) [OK] Aborted connections: 0.00% (0/9725) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts) [!!] Joins performed without indexes: 4747 [OK] Temporary tables created on disk: 9% (11K on disk / 122K total) [OK] Thread cache hit rate: 99% (10 created / 9K connections) [OK] Table cache hit rate: 97% (235 open / 241 opened) [OK] table_definition_cache(8000) is upper than number of tables(353) [OK] Open file limit used: 0% (57/80K) [OK] Table locks acquired immediately: 100% (900 immediate / 900 locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 95.5M [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 4 thread(s). [--] Using default value is good enough for your version (10.4.12-MariaDB-1:10.4.12+maria~buster-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 24.2% (31K used / 131K cache) [!!] Cannot calculate MyISAM index size - re-run script as root user -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 2.5G/69.8M [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 384.0M * 2/2.5G should be equal to 25% [OK] InnoDB buffer pool instances: 2 [--] Number of InnoDB Buffer Pool Chunk : 20 for 2 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.93% (9223051 hits/ 9229566 total) [!!] InnoDB Write Log efficiency: 83.88% (41174 hits/ 49089 total) [OK] InnoDB log waits: 0.00% (0 waits / 7915 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/312.0K [OK] Aria pagecache hit rate: 99.7% (678K cached / 1K 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: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysql/mysql_error.log file Control error line(s) into /var/log/mysql/mysql_error.log file Set up a Secure Password for netdata@localhost user: SET PASSWORD FOR 'netdata'@'SpecificDNSorIp' = PASSWORD('secure_password'); MySQL was started within the last 24 hours - recommendations may be inaccurate 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). Variables to adjust: join_buffer_size (> 512.0K, or always use indexes with JOINs)
為您的 my.cnf 考慮的建議
$$ mysqld $$部分
table_open_cache=500 # from 40,000 less than 400 opened_tables in 36 hours table_definition_cache=500 # from 8,000 less than 200 opened_table_definitions in 36 h sort_buffer_size=512K # from 1M to conserve RAM, expect sort_merge_passes of 4 to increase max_connections=32 # from 100 to support your max_used_connection of 15 in 36 h您會發現您的 RAM 佔用空間在“頂部”報告中更小，並且沒有性能損失。查看我的個人資料、網路個人資料以獲取聯繫資訊和免費下載的實用程序腳本以幫助進行性能調整。當使用量增加時，考慮添加 8GB RAM。
需要索引來降低每秒 3 的 select_scan 速率。
log_slow_verbosity=query_plan,explain # from query_plan to enhance Slow Log.