Properties: 
Applicable To InnoDB Server Startup Option --innodb_log_file_size=<value> Scope Global Dynamic Yes Possible Values Integer: Range: 1M - 4G
<1M will be adjusted to 1M Default Value 5M Category Performance, Maintenance
Description:

This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB.

InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? As stated in MySQL manual "The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.", these logs help InnoDB in running more






  [Read more...]

Properties:

Applicable To MyISAM Server Startup Option --bulk_insert_buffer_size=<value> Scope Both Dynamic Yes Possible Values Integer:

Range: 0 - 4294967295 (4G)

Default Value 8388608 (8M) Category Performance

Description:

This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like LOAD DATA INFILE..., INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., (...)

Bulk inserts are often used to minimize disk writes etc and are very common in applications inserting lots of data. I often use bulk inserting using the following technique: (pseudocode)

• CREATE TEMPORARY TABLE tmp_table LIKE

  [Read more...]

Properties:

Applicable To MySQL Server Server Startup Option --log-queries-not-using-indexes Scope Global Dynamic Yes Possible Values Boolean Default False Category Performance, Monitoring, Best Practices

Description:

If you have slow query logs enabled (with --log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.

Usage:

While designing a MySQL oriented application, I generally design my schema first without introducing any index (only exception being the PRIMARY ones). Get the application ready. Next enable

  [Read more...]

Properties:

Applicable To MySQL Server Introduced In 5.1.5 Server Startup Option --binlog-format=<value> Scope Both Dynamic Yes Possible Values enum(ROW, STATEMENT, MIXED) Default < 5.1.12: STATEMENT
>= 5.1.12: MIXED Categories Replication, Performance

Description:

Starting with 5.1.5, MySQL has implemented ROW based replication format which logs the physical changes to individual row changes. This looks like the most optimal way to many users. But it is not always, rather not optimal most of the times. E.g. consider a statement that does bulk insert of thousands of rows. In ROW based logging,

  [Read more...]

Properties:

Applicable To InnoDB on Unix like OS Server Startup Option --innodb_flush_method=<value> Scope Global Dynamic No Possible Values enum(O_DSYNC, O_DIRECT, <<none>> ) Default Value <<none>> Category Performance

Description:

This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance. By default, InnoDB uses fsync() (without O_DSYNC) to flush both log and data files.

Setting this variable to O_DIRECT will result in InnoDB using O_DIRECT while

  [Read more...]

Properties:

Applicable To MySQL/Linux Server Startup Option --large-pages Scope Global Dynamic No way Possible Values True|False flag Default Value False Category Performance, Administration

Description:

This option, currently available only for Linux, if set enables the usage of large pages by MySQL. Many operating systems/system architectures support optional memory pages bigger than the default size (4 KB). Usually the large page size is 2 MB, this can be checked in the large_page_size variable in MySQL.

For applications that do a lot of memory accesses, as MySQL can, reduced TLB (Translation Lookaside Buffer) misses lead to better

  [Read more...]

Well, the first summary of last 10 variable day outs.

Day Outs:

• Day Out #1: delay_key_write
• Day Out #2: key_buffer_size
• Day Out #3: max_connect_errors
• Day Out #4: innodb_buffer_pool_size
• Day Out #5: innodb_thread_concurrency
• Day Out #6:

  [Read more...]

Properties:

Applicable To InnoDB Server Startup Option --innodb-file-per-table Scope Global Dynamic General InnoDB Engine: No
InnoDB plug-in: Yes Possible Values Enable|Disable Flag Default Value Disabled Category Maintenance

Description:

This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having this option enabled, makes it a lot easier to move bigger (or smaller) .ibd files to separate physical disks and/or have backups of certain tables without affecting others.

As

  [Read more...]

Properties:

Applicable To MySQL Server Server Startup Option --long-query-time=<value> Scope Both Dynamic Yes Possible Values (< 5.1.21): Integer
(>=5.1.21): Numeric Minimum Value (< 5.1.21): 1
(>=5.1.21): 0 Default Value 10 Category Monitoring

Description:

In case (as generally the case is) one wants to know about the bottlenecks in their system, MySQL's has a small answer to this in "Slow Query Logs". Any query that takes more time than specified by long_query_time, is labeled as

  [Read more...]

Properties:

Applicable To InnoDB Server Startup Option --innodb-additional-mem-pool-size=<value> Scope Global Dynamic No Possible Values Integer: Range: 524288 (512K) - 4294967295 (4G) Default Value 1048576 (1M) Category Performance

Description:

As per MySQL documentation, this pool is used to store data dictionary information and other internal data structures. If InnoDB runs out of memory on this pool, it starts allocating from OS.

Most of the additional memory pool usage goes to tables

  [Read more...]

Properties:

Applicable To InnoDB Introduced In 5.1.22 Server Startup Option --innodb-autoinc-lock-mode=<value> Scope Global Dynamic No Possible Values enum(0,1,2)
Interpretation:
Value Meaning 0 Traditional 1 Consecutive 2 Interleaved Default Value 1 (consecutive) Categories Scalability, Performance

  [Read more...]

Properties:

Applicable To InnoDB Type Status Variable Scope GLOBAL | SESSION Dynamic NA Possible Values Integer Default Value NA Category Performance

Description:

According to the official documentation, this variable defines "The number of logical reads that InnoDB could not satisfy from the buffer pool and had to do a single-page read.".

But we can go a little further to see the what happens in and around this variable. As per the

  [Read more...]

Properties:

Applicable To InnoDB Server Startup Option --innodb_thread_concurrency=<value> Scope Global Dynamic Yes Possible Values Integer:
Range: 0 - 1000
Interpretation:
MySQL Version Value 4.x 0 - sets it to 1
>500 - Infinite Concurrency < 5.0.19 0 - sets it to 1
>= 20 - Infinite Concurrency >= 5.0.19 0 - Infinite Concurrency




  [Read more...]

Properties:

Applicable To InnoDB Server Startup Option --innodb_buffer_pool_size=<value> Scope Global Dynamic No Possible Values Integer
Range: 1048576 (1MB) - 4294967295 (4GB)
(more for 64 bit machines) Default Value 8388608 (8MB) Category Performance

Description:

One of the Peter's "what to tune?" parameters, this innodb buffer pool is a cache for both indexes and data and should be considered as the most important option for InnoDB performance. In case your server is

  [Read more...]

 Properties:

Applicable To MySQL Server Server Startup Option --max_connect_errors=<value> Scope Global Dynamic Yes Possible Values Integer
Range: 1 - 4294967295 Default Value 10 Category Security

Description:

This variable determines how many interrupted connections can occur from a host. If the number of interrupted connections from this host surpasses this number, that host is blocked from further connections. All of the following, as listed here, will cause the counter to be incremented.

Client program did

  [Read more...]

Properties:

Engine(s) MyISAM Server Startup Option --key_buffer_size=<value> Scope Global Dynamic Yes Possible Values Integer
Range: 8 - 4294967295 (4 GB) Default Value 131072 (128 KB) Category Performance

Description:

This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.

Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the

  [Read more...]