Início / dba / Perguntas / 226435
Accepted
Alireza
Asked: 2019-01-07 03:48:41 +0800 CST

Como restringir as consultas mySQL para não afetar o servidor e derrubá-lo?

  • 772

Há momentos em que algumas consultas pesadas aumentam a carga do servidor e, após algum tempo, a CPU e a memória atingem 100%.

Este é my.cnfo arquivo de configuração:

[mysqld]
expire_logs_days= 2

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/storage/mysql

# LOGGING #
log-error                      = /var/storage/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/storage/mysql/mysql-slow.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/storage/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb-log-files-in-group      = 2
innodb_log_file_size=500M
innodb_buffer_pool_size=6G
innodb_flush_log_at_trx_commit=2
# recently added for compression, omit it if mysql has problems with it
innodb_file_format=Barracuda

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

[client]
default-character-set   = utf8mb4

[mysql]
default-character-set   = utf8mb4

O servidor possui 6 núcleos com 16 GB de RAM. Alguma sugestão para restringir MySQLe torná-lo seguro?

EDIT-1 Esta é uma das consultas lentas que levou 4,3 segundos MySQLpara retornar os dados e examinou cerca de 3 milhões de linhas:

# Time: 190107  6:22:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 204732  Schema: my_db  QC_hit: No
# Query_time: 4.306051  Lock_time: 0.000058  Rows_sent: 0  Rows_examined: 3253235
# Rows_affected: 0
SET timestamp=1546842150;
SELECT ((old_credit + (1*amount))/10) credit
FROM `credits`
WHERE user_id=' ' or user_id=(SELECT user_id FROM accounts WHERE email=' ')
ORDER BY id DESC
LIMIT 1;

A consulta é criada por um aplicativo de BI front-end (pode ser alterada até certo ponto).

Os índices da tabela são:

MariaDB [my_db]> show index from credits;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credits |          0 | PRIMARY    |            1 | id          | A         |     2014915 |     NULL | NULL   |      | BTREE      |         |               |
| credits |          1 | credit_id  |            1 | credit_id   | A         |     2014915 |     NULL | NULL   | YES  | BTREE      |         |               |
| credits |          1 | user_id    |            1 | user_id     | A         |      134327 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

EDIT-2:

Database changed
MariaDB [my_db]> show index from accounts;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| accounts |          0 | PRIMARY             |            1 | id          | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id             |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | email_idx           |            1 | email       | A         |      486034 |      190 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            2 | email       | A         |      486034 |       40 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            3 | phone       | A         |      486034 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql linux

1 respostas

  1. Best Answer

    Você realmente quer dizer = ' '? Isso é verificar se a string tem um caractere, ou seja, um espaço.

    ORé um assassino de desempenho, então vamos nos livrar dele usando UNION:

        ( SELECT  id, ((old_credit + (amount))/10) AS credit
            FROM  `credits`
            WHERE  user_id=' '
            ORDER BY  id DESC
            LIMIT  1;
    )
    UNION ALL
    ( SELECT  c.id, ((c.old_credit + (c.amount))/10) AS credit
            FROM  `credits` AS c
            JOIN  accounts AS a  ON a.user_id = c.user_id
            WHERE  a.email=' ' 
            ORDER BY  c.id DESC
            LIMIT  1;
    )
    ORDER BY  id DESC
    LIMIT  1;

    Índices necessários:

    credits:  (user_id, id)
accounts: (email, user_id)

    Isso pode ser ainda mais rápido e elimina a coluna extra (id) que a solução acima possui.

    SELECT ((c2.old_credit + (c2.amount))/10) AS credit
  FROM (
    ( SELECT  id
            FROM  `credits`
            WHERE  user_id=' '
            ORDER BY  id DESC
            LIMIT  1;
    )
    UNION ALL
    ( SELECT  c.id
            FROM  `credits` AS c
            JOIN  accounts AS a  ON a.user_id = c.user_id
            WHERE  a.email=' ' 
            ORDER BY  c.id DESC
            LIMIT  1;
    )
    ORDER BY  id DESC
    LIMIT  1;
  ) AS x
  JOIN credits c2  ON c2.id = x.id

    Os itens acima INDEXessão necessários aqui também. (E eu estou supondo que creditstem PRIMARY KEY(id).)

    Rows_examined: 3253235provavelmente diminuirá para menos de 10.

    Quanto a como evitar consultas impertinentes, MariaDB 10.1+, mas não MySQL, tem max_statement_time.

    Não vejo razão para você estar atingindo 100% de memória. (veja o comentário.) Mas cada cópia dessa consulta provavelmente consumiria um núcleo por 4,3 segundos. (Qualquer uma das minhas versões provavelmente não vai mastigar nem 4,3 milissegundos .)

    Você quer ajuda com outras consultas lentas?

    • 1

relate perguntas