opensocial.at database data seems corrupt
It seems like I do have a problem with my opensocial.at database 
.
The database ran full about a year ago (what a shame, I know :( ...) and I had to recover some of the data (otherwise I had to import a backup about ~12hours ago, I tried to avoid to loose posts for this period of time for my users ...). So I stopped it, started it with innodb_force_recovery=3, repaired it and checked it with mysqlcheck.
Two days ago, I updated the mariadb from 1.10.3 to 1.10.10 and now the problems occur again.
I stopped the MariaDB 1.10.3 container, updated it to 1.10.10 and started it again .. And MariaDB started a "crash recovery". I really don't know why... The crash recovery wasn't successfully (I tried it ~4 times), so I had to add innodb_force_recovery=3 again.
Now the database went up, but everytime I stopped it and started it again, the crash recovery appears again.
So after the instance was up and running, I dumped the whole database with mysqldump into one single *.sql , started a brand new MariaDB 1.10.10 and imported the dump again.
So far so good ..
But... unfortunately, after a restart, the crash recovery appears again. So I'm totally lost, what's now happening..
I noticed during the import that one batch of rows took about 2 hours to complete with the result query affected 0 rows.
here's my customized.cnf, which I'm using (it's a Hetzner root server with 64 GB RAM and 12 CPUs):
[mysqld]
query_cache_size = 0
query_cache_type = 0
performance_schema = ON
join_buffer_size = 140M
innodb_buffer_pool_size = 12G
innodb_log_buffer_size = 31M
innodb_log_file_size = 3G
table_open_cache = 1000
max_connections = 400
wait_timeout = 200
interactive_timeout = 4000
log-bin = mysqld-bin
transaction-isolation = READ-COMMITTED
binlog-format = ROW
skip-innodb-read-only-compressed = ON
innodb_read_only_compressed = OFF
innodb_use_native_aio = OFF
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1For the new MariaDB instance, I didn't add any customization to avoid any wrong options.
currently, it makes a recovery again:
db_1 | 2022-12-16 10:41:00 0 [Note] InnoDB: Rolled back recovered transaction 371172
db_1 | 2022-12-16 10:41:11 0 [Note] InnoDB: To roll back: 1 transactions, 7611737 rows
db_1 | 2022-12-16 10:41:26 0 [Note] InnoDB: To roll back: 1 transactions, 7608132 rows
db_1 | 2022-12-16 10:41:41 0 [Note] InnoDB: To roll back: 1 transactions, 7603940 rows
db_1 | 2022-12-16 10:41:46 0 [Note] DDL_LOG: Crash recovery executed 1 entries
db_1 | 2022-12-16 10:41:46 0 [Note] Server socket created on IP: '0.0.0.0'.
db_1 | 2022-12-16 10:41:46 0 [Note] Server socket created on IP: '::'.
db_1 | 2022-12-16 10:41:47 0 [Note] mariadbd: ready for connections.
db_1 | Version: '10.10.2-MariaDB-1:10.10.2+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
db_1 | 2022-12-16 10:41:56 0 [Note] InnoDB: To roll back: 1 transactions, 7601453 rows
db_1 | 2022-12-16 10:42:11 0 [Note] InnoDB: To roll back: 1 transactions, 7600146 rows
db_1 | 2022-12-16 10:42:26 0 [Note] InnoDB: To roll back: 1 transactions, 7598395 rows
db_1 | 2022-12-16 10:42:41 0 [Note] InnoDB: To roll back: 1 transactions, 7595967 rows
db_1 | 2022-12-16 10:42:56 0 [Note] InnoDB: To roll back: 1 transactions, 7594000 rows
db_1 | 2022-12-16 10:43:11 0 [Note] InnoDB: To roll back: 1 transactions, 7590646 rows
db_1 | 2022-12-16 10:43:26 0 [Note] InnoDB: To roll back: 1 transactions, 7585618 rows
db_1 | 2022-12-16 10:43:41 0 [Note] InnoDB: To roll back: 1 transactions, 7579929 rows
db_1 | 2022-12-16 10:43:56 0 [Note] InnoDB: To roll back: 1 transactions, 7574058 rows
db_1 | 2022-12-16 10:44:11 0 [Note] InnoDB: To roll back: 1 transactions, 7568214 rowslike this
Large database
!Friendica Admins
Hi everyone,
last week, I struggled with the size of my database for opensocial.at .. It went full (80GB) .. I added additional 10GB to the volume, but it seems to constantly increase and I think in a few weeks, I will be at the same situation like the last week.
Is there a possibility to shrink the database, or to wipe old data?
I recently deleted about 1.200 spam accounts (and set the register option to approval first ... lessons learned...). Are there leftovers inside the db which I can delete?
It's not that I don't have enough space, but I'm afraid of my backup borg-backup space in the near future ^^
Philipp Holzer
in reply to Philipp Holzer • • •at least the instance is up and running again.
But not THAT good..
@Michael Vogel do you have any suggestions for me?
Since months, the database time for the
Networkpage is unusual high as well:At first, I thought it's because of some missing optimizations. But I'm now sure that there's something wrong in the database ...
Torsten Torsten likes this.
Philipp Holzer
in reply to Philipp Holzer • • •I changed it back to my original settings:
As said, I do have 64 GB RAM, so I chose high values to optimize it as best as possible
Raroun
in reply to Philipp Holzer • • •@Philipp Holzer
Hi Philipp,
im not familiar with the Container Versions of MariaDB, because that version numbers donยดt fit the official "self installed" ones.
Mariadb >10.4 has some weird changes in indexing, which makes the database insanely slow.
You could try to include this into your mariadb.conf:
optimizer_use_condition_selectivity = 1
optimizer_switch='rowid_filter=off'
With this set, the behaviour of Maridb is like the versions before 10.4.
like this
Philipp Holzer and Steffen K9 ๐ฐ like this.
Philipp Holzer
in reply to Raroun • • •I added these two settings and increased the nproc/nofile to 102400
And now it's incredible fast again
Thanks for your help
Raroun
in reply to Philipp Holzer • • •@Philipp Holzer
My guess regarding the permament crash recovery:
When sending the database a shutdown, it flushes erverythin in memory in its files.
Depending on your hardware and database size, this could take a while - iยดve seen databases that took up to 10 minutes to do that.
If your container or OS is shutting down before the process ends, youll get a crash recovery on the next startup.
check if the maridb process is terminated before the container or OS is shut down.
If the databse is correctly shut down and the crash recovery starts again on the next database startup, you could try to remove the MariaDB Log files. The transactions are stored in that files.
You should back them up before removing them, just in case you need a rollback.
i donยดt know your mariadb install locations, but thats what i would do:
1.) systemctl stop mariadb
2.) cd /var/lib/mysql
3.) cp ib_logfile* /tmp
4.) rm -f ib_logfile*
5.) systemctl start mariadb
It is worth a try. but be sure to backup ib_logfile* first and do a database dump - better be safe than sorry.
After that procedure, no crash recovery will happen, because theres nothing to recover.
Philipp Holzer likes this.
Philipp Holzer
in reply to Philipp Holzer • • •OK thanks for your suggestions, I think @Raroun is right with the unclean shutdown.
I analyzed the shutdown process and found:
But "Exit 137" isn't a normal exit, it indicates a "OOM" shutdown.
like this
Roland Häder and Raroun like this.
Raroun
in reply to Philipp Holzer • • •Iโm sorry, I donโt use docker and I am not familiar with it - but that Exit Code looks like a good hint to start with :)
Philipp Holzer likes this.
utzer [Friendica]
in reply to Philipp Holzer • • •utzer [Friendica]
in reply to utzer [Friendica] • • •Philipp Holzer likes this.
Philipp Holzer
in reply to utzer [Friendica] • • •You mean mariadb.com/kb/en/mysql_upgradโฆ , don't you?
utzer [Friendica]
in reply to Philipp Holzer • • •@Philipp Holzer yes, that is what I mean. I forgot that one time.
And you ran mysqlcheck -A -o I guess?
Philipp Holzer likes this.
Philipp Holzer
in reply to utzer [Friendica] • • •Torsten Torsten
in reply to Philipp Holzer • • •Raroun likes this.
Raroun
in reply to Torsten Torsten • • •Me?
No donations needed. Iโm just happy to help :)