主页 / dba / 问题 / 321456
Accepted
lyeaf
Asked: 2022-12-27 18:31:16 +0800 CST

数据库每隔几个小时就会崩溃

  • 772

MariaDB 服务器每隔几个小时就会崩溃（已经一周了，是的，崩溃了几十次），然后自动重启。在之前的 2 次崩溃中，服务器没有再次启动，我不得不用 启动它mysqld --innodb_force_recovery=3，然后转储数据库/模式，擦除 mysql/mariadb 的所有数据，重新安装它，然后“导入“通过新安装的 MariaDB 服务器上的 .sql mysql < dump.sql（然后它工作了，“工作”意味着数据库至少可以运行，但它仍然每隔几个小时就会崩溃）

我是新手。只是想让你知道。

我使用 MariaDB，这是版本：mysql Ver 15.1 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

我使用的所有表都在 InnoDB 上。我所有的查询都很简单，没有做任何复杂的事情。INSERT INTO table(a,b,c) VALUES(1,2,3)<= 它并没有比这种类型的查询复杂得多，真的。（它主要是一个用于存档的数据库，所以有很多 INSERT，一些 UPDATE，很少的 SELECT）。90% 的请求是通过本地主机进行的。（带有aiomysql模块的 python 脚本将通过互联网获取数据，然后将其发送到本地 MariaDB 服务器）

我使用的 VPS 是：4.19.0-21-amd64 #1 SMP Debian 4.19.249-2 (2022-06-30) x86_64 GNU/Linux，使用 SSD，有 24GB 内存，14 个 vCores。CPU 使用率勉强为 20%，内存使用率也为 20%（根据我在top命令中看到的）

根据我在MySQL Workbench上看到的Server Status，大约有2700个InnoDB Writes per Second/ 420 Selects per second/ 120 Reads per seconds/ 2185个Connections，数据库总大小应该在140GB左右~

这些对我来说是正常的数字。我有 200 多个运行的 python 脚本实例，每个实例都创建一个连接池，aiomysql最少 10 个连接/最多 100 个连接，因此最少有 2000 个连接。

在my的内容下面.cnf file，我跟大家说了我是新手，所以慢慢的填上我认为可以解决问题的内容。在此之下，内容：/var/log/mysql/error.log这里是部分内容的链接/var/log/syslog：https ://ctxt.io/2/AAAQl4uRFg （对我来说有趣的部分是那两行Out 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

谢谢你的帮助，我现在很绝望。如果您有更多信息，我会尽力提供。请详细说明获取方式

（来自评论）

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 个回答

  1. Best Answer

    那是...问题不是由 MariaDB 引起的，它是由我的服务器上运行的 python 脚本引起的，该脚本存在内存泄漏。一旦内存完全填满，系统决定终止 mysqld，因为我猜它是使用最多内存的那个。然后最终泄漏的脚本会被杀死，但由于它受到监督并且会在崩溃时自动重新启动，这个问题只会再次出现。

    我不知道 python 脚本会泄漏内存...我修复了它的泄漏，现在 MySQL 不再崩溃了

    • 3

  2. 所以这是最少 2000 个连接

    这可能是问题所在。

    2700单行INSERTs每秒？
    是不是“爆”了？还是比较稳定？
    真正有多少“客户”？确定不是2000？

    设置max_connections为仅 200，并摆脱过多的池化。如果这是一个问题，请详细说明为什么需要 2000 多个连接。我们需要解决这个问题。

    如果可行，将 100 行收集到一个 batchedINSERT中。（我认为 Python 有一个“executemany()”。）如果您对插入行有时间限制，那么我们说“100 或 1 秒，以先到者为准”。

    请提供SHOW CREATE TABLE样品INSERT。索引可能存在问题，尤其是UNIQUE.

    日志显示 54 INSERTs[我猜] 正在回滚。即使是 54 也会威胁服务器自己绊倒，试图给每个线程一个平等的份额。在这个过程中，每个过程花费的时间都比它需要的要长。批处理 100 行将减少开销，足以使每秒插入 10 倍多的行成为可能。

    • 2

相关问题