0

I have a simple innodb Mariadb database used for logging (only inserts with insert ignore into a single large table).

I back up the database nightly with mysqldump --no-create-info --hex-blob --disable-keys --skip-extended-insert --single-transaction --quick while inserts are running (usually under 5-10 inserts per second, on a Hetzner VPS instance with SSD).

Randomly (once in cca 7-14 days) the inserts made during the mysqldump get stuck in Updating state (so that show full processlist; shows them with Time like 12000 s = time since the mysqldump). This is a problem as the app has a limited connection pool, and as all connections are used by stuck inserts, the app loses the ability to use the database.

More weird symptoms: When the db server gets into this state, service mariadb restart never finishes, is just stuck for minutes, and I cannot reboot the server with sudo reboot which works normally - the only working way I found is to run sudo systemctl --force --force reboot.

It seems to me that some database files get stuck during the backup (filesystem problem?) and so the database flush never finishes, not for inserts nor for database restart.

Any ideas about how to go debug this?

Mariadb version: 10.6.9-MariaDB-1:10.6.9+maria~ubu2004-log

Status, variable,s innodb status, queries, table definition: https://gist.github.com/tkafka/e43fbb7a4440eca7d7d4dd6e8b490630

2
  • You've included show create table log rather than request_log. Its a little suspicious, look at journalctl -u mariadb.service -n 400 to see anything significant before the forced shutdown. If you do a bug report (cost free!), install debuginfo packages and grab a backtrace.
    – danblack
    Aug 30, 2022 at 5:53
  • @danblack Thank you, this was a helpful command. So far it seemed like I/O got too busy, and was never able to catch up. I tweaked some InnoDB variables, and doubled the instance RAM from 4GB to 8GB, and so far I haven't seen it happen in past 2 weeks. Sep 19, 2022 at 18:18

1 Answer 1

0

So far it seemed like I/O got too busy, and was never able to catch up.

I tweaked some InnoDB variables, installed jemalloc allocator (that wasn't enough), and in the end I doubled the instance RAM from 4GB to 8GB, and so far I haven't seen it happen in past 2 weeks.

Changes in config:

[mysqld]

# speed up
# http://dba.stackexchange.com/a/41562/114138
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 4M
innodb_max_dirty_pages_pct = 90
innodb_flush_log_at_trx_commit = 0

# http://harrison-fisk.blogspot.cz/2009/02/my-favorite-new-feature-of-mysql-51.html
innodb_autoinc_lock_mode = 2
# needs --log-bin
binlog_format = row
transaction-isolation = READ-COMMITTED

# https://dba.stackexchange.com/questions/269040/mariadb-insert-queries-stuck-frequently
innodb_lru_scan_depth = 64
# default 200, but we apparently have slower ssd?
innodb_io_capacity = 64
# the ratio between capacity and max was 10 but let's try to bring it down
innodb_io_capacity_max = 128
# Use 0 for SSD drives; 1 for HDD
innodb_flush_neighbors = 0

query_cache_type = OFF
query_cache_size = 0

# mysqltuner.pl advices
join_buffer_size = 512K
table_definition_cache = 1000
table_open_cache = 25000
open_files_limit = 50000
performance_schema = ON
key_buffer_size = 32M
innodb_log_file_size = 128M

# allow defrag - see https://mariadb.com/kb/en/defragmenting-innodb-tablespaces/
innodb_defragment = 1

# prevent hangs? - see https://stackoverflow.com/a/18274214/38729
optimizer_search_depth = 0

# Use jemalloc

# 1. Build and configure
# here is how - do this before: https://gist.github.com/diginfo/be7347e6e6c4f05375c51bca90f220e8
# sudo apt-get -y install autoconf libxslt-dev xsltproc docbook-xsl
# git clone https://github.com/jemalloc/jemalloc.git
# cd jemalloc
# autoconf
# ./configure
# make dist
# sudo make install

# 2. Use on ubuntu:
# Put the following into /etc/systemd/system/mariadb.service.d/jemalloc.conf
# [Service]
# Environment=LD_PRELOAD=/usr/local/lib/libjemalloc.so.2

# 3. Restart
# sudo service mysqld restart
# sudo service mysqld status

# 4. Verify:
# SHOW VARIABLES LIKE 'version_malloc_library';
# - should show eg.
# key: version_malloc_library
# value: jemalloc 5.3.0-16-gadc70c051135ac8909ca37492d7b104150077033

2
  • 1
    Glad its alleviated. Want to show your show global status now? innodb_buffer_pool_size / innodb_log_buffer_size look far too low. 9y old dba ref is a bit old. innodb_max_dirty_pages_pct looks very high given low io capacity. Leaves very little buffer pool free. And innodb_io_capacity_ looks really low. I'd try to measure it (using general tool). optimizer_search_depth is suspicious.
    – danblack
    Sep 19, 2022 at 22:42
  • 1
    innodb_log_file_size also looks too low given number of writes (~32% from tuner). Few G maybe.
    – danblack
    Sep 19, 2022 at 22:44

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.