my.cnf(Centos 8)의 mysql에 대한 최적의 설정(mariadb 최적화 10.5)

my.cnf(Centos 8)의 mysql에 대한 최적의 설정(mariadb 최적화 10.5)

다음은 mysqltuner 결과입니다.

 [--] Skipped version check for MySQLTuner script
    [!!] Successfully authenticated with no password - SECURITY RISK!
    [!!] Your MySQL version 10.5.0-MariaDB is EOL software!  Upgrade soon!
    [OK] Operating on 64-bit architecture

    -------- Log file Recommendations ------------------------------------------------------------------
    [!!] Log file  doesn't exist

    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
    [--] Data in InnoDB tables: 4.4G (Tables: 227)
    [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
    [!!] There is no basic password file list!

    -------- CVE Security Recommendations --------------------------------------------------------------
    [--] Skipped due to --cvefile option undefined

    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 50m 57s (911K q [298.114 qps], 2K conn, TX: 3G, RX: 104M)
    [--] Reads / Writes: 99% / 1%
    [--] Binary logging is disabled
    [--] Physical Memory     : 31.2G
    [--] Max MySQL memory    : 3.2G
    [--] Other process memory: 0B
    [--] Total buffers: 417.0M global + 18.9M per thread (151 max threads)
    [--] P_S Max memory usage: 0B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 568.3M (1.78% of installed RAM)
    [OK] Maximum possible memory usage: 3.2G (10.24% of installed RAM)
    [OK] Overall possible memory usage with other process is compatible with memory available
    [OK] Slow queries: 0% (0/911K)
    [OK] Highest usage of available connections: 5% (8/151)
    [OK] Aborted connections: 0.00%  (0/2906)
    [!!] 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.
    [!!] Query cache efficiency: 0.0% (0 cached / 888K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (286 temp sorts / 50K sorts)
    [!!] Joins performed without indexes: 18
    [!!] Temporary tables created on disk: 81% (15K on disk / 18K total)
    [OK] Thread cache hit rate: 99% (8 created / 2K connections)
    [OK] Table cache hit rate: 93% (93 open / 99 opened)
    [OK] table_definition_cache(400) is upper than number of tables(391)
    [OK] Open file limit used: 0% (28/4K)
    [OK] Table locks acquired immediately: 100% (143 immediate / 143 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.0-MariaDB)

    -------- MyISAM Metrics ----------------------------------------------------------------------------
    [!!] Key buffer used: 18.2% (24M used / 134M 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
    [!!] InnoDB buffer pool / data size: 128.0M/4.4G
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M * 1/128.0M should be equal to 25%
    [OK] InnoDB buffer pool instances: 1
    [--] Number of InnoDB Buffer Pool Chunk : 1 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: 95.00% (920105182 hits/ 968548737 total)
    [!!] InnoDB Write Log efficiency: 33.6% (1776 hits/ 5285 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 7061 writes)

    -------- AriaDB Metrics ----------------------------------------------------------------------------
    [--] AriaDB is enabled.
    [OK] Aria pagecache size / total Aria indexes: 128.0M/320.0K
    [OK] Aria pagecache hit rate: 98.6% (936K cached / 13K 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:
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    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).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    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
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 

Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 4.4G) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

누가 이것을 최적화할 수 있습니까? 제가 무엇을 해야만 일을 더 빨리 만들 수 있습니까?

더 나은 CPU 사용을 위해 최적화하고 싶습니다.

서버 사양: 16 코어 32 스레드 32 GB RAM

mysql 설정에 최적의 구성이 필요

세트innodb_buffer_pool_size사용 가능한 RAM의 약 70%에 도달합니다.mysqltuner가 제안한 4.4G는 현재 당신의 모든 데이터를 처리할 것입니다.자라기를 기대한다면, 더 주세요.이 설정은 CPU가 아닌 I/O에 도움이 될 수 있습니다.

(실제로 "InnoDB Read 버퍼 효율성: 95.00%(920105182 hits/968548737 총)"를 보면 paultry 128M buffer_pool이 "working set"을 적절하게 처리하는 것 같습니다.)

"CPU 사용을 개선하기 위해 최적화"를 조정할 수 없습니다.느린 쿼리를 찾고 인덱싱(특히 '복합' 인덱스) 및 쿼리 공식화 작업을 수행할 수 있습니다.그것들은 CPU에 도움이 될 것입니다.

http://mysql.rjweb.org/doc.php/mysql_analysis

my.cnf for my.ini [mysqld] 섹션에 대한 제안

innodb_buffer_pool_size=6G
thread_handling=pool-of-threads

24시간의 가동 시간 후에 새로운 MySQ를 구입합니다.LTuner 전체 보고서가 게시되어 프로덕션에서 사용하지 않는 이 알파 릴리스에서도 진행 상황을 확인할 수 있습니다.

언급URL : https://stackoverflow.com/questions/59896816/optimal-setting-for-mysql-mariadb-optimization-10-5-in-my-cnf-centos-8