Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27772

Performance regression with default configuration in 10.6

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6
    • None
    • Server
    • None
    • SUSE Enterprise Linux 15 SP4, openSUSE Tumbleweed

    Description

      MariaDB 10.6 has O_DIRECT value as default for innodb_flush_method instead of fsync. This lead to a performance regression in the default configuration, regardless of the filesystem used.
      To benchmark the performance, I have used sysbench-0.4.8 oltp read test on btrfs (the most affected), ext4 and xfs.

      Filesystem threads innodb_flush_method=fsync innodb_flush_method=O_DIRECT
      ext4 1 6.5s 7.0s
      ext4 16 0.97s 1.15s
      brtfs 1 6.5s 77s
      brtfs 16 0.8s 3.1s
      xfs 1 6.4s 7.2s
      xfs 16 0.8s 1.1s

      I have used a mount point in /abuild and the following configuration in /etc/my.cnf.d/sysbench_testing.cnf:

      [mysqld]
      		 
      port = 3306
      		 
      datadir=/abuild/mysql-sysbench
      		 
      socket=/abuild/mysql-sysbench/sysbench-mysql.sock
      		 
      #innodb_flush_method=fsync

      To reproduce, run the following commands:

      $ # prepare the db
      		 
      $ sysbench --test=oltp --mysql-user=root \
      		 
             --mysql-socket=/abuild/mysql-sysbench/sysbench-mysql.sock \
      		 
             --mysql-db=test --mysql-port=3306 \
      		 
             --oltp-dist-type=uniform --oltp-read-only --oltp-order-ranges=0 \
      		 
             --oltp-table-size=1000000 --oltp-range-size=1000 prepare
      		 
      $ # run the benchmark
      		 
      $ sysbench --test=oltp --mysql-user=root \
      		 
             --mysql-socket=/abuild/mysql-sysbench/sysbench-mysql.sock \
      		 
             --mysql-db=test --mysql-port=3306 \
      		 
             --oltp-dist-type=uniform --oltp-read-only --oltp-order-ranges=0 \
      		 
             --oltp-table-size=1000000 --oltp-range-size=1000 \
      		 
             --num-threads=$NTHREADS run
      		 
      $ # cleanup the db
      		 
      $ sysbench --test=oltp --mysql-user=root \
      		 
               --mysql-socket=/abuild/mysql-sysbench/sysbench-mysql.sock \
      		 
      	 --mysql-db=test --mysql-port=3306  cleanup >/dev/null 1>&2

      I am also aware that some testing by some colleague lead to similar regression in xfs (353.5476s in 10.6 vs 16.9560s in 10.5).

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31227 innodb_flush_method=O_DIRECT causes 3x regression in workload

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-19895 Support "autoset" in SET GLOBAL for AUTO_SET system variables

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-24670 avoid OOM by linux kernel co-operative memory management

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-27812 Allow innodb_log_file_size to change without server restart

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-24854 Change innodb_flush_method=O_DIRECT by default

          • Major - Major loss of function.
          • Closed

          Activity

            People

              Unassigned Unassigned
              danyspin97 Danilo Spinella
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated: