There is a Synology DS1621xs+ (equiped with NVMe SSD cache and 24 GB RAM). OS: Synology DSM 7.1 File System: btrfs Docker version: 20.10.3
There is a MariaDB10 server running on Docker on this machine.
An identical database is running on the 'native' Synology MariaDB10 package on the same machine. (Dumped and copied from the Docker instance.)
Queries on the database of the 'native' MariaDB10 package run much faster than on the docker instance. Difference is at least 2.5x, but on some queries it is 20x.
What might be the reason of this huge perfomance difference, why are the queries slower on Docker than on the Synology Package version of MariaDB?
Dockerinfo:
Client:
Context: default
Debug Mode: false
Server:
Containers: 10
Running: 7
Paused: 0
Stopped: 3
Images: 36
Server Version: 20.10.3
Storage Driver: btrfs
Build Version: Btrfs v4.0
Library Version: 101
Logging Driver: db
Cgroup Driver: cgroupfs
Cgroup Version: 1
Plugins:
Volume: local
Network: bridge host ipvlan macvlan null overlay
Log: awslogs db fluentd gcplogs gelf journald json-file local logentries splunk syslog
Swarm: inactive
Runtimes: io.containerd.runc.v2 io.containerd.runtime.v1.linux runc
Default Runtime: runc
Init Binary: docker-init
containerd version: 3fa00912415f3e9c6f82dd72119179d599efd13b
runc version: 31cc25f16f5eba4d0f53e35374532873744f4b31
init version: ed96d00 (expected: de40ad0)
Security Options:
apparmor
Kernel Version: 4.4.180+
Operating System: (containerized)
OSType: linux
Architecture: x86_64
CPUs: 8
Total Memory: 23.25GiB
Name: NAS
ID: ZLGI:KUWA:HOGC:3J6W:B6NJ:CZLJ:ZQP2:HAG5:3DP6:OEPX:5WRW:IHAJ
Docker Root Dir: /volume2/@docker
Debug Mode: false
Registry: https://index.docker.io/v1/
Labels:
Experimental: false
Insecure Registries:
127.0.0.0/8
Live Restore Enabled: false
WARNING: No kernel memory TCP limit support
WARNING: No cpu cfs quota support
WARNING: No cpu cfs period support
WARNING: No blkio weight support
WARNING: No blkio weight_device support
WARNING: No blkio throttle.read_bps_device support
WARNING: No blkio throttle.write_bps_device support
WARNING: No blkio throttle.read_iops_device support
WARNING: No blkio throttle.write_iops_device support
`
MariaDB container log:
2022-11-19T08:55:19.209130033Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Buffer pool(s) load completed at 221119 9:55:19
2022-11-19T08:55:19.174115217Z stdout Version: '10.6.5-MariaDB-1:10.6.5+maria~focal' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
2022-11-19T08:55:19.174011946Z stdout 2022-11-19 9:55:19 0 [Note] mysqld: ready for connections.
2022-11-19T08:55:19.167072919Z stdout 2022-11-19 9:55:19 0 [Warning] 'proxies_priv' entry '@% root@mariadb-copy' ignored in --skip-name-resolve mode.
2022-11-19T08:55:19.163349561Z stdout 2022-11-19 9:55:19 0 [Note] Server socket created on IP: '::'.
2022-11-19T08:55:19.163211778Z stdout 2022-11-19 9:55:19 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-11-19T08:55:19.161820288Z stdout 2022-11-19 9:55:19 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2022-11-19T08:55:19.149901116Z stdout 2022-11-19 9:55:19 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-19T08:55:19.149840357Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-11-19T08:55:19.149391807Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: 10.6.5 started; log sequence number 3282096825; transaction id 7204
2022-11-19T08:55:19.148296829Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-11-19T08:55:19.148223400Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-11-19T08:55:19.148147151Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-11-19T08:55:19.147243337Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: 128 rollback segments are active.
2022-11-19T08:55:19.086673922Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Completed initialization of buffer pool
2022-11-19T08:55:19.085986215Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2022-11-19T08:55:19.085469623Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Using Linux native AIO
2022-11-19T08:55:19.068551244Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-19T08:55:19.068528556Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Number of pools: 1
2022-11-19T08:55:19.068487467Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Using transactional memory
2022-11-19T08:55:19.068374374Z stdout 2022-11-19 9:55:19 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-11-19T08:55:19.049836283Z stdout 2022-11-19 9:55:19 0 [Note] mysqld (server 10.6.5-MariaDB-1:10.6.5+maria~focal) starting as process 1 ...
2022-11-19T08:55:18.900158637Z stdout 2022-11-19 09:55:18+01:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.3.14+maria~bionic started.
2022-11-19T08:55:18.892594609Z stdout 2022-11-19 09:55:18+01:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-11-19T08:55:18.738312960Z stdout 2022-11-19 09:55:18+01:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.3.14+maria~bionic started.
MariaDB changed innodb_flush_method=O_DIRECT in 10.6 to aid the high end performance. As such MariaDB-10.6 is much more reliant on a innodb_buffer_pool_size and innodb_log_file_size to gain read/write performance.
As a work around, test 10.6 with innodb_flush_method=fsync. btrfs is significantly newer than today than in kernel 4.4.0 so maybe its O_DIRECT paths weren't as performing in 4.4.0.
It could also be MDEV-29967 broken linear read ahead that we're working hard to fix before the next release.
It might also be the 10.6 statistics are different try a recalculation using ANALYZE TABLES.
There's a couple of btrfs links/suggestions in this link.
Or it could be something totally different requiring: