MySQL DB causando alta IO executando o servidor Zabbix

Question

Meu host VPS está reclamando que estou usando de longe o mais alto IO no host. Estou executando um servidor de monitoramento Zabbix com um banco de dados innodb MySQL. O VPS tem 512MB de RAM. Eu não entendo porque o script mysqltuner.pl está recomendando innodb_buffer_pool_size > 1G quando eu só tenho 512MB. Qualquer conselho sobre onde devo começar seria muito apreciado. Eu entrei em contato com um especialista da Zabbix e recebi a mensagem 'tmp tables should not end on disk' e innodb buffer pool deve ser o maior possível na maioria dos casos ', mas não tenho certeza de como implementá-lo em termos de números.

Aqui está a saída do script mysqltuner.pl.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 1G (Tables: 144)
[!!] BDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Performance Metrics -------------------------------------------------
[--] Up for: 129d 6h 54m 3s (342M q [30.677 qps], 1M conn, TX: 112B, RX: 46B)
[--] Reads / Writes: 57% / 43%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (62% of installed RAM)
[OK] Slow queries: 0% (134/342M)
[OK] Highest usage of available connections: 33% (33/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/67.0K
[OK] Key buffer hit rate: 96.3% (31M cached / 1M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
[OK] Temporary tables created on disk: 13% (1M on disk / 10M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 46K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (406M immediate / 406M locks)
[!!] InnoDB data size / buffer pool: 1.8G/8.0M


-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 1G)

E aqui está meu my.cnf atual

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Saída do livre -m

             total       used       free     shared    buffers     cached
Mem:           492        486          6          0          2         44
-/+ buffers/cache:        439         53
Swap:          255        112        143

Saída do iostat

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.24    0.03    0.12    0.57    0.01   99.03

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda             24.47        36.26       748.69  405085498 8365183264
xvdb              1.16         9.50         6.87  106190600   76708192
xvdc              0.00         0.00         0.00       5961       1416

    

Answer 1

innodb_buffer_pool_size > 1G , porque o tamanho do seu conjunto de dados é de 1,8 GB.

Para diminuir o número de leituras, você precisa aumentar innodb_buffer_pool_size . Para diminuir o número de gravações, você precisa editar seus modelos do zabbix (desabilitar alguns itens desnecessários, como inodes livres, aumentar os intervalos entre as verificações).

Você tem a proporção de Leituras / Gravações em 57% / 43%, portanto, a ativação do Query Cache não ajudará (isso pode piorar as coisas, porque grava em tabelas invalida o cache ).

Pense em aumentar tmp_table_size e max_heap_table_size para evitar a criação de tabelas tmp no disco (13% das tabelas tmp). Tabelas temporárias estão em MB? é contar? Se é contra, é muito alto.

Diminuir o número de conexões para 50 (seu maior número foi 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usehl link sobre a otimização do InnoDB.

    

Answer 2

O buffer pool do InnoDB é REALMENTE pequeno demais (tente pelo menos 64M ou 128M). Pense em ativar o Cache de Threads, o Cache de Consultas e obter um table_cache maior, isso pode ajudar muito.