Por que nossa tabela do MySQL abre tão grande e com cache tão baixo?

Question

Estamos vendo uma estatística estranha sobre o número de janelas abertas. Eu adicionei o dump mysqltuner abaixo.

Diz que abrimos 94.000 tabelas nas últimas 24 horas. Mas há apenas 15.000 tabelas no total e sabemos que neste específico replicador de leitura e escravo apenas cerca de 100 tabelas estão sendo acessadas.

Além disso, dizemos que estamos armazenando apenas 2k desses 94k.

Como faço para entender esses números? E como consertar o problema de cache - com certeza poderíamos "aumentar o cache da tabela", mas isso não soa como o problema raiz.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.9-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 4G (Tables: 14960)
[--] Data in InnoDB tables: 225M (Tables: 570)
[!!] Total fragmented tables: 2115

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 13m 36s (647K q [80.798 qps], 28K conn, TX: 247B, RX: 542M)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 2.5G global + 2.7M per thread (1000 max threads)
[OK] Maximum possible memory usage: 5.2G (66% of installed RAM)
[OK] Slow queries: 0% (783/647K)
[OK] Highest usage of available connections: 5% (57/1000)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G
[OK] Key buffer hit rate: 100.0% (763M cached / 74K reads)
[OK] Query cache efficiency: 55.5% (335K cached / 604K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (312 temp sorts / 113K sorts)
[!!] Temporary tables created on disk: 47% (59K on disk / 124K total)
[OK] Thread cache hit rate: 99% (63 created / 28K connections)
[!!] Table cache hit rate: 2% (2K open / 94K opened)
[OK] Open file limit used: 75% (3K/5K)
[OK] Table locks acquired immediately: 99% (577K immediate / 583K locks)
[!!] Connections aborted: 45%
[OK] InnoDB data size / buffer pool: 225.3M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    table_cache (> 2048)

    

Answer 1

Veja Como o MySQL abre / fecha as tabelas no manual oficial para uma muito boa descrição do cache de tabela e como ele é usado junto com alguns comentários úteis. Cada tabela usada em uma consulta pode ter várias alças para cada conexão, o que significa que, se você tiver uma alta simultaneidade de solicitação e usar consultas que contenham muitas tabelas, cada tabela poderá exigir dezenas de identificadores.

Uma coisa útil que você pode fazer é usar alguma forma de monitoramento para verificar os valores de open_table / opened_tables a cada minuto ou mais e ver como eles se comportam. No meu caso, é fácil ver que picos repentinos no tráfego são causas prováveis de grandes aumentos em open_tables.

Há também essa pergunta SO , que é muito parecida com a sua.

    

Answer 2

lwdba @ localhost (DB information_schema) :: mostra variáveis como 'query%';
+ ------------------------------ + ---------- +
| Nome_variável | Valor

+ ------------------------------ + ---------- +
| query_alloc_block_size | 8192

| query_cache_limit | 1048576

| query_cache_min_res_unit | 4096

| query_cache_size | 67108864

| query_cache_type | ON

| query_cache_wlock_invalidate | FORA

| query_prealloc_size | 8192

+ ------------------------------ + ---------- +

Nesta exibição, query_cache_size (variável global) é 64M. O query_cache_limit é de 1 milhão.

Isso significa que eu poderia ter até 64 consultas em cache cujo conjunto de dados máximo é de 1 milhão ou menos, ou 128 consultas em cache com conjuntos de dados de 500.000 ou qualquer outra combinação que possa totalizar até 64 milhões.

Você pode precisar jogar alguns jogos com essas configurações ( query_cache_size e query_cache_limit ) para que conjuntos maiores de dados consultados sejam armazenados em cache corretamente. Caso contrário, as consultas que não atenderem aos limites impostos por essas duas variáveis não serão armazenadas em cache.