Konfiguracja percona server

[arve_lek]

#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html


[mysqld_safe]
open_files_limit = 1024000

[mysqld]
bind-address=127.0.0.1
user   = mysql
default-storage-engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp

# Errors #

log-error = /var/log/mysql/error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

max_allowed_packet = 32M
max-connect-errors = 1000000

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240
expire_logs_days = 14

 

Czy dobrze mam skonfigurowany plik ?

 

Obecnie stron na serwerze jest około 400. Każda strona to blog na WordPress.

 

FS-6T-V - Pentium G4400 - 8GB - SoftRaid 3x2To

Ubuntu Server 16.04 "Xenial Xerus" LTS

 

CPU mam przeważnie na poziomie 20-60%

 

Wcześniej miałem o wiele mniej więc nie wiem czy czasem czegoś nie popsułem

 

W htop mam od sporo:  /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

 

Nie wiem czy CPU na taką ilość stron to dużo czy mało. Jakieś sugestie, rady ?

 

 

[Mion]

9 minut temu, arve_lek napisał:

slow-query-log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log

Pawle świadomie zapisujesz długie ( w tym wypadku powyżej sekundy)  zapytania do pliku czy tak po porostu zostało ?

[arve_lek]

To akurat dodałem dzisiaj. Wygenerowało mi w https://tools.percona.com/

 

Więc widzę, że bez sensu zapisywać zapytania

 

[Mion]

Sen jest jeśli robi się to celowo czyli diagnostycznie... Ale jeśli Masz dużo stron na WP ( 400. )  co może generować dosyć duże obciążenie bazy, czasy wykonywania zapytań mogły by być dłuższe niż 1 sekunda... Z kolei logowania ich wszystkich mogło by w konsekwencji  zapełnić dysk, wiec faktycznie lepiej to wywalić.

--

Zobacz może tym skryptem: https://github.com/major/MySQLTuner-perl

Edytowane 31 Stycznia 2019 przez Mion

[arve_lek]

Dzięki, Skrypt wykrył sporo rekomendacji, także będę analizował jutro 

 

Najmniej to podoba mi się: 

 

  *** MySQL's maximum memory usage is dangerously high **

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 185.3M (Tables: 1276)
[--] Data in MyISAM tables: 943.8M (Tables: 5969)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3h 39m 1s (802K q [61.042 qps], 24K conn, TX: 2G, RX: 101M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 7.7G
[--] Max MySQL memory    : 7.2G
[--] Other process memory: 0B
[--] Total buffers: 6.1G global + 1.1M per thread (1000 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 6.1G (78.84% of installed RAM)
[!!] Maximum possible memory usage: 7.2G (92.89% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/802K)
[OK] Highest usage of available connections: 1% (12/1000)
[OK] Aborted connections: 0.00%  (1/24156)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 167K sorts)
[!!] Joins performed without indexes: 5746
[!!] Temporary tables created on disk: 77% (70K on disk / 90K total)
[OK] Thread cache hit rate: 99% (12 created / 24K connections)
[!!] Table cache hit rate: 9% (10K open / 104K opened)
[OK] Open file limit used: 1% (12K/1M)
[OK] Table locks acquired immediately: 99% (744K immediate / 744K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[!!] thread_pool_size between 16 and 36 when using InnoDB storage engine.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 99.7% (33M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/331.0M
[OK] Read Key buffer hit rate: 98.7% (12M cached / 170K reads)
[OK] Write Key buffer hit rate: 99.9% (1K cached / 1K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 6.0G/185.3M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (8.33333333333333 %): 256.0M * 2/6.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 48 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.93% (2174879 hits/ 2176322 total)
[!!] InnoDB Write Log efficiency: 175.54% (1701 hits/ 969 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2670 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (1048576) variable
    should be greater than table_open_cache (10240)
    Thread pool size for InnoDB usage (2)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 10240)
    thread_pool_size between 16 and 36 for InnoDB usage
    innodb_log_file_size should be (=768M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=6)

 

Edytowane 31 Stycznia 2019 przez arve_lek

[Mion]

Jeszcze jest coś takiego:

W dniu 15.09.2017 o 21:53, hemi napisał:

Dobrym startem będzie także skorzystanie z https://tools.percona.com/wizard - oczywiście tak jak mysqltuner nie jest to narzędzie, które idealnie dopasuje całą konfigurację jednak tworzy konfig dobry na start. 

Trochę już stare: http://blog.ksiazek.info/2011/07/23/ogolne-zasady-konfiguracji-mysql/
https://gagor.pl/2011/12/mysql-proste-metody-optymalizacji/

[arve_lek]

Jeszcze podsyłam link do pdf. Bardzo fajnie wytłumaczone:

 

https://issuu.com/panagiotistheodoropoulos/docs/pldc2012-optimizing-mysql-configura

 

Może komuś się przyda.