Ajustando as configurações do MySQL para lidar com grande carga de usuários

Question

Eu desenvolvi um jogo php que obviamente está dependendo das consultas do mysql. Eu estou correndo em um servidor monstro: Intel Xeon 7550 com 32 GB de RAM (Redhat x86_64 ES 5.0) com cPanel instalado. Eu lancei o jogo no Facebook e apenas 250 usuários puderam usá-lo antes que a CPU ficasse mental.

Eu tinha "top" e monitorei o uso da CPU. O MySQL foi em um ponto usando até 1600%! Todas as outras coisas foram em torno de 3%. Obviamente, preciso ajustar as configurações do mysql para liberar isso em uma escala maior.

Desculpas se esta pergunta for vaga. Meu conhecimento com o ajuste das configurações do servidor mysql é muito limitado. Quais configurações preciso ajustar? E o que devo definir para eles? Eu estou supondo que a memória é uma delas? Eu estou prevendo uma base de usuários de 10.000 sessões em execução simultaneamente. Com minhas configurações atuais, só posso ter 250. Qualquer orientação de administradores de servidores experientes seria muito apreciada.

    

Answer 1

Os problemas que vejo ...

1.) O Mysql usa muita memória para centenas / milhares de conexões por design ..

Basically you need to set up the confiuguration to help minimise memory usage
I dont think this is a memory problem
With a database of 200Mb, and 32Gb installed you should not be hitting your maximum memory
You only have 250 users and are maxing out CPU

2.) Mysql por design, dá uma conexão / thread para um núcleo (isso pode ser compartilhado)

This obviously can be shared with many threads but 1 thread cant breach a core.
The problem you are seeing is too many "SLOW/HEAVY" queries, whereby maximizing each core..
Therefore you cant take more users, as the CPU is at its limit..
- Start profiling your queries
- Index Correctly for Reads
- If needed use a write master and a read slave

Algumas perguntas

What is your ratio READS / WRITES???  
Have you tried Slow_query logging ( set it to 1 second )?  
Have you used "explain" on queries to see if indexing is working?  
Have you considered a job queue for writes?  
Are you using the mysql cache effectively for reads?

Realisticamente, seu problema é consultas / inserções ruins.
Se você puder resolver este problema, podemos ajudá-lo com sua configuração para minimizar o uso da memória.

A configuração basicamente não oferece muito para ajudar com o uso da CPU, isto é com você com o seu código!

Espero que isso esteja claro: D

    

Answer 2

Eu só tenho alguns pontos que você deve pensar.

32 GB de RAM em um servidor de banco de dados muito usado não é um "monstro", mas depende de como seu banco de dados é usado. Se você tem um bajillion de pedidos - E os dados (que estão sendo solicitados) não podem caber na RAM, você terá um sério problema com o sistema de disco martelando E / S no armazenamento de dados. Não parece que você precisa de mais memória RAM, já que você está maximizando a CPU.

1600% de uso da CPU é igual a 16 núcleos lógicos (8 físicos) em um X7550 maximizado. Você deve observar que o hyper-threading em servidores de banco de dados é altamente debatido, já que geralmente dá a você um pouquinho mais de desempenho de gravação e um pouquinho menos de desempenho de leitura. Por outro lado, desativá-lo não resolverá seu problema.

Parece-me que a sua base de dados e / ou a sua aplicação não está bem concebida em termos de otimização e tempo de processamento. 250 usuários literalmente matando núcleos 8x2,4GHz não é algo que eu chamaria de comum, e não é algo que as configurações do servidor mysql possam resolver para você.

Votei em mover esta pergunta para o Stack Overflow.

    

Answer 3

É bastante básico, mas o "script primer de ajuste de desempenho do mysql" ( link ) fornece algumas boas sugestões para começar.

Currently it handles recomendations for the following:

• Slow Query Log
• Max Connections
• Worker Threads
• Key Buffer
• Query Cache
• Sort Buffer
• Joins
• Temp Tables
• Table (Open & Definition) Cache
• Table Locking
• Table Scans (read_buffer)
• Innodb Status

    

Answer 4

Eu posso dar algumas sugestões gerais, pois otimizações mais específicas são difíceis / impossíveis para um diagnóstico "cego":

• O my.cnf padrão usa configurações muito simples (pelo menos todas as configurações padrão que já vi). Há uma variedade de exemplo my.cnf lá fora para lhe dar uma idéia aproximada do que começar com (procure por "mysql my.cnf optimization"). Esteja ciente de que as configurações exatas que você precisa ajustar dependem de seu aplicativo, portanto, as configurações que funcionam bem para outra pessoa podem não funcionar tão bem para você.
• Dizer "250 usuários" não é informação suficiente. O aplicativo está lendo / escrevendo pesado? Quantas consultas por segundo por usuário?
• O banco de dados está adequadamente projetado para o desempenho em mente? Cada tabela possui configuração de índices para consultas usadas com frequência? As consultas são simples, complexas ou super complexas? Esteja ciente de "dicas" em consultas que podem introduzir ordens de magnitude de problemas de desempenho. Você está filtrando registros no lado do banco de dados usando "WHERE" ou está filtrando no lado do servidor solicitando e ignorando vários registros por solicitação?
• Para um banco de dados de 200 MB com as configurações my.cnf corretas, todo o conjunto de trabalho do banco de dados deve caber facilmente na RAM, especialmente com um servidor com 32 GB de memória.
• Se o seu aplicativo é lido com consultas complexas, então, olhe para uma estratégia de cache para reduzir a carga no servidor MySQL.
• Como comparação: Eu tenho um servidor MySQL 1/5 da capacidade (CPU / RAM) do seu servidor que tem uma média de 100 consultas / segundo para um site do MediaWiki e está sempre 90-95% ocioso. Embora eu não saiba os detalhes de sua aplicação / configuração, acho que com alguns ajustes de my.cnf e alguns aplicativos e banco de dados de criação de perfil / otimização você pode aumentar seu desempenho em uma ordem de magnitude ou dois.
• Se você é sério sobre o desenvolvimento do MySQL, procure o livro High Performance MySQL . É uma ótima visão geral da configuração e do uso do MySQL para usuários iniciantes. O MySQL High Performance Blog também é um ótimo site para freqüentar.