MariaDB MySQL 최적화

MariaDB MySQL 최적화

저는 VPS에서 MariaDB의 최고 성능을 설정하는 것을 조금 고수했습니다.보유 코어: 6개, 16GB RAM, 300GB SSD, CloudLinux, LiteSpeed

 >>  MySQLTuner 1.7.0 - 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] Currently running supported MySQL version 10.2.13-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/dev.whitespot.co.err(611K)
[OK] Log file /var/lib/mysql/dev.whitespot.co.err exists
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is readable.
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is not empty
[OK] Log file /var/lib/mysql/dev.whitespot.co.err is smaller than 32 Mb
[!!] /var/lib/mysql/sqlhost.err contains 4467 warning(s).
[!!] /var/lib/mysql/sqlhost.err contains 164 error(s).
[--] 8 start(s) detected in /var/lib/mysql/dev.whitespot.co.err
[--] 1) 2018-02-14  3:17:43 140627065792640 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-02-14  3:08:56 140369082124416 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-02-14  3:04:17 140342812088448 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-02-14  2:27:29 139698717120640 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-02-14  2:27:05 140353362925696 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-02-14  0:59:46 140394864134272 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-02-14  0:58:53 140612241397888 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-02-14  0:40:07 140392565569664 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/lib/mysql/sqlhostname.err
[--] 1) 2018-02-14  3:17:39 140368255764224 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-02-14  3:08:52 140342016706304 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-02-14  3:04:13 139688655320832 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-02-14  2:27:26 140353156269824 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-02-14  2:26:59 140384804599552 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-02-14  0:59:42 140611259844352 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-02-14  0:58:49 140391611623168 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 2G (Tables: 4460)
[--] Data in InnoDB tables: 1G (Tables: 1872)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'netdata@localhost' has no password set.
[!!] User 'jb9363_user@%' hasn't specific host restriction.
[!!] User 'robak@%' hasn't specific host restriction.
[!!] User 'robak_alexadam@%' hasn't specific host restriction.
[!!] User 'robak_francesco@%' hasn't specific host restriction.
[!!] User 'robak_g@%' hasn't specific host restriction.
[!!] User 'robak_pro@%' hasn't specific host restriction.
[!!] User 'robak_tom@%' 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: 8h 12m 49s (4M q [162.139 qps], 51K conn, TX: 41G, RX: 1G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 15.7G
[--] Max MySQL memory    : 9.8G
[--] Other process memory: 1.5G
[--] Total buffers: 8.4G global + 4.7M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 8.5G (54.58% of installed RAM)
[OK] Maximum possible memory usage: 9.8G (62.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 36% (1M/4M)
[OK] Highest usage of available connections: 8% (24/300)
[OK] Aborted connections: 0.13%  (66/51527)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (24 temp sorts / 563K sorts)
[!!] Joins performed without indexes: 10333
[!!] Temporary tables created on disk: 69% (195K on disk / 282K total)
[OK] Thread cache hit rate: 99% (24 created / 51K connections)
[OK] Table cache hit rate: 98% (7K open / 7K opened)
[OK] Open file limit used: 38% (9K/25K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (10.2.13-MariaDB-log)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 31.3% (84M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/295.8M
[OK] Read Key buffer hit rate: 99.9% (34M cached / 34K reads)
[!!] Write Key buffer hit rate: 12.9% (112K cached / 97K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/1.6G
[OK] InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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.99% (205255768 hits/ 205283702 total)
[!!] InnoDB Write Log efficiency: 77.83% (185463 hits/ 238296 total)
[OK] InnoDB log waits: 0.00% (0 waits / 52833 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 93.9% (2M cached / 181K 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:
    Control warning line(s) into /var/lib/mysql/sqlhostname.err file
    Control error line(s) into /var/lib/mysql/sqlhostname.err file
    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
    MySQL started within last 24 hours - recommendations may be inaccurate
    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
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

SQL용 모니터링 툴을 설치했습니다.이 소프트웨어는 매 시간마다 느린 질의에 대해 이메일을 보냅니다.로그 파일이 이제 500mb...12시간 내에 생성된 로그 :/

저는 LiteSpeed + LS Cache를 통해 이 VPS 전용 WordPress 웹 사이트에서 실행합니다.제가 이것을 최적화할 수 있도록 도와주세요.웹 사이트가 느리다는 것은 아니지만 MySQL 최적화가 전체 서버 성능에 더 좋을 것이며 워드프레스 설치 속도도 조금 더 빨라질 것으로 생각합니다.

가능한 보고서와 지원을 볼 수 있도록 한 시간당 느린 질의 이메일 중 하나를 게시해 주시기 바랍니다.

상황을 개선하기 위해 지금 내 .cnf/ini [mysqld] 섹션에서 할 수 있는 것들,

max_connections=50  # from 300 to reduce RAM footprint
change next 4 on one day, please   shutdown/restart
#sort_buffer_size=val  # lead with # to use default
#read_buffer_size=val  # lead with # to use default
#read_rnd_buffer_size=val  # lead with # to use default
#join_buffer_size=val  # lead with # to use default
thread_cache_size=50  # from ? to reduce threads_created
innodb_thread_concurrency=8  # from 0 to enable
innodb_lru_scan_depth=128  # from 1024 to avoid overloading page_cleaners
innodb_page_cleaners=64  # from def for autolimit to be = innodb_buffer_pool_instances
innodb_buffer_pool_dump_at_shutdown=ON  # to prepare for WARM IBPool
innodb_buffer_pool_load_at_startup=ON  # to WARM the innodb_buffer_pool
innodb_flush_neighbors=0  # should be OFF when using SSD, there is not latency

행운을 빌어요, 평판이 15세가 되기 전에는 투표를 하거나 어떤 것도 받아들일 수 없을 거예요.사용자의 경우 하루에 한 번으로 변경을 제한하여 모니터링을 통해 이러한 분석 및 구현이 어떤 효과를 가져다 주는지 확인할 수 있습니다.

MySQLCalculator.com 을 사용하면 변경 전후의 RAM 설치 공간을 살펴보는데 2분이 소요됩니다.

언급URL : https://stackoverflow.com/questions/48786386/mariadb-mysql-optimisation