mysql tendo cargas

Question

Meu site e servidor são enormes e pesados, meu servidor tem 8 GB de RAM e 6 núcleos de processador.

Eu recebi uma mensagem da minha equipe de hospedagem, referente à carga do banco de dados mysql,

Hoje em dia, meu site está muito lento e o e-mail que recebi contém um arquivo de log

root@server [~]# mysqladmin pr
+-------+------------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 17969 | leechprotect | localhost | leechprotect | Sleep | 103 | | |
| 18706 | database_user | localhost | database_name | Sleep | 25 | | |
| 18717 | database_user | localhost | database_name | Query | 19 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18737 | database_user | localhost | database_name | Query | 19 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18745 | database_user | localhost | database_name | Query | 20 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18752 | database_user | localhost | database_name | Query | 19 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18777 | database_user | localhost | database_name | Query | 17 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18815 | database_user | localhost | database_name | Query | 15 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18926 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18951 | database_user | localhost | database_name | Sleep | 1 | | |
| 18952 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18953 | database_user | localhost | database_name | Sleep | 1 | | |
| 18954 | database_user | localhost | database_name | Sleep | 1 | | |
| 18955 | database_user | localhost | database_name | Query | 3 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18958 | database_user | localhost | database_name | Sleep | 1 | | |
| 18960 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18961 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18962 | database_user | localhost | database_name | Query | 3 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18963 | database_user | localhost | database_name | Query | 3 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18964 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18965 | database_user | localhost | database_name | Query | 1 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18967 | database_user | localhost | database_name | Sleep | 0 | | |
| 18968 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18974 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18978 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18979 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18980 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18982 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18983 | database_user | localhost | database_name | Sleep | 0 | | |
| 18984 | database_user | localhost | database_name | Query | 1 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18985 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18986 | database_user | localhost | database_name | Sleep | 0 | | |
| 18987 | database_user | localhost | database_name | Sleep | 0 | | |
| 18988 | database_user | localhost | database_name | Sleep | 0 | | |
| 18989 | database_user | localhost | database_name | Sleep | 0 | | |
| 18990 | root | localhost | | Query | 0 | | show processlist 

Eu não consigo entender esse log, alguém pode me ajudar neste

Meu principal comando

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
11050 mysql     15   0 1510m 272m 4080 S 629.0  7.0 675:31.90 mysqld
26990 database_user  16   0  130m  24m 6812 R 25.4  0.6   0:00.84 php
26989 database_user  16   0  155m  39m 7652 R 21.8  1.0   0:00.76 php
26988 database_user  16   0  154m  38m 7648 R 21.5  1.0   0:00.74 php
27011 database_user  16   0     0    0    0 R 18.8  0.0   0:00.57 php
26835 database_user  16   0  156m  40m 7632 R 17.5  1.0   0:00.69 php
26909 database_user  16   0     0    0    0 Z 16.5  0.0   0:00.68 php <defunct>
26977 database_user  16   0     0    0    0 Z 14.2  0.0   0:00.51 php <defunct>
26947 database_user  15   0  154m  37m 7632 R 12.9  1.0   0:00.58 php
26844 database_user  16   0     0    0    0 Z 11.2  0.0   0:00.59 php <defunct>
26956 database_user  15   0  154m  38m 7632 R 10.9  1.0   0:00.42 php
27005 database_user  16   0  146m  30m 7596 R  8.3  0.8   0:00.25 php
27058 database_user  16   0  139m  22m 7328 S  7.9  0.6   0:00.24 php
26878 database_user  16   0     0    0    0 Z  7.6  0.0   0:00.44 php <defunct>
27052 database_user  17   0  140m  23m 7292 R  7.6  0.6   0:00.23 php
27037 database_user  15   0  143m  26m 7320 S  7.3  0.7   0:00.22 php
26964 database_user  16   0     0    0    0 Z  6.9  0.0   0:00.29 php <defunct>

:)

    

Answer 1

O que sua equipe de hospedagem forneceu não é um log, mas o processo atual do Mysql que foi obtido de mysqladmin pr (ocesslist).

E a sua lista proc do MySQL mostra que você tem várias instâncias principalmente de 2 consultas:

Contagem SELECT (friend_id) AS total_friends FROM se_friends ONDE friend_status = '1'

SELECT DISTINCT SQL_CALC_FOUND_ROWS se_users.user_id, se_users.user_username, se_users.us

Não sei se estão completos, mas você deve otimizar suas consultas. por exemplo:- você deve usar count (*) em vez de count (friend_id)

E você não tem cache de consulta ativado? Se não, você deve permitir que isso melhore o desempenho imensamente. Se fizer isso, você deve verificar o status do cache de consulta para ajustá-lo ainda mais, para que as consultas usadas com mais frequência permaneçam no cache.

Como na saída superior do sistema, o Mysql está recebendo mais de 600% da CPU e parece que o seu servidor Mysql precisa de alguns ajustes. Btw, sua interpretação principal está incorreta, não é o usuário db (usuário mysql), mas o usuário do sistema.

Como muitos processos PHP também consomem a carga do servidor, seu servidor geral requer algum ajuste. Faça isso mais cedo ou mais tarde.

Para otimizar o Mysql, você deve tentar ajustar as seguintes variáveis:

As variáveis a seguir definirão os buffers globais

max_connections

max_user_connections

table_cache

key_buffer_size

query_cache_size

as variáveis abaixo serão definidas por buffers de thread:

tmp_table_size max_heap_table_size

read_buffer_size sort_buffer_size

join_buffer_size

vars abaixo tem que ser ajustado Se você estiver usando o InnoDB:

innodb_log_buffer_size

innodb_additional_mem_pool_size

innodb_buffer_pool_size

Existem muitas outras variáveis que precisam ser analisadas também. No entanto, se você ajustar as variáveis acima para se adequar à sua necessidade, isso representará uma enorme diferença em termos de desempenho.

However, don't do it unless you really understand what should be the right value for those variables. So learn it before you go about tuning Mysql on a production server as it might backfire if you don't do properly.

Se você não tem tempo suficiente para aprender, traga alguém que conheça o trabalho para fazê-lo.

    

Answer 2

Vários processos em 'Enviando dados' indicam que há algo muito errado com o tratamento de respostas na camada lógica ou que muitas consultas estão retornando conjuntos de resultados muito grandes. Você não mencionou em que a camada lógica está implementada.

Olhando para o SQL, o mesmo SQL aparece de novo e de novo:

SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1'

Espero sinceramente que isso seja truncado - e que exista uma parte mais significativa e específica do comando que está faltando - caso contrário, isso não faz sentido algum. Você deve poder ver mais do seu log de consultas lentas.

Essa consulta retorna apenas uma única linha. Mas está levando cerca de 2 segundos para o SQL executar e para a lógica recuperar os dados (já que não há nenhum deles executando, isso sugere que a consulta está rodando muito rapidamente, menos de 0.1 segundos - a maior parte do tempo é ocupada pela camada lógica). Embora isso possa ocorrer devido a uma rede muito lenta entre o banco de dados e a camada lógica, esse não é o caso aqui (as conexões são do host local). Também pode ocorrer onde há um problema com a alternância de contexto - o servidor de banco de dados e o cliente estão constantemente sendo antecipados por algum outro processo. Mas com 6 núcleos, isso precisaria ser um processo muito agressivo.

Se você conseguir que sua camada lógica recupere os dados, isso fará uma grande diferença na carga.

Por favor, forneça mais detalhes sobre o SQL sendo executado e a camada lógica e a saída de 'top'

    

Answer 3

Analisando sua coluna de tempo acima, parece que a seguinte declaração é a mais lenta:

SELECT DISTINCT SQL_CALC_FOUND_ROWS se_users.user_id, se_users.user_username, se_users.us

Está demorando mais, e parece que é apenas selecionar toda a tabela inteira, mas não posso dizer com certeza porque parece que o resto da declaração foi cortada ...

Para alterar isso, você pode incluir uma instrução WHERE que faça referência a índices, use o LIMIT comando, ou use o comando EXPLAIN para depurar sua declaração.

Você também pode procurar em mysqltuner se tiver a capacidade / acesso para ajustar o banco de dados. Se você está tentando depurar a carga do seu banco de dados MySQL, a saída do topo provavelmente não é relevante.