mysqld Options (full list)
Contents
This page lists all of the options for mysqld
.
See mysqld startup options for which files and groups mysqld reads for it's default options.
Compatibility options
The following options have been added to MariaDB to make it more compliant with other MariaDB and MySQL versions:
Option | Description |
---|---|
-a, | Use ANSI SQL syntax instead of MySQL syntax. This mode will also set transaction isolation level serializable. |
| Use new functionality that will exist in next version of MariaDB. This function exist to make it easier to prepare for an upgrade. For version 5.1 this functions enables the LIST and RANGE partitions functions for ndbcluster. |
| Use compatible behavior with previous main version for some functionality. For MariaDB 5.1 this means that we are using the old, MySQL 5.1 compatible, way to calculate checksums for records. If you are using -- old, CHECKSUM TABLE will always do a full table scan. |
| Use old, non-optimized alter table. |
| Use old password encryption method (needed for 4.0 and older clients). |
| Enable old-style user limits (before 5.0.3, user resources were counted per each user+host vs. per account). |
| Disable some potential unsafe optimization. For 5.2 these are: INSERT DELAYED is disabled, myisam_recover_options is set to DEFAULT (automatically recover crashed MyISAM files) and the query cache is disabled. For Aria table, disable bulk insert optimization to enable one to use maria_read_log to recover tables even if tables are deleted (good for testing recovery). |
| Disables -- new in 5.2. In 5.1 used to disable some new potentially unsafe functions. |
Replication options
Option | Description | Introduced |
---|---|---|
| Option used by mysql-test for debugging and testing of replication. | |
| Auto-increment columns are incremented by this. | |
| Offset added to Auto-increment columns. Used when auto-increment-increment != 1. | |
| Write Annotate_rows events to the binary log. | MariaDB 5.3 |
| Type of BINLOG_CHECKSUM_ALG. Include checksum for log events in the binary log. Possible values are NONE and CRC32; default is NONE. | MariaDB 5.5 |
| Causes updates to non-transactional engines using statement format to be written directly to binary log. Before using this option, make sure that there are no dependencies between transactional and non-transactional tables such as in the statement INSERT INTO t_myisam SELECT * FROM t_innodb; otherwise, slaves may diverge from the master. | |
| Tells the master it should log updates for the specified database, and exclude all others not explicitly mentioned. | |
| Tells the master that updates to the given database should not be logged to the binary log. | |
| The maximum size of a row-based binary log event in bytes. Rows will be grouped into events smaller than this size if possible. The value has to be a multiple of 256. | |
| The size of the cache to hold the SQL statements for the binary log during a transaction. If you often use big, multi-statement transactions you can increase this to get more performance. | |
| Does not have any effect without '--log-bin '. Tell the master the form of binary logging to use: either 'row' for row-based binary logging, 'statement' for statement-based binary logging, or 'mixed'. 'mixed' is statement-based binary logging except for statements where only row-based is correct: Statements that involve user-defined functions (i.e., UDFs) or the UUID() function. | |
| Run fast part of group commit in a single thread, to optimize kernel thread scheduling. See the binlog_optimize_thread_scheduling system variable for more information. | MariaDB 5.2 |
| Option used by mysql-test for debugging and testing of replication. | |
| Set the replication role. | |
| Command(s) that are executed by a slave server each time the SQL thread starts. | |
| Basename for all log files and the .pid file. This sets all log file names at once (in 'datadir') and is normally the only option you need for specifying log files. This is especially recommend to be set if you are using replication as it ensures that your log file names are not depending on your host name. Sets names for --log-bin , --log-bin-index , --relay-log , --relay-log-index , --general-log-file , --log-slow-query-log-file , --log-error-file , and --pid-file . | MariaDB 5.2 |
| Log update queries in binary format. Optional argument should be name for binary log. If not given datadir/'log-basename'-bin or 'datadir'/mysql-bin will be used (the later if --log-basename is not specified). We strongly recommend you to use either --log-basename or specify a filename to ensure that replication doesn't stop if the real hostname of the computer changes. | |
| File that holds the names for last binary log files. | |
| If equal to 0 (the default), then when --log-bin is used, creation of a stored function (or trigger) is allowed only to users having the SUPER privilege, and only if this stored function (trigger) may not break binary logging.Note that if ALL connections to this server ALWAYS use row-based binary logging, the security issues do not exist and the binary logging cannot break, so you can safely set this to 1. | |
| (deprecated) Use log-bin-trust-function-creators. | |
| Master hostname or IP address for replication. If not set, the slave thread will not be started. Note that the setting of master-host will be ignored if there exists a valid master.info file. | |
| The location and name of the file that remembers the master and where the I/O replication thread is in the master's binary logs. | |
| The password the slave thread will authenticate with when connecting to the master. If not set, an empty password is assumed. The value in master.info will take precedence if it can be read. | |
| The port the master is listening on. If not set, the compiled setting of MYSQL_PORT is assumed. If you have not tinkered with configure options, this should be 3306. The value in master.info will take precedence if it can be read. | |
| The number of tries the slave will make to connect to the master before giving up. | |
| Enable the slave to connect to the master using SSL. | |
| Master SSL CA file. Only applies if you have enabled master-ssl. | |
| Master SSL CA path. Only applies if you have enabled master-ssl. | |
| Master SSL certificate file name. Only applies if you have enabled master-ssl. | |
| Master SSL cipher. Only applies if you have enabled master-ssl. | |
| Master SSL keyfile name. Only applies if you have enabled master-ssl. | |
| The username the slave thread will use for authentication when connecting to the master. The user must have FILE privilege. If the master user is not set, user test is assumed. The value in master.info will take precedence if it can be read. | |
| Option used by mysql-test for debugging and testing of replication. | |
| If non-zero: relay log will be rotated automatically when the size exceeds this value; if zero (the default): when the size exceeds max_binlog_size. 0 excepted, the minimum value for this variable is 4096. | |
| The location and name to use for relay logs. | |
| The location and name to use for the file that keeps a list of the last relay logs. | |
| The location and name of the file that remembers where the SQL replication thread is in the relay logs. | |
| 0 = do not purge relay logs. 1 = purge them as soon as they are no more needed. | |
| Maximum space to use for all relay logs. | |
| Tells the slave to reproduce Annotate_rows events received from the master in its own binary log. This option is sensible only when used in tandem with the log-slave-updates option. | MariaDB 5.3 |
| Tells the slave thread to restrict replication to the specified database. To specify more than one database, use the directive multiple times, once for each database. Note that this will only work if you do not use cross-database queries such as UPDATE some_db.some_table SET foo='bar' while having selected a different or no database. If you need cross database updates to work, make sure you have 3.23.28 or later, and use replicate-wild-do-table=db_name.%. As of MariaDB 5.5.22 this variable is dynamic. | |
| Tells the slave thread to restrict replication to the specified table. To specify more than one table, use the directive multiple times, once for each table. This will work for cross-database updates, in contrast to replicate-do-db. As of MariaDB 5.5.22 this variable is dynamic. | |
| This option tells the slave whether to replicate events that are marked with the @@skip_replication flag. See Selectively skipping replication of binlog events for details. | MariaDB 5.5.21 |
| Tells the slave thread to not replicate to the specified database. To specify more than one database to ignore, use the directive multiple times, once for each database. This option will not work if you use cross database updates. If you need cross database updates to work, make sure you have MySQL 3.23.28 or later, and use replicate-wild-ignore-table=db_name.%. As of MariaDB 5.5.22 this variable is dynamic. | |
| Tells the slave thread to not replicate to the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates, in contrast to replicate-ignore-db. As of MariaDB 5.5.22 this variable is dynamic. | |
| Updates to a database with a different name than the original. Example: replicate-rewrite-db=master_db_name->slave_db_name. | |
| In replication, if set to 1, do not skip events having our server id. Default value is 0 (to break infinite loops in circular replication). Can't be set to 1 if --log-slave-updates is used. | |
| Tells the slave thread to restrict replication to the tables that match the specified wildcard pattern. To specify more than one table, use the directive multiple times, once for each table. This will work for cross-database updates. Example: replicate-wild-do-table=foo%.bar% will replicate only updates to tables in all databases that start with foo and whose table names start with bar. As of MariaDB 5.5.22 this variable is dynamic. | |
| Tells the slave thread to not replicate to the tables that match the given wildcard pattern. To specify more than one table to ignore, use the directive multiple times, once for each table. This will work for cross-database updates. Example: replicate-wild-ignore-table=foo%.bar% will not do updates to tables in databases that start with foo and whose table names start with bar. As of MariaDB 5.5.22 this variable is dynamic. | |
| Hostname or IP of the slave to be reported to the master during slave registration. Will appear in the output of SHOW SLAVE HOSTS. Leave unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP of the slave from the socket once the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts. | |
| Undocumented. | |
| Port for connecting to slave reported to the master during slave registration. Set it only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If not sure, leave this option unset. | |
| Undocumented. | |
| Undocumented and not used. | |
| Uniquely identifies the server instance in the community of replication partners. | |
| When this variable is set to true, events are logged into the binary log with the flag @@skip_replication set. Such events will not be replicated by slaves that run with --replicate-events-marked-for-skip set different from its default of REPLICATE . See Selectively skipping replication of binlog events for details. | MariaDB 5.5.21 |
| Modes for how replication events should be executed. Legal values are STRICT (default) and IDEMPOTENT. In IDEMPOTENT mode, replication will not stop for operations that are idempotent. In STRICT mode, replication will stop on any unexpected difference between the master and the slave. | |
| The location where the slave should put its temporary files when replicating a LOAD DATA INFILE command. | |
| Tells the slave thread to continue replication when a query event returns an error from the provided list. | |
| Use compression on master/slave protocol. | |
| Number of seconds to wait for more data from a master/slave connection before aborting the read. | |
| Number of times the slave SQL thread will retry a transaction in case it failed with a deadlock or elapsed lock wait timeout, before giving up and stopping. | |
| Option used by mysql-test for debugging and testing of replication. | |
| Non-default option to alias SYSDATE() to NOW() to make it safe for replication. Since 5.0, SYSDATE() has returned a `dynamic' value different for different invocations, even within the same statement. |
Optimizer options
Option | Description |
---|---|
| Allow big result sets by saving all temporary sets on file (solves most 'table full' errors). |
| Size of tree cache used in bulk insert optimization. Note that this is a limit per thread. |
| Number of rows to be examined for a query to be considered expensive. See the expensive_subquery_limit system variable. |
| The size of the buffer that is used for full joins. |
| Don't allow creation of heap tables bigger than this. |
| Joins that are probably going to read more than max_join_size records return an error. |
| The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored). |
| Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization |
| Controls the heuristic(s) applied during query optimization to prune less-promising partial plans from the optimizer search space. Meaning: 0 - do not apply any heuristic, thus perform exhaustive search; 1 - prune plans based on number of retrieved rows. |
| Maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to compile a query. Smaller values than the number of tables in a relation result in faster optimization, but may produce very bad query plans. If set to 0, the system will automatically pick a reasonable value; if set to MAX_TABLES+2, the optimizer will switch to the original find_best (used for testing/comparison). |
| optimizer_switch=option=val[,option=val...], where option={index_merge, index_merge_union, index_merge_sort_union, index_merge_intersection} and val={on, off, default}. |
| Allocation block size for extra blocks (after query_prealloc_size is exhausted) during query parsing and execution. |
| Persistent buffer for query parsing and execution. This is allocated at connection start and freed on disconnect. |
| Allocation block size for storing ranges during optimization. |
| Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value. |
| Maximum size in bytes of the memory available to the Rowid-merge strategy. See Non-semi-join subquery optimizations for more information. |
| Alias for read_buffer_size. Removed in MariaDB 5.5 |
| Removed in MariaDB 5.1.3; use --table_open_cache instead. |
| The number of cached table definitions. |
| The number of cached open tables. |
| If an internal in-memory temporary table exceeds this size, MySQL will automatically convert it to an on-disk MyISAM/Aria table. |
Options when debugging mysqld
Option | Description | Introduced |
---|---|---|
| Write error output on screen; don't remove the console window on windows. | |
| Write core on errors. | |
-#, | Use logging trough the DBUG library. Just using --debug will write a trafe of what mysqld is doing to /tmp/mysqld.trace. | |
| Do an assert in handler::print_error() if we get a crashed table. | |
-debug-crc-break=# | Call my_debug_put_break_here() if crc matches this number (for debug). | |
| Default debug log with flush after write. | |
| Enable the debug sync facility and optionally specify a default wait timeout in seconds. A zero value keeps the facility disabled. | |
| Set up signals usable for debugging. | |
| Enable/disable system sync calls. Syncs should only be turned off (--disable-sync-sys ) when running tests or debugging! | |
| Enable/disable system thread alarm calls. Should only be turned off (--disable-thread-alarm ) when running tests or debugging! | MariaDB 5.3 |
| If this is set different from zero, then it is the number of micro-seconds to sleep after running fsync() on the binary log to flush transactions to disk. This can thus be used to artificially increase the perceived cost of such fsync(). | MariaDB 5.3 |
Options for MyISAM
Option | Description |
---|---|
| Use concurrent insert with MyISAM. Disable with --concurrent-insert=0 . |
| Don't flush key buffers between writes for any MyISAM table. (Deprecated option, use --delay-key-write=all instead.) |
| After inserting delayed_insert_limit rows, the INSERT DELAYED handler will check if there are any SELECT statements pending. If so, it allows these to execute before continuing. |
| How long a INSERT DELAYED thread should wait for INSERT statements before terminating. |
| What size queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again. |
| Use system (external) locking (disabled by default). With this option enabled you can run myisamchk to test (not repair) tables while the server is running. Disable with --skip-external-locking . |
| Don't overwrite stale .MYD and .MYI even if no directory is specified. |
| The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 1GB on a 4GB machine that mainly runs MySQL is quite common. |
| This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in key cache. |
| The default size of key cache blocks. |
| The minimum percentage of warm blocks in key cache. |
| Log all MyISAM changes to file. |
| Syntax: myisam-recover[=option[,option...]], where option can be DEFAULT, BACKUP, BACKUP_ALL (new in 5.1.51) FORCE or QUICK. |
| Block size to be used for MyISAM index pages. |
| Default pointer size to be used for MyISAM tables. |
| Don't use the fast sort index method to created index if the temporary file would get bigger than this. |
| Can be used to restrict the total memory used for memory mmaping of myisam files |
| Specifies whether several threads should be used when repairing MyISAM tables. For values > 1, one thread is used per index. The value of 1 disables parallel repair. |
| The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. |
| Specifies how MyISAM index statistics collection code should threat NULLs. Possible values of name are "nulls_unequal" (default behavior for 4.1/5.0), "nulls_equal" (emulate 4.0 behavior), and "nulls_ignored". |
| Use memory mapping for reading and writing MyISAM tables. |
Options for InnoDB
Option | Default Value | Description |
---|---|---|
| ON | This variable controls whether or not to load the InnoDB storage engine. Possible Values are ON , OFF , or FORCE . If set to OFF , since InnoDB is the default storage engine, the server will not start unless another storage engine has been chosen with --default-storage-engine . |
| estimate | replaced by innodb_adaptive_flushing_method in 5.5 |
| FALSE | Set whether to use adaptive flushing to determine when to flush dirty buffer cache pages. |
| estimate | |
| TRUE | Used to enable or disable InnoDB adaptive hash indexes. |
| 1 | Number of partitions for use in adaptive searching. |
| 150000 | |
| 8388608 | The size (in bytes) of the memory pool InnoDB uses to store internal data structures (including data dictionary information). Possible values are between 2097152 and 4294967295 . |
| 0 | |
| 8 | Increment size (in MB) for extending the size of an auto-extended shared tablespace file. Does not affect per-table tablespace files created if you use --innodb_file_per_table=1 . |
| 1 | Sets the lock mode to use when generating auto-increment values. Possible values are 0 (traditional), 1 (consecutive), or 2 (interleaved). |
| 0 | Whether to wait for the LRU dump to be completely restored on restart before reporting back to the server on successful startup. |
| 1 | Number of instances into which the buffer pool is divided. |
| ON | |
| ON | |
| ON | |
| 0 | Whether to preallocate pages in the buffer pool on start. |
| 0 | Seconds between automatic buffer pool dumps. |
| TRUE | |
| 0 | |
| 134217728 | The size (in bytes) of the InnoDB buffer pool. Allowed values on 32-bit systems range from 1048576 to 2 32 -1 . On dedicated database servers this value can normally be safely set to 80% of physical memory, but may need to be reduced depending on your individual setup. |
| inserts | |
| 0 | |
| TRUE | Whether to ensure extra fault tolerance with checksums. |
| ON | |
| ON | |
| ON | |
| ON | |
| 0 | Set the number of threads which can commit at the same time. '0 ' permits any number of transactions to commit at the same time. |
| 500 | The number of free "tickets" a thread is given which it can use to enter and leave InnoDB without being subject to a concurrency check or queuing. |
| assert | Action to perform when corruption is detected |
| (No default value) | The path to individual data files. |
| (No default value) | The common part of the directory path to data files. |
| 0 | |
| TRUE | |
| (No default value) | |
| 0 | |
| 0 | |
| 0 | Removed in XtraDB 5.5 |
| FALSE | |
| FALSE | |
| FALSE | |
| 1 | Possible values are '0 ' (slow shutdown with a full purge and insert buffer merge before shutdown), '1 ' (fast shutdown without the purge and merge), or '2 ' (flush logs and exit hard, as if MariaDB had crashed). Only use innodb_fast_shutdown=2 in emergency or troubleshooting situations (e.g. if data is at risk of corruption and you need to shutdown as fast as possible). |
| Antelope | The file format to use for new InnoDB tables. Possible values are 'Antelope ' or 'Barracuda '. |
| Barracuda | |
| 4 | |
| FALSE | If disabled, InnoDB will create tables in the system tablespace. If enabled, InnoDB will create each new table with its own .ibd file. |
| 1 | Possible values are '0 ' (write the log buffer to the log file once per second and flush to disk but do nothing at a transaction commit), '1 ' (write the log buffer to the log file and flush to disk at each transaction commit), or '2 ' (write the log buffer to the log file at each commit but flush to disk once per second). Option '1 ' is required for full ACID compliance. |
| 3 | |
| (No default value) | Possible values are 'O_DSYNC ', or 'O_DIRECT '. This variable is only useful on Linux and Unix. The flush method is always 'async_unbuffered ' on Windows. |
| 1 | |
| 0 | Whether to permit loading corrupt tables |
| 0 | Possible values are '0 ', '1 ', '2 ', '3 ', '4 ', '5 ', or '6 '. |
| 100 | |
| 1 | |
| 9223372036854775807 | |
| 0 | Whether to permit importing of .ibd files exported with XtraBackup |
| ON | |
| 200 | On 32-bit systems, possible values range from 100 to 2 32 -1 (2 ). On 64-bit systems, possible values range from 100 to 2 64 -1 (2 ). Set this value to the approximate number if I/O operations your system can perform per second. |
| 0 | Whether to permit large index prefixes |
| 0 | |
| 50 | The amount of time and InnoDB transaction will wait (in seconds) before it gives up. Possible values range from '1 ' to '1073741824 '. |
| ON | |
| ON | |
| FALSE | |
| 512 | |
| 8388608 | The size (in bytes) of the buffer used for writing log files on disk. Possible values range from '262144 ' to '4294967295 '. |
| 5242880 | The size (in bytes) of each log file in a log group. Possible values range from '108576 ' to '4294967295 '. |
| 2 | The number of log files in the log group. Allowed values range from '2 ' to '100 '. |
| (No default value) | Directory path to the InnoDB redo log files. The default (if no value is set) is to put the log files in the data directory. |
| 4096 | Limit in bytes of the changed page bitmap files. |
| 1000000 | Limit to the number of changed page bitmap files. |
| 75 | Possible values range from '0 ' to '99 '. InnoDB will try to write pages from the buffer pool so that the percentage of dirty (unwritten) pages does not exceed this value. |
| 0 | How to delay INSERT , UPDATE , and DELETE operations. '0 ' means "no delays". |
| 1048576 | Size in bytes of the block used for merge sorting in fast index creation. |
| 1 | The number of identical copies of log groups to keep. Should be '1 '. |
| 37 | The percentage of the buffer pool to use for the old block sublist. Possible values range from '5 ' to '95 '. |
| 0 | How long (in milliseconds) an inserted block must stay in the old sublist after its first access before it can be moved to the new sublist. '0 ' means "no delay". Possible values range from '0 ' to '2 32 -1 ' (2 ). |
| 300 | The max number of .ibd files InnoDB can keep open simultaneously. Allowed values range from '10 ' to '4294967295 '. Only applies if you use multiple tablespaces. |
| FALSE | |
| 16384 | |
| 0 | |
| 0 | Whether to write transaction deadlock information to the error log. |
| 20 | Units of redo log records that will trigger a purge operation. |
| 0 | Number of threads (0 or 1) dedicated to XtraDB purge operations. |
| 0 | Enable random read-ahead optimization technique. |
| linear | |
| 56 | Allowed values range from '0 ' to '64 '. Default of '56 ' means that InnoDB must read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent. |
| 4 | The number of I/O threads for InnoDB read operations. Possible values range from '1 ' to '64 '. |
| 0 | |
| 0 | Whether to overwrite the relay log info file on crash recovery if it differs from the InnoDB record. |
| 0 | Replication thread delay (in milliseconds) on a slave server if innodb_thread_concurrency is reached. Allowed values range from '0 ' (no delay) to '4294967295 '. |
| FALSE | |
| 128 | Number of rollback segments to use within a transaction. |
| ON | |
| 10 | |
| 0 | |
| 6 | |
| 1 | |
| nulls_equal | Determines how the server treats NULL values when collecting statistics regarding index value distribution for InnoDB tables. Possible values are 'nulls_equal ', 'nulls_unequal ', or 'nulls_ignored '. |
| TRUE | Controls whether or not to update statistics during metadata statements (e.g. SHOW TABLE STATUS or SHOW INDEX ) or when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables. |
| 8 | The number of index pages to sample when calculating index distribution statistics. Possible values range from '1 ' to '2 64 -1 ' (2 ). |
| 1 | |
| FALSE | |
| FALSE | Controls whether or not to return errors instead of warnings for certain conditions. This option is analogous to strict SQL mode. |
| TRUE | Controls whether of not to use a two-phase commit in XA transactions (this causes an extra disk flush for transaction preparation). |
| 30 | The number of times a thread will wait for an InnoDB mutex to be freed before the thread is suspended. Possible values range from '0 ' to '4294967295 '. |
| ON | |
| ON | |
| ON | |
| TRUE | |
| ON | |
| 0 | How many concurrent operating system threads InnoDB will try and stay at or below. Possible values range from '0 ' (no concurrency checking) to '1000 '. |
| FALSE | |
| 10000 | |
| 0 | Whether to track changed pages for faster incremental backup with Xtrabackup. |
| ON | |
| 0 | |
| 1 | |
| 1 | |
| 1 | |
| TRUE | If TRUE , InnoDB will use the operating system's memory allocator. If 'FALSE ', InnoDB will use its own. |
| FALSE | |
| 4 | The number of I/O threads for write operations. Possible values range from '1 ' to '64 '. |
Options for PBXT
The following system variables are available for controlling the behavior of the PBXT engine. The PBX engine is no longer maintained, and is not available from MariaDB 5.5.
System variables can be set in the my.cnf file or on the mysqld command line.
Variables that can be set dynamically can be set using the SET GLOBAL
command which is sent to the server. System variables that can be set
dynamically are specified as such below.
Variables that specify a number of bytes may include a unit indication after the value. For example: 100KB, 64MB, etc. There should be no space between the number and the unit. Units are case insensitive (KB = Kb = kb). If no unit is specified then bytes is assumed. The recognized units are:
- KB (or K) - Kilobyte, 1024 bytes
- MB (or M) - Megabyte, 1024 KB
- GB (or G) - Gigabyte, 1024 MB
- TB (or T) - Terabyte, 1024 GB
- PB (or P) - Petabyte, 1024 TB
Variables which use this type of value are: pbxt_index_cache_size
,
pbxt_record_cache_size
, pbxt_log_cache_size
,
pbxt_log_file_threshold
, pbxt_checkpoint_frequency
,
pbxt_data_log_threshold
, pbxt_log_buffer_size
,
pbxt_data_file_grow_size
, and pbxt_row_file_grow_size
.
Option | Default Value | Description |
---|---|---|
| ON | |
| 0 | |
| ON |
PBXT Cache Memory Variables
Option | Default Value | Description |
---|---|---|
| 32MB | The amount of memory allocated to the index cache. The memory allocated here is used only for caching index pages (.xti files). |
| 32MB | This is the amount of memory allocated to the record cache used to cache table data. This memory is used to cache changes to the handle data (.xtd) and row index (.xtr) files. |
| 16MB | The amount of memory allocated to the transaction log cache used to cache on transaction log data (xlog-*.xt files). |
PBXT Transaction Manager Variables
Option | Default Value | Description |
---|---|---|
| 32MB | The size of a transaction log file (xlog-*.xt files) before "rollover", and a new log file is created. |
| 1MB | The size of the global transaction log buffer (the engine allocates 2 buffers of this size). Data to be written to a transaction log file is first written to the transaction log buffer. Since the buffer is flushed on transaction commit, it only makes sense to use a large transaction log buffer if you have longer running transactions, or many transaction running in parallel. |
| 24MB | The amount of data written to the transaction log before a checkpoint is performed. |
| 0 | Determines the priority of the background Sweeper thread. Possible values are '0 ' (Low), '1 ' (Normal), or '2 ' (High). The Sweeper is responsible for removing deleted records and index entries (deleted records also result from UPDATE statements). If many old deleted records accumulate search operations become slower. Therefore it may improve performance to increase the priority of the Sweeper on a machine with 4 or more cores. |
| 0 | The parameter determines how PBXT manages auto-increment values. Possible values are '0 ' (MySQL standard) or '1 ' (Previous IDs are never re-used).In the standard 'MySQL' mode it is possible that an auto-increment value is re-issued. This occurs when the maximum auto-increment value is deleted, and then MariaDB is restarted. This occurs because the next auto-increment value to be issued is determined at startup by retrieving the current maximum auto-increment value from the table. In mode 1, auto-increment values are never re-issued because PBXT automatically incrementing the table level AUTO_INCREMENT table option. The AUTO_INCREMENT table is incremented in steps of 100. Since this requires the table file to be flushed to disk, this can influence performance. |
| 1 | This variable specifies the durability of recently committed transactions. By reducing the durability, the speed of write operations can be increased. ' 0 ' - Lowest durability, the transaction log is not written or flushed on transaction commit. In this case it is possible to loose transactions if the server executable crashes.' 1 ' - Full-durability, the transaction log is written and flushed on every transaction commit.' 2 ' - Medium durabilty, the transaction log is written, but not flushed on transaction commit. In this case it is possible to loose transactions of the server machine crashes (for example, a power failer).In all cases, the transaction log is flushed at least once every second. This means that it is only every possible to loose database changes that occurred within the last second. |
| 3 | The number of transaction log files on disk before logs that are no longer required are deleted, default value is 3. The number of transaction logs on disk may exceed this number if the logs are still being read. If a transaction log has been read (i.e. the log is offline), it will be recycled for writing again, unless it must be deleted because the number of logs on disk exceeds this threshold. Recycling logs is an optimization because the writing a pre-allocated file is faster then writing to the end of a file. Note: an exception to this rule is Mac OS X. On Mac OS X old log files are not recycled because writing pre-allocated file is slower than writing to the end of file. |
| 0 | This variable determines what happens to a transaction log when it is offline. A log is offline if PBXT is no longer reading or writing to the log. There are 3 possibilities: ' 0 ' - Recycle log (default). This means the log is renamed and written again.' 1 ' - Delete log (default on Mac OS X).' 2 ' - Keep log. The logs can be used to repeat all operations that were applied to the database. |
| TRUE | This variable determines if XA (2-phase commit) support is enabled. |
PBXT Data Log Variables
PBXT stores part of the database in the data logs. This is mostly data from rows containing long VARCHAR fields or BLOB data. The data logs are managed by the "compactor" thread. When a record is deleted from a data log, the data is marked as garbage. When the total garbage in a data log reaches a certain threshold, the compactor thread compacts the data log by copying the valid data to a new data log, and deleting the old data log.
The following system variables control the way data logs are managed.
Option | Default Value | Description |
---|---|---|
| 64MB | The maximum size of a data log file. PBXT can create a maximum of 32000 data logs, which are used by all tables. So the value of this variable can be increased to increase the total amount of data that can be stored in the database. |
| 256MB | This is the size of the buffer used when writing a data log. The engine allocates one buffer per thread, but only if the thread is required to write a data log. |
| 50 | The percentage of garbage in a data log file before it is compacted. This is a value between 1 and 99. |
PBXT File Grow Variables
The handle data and row index files are grown in chunks.
Option | Default Value | Description |
---|---|---|
| 2MB | The grow size of the handle data (.xtd) files. |
| 256KB | The grow size of the row index (.xtr) files. |
Other Options
Option | Description | Introduced |
---|---|---|
| Allows use of UDFs consisting of only one symbol xxx() without corresponding xxx_init() or xxx_deinit() . That also means that one can load any function from any library, for example exit() from libc.so | |
| Creating and dropping stored procedures alters ACLs. Disable with ' '. | |
| The number of outstanding connection requests MariaDB can have. This comes into play when the main thread gets very many connection requests in a very short time. | |
-b, | Path to installation directory. All paths are usually resolved relative to this. | |
| IP address to bind to | |
| Used by mysql installation scripts. | |
| Don't ignore client side character set value sent during handshake. | |
| Set the filesystem character set. | |
-C, | Set the default character set. | |
| Directory where character sets are. | |
-r, | Chroot mysqld daemon during startup. | |
| Set the default collation. | |
| Default completion type | |
| The number of seconds the mysqld server is waiting for a connect packet before responding with 'Bad handshake'. | |
-h, | Path to the database root. | |
| The DATETIME /TIMESTAMP format (unused). | |
| The DATE format (unused). | |
| Long search depth for the two-step deadlock detection (only for Aria). See also Aria Server System Variables. | |
| Short search depth for the two-step deadlock detection (only for Aria). See also Aria Server System Variables. | |
| Long timeout for the two-step deadlock detection (in microseconds) (only for Aria). See also Aria Server System Variables. | |
| Short timeout for the two-step deadlock detection (in microseconds) (only for Aria). See also Aria Server System Variables. | |
| Set the default character set (deprecated option, use instead). | |
| Set the default collation (deprecated option, use instead). | |
| Set the default storage engine (table type) for tables. | |
| (deprecated) Use ' '. | |
| Set the default time zone. | |
| The default week format used by WEEK() functions. | |
| Type of DELAY_KEY_WRITE. | |
| Load keys for des_encrypt() and des_encrypt from given file. | |
| Precision of the result of '/' operator will be increased on that value. | |
| Deprecated option, use instead. | |
| Push supported query conditions to the storage engine. | |
| Enable/disable the event scheduler. | |
-T, | Used for debugging. Use at your own risk. | |
| If non-zero, binary logs will be purged after expire_logs_days days; possible purges happen at startup and at binary log rotation. | |
| The number of connections on 'extra-port. | |
| Extra port number to use for tcp-connections in a one-thread-per-connection manner. 0 means don't use another port | |
| Flush tables to disk between SQL commands. | |
| A dedicated thread is created to flush all tables at the given interval. | |
| List of operators for MATCH ... AGAINST ( ... IN BOOLEAN MODE). | |
| The maximum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable. | |
| The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable. | |
| Number of best matches to use for query expansion. | |
| Use stopwords from this file instead of built-in list. | |
| Enable/disable general log. | |
| Log connections and queries to given file. | |
| The maximum length of the result of function group_concat. | |
-?, | Display this help and exit. | |
| Disable initialization of builtin InnoDB plugin. | |
| Tells the server that this directory can never be a database. That means two things - firstly it is ignored by the SHOW DATABASES command and INFORMATION_SCHEMA tables. And secondly, USE, CREATE DATABASE and SELECT statements will return an error if the database from the ignored list specified. Use this option several times if you need to ignore more than one directory. To make the list empty set the void value to the option as --ignore-db-dir=. Corresponding read-only variable @@ignore_db_dirs shows the current list. New in 5.3.9. | MariaDB 5.3 |
| Command(s) that are executed for each new connection that has not the SUPER USER privilege. | |
| Read SQL commands from this file at startup. | |
| The number of seconds the server waits for activity on an interactive connection before closing it. | |
-L, | Client error messages in given language. May be given as a full path. Depricated option in 5.5. One should use --lc-messages and --lc-messages-dir instead. | |
| Enable support for large pages. Disable with ' '. | |
| Set the language used for the error messages. Default en_us. | MariaDB 5.5 |
| Directory where error message directories are (czech, english etc) | MariaDB 5.5 |
| Set the language used for the month names and the days of the week. | |
| Enable/disable LOAD DATA LOCAL INFILE (takes values 1 or 0). | |
-l, | Log connections and queries to file (deprecated option, use / instead). | |
| Error log file. | |
-0, | Log some extra information to update log. Please note that this option is deprecated; see option. | |
| Syntax: log-output[=value[,value...]], where "value" could be TABLE, FILE or NONE. | |
| Log queries that are executed without benefit of any index to the slow log if it is open. | |
| Don't log extra information to update and slow-query logs. | |
| Tells the slave to log the updates from the slave thread to the binary log. You will need to turn it on if you plan to daisy-chain the slaves. | |
| Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. | |
| Log slow queries to given log file. Defaults logging to hostname-slow.log | |
| Log only the queries that followed certain execution plan. Multiple flags allowed in a comma-separated string. [admin, filesort, filesort_on_disk, full_join, full_scan, query_cache, query_cache_miss, tmp_table, tmp_table_on_disk]. Sets log-slow-admin-command to ON | |
| Log slow queries to a table or log file. Defaults logging to table mysql.slow_log or hostname-slow.log if is used. Must be enabled to activate other slow log options. (deprecated option, use / instead) | |
| If set, only write to slow log every 'log_slow_rate_limit' query (use this to reduce output on slow query log) | |
| Log slow statements executed by slave thread to the slow log if it is open. | |
| Log all queries that have taken more than long_query_time seconds to execute to file. The argument will be treated as a decimal value with microsecond precission. | |
| Choose how verbose the messages to your slow log will be. Multiple flags allowed in a comma-separated string. [query_plan, innodb] | |
| Path to transaction coordinator log (used for transactions that affect more than one storage engine, when binary log is disabled). | |
| Size of transaction coordinator log. | |
| The update log is deprecated since version 5.0, is replaced by the binary log and this option just turns on instead. | |
-W, | Log warnings to the log file. See log_warnings for details. | |
| Log all queries that have taken more than long_query_time seconds to execute. The argument will be treated as a decimal value with microsecond precision. | |
| INSERT/DELETE/UPDATE has lower priority than selects. | |
| If set to 1, table names are stored in lowercase on disk and table names will be case-insensitive. Should be set to 2 if you are using a case-insensitive file system. | |
| Deprecated in 5.1.17 and removed in 5.5. The number of seconds the slave thread will sleep before retrying to connect to the master, in case the master goes down or the connection is lost. | |
| The maximum packet length to send to or receive from server. | |
| Can be used to restrict the total size used to cache a multi-transaction query. | |
| Binary log will be rotated automatically when the size exceeds this value. Will also apply to relay logs if max_relay_log_size is 0. The minimum value for this variable is 4096. | |
| The number of simultaneous clients allowed. | |
| If there is more than this number of interrupted connections from a host this host will be blocked from further connections. | |
| Don't start more than this number of threads to handle INSERT DELAYED statements. If set to zero, which means INSERT DELAYED is not used. | |
| Max number of errors/warnings to store for a statement. | |
| Max number of bytes in sorted records. | |
| Maximum number of prepared statements in the server. | |
| Maximum stored procedure recursion depth. (discussed with docs). | |
| Maximum number of temporary tables a client can keep open at a time. | |
| The maximum number of active connections for a single user (0 = no limit). | |
| After this many write locks, allow some read locks to run in between. | |
| Lock mysqld in memory. | |
| Don't log queries which examine less than min_examined_row_limit rows to file. | |
| Size of buffer to use when using multi-range read with range access. See Multi Range Read optimization. | |
| Deprecated in 5.1 and removed in 5.5. Use mrr_buffer_size instead. | |
| Force ndbcluster to always copy tables at alter table (should only be used if on-line alter table fails). | |
| Buffer length for TCP/IP and socket communication. | |
| Number of seconds to wait for more data from a connection before aborting the read. | |
| If a read on a communication port is interrupted, retry this many times before giving up. | |
| Number of seconds to wait for a block to be written to a connection before aborting the write. | |
| (Deprecated): Only use one thread (for debugging under Linux). Use thread-handling=no-threads instead. | |
| If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files. | |
| Pid file used by safe_mysqld. | |
| Optional semicolon-separated list of plugins to load, where each plugin is identified as name=library, where name is the plugin name and library is the plugin library in plugin_dir. | |
| Directory for plugins. | |
-P ,
| Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). | |
| Maximum time in seconds to wait for the port to become free. (Default: No wait). | |
| The size of the buffer that is allocated when preloading indexes. | |
| Limit of query profiling memory. | |
| Seconds between sending progress reports to the client for slow commands. Set to 0 to disable progress. New in 5.3 | |
| Don't cache results that are bigger than this in the query cache. | |
| Minimal size of unit in which space for results is allocated in the query cache (last unit will be trimmed after writing all result data). | |
| The memory allocated to store results from old queries in the query cache. | |
| 0 = OFF = Don't cache or retrieve results in the query cache. 1 = ON = Cache all results except SELECT SQL_NO_CACHE ... queries. 2 = DEMAND = Cache only SELECT SQL_CACHE ... queries. | |
| Invalidate queries in query cache on LOCK for write. | |
| Make all non-temporary tables read-only, with the exception of replication (slave) threads and users with the SUPER privilege. | |
| When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. | |
| Deprecated and removed in MariaDB 5.5.3; use GRANT SHOW DATABASES instead. | |
| Don't allow new user creation by the user who has no write privileges to the mysql.user table. | |
| Simulate memory shortage when compiled with the option. | |
| Disallow authentication for accounts that have old (pre-4.1) passwords. | |
| Limit LOAD DATA , SELECT ... OUTFILE , and LOAD_FILE() to files within specified directory. | |
-O ,
| Change the value of a variable. Please note that this option is deprecated; you can set variables directly with ' '. | |
| Show user and password in SHOW SLAVE HOSTS on this master. | |
| Start without grant tables. This gives all users FULL ACCESS to all tables. | |
| Don't cache host names. | |
| Deprecated option, use instead. | |
| Don't resolve hostnames. All hostnames are IP's or 'localhost'. | |
| Don't allow connection with TCP/IP. | |
| Don't allow the SHOW DATABASES command. | |
| If set, slave is not autostarted. | |
| Don't print a stack trace on failure. | |
| Don't allow symlinking of tables. Deprecated option. Use instead. | |
| Don't give threads different priorities. Deprecated option. | |
| Enable/disable slow query log. | |
| If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented. | |
| Log slow queries to given log file. Defaults logging to hostname-slow.log. Must be enabled to activate other slow log options. | |
| Socket file to use for connection. | |
| Each thread that needs to do a sort allocates a buffer of this size. | |
| The update log is deprecated since version 5.0, is replaced by the binary log and this option does nothing anymore. | |
| Syntax: sql-mode=option[,option[,option...]] where option can be one of: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, NO_UNSIGNED_SUBTRACTION. | |
| Enable SSL for connection (automatically enabled with other flags).Disable with ' '. | |
| CA file in PEM format (check OpenSSL docs, implies ). | |
| CA directory (check OpenSSL docs, implies ). | |
| X509 cert in PEM format (implies ). | |
| SSL cipher to use (implies ). | |
| X509 key in PEM format (implies ). | |
-s ,
| Enable symbolic link support. | |
| Synchronously flush binary log to disk after every #th event. Use 0 (default) to disable synchronous flushing. | |
| Sync .frm to disk on create. Enabled by default. | |
| Timeout in seconds to wait for a table level lock before returning an error. Used only if the connection has active cursors. | |
| Decision to use in heuristic recover process. Possible values are COMMIT or ROLLBACK. | |
| Using this option will cause most temporary files created to use a small set of names, rather than a unique name for each new file. | |
| Expect that server aborts with 'abort'; Don't write out server variables on 'abort'. Useful only for test scripts | |
| Ignore wrong enums values in command line arguments. Useful only for test scripts | |
| How many threads we should keep in a cache for reuse. | |
| Permits the application to give the threads system a hint for the desired number of threads that should be run at the same time. | |
| Define threads usage for handling queries: one-thread-per-connection, pool-of-threads, or no-threads. | |
| How many threads we should create to handle query requests in case of 'thread_handling=pool-of-threads'. | |
| The stack size for each thread. | |
| Specify whether to time mutexes (only InnoDB mutexes are currently supported). | |
| The TIME format (for future). | |
-t ,
| Path for temporary files. Several paths may be specified, separated by a colon (:), in this case they are used in a round-robin fashion. | |
| Default transaction isolation level. | |
| Allocation block size for transactions to be stored in binary log. | |
| Persistent buffer for transactions to be stored in binary log. | |
| 1 = YES = Don't issue an error message (warning only) if a VIEW without presence of a key of the underlying table is used in queries with a LIMIT clause for updating. 0 = NO = Prohibit update of a VIEW, which does not contain a key of the underlying table and the query uses a LIMIT clause (usually get from GUI tools). | |
| Enable symbolic link support. Deprecated option; use instead. | |
-u ,
| Run mysqld daemon as user. | |
-v ,
| Used with option for detailed help. | |
-V ,
| Output version information and exit. | |
| The number of seconds the server waits for activity on a connection before closing it. | |
| Deprecated; use instead. |
Default values
You can verify the default values for an option by doing:
mysqld --no-defaults --help --verbose
The default values are at the end of this list