mysql ajuste de desempenho, escrever, obter db em ram

Question

Estou tentando melhorar o desempenho de um site otimizando o servidor Mysql. Além da otimização geral, tenho a sensação de que algumas operações de gravação levam um tempo inaceitavelmente longo.

O servidor é um servidor Mac os x 10.5.8 (acho que este é um sistema operacional de 32 bits) com intel xeon de núcleo quádruplo de 2x2,8 GHz e 8 GB de RAM.

Existem 2 tabelas InnoDB que raramente são usadas e as outras 45 são MyISAM.

O banco de dados inteiro é de 1,2 GB, o que inclui uma tabela de log de 400 MB que é gravada e nunca é lida pelo aplicativo.

Eu diria que ter o banco de dados inteiro na RAM melhoraria o desempenho. Também suponho que tolerar alguma perda de dados em caso de falha do sistema poderia melhorar o desempenho da gravação, mas não sei como configurá-lo para tirar vantagem disso. Os dados que podem ser perdidos não são críticos e podem ser recriados por um usuário se forem perdidos.

O site tem cerca de 1 ou 2 visitantes simultâneos.

Eu executei algumas iterações do mysqltuner.pl. Aqui estão os resultados atuais:

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user ''@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 57s (4K q [4.126 qps], 122 conn, TX: 6M, RX: 1M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 922.0M global + 12.4M per thread (100 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.1G (26% of installed RAM)
[OK] Slow queries: 0% (0/4K)
[OK] Highest usage of available connections: 2% (2/100)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[OK] Query cache efficiency: 42.5% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 36 sorts)
[OK] Temporary tables created on disk: 8% (6 on disk / 68 total)
[OK] Thread cache hit rate: 98% (2 created / 122 connections)
[OK] Table cache hit rate: 53% (58 open / 109 opened)
[OK] Open file limit used: 4% (103/2K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate

e aqui está meu my.cnf

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 512M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-thread-priority
log_slow_queries = 1
long_query_time = 1

Antes de reiniciar o mysql eu tive 3 consultas lentas de talvez 100 mil consultas, mas onde eu encontro o log com essas consultas?

//

    

Answer 1

Você disse que "tem a sensação de que algumas operações de gravação levam um tempo inaceitavelmente longo". O que te dá esse sentimento? Você já tentou medir tudo isso?

Se você está no OS X, o dtrace é uma ótima ferramenta para medir esse tipo de coisa. Algumas pessoas fizeram algumas as trabalhe para você . No mínimo, use vmstat , iostat ou iotop para determinar se você está vendo lentidões significativas nos intervalos de disco.

Seu query_cache_size provavelmente é muito grande. As gravações precisam invalidar todas as entradas dessa tabela do cache de consulta. Quanto maior o cache de consulta, mais tempo isso leva e isso é provavelmente algo que está tornando suas gravações mais lentas. O manual recomenda "dezenas de megabytes". Você deve alterá-lo em etapas e medir o desempenho após cada alteração para determinar o tamanho do seu.

O valor Table locks acquired immediately: 100% (3K immediate / 3K locks) sugere que o bloqueio de tabela MyISAM não é um problema para você. Isso pode se tornar um problema significativo com tabelas MyISAM que experimentam uma proporção maior de gravações para leituras.

As consultas que gravam no disco são incluídas no rastreamento de consultas lentas, portanto, se você estiver vendo consultas lentas para INSERT, UPDATE, REPLACE, DELETE, TRUNCATE etc., isso seria uma dica de que você tem um problema.

O log de consultas lentas pode ser um arquivo ou uma tabela ou ambos. Como você não parece ter especificado um local para isso, ele deve usar os padrões. Da parte do manual no log de consultas lentas e a parte em locais de log :

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

    

Answer 2

Dados esses fatores que você mencionou

• MySQL 5.0.92
• sistema operacional de 32 bits

Isso rapidamente elimina o uso do InnoDB, já que o MySQL 5.0 não tem o InnoDB que pode envolver múltiplos núcleos (a versão mais antiga do MySQL que possui o InnoDB para engajar múltiplos núcleos é o 5.1.38 plug-in InnoDB).

Você não pode ter o banco de dados inteiro na RAM porque o MyISAM armazena apenas índices. No entanto, você pode carregar todos os índices MyISAM no buffer de chaves.

A primeira coisa que você precisa fazer é calcular o tamanho correto < strong> key_buffer_size . Aqui está uma consulta para calcular isso para você:

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) 
recommended_key_buffer_size FROM 
(SELECT LEAST(POWER(2,31),KBS1) KBS 
FROM (SELECT SUM(index_length) KBS1 
FROM information_schema.tables 
WHERE engine='MyISAM' AND 
table_schema NOT IN ('information_schema','mysql')) AA ) A, 
(SELECT 2 PowerOf1024) B; 

Por favor, note que a recomendação se limitará a 2G se a soma de todos os índices MyISAM exceder 2G. Observe também: a instrução SQL tem a cláusula (SELECT 2 PowerOf1024) B . Isso produzirá a recomendação em MB. Usando (SELECT 1 PowerOf1024) B de saídas em KB. Usando (SELECT 3 PowerOf1024) B outputs em GB.

OK agora você sabe o tamanho para fazer o MyISAM Key Buffer. Como você carrega?

Execute isto:

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',
db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,
A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb;

Esta consulta mostrará todas as consultas que você precisa executar para forçar todas as páginas de índice MYI no buffer de chaves. Envie esta consulta para um script e execute essa saída:

SQLSTMT="SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM (SELECT engine,table_schema db,table_name tb,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN (SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B USING (table_schema,table_name)  WHERE A.index_type <> 'FULLTEXT' ORDER BY table_schema,table_name,index_name,seq_in_index) A GROUP BY table_schema,table_name,index_name) AA ORDER BY db,tb;"
mysql -u... -p.... -AN -e"${SQLSTMT}" > MyISAMIndexPreload.sql
mysql -u... -p.... -A < MyISAMIndexPreload.sql

Experimente!

    

Answer 3

Se você tem 8Gb de RAM, isso não significa que coloque todo o seu banco de dados em RAM.Os usa alguma memória para operações internas também.

Eu sugeriria alguma configuração em my.cnf, eles podem funcionar para você

Increase your 'key_buffer' from 384M to some high value may be to 512MB as you have sufficien RAM 

Tamanho do Key Buffer, usado para cache de blocos de índice para tabelas MyISAM. Não o configure com mais de 30% de sua memória disponível, pois alguns também é requerido pelo SO para armazenar as linhas em cache. Mesmo se você não estiver usando Tabelas MyISAM, você ainda deve configurá-lo para 8-64M, como também será usado para tabelas de disco temporárias internas.

set table_open_cache  to 2048

UPDATE

Para a configuração adequada do seu my.cnf, visite otimizando my.cnf

Tente, pode ajudar você ...