Subconsultas e cache do MySQL para tabela de linhas de mais de 18 milhões
Como este é o meu primeiro post, parece que só posso postar 1 link, por isso listei os sites aos quais me refiro na parte inferior. Em poucas palavras, meu objetivo é fazer com que o banco de dados retorne os resultados mais rapidamente. Tentei incluir o máximo de informações relevantes possível para ajudar a enquadrar as perguntas na parte inferior do post.
Informações da máquina8 processors
model name : Intel(R) Xeon(R) CPU E5440 @ 2.83GHz
cache size : 6144 KB
cpu cores : 4
top - 17:11:48 up 35 days, 22:22, 10 users, load average: 1.35, 4.89, 7.80
Tasks: 329 total, 1 running, 328 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 87.4%id, 12.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8173980k total, 5374348k used, 2799632k free, 30148k buffers
Swap: 16777208k total, 6385312k used, 10391896k free, 2615836k cached
No entanto, estamos olhando para mover a instalação do mysql para uma máquina diferente no cluster com 256 GB de RAM
Informações da tabelaMinha tabela MySQL parece
CREATE TABLE ClusterMatches
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cluster_index INT,
matches LONGTEXT,
tfidf FLOAT,
INDEX(cluster_index)
);Possui aproximadamente 18 milhões de linhas, existem 1 milhão de cluster_index e 6 mil correspondências únicas. A consulta sql que estou gerando no PHP é semelhante.
Consulta SQL$sql_query="SELECT `matches`,sum(`tfidf`) FROM
(SELECT * FROM Test2_ClusterMatches WHERE `cluster_index` in (".$clusters."))
AS result GROUP BY `matches` ORDER BY sum(`tfidf`) DESC LIMIT 0, 10;";onde $ cluster contém uma sequência de aproximadamente 3.000 cluster_index's separados por vírgula. Essa consulta utiliza aproximadamente 50.000 linhas e leva aproximadamente 15 segundos para ser executada. Quando a mesma consulta é executada novamente, leva aproximadamente 1s para ser executada.
UsoO conteúdo da tabela pode ser assumido como estático.Baixo número de usuários simultâneosAtualmente, a consulta acima é a única consulta que será executada na tabelaSubconsultaBaseado neste post [stackoverflow: cache / reutilizar uma subconsulta no MySQL] [1] e a melhoria no tempo de consulta, acredito que minha subconsulta possa ser indexada.
mysql> EXPLAIN EXTENDED SELECT `matches`,sum(`tfidf`) FROM
(SELECT * FROM ClusterMatches WHERE `cluster_index` in (1,2,...,3000)
AS result GROUP BY `matches` ORDER BY sum(`tfidf`) ASC LIMIT 0, 10;
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 48528 | Using temporary; Using filesort |
| 2 | DERIVED | ClusterMatches | range | cluster_index | cluster_index | 5 | NULL | 53689 | Using where |
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
De acordo com este artigo anterior [Otimizando o MySQL: consultas e índices] [2] em Informações adicionais - os ruins para serem vistos aqui são "usando temporário" e "usando filesort"
Informações de configuração do MySQLO cache de consulta está disponível, mas desativado efetivamente, pois o tamanho está definido como zero
mysqladmin variables;
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| bdb_cache_size | 8384512 |
| binlog_cache_size | 32768 |
| expire_logs_days | 0 |
| have_query_cache | YES |
| flush | OFF |
| flush_time | 0 |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| sort_buffer_size | 2097144 |
| table_cache | 64 |
| thread_cache_size | 0 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| read_rnd_buffer_size | 262144 |
+---------------------------------+----------------------+
Com base neste artigo em [Mysql Database Performance Turning] [3], acredito que os valores que preciso ajustar sejam
table_cachekey_buffersort_bufferread_buffer_sizerecord_rnd_buffer (para os termos GROUP BY e ORDER BY)Áreas identificadas para aprimoramento - MySQL Query tweaksAlterando o tipo de dados para correspondências para um índice que é um int apontando para outra tabela [O MySQL realmente usará um formato de linha dinâmico se contiver campos de comprimento variável como TEXT ou BLOB, o que, nesse caso, significa que a classificação precisa ser feita no disco . A solução não é evitar esses tipos de dados, mas dividir esses campos em uma tabela associada.] [4]Indexando o novo campo match_index para que o GROUP BYmatches ocorre mais rápido, com base na declaração ["Você provavelmente deve criar índices para qualquer campo no qual estiver selecionando, agrupando, solicitando ou ingressando."] [5]FerramentasPara ajustar executar, pretendo usar
[Explique] [6] fazendo referência ao [formato de saída] [7][ab - Ferramenta de benchmarking do servidor HTTP Apache] [8][Criação de perfil] [9] com [dados do log] [10]Tamanho futuro do banco de dadosO objetivo é criar um sistema que possua 1 milhão de valores únicos de cluster_index 1M de valores únicos de correspondência, aproximadamente 3.000.000.000 de linhas de tabela com um tempo de resposta para a consulta de cerca de 0,5s (podemos adicionar mais RAM conforme necessário e distribuir o banco de dados pelo cluster)
QuestõesAcho que queremos manter todo o conjunto de registros em memória ram para que a consulta não toque no disco, se mantivermos o banco de dados inteiro no cache do MySQL, isso eliminará a necessidade de memcachedb?Está tentando manter o banco de dados inteiro no cache do MySQL uma estratégia ruim, pois não foi projetada para ser persistente? Algo como memcachedb ou redis seria uma abordagem melhor, se sim, por que?A tabela temporária "resultado" criada pela consulta é destruída automaticamente quando a consulta é concluída?Devemos mudar do Innodb para o MyISAM [como bom para leitura de dados pesados, onde o InnoDB é bom para gravação pesada] [11]?meu cache não parece estar zerado em [Query Cache Configuration] [12], por que a consulta atualmente ocorre mais rapidamente na segunda vez em que a executo?posso reestruturar minha consulta para eliminar a ocorrência de "using temporary" e "using filesort", devo usar uma junção em vez de uma subconsulta?como você vê o tamanho do MySQL [Data Cache] [13]?que tipo de tamanhos para os valores table_cache, key_buffer, sort_buffer, read_buffer_size, record_rnd_buffer você sugeriria como ponto de partida?Ligações1: stackoverflow.com/questions/658937/cache-re-use-a-subquery-in-mysql2: databasejournal.com/features/mysql/article.php/10897_1382791_4/Optimizing-MySQL-Queries-and-Indexes.htm3: debianhelp.co.uk/mysqlperformance.htm4: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/5: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/6: dev.mysql.com/doc/refman/5.0/en/explain.html7: dev.mysql.com/doc/refman/5.0/en/explain-output.html8: httpd.apache.org/docs/2.2/programs/ab.html9: mtop.sourceforge.net/10: dev.mysql.com/doc/refman/5.0/en/slow-query-log.html11: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/12: dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html13: dev.mysql.com/tech-resources/articles/mysql-query-cache.html