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

update defaults and simplify mysqld config parameters

Details

    • 5.5.51 & 10.2.2, 10.2.2-1, 10.2.3-1, 10.2.3-2, 10.2.4-4, 10.2.4-1, 10.2.4-5, 10.0.29

    Description

      Poor MySQL has received some negative feedback over the years 1 2. As entertaining as it is watching and listening to developers struggle and whine, I think it's about time we changed some of the defaults and made the config easier to understand.

      Oracle are working on changing more defaults in 5.7 3 4 5, I think we should too.

      Something else they have done, is to radically change the simplicity of the default server config file, /etc/my.cnf:
      https://raw.githubusercontent.com/mysql/mysql-server/5.7/support-files/my-default.cnf.sh

      It's quite clear there, the sql_mode has been set, which will satisfy most developers and users looking for an ACID compliant database with little tweaking, and a rough note on increasing or decreasing RAM needed for InnoDB. MariaDB now probably needs something similar for Galera, CONNET and TokuDB.

      1. http://sql-info.de/mysql/gotchas.html
      2. http://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/
      3. http://www.tocker.ca/2015/01/23/proposal-to-change-additional-defaults-in-mysql-5-7.html
      4. http://www.tocker.ca/2015/01/14/proposal-to-change-replication-and-innodb-settings-in-mysql-5-7.html
      5. http://www.tocker.ca/2015/02/24/proposal-to-change-additional-defaults-in-mysql-5-7-february-edition.html

      Proposed new defaults:

      innodb_autoinc_lock_mode            = 2         (was: 1)
      		 
      innodb_buffer_pool_dump_at_shutdown = ON        (was: OFF)
      		 
      innodb_buffer_pool_dump_pct         = 25        (was: 100)
      		 
      innodb_buffer_pool_load_at_startup  = ON        (was: OFF)
      		 
      innodb_checksum_algorithm           = CRC32     (was: INNODB)
      		 
      innodb_file_format                  = Barracuda (was: Antelope)
      		 
      innodb_file_format_max              = Barracuda (was: Antelope)
      		 
      innodb_large_prefix                 = ON        (was: OFF)
      		 
      innodb_log_compressed_pages         = ON        (was: OFF)
      		 
      innodb_purge_threads                = 4         (was: 1)
      		 
      innodb_strict_mode                  = ON        (was: OFF)
      		 
       
      		 
      binlog_annotate_row_events          = ON        (was: OFF)
      		 
      binlog_format                       = MIXED     (was: STATEMENT)
      		 
      group_concat_max_len                = 1M        (was: 1025)
      		 
      lock_wait_timeout                   = 86400(1day)(was: 1year)
      		 
      log_slow_admin_statements           = ON        (was: OFF)
      		 
      log_slow_slave_statements           = ON        (was: OFF)
      		 
      log_warnings                        = 2         (was: 1)
      		 
      max_allowed_packet                  = 16M       (was: 4M)
      		 
      replicate_annotate_row_events       = ON        (was: OFF)
      		 
      slave_net_timeout                   = 60        (was: 3600)
      		 
      sync_binlog                         = 1         (was: 0)
      		 
      aria_recover                        = FORCE, BACKUP (was: NORMAL)
      		 
      myisam_recover_options              = FORCE, BACKUP (was: OFF)

      Attachments

        Issue Links

          includes

          Task - A task that needs to be done. MDEV-7261 Backport the default autosized value of "table_definition_cache" from MySQL 5.6.8+

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8429 Change binlog_checksum default to match MySQL 5.6.6+

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-8923 [PATCH] port innodb_buffer_pool_dump_pct + have buffer pool dump and reload set as default

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-9999 Change the default value of optimizer_use_condition_selectivity

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13247 innodb_log_compressed_pages=OFF breaks crash recovery of ROW_FORMAT=COMPRESSED tables

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-6066 Merge new defaults from 5.6 and 5.7

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7845 update date column with erroneous date does not return an error but sets to null

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-10244 Change default to use optimistic mode of in-order parallel replication

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11062 Different result for time type with optimizer_use_condition_selectivity=4

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11082 mysql_client_test: test_ps_query_cache fails with group-concat-max-len=1M

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11092 Assertion `!writer.checksum_len || writer.remains == 0' failed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11094 Blackhole table updates on slave fail when row annotation is enabled

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11095 rpl.rpl_row_mysqlbinlog test fails if row annotation enabled

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11235 LOAD DATA does not honor strict modes

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11269 In NO_ZERO_DATE mode, multiple timestamp fields with no default can't be created

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11527 Virtual columns do not get along well with NO_ZERO_DATE

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-11592 REPLACE IGNORE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11666 rpl.rpl_row_index_choice fails with higher binlog-row-event-max-size

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11685 sql_mode can't be set with non-ascii connection charset

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11691 CSV: Table marked as crashed and should be repaired

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11819 NO_ZERO_IN_DATE: Incorrect generated column value

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11822 Virtual columns: No warning on data truncation

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11908 New default configuration produces warnings about itself upon startup

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11995 ALTER TABLE proceeds despite reporting ER_TOO_LONG_KEY error

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9474 group_concat_max_len not being read from /etc/my.cnf

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-10981 Check for side effects on switching on ERROR_FOR_DIVISION_BY_ZERO

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11040 Different checksum values

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11060 sql/protocol.cc:532: void Protocol::end_statement(): Assertion `0' failed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11874 "Data too long for column" instead of "Invalid default value for" upon ALTER in strict mode

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11875 Inconsistent behavior of CREATE TABLE AS ... <string function> depending on strict mode

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11876 CONVERT produces a warning and result depends on strict mode

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11888 SELECT produces more warnings in non-strict mode than in strict mode

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11890 Effect of NO_ZERO_DATE and NO_ZERO_IN_DATE is reversed

          • Major - Major loss of function.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11891 Warnings on wrong function parameters are not quite accurate

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11892 Warning caused by NO_ZERO_DATE is different for TIMESTAMP and other date-related columns

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11893 Query execution plan depends on strict mode

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11895 NO_ZERO_DATE affects timestamp values without any warnings

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11907 Bogus 'Division by 0' warnings upon using COUNT as a window function

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13272 Document default change on parameter innodb_log_compressed_pages=ON

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 24 older comments
            elenst Elena Stepanova added a comment -

            I disagree, rather strongly. Keeping binary logs can be crucially important for various reasons. Removing them automatically by default can be a disaster, which the user will only find out when it's too late. Size does not matter here – the activity can be low, the disk can be large, and any reasonable real-life setup will monitor the disk status anyway, and the admin will make the decision about binary logs if it comes to that.

            elenst Elena Stepanova added a comment - I disagree, rather strongly. Keeping binary logs can be crucially important for various reasons. Removing them automatically by default can be a disaster, which the user will only find out when it's too late. Size does not matter here – the activity can be low, the disk can be large, and any reasonable real-life setup will monitor the disk status anyway, and the admin will make the decision about binary logs if it comes to that.
            danblack Daniel Black added a comment -

            Fair call. disk monitoring is a much better approach.

            danblack Daniel Black added a comment - Fair call. disk monitoring is a much better approach.
            wlad Vladislav Vaintroub added a comment - - edited

            I propose (discussed with elenst in the past while analyzing MDEV-11226)

            thread_pool_max_threads=65535

            Usually, there should not really be an upper bound to thread count by default, but DBAs who know their workload well, might want set it.
            had a patch for this variable myself, but since you're already at it, and chance the same places, maybe it should go into this one.

            wlad Vladislav Vaintroub added a comment - - edited I propose (discussed with elenst in the past while analyzing MDEV-11226 ) thread_pool_max_threads=65535 Usually, there should not really be an upper bound to thread count by default, but DBAs who know their workload well, might want set it. had a patch for this variable myself, but since you're already at it, and chance the same places, maybe it should go into this one.
            elenst Elena Stepanova added a comment - - edited

            nirbhay_c,

            As requested, I've looked at bb-10.2-mdev7635, diff between fd0479ce592e0b7c13d67b5deda62e9090956309 and the current e2d94b684a070b94519cd45e923a3c41267f66c4.

            There are a few suites that haven't been fixed. I've noticed

            • mroonga/* (most important, since it's a part of the default set)
            • jp (also runs in buildbot on fulltest)
            • engines/*

            I expect you're going to adjust them as well.

            Also I have some doubts about innodb_storedproc* tests. Unlike in other tests, here you've kept the strict mode without ignore close, so that it didn't allow data to be loaded, which naturally further caused differences in result sets which were re-recorded. Unless you did it on purpose, maybe it makes sense to restore the test logic.

            Apart from those, it looks mostly fine. I have some technical suggestions/fixes, all of which are pushed into bb-10.2-mdev7635.elenst, see
            https://github.com/MariaDB/server/commits/bb-10.2-mdev7635.elenst

            I intentionally created very itemized commits, so that it was clear which is doing what, and you could pick them up separately (they are mostly independent, except for maybe one or two).
            You only need those that start with "Additional test fixes for MDEV-7635 #N". There are 13 of them now.
            You don't need two others, one of which is to exclude mroonga from defaults (you don't need to merge it as it has to be fixed anyway), and one to disable a test which currently fails on 10.2 (you don't need to merge it either, it's already disabled in 10.2 and will be re-enabled when the problem is fixed).

            Not related to tests directly, I still have some concerns about sync-binlog (see MDEV-11900), and about the new combination of slow-log options which ends up with a warning (see MDEV-11908), I don't think it makes any sense.

            elenst Elena Stepanova added a comment - - edited nirbhay_c , As requested, I've looked at bb-10.2-mdev7635 , diff between fd0479ce592e0b7c13d67b5deda62e9090956309 and the current e2d94b684a070b94519cd45e923a3c41267f66c4. There are a few suites that haven't been fixed. I've noticed mroonga/* (most important, since it's a part of the default set) jp (also runs in buildbot on fulltest) engines/* I expect you're going to adjust them as well. Also I have some doubts about innodb_storedproc* tests. Unlike in other tests, here you've kept the strict mode without ignore close, so that it didn't allow data to be loaded, which naturally further caused differences in result sets which were re-recorded. Unless you did it on purpose, maybe it makes sense to restore the test logic. Apart from those, it looks mostly fine. I have some technical suggestions/fixes, all of which are pushed into bb-10.2-mdev7635.elenst, see https://github.com/MariaDB/server/commits/bb-10.2-mdev7635.elenst I intentionally created very itemized commits, so that it was clear which is doing what, and you could pick them up separately (they are mostly independent, except for maybe one or two). You only need those that start with "Additional test fixes for MDEV-7635 #N". There are 13 of them now. You don't need two others, one of which is to exclude mroonga from defaults (you don't need to merge it as it has to be fixed anyway), and one to disable a test which currently fails on 10.2 (you don't need to merge it either, it's already disabled in 10.2 and will be re-enabled when the problem is fixed). Not related to tests directly, I still have some concerns about sync-binlog (see MDEV-11900 ), and about the new combination of slow-log options which ends up with a warning (see MDEV-11908 ), I don't think it makes any sense.
            nirbhay_c Nirbhay Choubey (Inactive) added a comment -

            thread_pool_max_threads=65535
            https://github.com/MariaDB/server/commit/def258061b884df91624562c27c818f9d1fa2d5c

            nirbhay_c Nirbhay Choubey (Inactive) added a comment - thread_pool_max_threads=65535 https://github.com/MariaDB/server/commit/def258061b884df91624562c27c818f9d1fa2d5c

            People

              nirbhay_c Nirbhay Choubey (Inactive)
              mathnode Richard Bensley
              Votes:
              3 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.