2

The MariaDB server will crash every few hours(it's been a week so yeah, dozens of crashes), and restart automatically. In 2 previous crashes it went as far as the server not starting up again, and I had to start it with mysqld --innodb_force_recovery=3, then make a dump of the databases/schemas, erase all data of mysql/mariadb, re-install it, and "import" the .sql on the newly re-installed MariaDB server through mysql < dump.sql (Then it worked, by "worked" it means the database could at least run, but it would still crash every few hours)

I'm a newbie. Just so you know.

I use MariaDB, here's the version: mysql Ver 15.1 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

All tables I'm using are on InnoDB. All my queries are simple, nothing complicated is being done. INSERT INTO table(a,b,c) VALUES(1,2,3) <= it doesn't get much more complicated than this type of query, really. (It's mainly a database meant for archiving, so a lot of INSERTs, some UPDATEs, very few SELECTs). 90% of the requests are through localhost. (A python script with aiomysql module will fetch data over the internet, and then send it to the local MariaDB server)

The VPS I use is on: 4.19.0-21-amd64 #1 SMP Debian 4.19.249-2 (2022-06-30) x86_64 GNU/Linux, uses an SSD, has 24GB of memory, 14 vCores. The CPU usage is barely at 20%, memory usage at 20% too(based on what I see on top command)

Based on the Server Status I see on MySQL Workbench, there are about 2700 InnoDB Writes per Second / 420 Selects per second / 120 Reads per seconds / 2185 Connections, the database total size should be about 140GB~

Those are normal numbers to me. I have 200+ instances of a python script running, each creates a pool of connections with aiomysql of minimum 10 connections/maximum 100, so that's minimum 2000 connections.

Below the content of my .cnf file, I told you I was a newbie, so I filled it gradually with what I thought would resolve the issue. And below that, the content of : /var/log/mysql/error.log And here a link of a part of the content of /var/log/syslog : https://ctxt.io/2/AAAQl4uRFg (The interesting part to me being those 2 lines 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

Thank you for help, I'm desperate at this point. If you more information I'll try and provide it. Please be detailed as to how to get it

(From Comments)

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

2 Answers 2

3

That's... The issue wasn't caused by MariaDB it was caused by a python script running on my server that was having memory leaks. Once the memory was completely filled, system decided to kill mysqld because I guess it was the one that was using the most memory. Then eventually the leaking script would get killed, but as it was supervised and would be restarted automatically upon crash, the issue would just reiterate again.

I had no idea a python script could leak memory... I fixed the leak on it, now MySQL doesn't crash anymore

2

so that's minimum 2000 connections

That's probably the problem.

2700 single-row INSERTs per second?
Is it "bursty"? Or reasonably steady?
How many "clients" are there really? Surely not 2000?

Set max_connections to only 200, and get rid of the excessive pooling. If that is a problem, please elaborate on why you need 2000+ connections. We will need to work around that.

If practical, gather, say, 100 rows into a single batched INSERT. (I think Python has an "executemany()".) If you have a time constraint on getting the rows inserted, then let's say "100 or 1 second, whichever comes first".

Please provide SHOW CREATE TABLE and a sample INSERT. There may be issues with indexes, especially UNIQUE.

The log shows 54 INSERTs [I guess] being rolled back. Even 54 threatens the server to stumble over itself, trying to give each thread an equal share. In the process, each process takes longer than it needs to. Batching the 100 rows will cut back on the overhead enough to make it possible to insert 10 times as many rows per second.

11
  • So basically what I do with this database is archive in real-time messages sent on a social media chat. There are at any time between 500 and 2500+ messages per seconds sent, so the amount of INSERTs I make equal that. I have a script that does the archiving. And I have 200+ processes/instances of that script running, because of how the social media works(IRC architecture that we connectss through websockets that will allow 20 chats to be accessed, more will result in disconnections/errors)
    – lyeaf
    Dec 27, 2022 at 8:08
  • Now for each process I created, to avoid creating new connections every time they INSERT a message in the database, I created a pool of connections with aiomysql that for each process creates minimum 10 connections. I considered it was a good compromise (Not too many, not too little), that's why there are 2000+ connections
    – lyeaf
    Dec 27, 2022 at 8:12
  • Here's result of SHOW 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
    – lyeaf
    Dec 27, 2022 at 8:17
  • And a sample query I make : INSERT INTO messages(message_id, message, user_id, date_sent, channel_sent, stream_sent) VALUES('{message_id}', %s, {user_id}, FROM_UNIXTIME(date), {channel_id}, '{stream_id}')",
    – lyeaf
    Dec 27, 2022 at 8:19
  • I made it to send INSERTs by batches every 5 seconds | 100 messages as you advised, I will update you in a few hours if MariaDB crashes again or not
    – lyeaf
    Dec 27, 2022 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.