mysql-maria 개선을 위한 mysqlTurner 팁DB

mysql-maria 개선을 위한 mysqlTurner 팁DB

MySQL 설정을 수정하기 위해 도움이 필요합니다.
MysqlTurner를 사용해 본 결과 다음과 같습니다.

 >>  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
[--] Performing tests on 127.0.0.1:3306
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 10.2.32-MariaDB-10.2.32+maria~xenial
[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 MyISAM tables: 203.2M (Tables: 84)
[--] Data in InnoDB tables: 967.1M (Tables: 162)
[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
[!!] User 'sculli@%' does not specify hostname restrictions.
[--] There are 620 basic passwords in the list.
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 9h 22m 30s (3M q [108.604 qps], 37K conn, TX: 29G, RX: 543M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 3.8G
[--] Other process memory: 0B
[--] Total buffers: 433.0M global + 22.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.5G (32.39% of installed RAM)
[OK] Maximum possible memory usage: 3.8G (48.29% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (134/3M)
[OK] Highest usage of available connections: 62% (95/151)
[OK] Aborted connections: 0.00%  (0/37538)
[!!] 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 / 3M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (298 temp sorts / 722K sorts)
[!!] Joins performed without indexes: 13975
[!!] Temporary tables created on disk: 70% (283K on disk / 400K total)
[OK] Thread cache hit rate: 99% (95 created / 37K connections)
[OK] Table cache hit rate: 97% (239 open / 245 opened)
[OK] table_definition_cache(600) is upper than number of tables(514)
[OK] Open file limit used: 0% (44/16K)
[OK] Table locks acquired immediately: 100% (18K immediate / 18K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] 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.2.32-MariaDB-10.2.32+maria~xenial)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.4% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/23.3M
[OK] Read Key buffer hit rate: 100.0% (5M cached / 200 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/967.1M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/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.64% (4017613026 hits/ 4200815082 total)
[!!] InnoDB Write Log efficiency: 5.17% (1142 hits/ 22095 total)
[OK] InnoDB log waits: 0.00% (0 waits / 20953 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.4% (40M cached / 246K 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: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for 'sculli'@% to sculli@SpecificDNSorIp
    UPDATE mysql.user SET host ='SpecificDNSorIp' WHERE user='sculli' AND host ='%'; FLUSH PRIVILEGES;
    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.
             
             (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
    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 (> 4M, or use smaller result sets)
    join_buffer_size (> 4.0M, or always use indexes with JOINs)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 967.1M) if possible.
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.  

서버 상세: _mysql Ver 15.1Distributed 10.2.32-MariaDB, readline 5.2_Ubuntu 16.04.6 LTS \n \l (알고 있습니다...i know) 4 CPU 8 Gb RAM 7 Gb 스왑

*.cnf 설정:

# MariaDB-specific config file.
# Read by /etc/mysql/my.cnf

[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
#default-character-set = utf8 

[mysqld]
table_definition_cache = 600
#

### 20200711 mysqltuner
query_cache_limit = 4M
join_buffer_size = 4M
tmp_table_size = 32M
max_heap_table_size = 32M


# * Character sets
# 
# Default is Latin1, if you need UTF-8 set all this (also in client section)
#
#character-set-server  = utf8 
#collation-server      = utf8_general_ci 
#character_set_server   = utf8 
#collation_server       = utf8_general_ci 
# Import all .cnf files from configuration directory
!includedir /etc/mysql/mariadb.conf.d/

게시된 제한된 정보에서 my.cnf [mysqld]섹션에서 고려해야 할 사항

thread_cache_size=256  # to reduce threads_created count
aria_pagecache_division_limit=50  # from 100 for Hot/Warm storage area
aria_pagecache_age_threshold=900  # from 300 to delay age out to reduce reads
innodb_buffer_pool_size=2G  # from 128M to support in RAM with room for growth

프로필, 네트워크 프로필에서 연락처 정보를 확인하고 FAQ 페이지에서 'Q'를 확인하십시오.인덱스를 사용하지 않는 조인 또는 쿼리를 찾으려면 어떻게 해야 합니까? MySQ에 나열된 13,975개의 "인덱스 없이 수행된 조인"을 유발하는 쿼리를 찾기 위한 팁LTuner 보고서해당 쿼리를 설명했으면 적절한 인덱스를 생성하여 읽기를 줄입니다.

언급URL : https://stackoverflow.com/questions/63151970/mysqlturner-tips-for-improvement-my-mysql-mariadb