Konfiguracja MySQL

[My4tic 1]

Cześć,

 

mam dziwny problem z SQLem. Wygląda to tak jak gdyby serwer był przeciążony natomiast load jest na poziomie max. 1.0, użycie RAMu: 14 Gb z 32 Gb. Może dysk? Jak to zweryfikować?

 

Sprzęt:

 

Intel® Xeon® CPU E3-1231 v3 @ 3.40GHz

Ram 32 Gb

SDD Crucial_CT500MX200SSD1

CentOS

Mysql 5.6.36

 

Na serwerze działa tylko MySQL. Główna baza to innodb.

max_allowed_packet      = 32M
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_write_io_threads = 8
innodb_read_io_threads  = 8
query_cache_limit       = 1M
query_cache_size        = 256M
join_buffer_size        = 2M
tmp_table_size          = 128M
max_heap_table_size     = 128M
max_connections         = 1024
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_rnd_buffer_size    = 2M
key_buffer_size         = 768M
thread_cache_size       = 20

Mysqtunner

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 0h 36m 59s (55M q [626.885 qps], 8K conn, TX: 43G, RX: 7G)
[--] Reads / Writes: 53% / 47%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 14.5G
[--] Other process memory: 102.9M
[--] Total buffers: 13.1G global + 6.4M per thread (214 max threads)
[--] P_S Max memory usage: 78M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 13.5G (43.13% of installed RAM)
[OK] Maximum possible memory usage: 14.5G (46.60% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (13/55M)
[OK] Highest usage of available connections: 18% (40/214)
[OK] Aborted connections: 0.74%  (63/8486)
[!!] 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 / 28M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (4 temp sorts / 16K sorts)
[!!] Joins performed without indexes: 2892
[OK] Temporary tables created on disk: 12% (8K on disk / 66K total)
[OK] Thread cache hit rate: 99% (50 created / 8K connections)
[!!] Table cache hit rate: 1% (400 open / 22K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (56M immediate / 56M locks)


-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 78.9M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (150M used / 805M cache)
[OK] Key buffer size / total MyISAM indexes: 768.0M/8.8M
[OK] Read Key buffer hit rate: 100.0% (107M cached / 835 reads)
[!!] Write Key buffer hit rate: 0.0% (26M cached / 813 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 12.0G/4.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.78125 %): 48.0M * 2/12.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (36887667365 hits/ 36887702301 total)
[OK] InnoDB Write log efficiency: 98.55% (26600823 hits/ 26991455 total)
[!!] InnoDB log waits: 0.01% (42 waits / 390632 writes)


-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache (400)
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    table_open_cache (> 400)
    innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=6G) if possible.
    innodb_buffer_pool_instances(=12)
    innodb_log_buffer_size (>= 8M)

Edytowano Czerwiec 3, 2017 przez My4tic (zobacz historię edycji)

[hemi 20]

Wyłącz query_cache, performance_schema (o ile nie uzywasz, a pewnie nie uzywasz), zmniejsz key_buffer_size (skoro nie korzystasz z mysiam to po co az 768M na klucze myisam'a...).

Do tego włącz slow_query_log, ustaw long_query_time na 0 sek (aby logowalo Ci wszystkie zapytania bez wzgledu na to ile sie wykonuja), zbierz dane z paru minut, potem long_query_time na 1 i np za pomocą narzędzia pt-query-digest (z pakietu percona-toolkit) przemiel slow log i zobacz które zapytania najbardziej Ci "zamulają". Tutaj bym głównie obstawiał źle napisane zapytania sql i/lub niepoprawne (lub w ogole nie ustawione) indeksy na bazie.

[systemy.internetowe 7]

mam dziwny problem z SQLem. Wygląda to tak jak gdyby serwer był przeciążony natomiast load jest na poziomie max. 1.0, użycie RAMu: 14 Gb z 32 Gb. Może dysk? Jak to zweryfikować?

 

Jak się objawia te "przeciążanie", jak je diagnozujesz ... napisz coś więcej na ten temat ?

[nnd_newbie 10]

Taka mała dygresja, ale jeśli serwer dedykowany tylko dla SQLa to czemu go ograniczasz do połowy RAMu? Reszta się marnuje. W innodb-buffer-pool-size ładuj ile się da, niech sobie używa.