Mariadb
MariaDb 性能問題
使用 mysqltuner，我們面臨使用此數據庫的應用程序的性能問題。我們有 128 GB 的物理記憶體。但是這個數據庫設置為 16 GB。如何增加這個數據庫的記憶體和性能。我只是想調整 SQL 以更好地執行並了解如何自己做。
這是我的 mysqltuner 結果；
>> MySQLTuner 1.7.2 - 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] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 10.5.13-MariaDB-1:10.5.13+maria~bionic [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: (0B) [!!] Log file doesn't exist [!!] Log file isn't readable. -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 53G (Tables: 586) [--] Data in InnoDB tables: 8G (Tables: 61) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [!!] User 'mariadb.sys@localhost' has no password set. [!!] User 'admin@%' hasn't specific host restriction. [--] There are 612 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 17d 2h 55m 12s (5B q [3K qps], 1M conn, TX: 4370G, RX: 1173G) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 124.4G [--] Max MySQL memory : 16.8G [--] Other process memory: 12.0G [--] Total buffers: 16.3G global + 3.7M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 16.5G (13.23% of installed RAM) [OK] Maximum possible memory usage: 16.8G (13.54% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (171/5B) [OK] Highest usage of available connections: 27% (42/151) [OK] Aborted connections: 0.09% (1174/1257762) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 31.6% (1B cached / 4B selects) [!!] Query cache prunes per day: 18570996 [OK] Sorts requiring temporary tables: 0% (12 temp sorts / 60M sorts) [!!] Joins performed without indexes: 96248 [!!] Temporary tables created on disk: 31% (3M on disk / 9M total) [OK] Thread cache hit rate: 99% (1K created / 1M connections) [OK] Table cache hit rate: 25% (1K open / 4K opened) [OK] Open file limit used: 5% (1K/32K) [OK] Table locks acquired immediately: 99% (765M immediate / 766M 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: 32 thread(s). [--] Using default value is good enough for your version (10.5.13-MariaDB-1:10.5.13+maria~bionic) -------- MyISAM Metrics ---------------------------------------------------------------------------- [OK] Key buffer used: 100.0% (134M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/15.7G [OK] Read Key buffer hit rate: 96.8% (20B cached / 648M reads) [OK] Write Key buffer hit rate: 98.5% (368M cached / 363M writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 16.0G/8.5G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (6.25 %): 1.0G * 1/16.0G should be equal 25% [!!] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 16 for 1 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: 100.00% (577364940319 hits/ 577365358867 total) [!!] InnoDB Write Log efficiency: 64.94% (744310 hits/ 1146064 total) [OK] InnoDB log waits: 0.00% (0 waits / 401754 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/328.0K [OK] Aria pagecache hit rate: 99.7% (95M cached / 320K reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled. -------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled. -------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations --------------------------------------------------------------------------- General recommendations: Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Restrict Host for user@% to user@SpecificDNSorIp Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Performance should be activated for better diagnostics Consider installing Sys schema from https://github.com/mysql/mysql-sys Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_size (> 20M) join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) performance_schema = ON enable PFS innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=4G) if possible. innodb_buffer_pool_instances(=16)任何幫助真的很感激。
- 一旦您的 RAM 是數據集大小的兩倍，添加更多 RAM 將無助於提高性能。現在大部分 128GB 都沒有使用。
- 在許多情況下，可以通過使用更好的索引或更好的公式來加速慢查詢。我們需要查看這樣的查詢。
- 在大多數情況下，MyISAM 比 InnoDB 慢。所以切換引擎。
- 在 17 天內打開 4K 表是相當微不足道的；我不會擔心這個指標。
- 硬碟還是固態硬碟？我問是因為 3K qps 相當高。我們可能需要尋找減少查詢數量和/或將查詢合併到事務中的方法。
- 下一步：http: //mysql.rjweb.org/doc.php/mysql_analysis
為了節省已經完成的投資，請考慮為您的 my.cnf 提供這些建議
$$ mysqld $$部分。
key_cache_block_size=16384 # from 1024 to manage larger blocks, reducing overhead key_cache_division_limit=50 # from 100 percent for Hot/Warm caches key_cache_age_threshold=7200 # from 300 seconds to delay AGE OUT of ndx data key_buffer_size=12G # from 128M for about 75% of your MyISAM indexes in RAM query_cache_min_res_unit=512 # from 4096 to store more results in available Query Cache thread_cache_size=60 # to cover highest usage + 8 and a few spares請查看我們的個人資料以及聯繫資訊以獲得更多幫助。請讓我們知道這 6 項更改在正常執行 3 天后如何影響您的性能。