Skip to main content

Search

Items tagged with: MariaDB

I now run #mariadb with

--optimizer-use-condition-selectivity=1 --optimizer-switch='rowid_filter=off' --innodb-buffer-pool-size=6G --query-cache-size=64M --max-heap-table-size=64M --tmp-table-size=64M

That helps a lot overall, but the personal page is still about 8 to 10 times slower than everything else.

#MariaDB

Question for the #MySQL / #MariaDB buffs, I have three tables defined thus:
CREATE TABLE IF NOT EXISTS `user` (
	`uid` mediumint unsigned NOT NULL auto_increment COMMENT 'sequential ID',
	...
	 PRIMARY KEY(`uid`)
);
CREATE TABLE IF NOT EXISTS `gserver` (
	`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
	...
	 PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `user-gserver` (
	`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
	`gsid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Gserver id',
	...
	PRIMARY KEY(`uid`,`gsid`),
	FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
	FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
);

Running the last query triggers the error Foreign key constraint is incorrectly formed. Is there no way to reference multiple tables in foreign keys out of a compound primary key?

#SQL #Database #DBA
#mysql #database #SQL #MariaDB #dba

Moved my #Friendica instance database from #MariaDB 10.6 to 10.11.

Now running with default settings instead of customized. The experience seems smoother so far
#friendica #MariaDB

@7homas ๐Ÿณ๏ธโ€๐ŸŒˆ๐Ÿ––๐Ÿป Der RAM wird ja des Servers wird ja nicht wirklich beansprucht. @๐—๐—ฎ๐—ธ๐—ผ๐—ฏ :๐—ณ๐—ฟ๐—ถ๐—ฒ๐—ป๐—ฑ๐—ถ๐—ฐ๐—ฎ: ๐Ÿ‡ฆ๐Ÿ‡น โœ… hatte oben eine top-Kopfzeile gepostet, da geht es eher zart zur Sache. Entweder die DB hat nicht wirklich eine relevante GrรถรŸe, oder sie ist so konfiguriert, dass sie den RAM nicht ausnutzt.
Vllt. gibt's hier ja irgendwo einen #Admin der sich mit #MariaDB auskennt?
#admin #MariaDB @jakob ๐Ÿ‡ฆ๐Ÿ‡น โœ… @๐Ÿ‘ค ๐Ÿณ๐—ต๐—ผ๐—บ๐—ฎ๐˜€ โœ…๐Ÿ––๐Ÿ”’

@Jakob@Michael Vogel
und verwende diese fรผr den Devel-Branch und eigene Entwicklungen...
Wรผrde ich nicht machen, besser zwei neue aufsetzen.
Ich kann dir empfehlen, wenn du #MariaDB nutzte, Mariabackup zu nutzen fรผr die DB, dann alles per Borgbackup auf irgendein SSH erreichbares Ziel zu schaufeln, dass ist wirklich brauchbar, auch wenn du das 60 GB Daten zu sichern hast (was bei Friendica ja nach paar Jahren der Fall ist).

Alternativ, habe ich aber nicht am laufen, ein Disk Snapshot nach remote machen, aber da bin ich nicht sicher wie das genau geht, aber denke das ist nochmal besser. Snapshots lokal kombiniert mit meinem Setup von oben sind auch eine Mรถglichkeit, da kann man sicher easy paar Tage aufbewahren.
#MariaDB @Michael Vogel @jakob ๐Ÿ‡ฆ๐Ÿ‡น โœ…

Hello #MariaDB people, my #Friendica server is down since I installed the latest MariaDB update and it also switched from #PHP 7 to 8.

All I get in the php error log is this:
4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

Any ideas are welcome! I need your help!

!Friendica Support #help
#friendica #help #php #MariaDB !Friendica Support

@Michael Vogel ok, so I need to figure out what changed in the latest #MariaDB version compared to the one before. Any tip which forum to use?
#MariaDB @Michael Vogel

#friendica #MariaDB #RAM #oom @utzer [Friendica] !Friendica Support

Hello !Friendica Support,
anyone any idea what this messages in journalctl mean? I got lots of these before my server crashed and have no Idea what this is. 

Mai 28 03:10:26 friendica mariadbd[553]: 2021-05-28  3:10:26 0 [Warning] InnoDB: A long semaphore wait:
Mai 28 03:10:26 friendica mariadbd[553]: --Thread 139676422764096 has waited at row0upd.cc line 179 for 465.00 seconds the semaphore:
Mai 28 03:10:26 friendica mariadbd[553]: S-lock on RW-latch at 0x55a2a8136178 created in file dict0dict.cc line 1041
Mai 28 03:10:26 friendica mariadbd[553]: a writer (thread id 139674262697536) has reserved it in mode  wait exclusive
Mai 28 03:10:26 friendica mariadbd[553]: number of readers 1, waiters flag 1, lock_word: ffffffff
Mai 28 03:10:26 friendica mariadbd[553]: Last time write locked in file dict0stats.cc line 2487


I guess something was wrong with either MariaDB or the VM itself that cause long operation times on the database which led to lots of long running locks. But what do I do with this?

#Friendica #MariaDB #Mysql #Linux #Semaphore
#friendica #linux #mysql #MariaDB #Semaphore !Friendica Support

Since a few days sometimes my #mariadb load spikes to 140, anyone else see this, it just takes 1 minute and normalizes again. till now I was to slow to capture the mytop view, just the end of such thing in this capture.

When it is over the load normalizes at 0.8 again.

@Steffen K9 ๐Ÿฐ did you experience anything like that? I saw it twice, once yesterday and just now.
@Michael Vogel any idea what happens?

#friendica !Friendica Support

#friendica #MariaDB @Michael Vogel @Steffen K9 ๐Ÿฐ !Friendica Support

I files a #MariaDB bug, hat happens on my #Friendica server. Didn't really beliefe it is a bug in MariaDB, but there is a first comment seeming to be positive about my filed bug.

See here: https://jira.mariadb.org/browse/MDEV-25031

Anyone else having this problem?

!Friendica Support

#friendica #MariaDB !Friendica Support

@Mathias Hellquist@Michael Vogel
Turns out they aren't actually compatible enough to do a backup->restore of tables and data.

yes, they are not compatible anymore sind MySQL 8, neither compatible to MySQL 7.x nor to #MariaDB.
#MariaDB @Michael Vogel @Mathias Hellquist

โ‡ง