Início / dba / Perguntas / 321456
Accepted
lyeaf
Asked: 2022-12-27 18:31:16 +0800 CST

O banco de dados continua travando a cada poucas horas

  • 772

O servidor MariaDB falhará a cada poucas horas (já faz uma semana, sim, dezenas de falhas) e reiniciará automaticamente. Em 2 travamentos anteriores, o servidor não inicializou novamente e tive que iniciá-lo com mysqld --innodb_force_recovery=3, fazer um dump dos bancos de dados/esquemas, apagar todos os dados do mysql/mariadb, reinstalá-lo e "importar " o .sql no servidor MariaDB recém-instalado mysql < dump.sql(então funcionou, por "funcionou" significa que o banco de dados poderia pelo menos ser executado, mas ainda travaria a cada poucas horas)

Eu sou um novato. Só para você saber.

Eu uso MariaDB, aqui está a versão:mysql Ver 15.1 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Todas as tabelas que estou usando estão no InnoDB. Todas as minhas consultas são simples , nada complicado está sendo feito. INSERT INTO table(a,b,c) VALUES(1,2,3)<= não fica muito mais complicado do que esse tipo de consulta, na verdade. (É principalmente um banco de dados destinado a arquivamento, portanto, muitos INSERTs, alguns UPDATEs, poucos SELECTs). 90% das requisições são via localhost. (Um script python com aiomysqlmódulo buscará dados pela Internet e os enviará para o servidor MariaDB local)

O VPS que uso é ligado: 4.19.0-21-amd64 #1 SMP Debian 4.19.249-2 (2022-06-30) x86_64 GNU/Linux, usa SSD, tem 24GB de memória, 14 vCores. O uso da CPU mal chega a 20%, o uso da memória também está em 20% (com base no que vejo no topcomando)

Com base no status do servidor que vejo no MySQL Workbench, há cerca de 2700 InnoDB Writes per Second/420 Selects per second/120 Reads per seconds/2185 Connections, o tamanho total do banco de dados deve ser de cerca de 140 GB~

Esses são números normais para mim. Eu tenho mais de 200 instâncias de um script python em execução, cada uma cria um pool de conexões com aiomysqlum mínimo de 10 conexões/máximo de 100, ou seja, um mínimo de 2.000 conexões.

Abaixo do conteúdo do meu .cnf file, eu disse que era novato, então preenchi aos poucos com o que achei que resolveria o problema. E abaixo disso, o conteúdo de : /var/log/mysql/error.logE aqui um link de uma parte do conteúdo de /var/log/syslog: https://ctxt.io/2/AAAQl4uRFg (A parte interessante para mim são essas 2 linhasOut of memory: Kill process 10042 (mysqld) score 333 or sacrifice child. Killed process 10042 (mysqld) 

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
#port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
innodb_buffer_pool_size = 16G
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M

    #
    # * Fine Tuning
    #
    #key_buffer_size        = 16M
    max_allowed_packet     = 2G
    #thread_stack           = 192K
    thread_cache_size      = 38
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    #myisam_recover_options = BACKUP
    max_connections        = 10000
    table_cache            = 3000
    #thread_concurrency     = 10
    innodb_read_io_threads  = 8
    innodb_write_io_threads = 8
    innodb_file_per_table   = 1
    innodb_flush_log_at_trx_commit = 2
    enforce_storage_engine  = InnoDB
    #
    # * Query Cache Configuration
    #
    #query_cache_limit      = 1M
    query_cache_type                = 0
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file       = /var/log/mysql/mysql.log
    #general_log            = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Enable the slow query log to see queries with especially long duration
    #slow_query_log_file    = /var/log/mysql/mariadb-slow.log
    #long_query_time        = 10
    #log_slow_rate_limit    = 1000
    #log_slow_verbosity     = query_plan
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    #max_binlog_size        = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = exclude_database_name
    
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    #chroot = /var/lib/mysql/
    #
    # For generating SSL certificates you can use for example the GUI tool "tinyca".
    #
    #ssl-ca = /etc/mysql/cacert.pem
    #ssl-cert = /etc/mysql/server-cert.pem
    #ssl-key = /etc/mysql/server-key.pem
    #
    # Accept only connections using the latest and most secure TLS protocol version.
    # ..when MariaDB is compiled with OpenSSL:
    #ssl-cipher = TLSv1.2
    # ..when MariaDB is compiled with YaSSL (default in Debian):
    #ssl = on
    
    #
    # * Character sets
    #
    # MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
    # utf8 4-byte character set. See also client.cnf
    #
    character-set-server  = utf8mb4
    collation-server      = utf8mb4_general_ci
    
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    
    #
    # * Unix socket authentication plugin is built-in since 10.0.22-6
    #
    # Needed so the root database user can authenticate without a password but
    # only when running as the unix root user.
    #
    # Also available for other users if required.
    # See https://mariadb.com/kb/en/unix_socket-authentication-plugin/
    
    # this is only for embedded server
    [embedded]
    
    # This group is only read by MariaDB servers, not by MySQL.
    # If you use the same .cnf file for MySQL and MariaDB,
    # you can put MariaDB-only options here
    [mariadb]
    # This group is only read by MariaDB-10.3 servers.
    # If you use the same .cnf file for MariaDB of different versions,
    # use this group for options that older servers don't understand
    [mariadb-10.3]

2022-12-26 23:20:30 0 [Note] InnoDB: Buffer pool(s) load completed at 221226 23:20:30
2022-12-26 23:48:18 0 [Note] InnoDB: Using Linux native AIO
2022-12-26 23:48:18 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-26 23:48:18 0 [Note] InnoDB: Uses event mutexes
2022-12-26 23:48:18 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-12-26 23:48:18 0 [Note] InnoDB: Number of pools: 1
2022-12-26 23:48:18 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-12-26 23:48:18 0 [Note] InnoDB: Initializing buffer pool, total size = 16G, instances = 8, chunk size = 128M
2022-12-26 23:48:19 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-26 23:48:19 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-26 23:48:20 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=244433492785
2022-12-26 23:48:27 0 [Note] InnoDB: 54 transaction(s) which must be rolled back or cleaned up in total 54 row operations to undo
2022-12-26 23:48:27 0 [Note] InnoDB: Trx id counter is 145319537
2022-12-26 23:48:27 0 [Note] InnoDB: Starting final batch to recover 267086 pages from redo log.
2022-12-26 23:48:34 0 [Note] InnoDB: To recover: 231381 pages from log
2022-12-26 23:48:49 0 [Note] InnoDB: To recover: 142626 pages from log
2022-12-26 23:49:04 0 [Note] InnoDB: To recover: 64756 pages from log
2022-12-26 23:49:19 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-12-26 23:49:19 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
2022-12-26 23:49:19 0 [Note] InnoDB: To roll back: 54 transactions, 54 rows
2022-12-26 23:49:19 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-12-26 23:49:19 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-12-26 23:49:19 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-26 23:49:19 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319470
2022-12-26 23:49:19 0 [Note] InnoDB: Waiting for purge to start
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319063
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319071
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319337
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319497
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319425
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319510
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319389
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319079
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319106
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319189
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319518
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319269
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319477
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319083
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319525
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319070
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319311
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319227
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319363
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319387
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319522
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319469
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319107
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319094
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319075
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319456
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319306
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319080
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319266
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319280
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319234
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319276
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319172
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319392
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319404
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145318827
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319408
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319402
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319340
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319480
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319072
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319064
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319364
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319520
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319330
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319304
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319500
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319508
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319069
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319073
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319495
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319109
2022-12-26 23:49:19 0 [Note] InnoDB: Rolled back recovered transaction 145319511
2022-12-26 23:49:19 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2022-12-26 23:49:19 0 [Note] InnoDB: 10.3.36 started; log sequence number 244434151651; transaction id 145319592
2022-12-26 23:49:19 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-12-26 23:49:19 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-12-26 23:49:19 0 [Note] Recovering after a crash using tc.log
2022-12-26 23:49:19 0 [Note] Starting crash recovery...
2022-12-26 23:49:19 0 [Note] Crash recovery finished.
2022-12-26 23:49:19 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-12-26 23:49:19 0 [Note] Reading of all Master_info entries succeeded
2022-12-26 23:49:19 0 [Note] Added new Master_info '' to hash table
2022-12-26 23:49:19 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.36-MariaDB-0+deb10u2'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 10
2022-12-26 23:49:27 7418 [Note] InnoDB: Number of pools: 2
2022-12-26 23:50:28 47992 [Note] InnoDB: Number of pools: 3

Obrigado pela ajuda, estou desesperado neste momento. Se você tiver mais informações, tentarei fornecê-las. Por favor, seja detalhado sobre como obtê-lo

(Dos comentários)

CREATE TABLE : mysql CREATE TABLE `messages` (
    `message_id` varchar(46) CHARACTER SET utf8mb4
           COLLATE utf8mb4_bin NOT NULL, 
    `message` text CHARACTER SET utf8mb4
           COLLATE utf8mb4_bin DEFAULT NULL, 
    `user_id` int(11) NOT NULL, 
    `date_sent` timestamp NOT NULL
          DEFAULT current_timestamp()
          ON UPDATE current_timestamp(),
    `channel_sent` int(11) NOT NULL, 
    `stream_sent` varchar(13) NOT NULL, 
    PRIMARY KEY (`message_id`), 
    KEY `idx_user_id` (`user_id`), 
    KEY `idx_channel_sent` (`channel_sent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
mariadb

2 respostas

  1. Best Answer

    Isso é ... O problema não foi causado pelo MariaDB, foi causado por um script python em execução no meu servidor que estava com vazamentos de memória. Uma vez que a memória estava completamente cheia, o sistema decidiu matar o mysqld porque eu acho que era o que estava usando mais memória. Então, eventualmente, o script que vazava seria eliminado, mas como era supervisionado e reiniciado automaticamente após a falha, o problema apenas se reiterava novamente.

    Eu não tinha ideia de que um script python poderia vazar memória ... consertei o vazamento, agora o MySQL não trava mais

    • 3

  2. então são no mínimo 2.000 conexões

    Esse é provavelmente o problema.

    2700 linha única INSERTspor segundo?
    É "estouro"? Ou razoavelmente estável?
    Quantos "clientes" existem realmente? Certamente não 2000?

    Defina max_connectionspara apenas 200 e elimine o pooling excessivo. Se isso for um problema, explique por que você precisa de mais de 2.000 conexões. Teremos que contornar isso.

    Se for prático, reúna, digamos, 100 linhas em um único arquivo INSERT. (Acho que o Python tem um "executemany()".) Se você tiver uma restrição de tempo para inserir as linhas, digamos "100 ou 1 segundo, o que ocorrer primeiro".

    Por favor, forneça SHOW CREATE TABLEe uma amostra INSERT. Pode haver problemas com índices, especialmente UNIQUE.

    O log mostra 54 INSERTs[eu acho] sendo revertido. Mesmo 54 ameaça o servidor tropeçar em si mesmo, tentando dar a cada thread uma parte igual. No processo, cada processo leva mais tempo do que o necessário. O agrupamento de 100 linhas reduzirá a sobrecarga o suficiente para possibilitar a inserção de 10 vezes mais linhas por segundo.

    • 2

relate perguntas