Skip to main content

friendica (DFRN) - Link to source

opensocial.at database data seems corrupt

!Friendica Support

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=1

For 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 rows
!Friendica Support

Friendica Support reshared this.

in reply to Philipp Holzer

Philipp Holzer
friendica (DFRN) - Link to source
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 Network page is unusual high as well:

Datenbank: 8.827/0, Netzwerk: 0, Darstellung: 0.07, Sitzung: 0, I/O: 0, Sonstiges: 0.32, Gesamt: 9.22
Class-Init: 0.012, Boot: 0.005, Init: 0, Inhalt: 9.205, Sonstiges: 0.002, Gesamt: 9.224

At first, I thought it's because of some missing optimizations. But I'm now sure that there's something wrong in the database ...

@Michael Vogel

Friendica Support reshared this.

in reply to Philipp Holzer

Philipp Holzer
friendica (DFRN) - Link to source
Philipp Holzer
•

I changed it back to my original settings:

[mysqld]
performance_schema = ON

join_buffer_size = 140M
innodb_buffer_pool_size = 46G
innodb_log_buffer_size = 31M
innodb_log_file_size = 3G
table_open_cache = 3000
max_connections = 400
wait_timeout = 200
interactive_timeout = 4000

As said, I do have 64 GB RAM, so I chose high values to optimize it as best as possible

Friendica Support reshared this.

in reply to Philipp Holzer

Raroun
friendica - Link to source
Raroun
• •

@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.

@Philipp Holzer
This entry was edited (1 year ago)

Friendica Support reshared this.

in reply to Raroun

Philipp Holzer
friendica (DFRN) - Link to source
Philipp Holzer
•

I added these two settings and increased the nproc/nofile to 102400
And now it's incredible fast again :-)

Datenbank: 0.137/0, Netzwerk: 0, Darstellung: 0.07, Sitzung: 0, I/O: 0, Sonstiges: 0.07, Gesamt: 0.29
Class-Init: 0.011, Boot: 0.005, Init: 0, Inhalt: 0.268, Sonstiges: 0.002, Gesamt: 0.286

Thanks for your help

Friendica Support reshared this.

in reply to Philipp Holzer

Raroun
friendica - Link to source
Raroun
• •

@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
This entry was edited (1 year ago)

Friendica Support reshared this.

in reply to Philipp Holzer

Philipp Holzer
friendica (DFRN) - Link to source
Philipp Holzer
•

OK thanks for your suggestions, I think @Raroun is right with the unclean shutdown.

I analyzed the shutdown process and found:

db_1     | 2022-12-16 21:10:26 0 [Note] mariadbd (initiated by: unknown): Normal shutdown
db_1     | 2022-12-16 21:10:26 0 [Note] InnoDB: FTS optimize thread exiting.
db_1     | 2022-12-16 21:10:26 0 [Note] InnoDB: Starting shutdown...
db_1     | 2022-12-16 21:10:26 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
db_1     | 2022-12-16 21:10:26 0 [Note] InnoDB: Restricted to 746368 pages due to innodb_buf_pool_dump_pct=25
db_1     | 2022-12-16 21:10:26 0 [Note] InnoDB: Buffer pool(s) dump completed at 221216 21:10:26
opensocial_db_1 exited with code 137

But "Exit 137" isn't a normal exit, it indicates a "OOM" shutdown.

@Raroun

Friendica Support reshared this.

in reply to Philipp Holzer

Raroun
friendica - Link to source
Raroun
• •
Yep, it’s a OOM sigkill from docker.
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 :)

Friendica Support reshared this.

in reply to Philipp Holzer

Torsten Torsten
mastodon - Link to source
Torsten Torsten
• •
Wow, now it's super fast... thanks for the work. Do you have a way to receive donations?

Friendica Support reshared this.

⇧