MariaDB (mysqld) problem - not enabled multi-thread (but it's enabled in my.cnf!)?

ShinJii

Verified User
Joined
Mar 20, 2014
Messages
219
Hi,
I spent today like 15 hours on this and I have no idea why this f**** sh** working only on one thread? I have everything turned on in /etc/my.cnf and nothing working (it's fresh install/new server -> centos 8/alma linux)!
If I want do backup of databases - it works only on one thread and takes forever,
If I want to update mariadb throught custombuild - it takes forever because of dumping databases with one thread...
Now it f***ed up and after reboot server it recovers pages from log... with what? ONE THREAD! !!!!

Why just one thread? I have 48 cores with 96 threads... i remember that was working on all cores in my second server (centos 7) - what changed in centos 8?! How to get it work on all possible resources to make everything faster?!?!

here you go with a screen....

EDIT: MysqlTUNER also takes so long time....after I uploaded 2000 databases... before that it was fine! on second server (centos 7) it was working always fast and it had just 12 threads! so.... I don't know whats wrong with that new server and system...

EDIT 2: I guess it repaired somehow... is it possible? I turned on my script with statistics fom all databases and.... after it was done now it's everything working fine lol weird... even mysqltuner.
 

Attachments

  • Screenshot_20220404_051358.png
    Screenshot_20220404_051358.png
    120.1 KB · Views: 15
Last edited:
Ok, after short night i'm back again.... maybe websites works fine but.... if I do something like:

Bash:
/usr/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --skip-write-binlog --optimize --all-databases

I ran this on my two servers at the same time... old one (centos 7 + Intel Xeon E5-1660v3 8c/16t + nvme disk - same disk as system) and new one (centos 8 [alma linux] + 2xIntel Xeon Gold 6248R 48c/96t + system disk is normal SSD+ mysql I mounted on second NVME disk).

OLD ONE is faster 100% - why? Old one is now at 2300 database and new one just at 1300..... so you see it's moving faster for that moment about 1000 databases!!!
Both servers are ext4 filesystem.

If I did some operations between NVME <-> NVME (because I have few nvme disks there) - transfers were like 1000-1200mb/s, but from system disk (SSD) to nvme - 150-200mb/s - it's maybe because of that? But I guess it shouldn't be that disproportion in commands like above... it's an hour since old server's done doing optimize databases.... and new server is still doing this XD
Old server: MariaDB 10.4
New server: MariaDB 10.5

Any ideas guys? It's stupid....
@smtalk
@zEitEr
@Richard G
 
Last edited:
mysql configs identical? next variables tuned according to your new hardware?
tmp_table_size
max_heap_table_size
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_io_capacity
innodb_read_io_threads
 
mysql configs identical? next variables tuned according to your new hardware?
tmp_table_size
max_heap_table_size
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_io_capacity
innodb_read_io_threads

Now it's not identical.... after all that bullshit I changed something... but at beginning it was identical.
Now I added that variables which you pasted, on old server I didn't have them.


That:
Code:
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_io_capacity
innodb_read_io_threads


But I think that's deeper problem.... with alma linux or... i don't know....
 
some google and I see a lot of same problems: https://dba.stackexchange.com/quest...ter-upgrade-to-mariadb-10-5-group-concat-join
---
so try to upgrade to 10.6 or downgrade to 10.4 (always make backups)
I saw this post and upgraded to 10.6 but... after that I'm scared to test it with
Bash:
/usr/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --skip-write-binlog --optimize --all-databases

Because today I did this and I don't know why mariadb destroyed and tried to recover but.... it didn't go well (I had to backup and do fresh installation) and my clients were angry ;D
 
check syntax, maybe new version need another parameters, also i think there is no sense to optimize freshly uploaded databases, just test performance by some requests or some test DB and test script (benchmark)
 
check syntax, maybe new version need another parameters, also i think there is no sense to optimize freshly uploaded databases, just test performance by some requests or some test DB and test script (benchmark)
It's not about it... it's working better on websites than old server and I'm satisfied with that...

But why creating,restoring backups and recreating tables are taking longer... I don't get it.
 
backups may use free space on slower disk? (swap/temp file)? open atop in one console and run some "creating,restoring backups and recreating tables" to check which disk loaded more.
 
Back
Top