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

update defaults and simplify mysqld config parameters

    XMLWordPrintable

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

            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.