Performance/Resource Utilization issue on Windows

[Ryntak94]

Windows version / build number: Microsoft Windows [Version 10.0.22631.5189]

WSL os and version (default from running wsl --instal Ubuntu):
Distributor ID: Ubuntu
Description: Ubuntu 24.04.2 LTS
Release: 24.04
Codename: noble

.wslconfig
[wsl2]
memory=27GB
processors=16

Steps required to reproduce:

Ensure that you have npm installed (I'm using node 18, anything more recent will likely work. nvm can solve this problem for you if you have issues.
Ensure you have make installed sudo apt install make
Ensure you have docker desktop installed and configured to work with wsl.

Clone this repo into the wsl file system. (I cd ~ before cloning it)

make start
This will
Install node_modules
Start the docker container
Create the dump.sql file (2GB, but you can make this smaller if you'd like. It just needs to be large enough that it takes time to import into mariadb)
Start importing the dump file into mariadb by copying it into the container first, then importing it from the file in the container.

Once you see the line docker compose exec -T mariadb mariadb --user root --max-allowed-packet=1G --quick -e "source /tmp/dump.sql" open a separate instance of wsl and navigate to the repo and run make stats. This will show you the the cpu usage by the container.

You can also use make exec_mariadb_container and once inside top -b -n 1 to see the cpu and mem usage.

Comparing between a mac and wsl:
Mac:
make stats shows 40% minimum cpu usage by the container in this example. It also shows 2.62@ MEM%
make exec_mariadb_container >> top -b -n 1 shows 30-50% %CPU and 1.3 %MEM by the mysql user/mariadbd command
make exec_mariadb_container >> top -b -n 1 shows 12.5 %CPU and .1 %MEM by the root user/mariadb command

WSL:
make stats shows 6% minimum cpu usage, 20% max cpu usage by the container in this example. It also shows .6% MEM %
make exec_mariadb_container >> top -b -n 1 shows 13-20 %CPU and .6 %MEM by the mysql user/mariadbd command
make exec_mariadb_container >> top -b -n 1 shows 0 %CPU and 0 %MEM by the root user/mariadb command

In my work scenario, it's a much more complicated dump file and the Mac will hit 250% MEM% from make stats, here, where as the WSL is the exact same.

Please advise if there is anything I can do to make the WSL performance better.
I have tried the same thing on Windows without WSL and a brand new Docker Desktop installation without WSL and also experienced the same issue

Thank you!

[grooverdan]

That's a very detailed test case and I'm sure I could run it.

Before I do, I don't exactly follow which is the Performance/Resource Utilization issue that you are resolving. Mac will create/use a Linux VM for its containers. My experience of ighting/tolerating WSL compatibility issues in which it appears to expose linux syscalls that get translated into Windows core interfaces. They are rather different environments and I don't see how a percentage cpu or MEM on either maps particularly strongly to each other. The time to complete the load might have some comparison value but it will also depend on the filesystem.

On general performance improvement move to a newer MariaDB version, maybe even 11.4, and add a --innodb-buffer-pool-size=2G and --innodb-log-file-size=2G to the command of your compose file to explicitly allocate those expanded sizes to MariaDB.

By tried on Windows without WSL you mean running a native Windows MariaDB instance? Perhaps some measure from Windows Performance Toolkit could help.

[Ryntak94]

@grooverdan

Thanks for the question.

By "Tried on Windows without WSL" I mean that I reinstalled docker desktop and didn't configure it for WSL2, instead letting it use Hyper-V.

For work, what I'm trying to do is get our dbrestore command to be faster for windows users. In all four cases (Mac, Native Ubuntu, WSL, Windows) I've used docker. However, it takes about 10x the time on Windows or WSL as it takes on native Ubuntu or on Mac. This translates to about 7 minutes on Mac/Ubuntu and ~65 minutes on Windows and WSL.

This afternoon I looked at the output of vmstat 1 on WSL and Mac and saw that we were getting 92%+ idle times on WSL and 80%-93% idle times on Mac. However, the bo (block write) was 10x larger on Mac than on WSL.

[Ryntak94]

I also realized that by copying the dump file into the container, I wasn't actually putting it in the docker-desktop distro. I was instead actually moving it into the windows file system, as the file system of the container exists as a vhdx file inside of the windows file system. So I've also tried piping the contents of the dump file into mariadb server on both Mac and WSL with similar results.

[Ryntak94]

On general performance improvement move to a newer MariaDB version, maybe even 11.4, and add a --innodb-buffer-pool-size=2G and --innodb-log-file-size=2G to the command of your compose file to explicitly allocate those expanded sizes to MariaDB.

I can give the config changes a shot, but moving to a new MariaDB version isn't something that I can really pull the trigger on, unfortunately. I'll look into it with the team, though.

[grooverdan]

Alternative:

A faster restore mechanism is to use 7a5cf95 which I added just before 10.6.16. Follow steps in the .test/run.sh test case.

This enables a mariadb-backup that was taken, prepared, and tared up (optionally compressed) to be placed in a startup volume mount on /docker-entrypoint-initdb.d to directly store your data rather than a SQL dump.

WSL/Hyper V of Linux containers:

The difference on WSL vs Ubuntu could be examined possibly some timing results between procmon on Windows and strace on linux and examining where the time is. Examining a flame graph - windows - https://learn.microsoft.com/en-gb/windows-hardware/test/wpt/graphs#flame_graphs vs Linux on the same load may indicate the parts of MariaDB that are impacted by the WSL environment. 10x slower is quite substantial. Unfortunately I don't have time anytime soon. I'll ask around but if you do come up with results, please post and issue on https://jira.mariadb.org/.

[Ryntak94]

After a lot more digging, I think the issue may actually just have to do with read/write performance given wsl's fs system. I think this would be solved by having a second drive that I could mount and format to ext4, but unfortunately our work laptops don't have a seocnd nvme slot.

Alternative:

A faster restore mechanism is to use 7a5cf95 which I added just before 10.6.16. Follow steps in the .test/run.sh test case.

This enables a mariadb-backup that was taken, prepared, and tared up (optionally compressed) to be placed in a startup volume mount on /docker-entrypoint-initdb.d to directly store your data rather than a SQL dump.

I think this may be exactly what we need! Thank you, will look into this in the morning and see if we can't get one of these!

[grooverdan]

I think the issue may actually just have to do with read/write performance given wsl's fs system

Slightly surprising. A volume space of /var/lib/mysql I'd expected to have a pass though effect rather than the overlay fs like behaviour of the rest of the running image.

Looking forward to the results of testing and the mariadb-backup based initialisation mechanism (noting it superceeds most environment variables around initialisation).

[Ryntak94]

The difference on WSL vs Ubuntu could be examined possibly some timing results between procmon on Windows and strace on linux and examining where the time is. Examining a flame graph - windows - https://learn.microsoft.com/en-gb/windows-hardware/test/wpt/graphs#flame_graphs vs Linux on the same load may indicate the parts of MariaDB that are impacted by the WSL environment. 10x slower is quite substantial. Unfortunately I don't have time anytime soon. I'll ask around but if you do come up with results, please post and issue on https://jira.mariadb.org/.

So I got a bit confused here, this is generally out of my wheelhouse. procmon vs strace, why wouldn't I use strace in wsl if I'm using strace in Ubunt? Or are you suggesting to use strace inside the containers and then strace in wsl and Ubuntu and then procmon outside wsl on windows?

[Ryntak94]

I managed to try with the mariadb-backup tool but we've run into an issue that we backup from the staging database and need direct access to the file system, and we want this to run on a schedule.

Do you have any advice for this? It's the last piece of the puzzle.

[grooverdan]

I managed to try with the mariadb-backup tool but we've run into an issue that we backup from the staging database and need direct access to the file system, and we want this to run on a schedule.

Do you have any advice for this? It's the last piece of the puzzle.

Assuming a running instance:

$ podman run --name ma -d --env MARIADB_ROOT_PASSWORD=pass --rm  mariadb:10.6.16
82dbd391648384615a0b0281f268d7fe8afb71f4fc9d3fe3019f32df1216aa58

Execute within the same container the backup and stream it to the outside:

$ podman exec ma  mariabackup --backup --stream=mbstream  --user=root --password=pass > d/backup.mb

New volume container to prepare the backup:

$ podman volume create backup

$ podman run --name ma_restore  -v ./d:/backup -ti --entrypoint /bin/bash -v backup:/var/lib/mysql   --workdir /var/lib/mysql --rm  mariadb:10.6.16 
root@bbd56feccc90:/var/lib/mysql# mkdir r; cd r
root@bbd56feccc90:/var/lib/mysql/r# mbstream -x < /backup/backup.mb 
root@bbd56feccc90:/var/lib/mysql/r# ls
aria_log.00000001  aria_log_control  backup-my.cnf  ib_logfile0  ibdata1  mysql  performance_schema  sys  xtrabackup_checkpoints  xtrabackup_info
root@bbd56feccc90:/var/lib/mysql/r# mariadb-backup --prepare --target-dir .
mariadb-backup based on MariaDB server 10.6.16-MariaDB debian-linux-gnu (x86_64)
[00] 2025-05-14 04:36:12 cd to /var/lib/mysql/r/
[00] 2025-05-14 04:36:12 open files limit requested 0, set to 524288
[00] 2025-05-14 04:36:12 This target seems to be not prepared yet.
[00] 2025-05-14 04:36:12 mariabackup: using the following InnoDB configuration for recovery:
[00] 2025-05-14 04:36:12 innodb_data_home_dir = .
[00] 2025-05-14 04:36:12 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2025-05-14 04:36:12 innodb_log_group_home_dir = .
[00] 2025-05-14 04:36:12 InnoDB: Using Linux native AIO
[00] 2025-05-14 04:36:12 Starting InnoDB instance for recovery.
[00] 2025-05-14 04:36:12 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2025-05-14  4:36:12 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2025-05-14  4:36:12 0 [Note] InnoDB: Number of pools: 1
2025-05-14  4:36:12 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2025-05-14  4:36:12 0 [Note] InnoDB: Using Linux native AIO
2025-05-14  4:36:12 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
2025-05-14  4:36:12 0 [Note] InnoDB: Completed initialization of buffer pool
2025-05-14  4:36:12 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=42664,42664
[00] 2025-05-14 04:36:12 Last binlog file , position 0
[00] 2025-05-14 04:36:12 completed OK!
root@bbd56feccc90:/var/lib/mysql/r# tar -Jcf ../backup.tar.xz .
root@bbd56feccc90:/var/lib/mysql/r# cd ..
root@bbd56feccc90:/var/lib/mysql# rm -rf r/