The Magic Behind the innodb_dedicated_server=ON

Two days ago I noted this tweet by Mark Callaghan:

"This month I learned that innodb_dedicated_server is great. Don't need to set many options beyond it. I have been doing it wrong."

and decided to check what this great setting (that I also never cared to use with MySQL 8) is really doing. I could, surely, just re-read the fine manual (and I did that), but what can be better than just trying it?

Yesterday I had some free time in the morning for testing random things and was sitting near my good old Fedora (yes, I know, time to use 32 if not 33...) test box, with 8G of RAM and 4 cores:

[openxs@fc29 8.0]$ pt-summary

# Percona Toolkit System Summary Report ######################

        Date | 2020-06-20 05:27:35 UTC (local TZ: EEST +0300)

    Hostname | fc29

      Uptime | 39 min,  1 user,  load average: 0.29, 0.37, 0.39

    Platform | Linux

     Release | Fedora release 29 (Twenty Nine)

      Kernel | 5.3.11-100.fc29.x86_64

Architecture | CPU = 64-bit, OS = 64-bit

   Threading | NPTL 2.28

    Compiler | GNU CC version 8.3.1 20190223 (Red Hat 8.3.1-2).

     SELinux | Enforcing

 Virtualized | No virtualization detected

# Processor ##################################################

  Processors | physical = 1, cores = 4, virtual = 4, hyperthreading = no

      Speeds | 1x1999.777, 1x1999.816, 1x2001.216, 1x2030.647

      Models | 4xIntel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz

      Caches | 4x2048 KB

# Memory #####################################################

       Total | 7.7G

        Free | 247.4M

        Used | physical = 2.1G, swap allocated = 0.0, swap used = 0.0, virtual = 2.1G

      Shared | 198.6M

     Buffers | 5.4G

      Caches | 5.1G

       Dirty | 352 kB

     UsedRSS | 4.3G

  Swappiness | 60

 DirtyPolicy | 20, 10

 DirtyStatus | 0, 0

...

and it had MySQL 8.0.18 there (also time to rebuild from recent GitHub source, I know...). So, I tried to compare settings with --no-defaults:

[openxs@fc29 8.0]$ ./bin/mysqld_safe --no-defaults --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data &

[1] 3764

[openxs@fc29 8.0]$ 2020-06-20T05:21:24.289203Z mysqld_safe Logging to '/home/openxs/dbs/8.0/data/fc29.err'.

2020-06-20T05:21:24.378630Z mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/8.0/data

[openxs@fc29 8.0]$ ./bin/mysql -uroot -e'select version()';

+-----------+

| version() |

+-----------+

| 8.0.18    |

+-----------+

[openxs@fc29 8.0]$ ./bin/mysql -uroot -e'show global variables' >/tmp/default.txt

[openxs@fc29 8.0]$ ./bin/mysql -uroot -e'shutdown';

[openxs@fc29 8.0]$ 2020-06-20T05:22:35.220091Z mysqld_safe mysqld from pid file /home/openxs/dbs/8.0/data/fc29.pid ended

[1]+  Done                    ./bin/mysqld_safe --no-defaults --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data

[openxs@fc29 8.0]$ cat /tmp/default.txt | grep innodb_dedicated

innodb_dedicated_server OFF

Then I've added the magic option and repeated the same steps:

[openxs@fc29 8.0]$ ./bin/mysqld_safe --no-defaults --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --innodb_dedicated_server=ON &

[1] 3993

[openxs@fc29 8.0]$ 2020-06-20T05:23:10.824899Z mysqld_safe Logging to '/home/openxs/dbs/8.0/data/fc29.err'.

2020-06-20T05:23:10.870749Z mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/8.0/data

[openxs@fc29 8.0]$ tail -f data/fc29.err 

2020-06-20T05:23:10.870749Z mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/8.0/data

2020-06-20T05:23:10.888077Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)

2020-06-20T05:23:10.888097Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)

2020-06-20T05:23:11.168384Z 0 [System] [MY-010116] [Server] /home/openxs/dbs/8.0/bin/mysqld (mysqld 8.0.18) starting as process 4117

 100 200 300 400 500

 100 200 300 400 500

 100 200 300 400 500

 100 200 300 400 500

 100 200 300 400 500

 100 200 300 400 500

2020-06-20T05:23:59.904782Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2020-06-20T05:24:00.145885Z 0 [System] [MY-010931] [Server] /home/openxs/dbs/8.0/bin/mysqld: ready for connections. Version: '8.0.18'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.

2020-06-20T05:24:00.218482Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

^C

[openxs@fc29 8.0]$ ./bin/mysql -uroot -e'show global variables' >/tmp/dedicated.txt

So, what was the real difference? Here it is, thanks diff:

[openxs@fc29 8.0]$ diff -u /tmp/default.txt /tmp/dedicated.txt 

--- /tmp/default.txt    2020-06-20 08:22:25.674368791 +0300

+++ /tmp/dedicated.txt  2020-06-20 08:24:40.938561950 +0300

@@ -130,11 +130,11 @@

 innodb_buffer_pool_dump_pct    25

 innodb_buffer_pool_filename    ib_buffer_pool

 innodb_buffer_pool_in_core_file    ON

-innodb_buffer_pool_instances   1

+innodb_buffer_pool_instances   8

 innodb_buffer_pool_load_abort  OFF

 innodb_buffer_pool_load_at_startup ON

 innodb_buffer_pool_load_now    OFF

-innodb_buffer_pool_size    134217728

+innodb_buffer_pool_size    6442450944

 innodb_change_buffer_max_size  25

 innodb_change_buffering    all

 innodb_checksum_algorithm  crc32

@@ -147,7 +147,7 @@

 innodb_data_file_path  ibdata1:12M:autoextend

 innodb_data_home_dir   

 innodb_deadlock_detect ON

-innodb_dedicated_server    OFF

+innodb_dedicated_server    ON

 innodb_default_row_format  dynamic

 innodb_directories 

 innodb_disable_sort_file_cache OFF

@@ -157,7 +157,7 @@

 innodb_fill_factor 100

 innodb_flush_log_at_timeout    1

 innodb_flush_log_at_trx_commit 1

-innodb_flush_method    fsync

+innodb_flush_method    O_DIRECT_NO_FSYNC

 innodb_flush_neighbors 0

 innodb_flush_sync  ON

 innodb_flushing_avg_loops  30

@@ -183,8 +183,8 @@

 innodb_log_buffer_size 16777216

 innodb_log_checksums   ON

 innodb_log_compressed_pages    ON

-innodb_log_file_size   50331648

-innodb_log_files_in_group  2

+innodb_log_file_size   536870912

+innodb_log_files_in_group  6

 innodb_log_group_home_dir  ./

 innodb_log_spin_cpu_abs_lwm    80

 innodb_log_spin_cpu_pct_hwm    50

@@ -205,7 +205,7 @@

 innodb_online_alter_log_max_size   134217728

 innodb_open_files  431

 innodb_optimize_fulltext_only  OFF

-innodb_page_cleaners   1

+innodb_page_cleaners   4

 innodb_page_size   16384

 innodb_parallel_read_threads   4

 innodb_print_all_deadlocks OFF

[openxs@fc29 8.0]$ 

Actually, fine manual page that Mark Callaghan pointed to, explain most of these changes:

"When innodb_dedicated_server is enabled, InnoDB automatically configures the following variables:

• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_log_files_in_group (as of MySQL 8.0.14)
• innodb_flush_method

Only consider enabling innodb_dedicated_server if the MySQL instance resides on a dedicated server where it can use all available system resources."

It described the settings in details. For systems with >4G of RAM (like mine) 75% of RAM is allocated for innodb_buffer_poll_size. Then if innodb_buffer_pool_size <= 8G, innodb_log_file_size is set to 512M. Number of redo log files in group is explained properly:

"The number of log files is configured according to the automatically configured buffer pool size (in gigabytes)."

That's why we see 6G for innodb_buffer_pool_size (75% of almost 8G) and 6 for innodb_log_files_in_group. I am not sure if that many (or more than 3) log files make any practical sense or help with anything, but probably it's a way to make sure total capacity of redo log is "large enough", while each log is not larger than 2048M.

As for setting innodb_flush_method, the value of O_DIRECT_NO_FSYNC is questionable, because of a known bug, Bug #94912 - "O_DIRECT_NO_FSYNC possible write hole". The manual is clear about this case:

"Data loss is possible if redo log files and data files reside on different storage devices, and a crash occurs before data file writes are flushed from a device cache that is not battery-backed. If you use or intend to use different storage devices for redo log files and data files, and your data files reside on a device with a cache that is not battery-backed, use O_DIRECT instead."

But I am not yet sure if the implementation is smart enough NOT to make this setting when redo logs are stored on different filesystem. Something to check later.

You may ask why setting innodb_page_cleaners to 4 is  NOT documented? Well, it is the default setting and the reason why it was adjusted to 1 in my initial configuration is explained in the manual here:

"If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances. "

I had one buffer pool instance by default, as default innodb_buffer_pool_size was < 1G. As a result of this magic tuning it's 6G, so 8 instances are used (and we can use 4 innodb_page_cleaners). Again, a questionable (but default) setting.

The default value of innodb_page_cleaners, 4, is not the best fit for all. As recent Percona blog stated:

"With 4 cleaner threads, InnoDB is able to flush at a very high rate. Actually, unless you are using Percona Server for MySQL with the parallel doublewrite buffers feature, very likely the doublewrite buffer will bottleneck before the cleaner threads."

After reading the manual pages related to the feature, I found it well documented. The only formal problem was reported as Bug #99945 - "Missing details in the description of innodb_dedicated_server".

Sometimes we have to clarify the details of some MySQL features, to find out there is no real magic behind them.

To summarize:

• I would not say that innodb_dedicated_server setting replaces setting of really many options. Just 4 by the manual and 6 in my corner case that compared to --no-defaults settings.
• Some settings are obvious and good (use 75% of RAM for InnoDB buffer pool if RAM is large enough, use reasonably large redo log space, others are somewhat questionable. The company I work for moves to the opposite direction with them for version 10.5.
• The feature is well documented.
• Personally I do not think that doing one setting instead of 4-6, for key InnoDB variables, is such a big deal for any use cases besides some artificial benchmarks.