Qual a melhor forma de otimizar o computador para consultas intensivas do Mysql?

Minha melhor resposta não foi na otimização do sistema, mas na otimização de consultas. Descobri que, ao reestruturar minhas consultas, poderia fazer com que utilizassem mais memória RAM e menos disco rígido. Isso aproveitou as atualizações de velocidade do PC2. No final, o PC2 agora completa as consultas em 649 segundos; cerca de 9 vezes mais rápido do que PC1 os concluía. Durante a consulta, o PC2 agora executa 100% da CPU, 77% da Mem e 21% do disco.

Originalmente, descobri que a criação de uma série de tabelas temporárias criava o melhor desempenho em meu laptop. A partir dessas tabelas temporárias, eu selecionaria os dados desejados dessas tabelas temporárias. Mas quando tive mais memória RAM disponível com o PC2, a velocidade melhorou muito para simplesmente extrair os dados diretamente das tabelas principais por meio de junções.

Exemplo Original - Minha estrutura ficou assim:

CREATE TABLE temp1 AS SELECT * FROM table1 WHERE x > a AND y > b AND z > c;

CREATE TABLE temp2 AS SELECT * FROM table2 WHERE x > a AND y > b AND z > c;

CREATE TABLE temp3 AS SELECT * FROM table3 WHERE x > a AND y > b AND z > c;

INSERT INTO tableresults SELECT * FROM temp1 JOIN temp2 JOIN temp3 
WHERE temp1.x = x AND temp2.y = y AND temp3.z = z;

INSERT INTO tableresults SELECT * FROM temp1 JOIN temp2 JOIN temp3 
WHERE temp1.x = y AND temp2.y = z AND temp3.z = x;

INSERT INTO tableresults SELECT * FROM temp1 JOIN temp2 JOIN temp3 
WHERE temp1.x = z AND temp2.y = x AND temp3.z = y;

No meu laptop e PC1, esse foi o equilíbrio certo entre uso de RAM e ROM.

Novo Exemplo - Mudei minha estrutura para ficar assim:

INSERT INTO tableresults SELECT * FROM table1 JOIN table2 JOIN table3 
WHERE table1.x = x AND table2.y = y AND table3.z = z;

INSERT INTO tableresults SELECT * FROM table1 JOIN table2 JOIN table3 
WHERE table1.x = y AND table2.y = z AND table3.z = x;

INSERT INTO tableresults SELECT * FROM table1 JOIN table2 JOIN table3 
WHERE table1.x = z AND table2.y = x AND table3.z = y;

Com o PC2 não havia um bom motivo para gravar tabelas temporárias no disco rígido quando havia bastante memória RAM para fazer o trabalho.

Portanto, meu resumo do que aprendi é o seguinte: dividir tabelas em tabelas temporárias menores pode ser muito útil quando você está com pouca memória RAM. Mas se você tiver RAM para evitar essas etapas extras, é muito mais rápido eliminá-las. Mas é um ato de equilíbrio. Às vezes você deseja etapas mais intermediárias e outras vezes é melhor ir direto.

Sugestões a serem consideradas para a seção PC1 my.ini [mysqld]

log_bin=0  # since you are not replicating anywhere.
innodb_io_capacity=500  # from 200 to use more of available SSD IOPS 
innodb_buffer_pool_size=8G  # from 256M for about 50% available RAM
table_open_cache_instances=3  # from 16 because only 4 cores are available.
innodb_thread_concurrency=0  # from 9 to allow OS to manage limit

Muitas outras oportunidades de ajuste de desempenho estão disponíveis.

Visite nosso site para baixar nosso script utilitário gratuito para o script findfragtables.sql para reduzir data_free (free_space) em tabelas que estão superprovisionadas neste espaço. OPTIMIZE TABLE nome_tabela; para as tabelas identificadas.

Sugestões a serem consideradas para a seção PC2 my.ini [mysqld]

log_bin=0  # from ON (1) since you are NOT replicating anywhere
innodb_io_capacity=500  # from 200 to use more available SSD IOPS
table_open_cache_instances=6  # from 16 because only 8 cores are available.
net_buffer_length=96K  # from 16K to reduce packet count in/out
innodb_buffer_pool_instances=4  # from 20 to mgmt reduce overhead

Existem muito mais oportunidades para melhorar o desempenho.