Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 16 6 Older Entries

Displaying posts with tag: Day Out (reset)

Variable's Day Out #16: innodb_log_file_size
+0 Vote Up -0Vote Down
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...]
Variable's Day Out #15: bulk_insert_buffer_size
+0 Vote Up -0Vote Down

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...]
Variable's Day Out #14: log_queries_not_using_indexes
+0 Vote Up -0Vote Down

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...]
Variable's Day Out #13: binlog_format
+0 Vote Up -0Vote Down

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...]
Variable's Day Out #12: innodb_flush_method
+0 Vote Up -0Vote Down

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...]
Variable's Day Out #11: large_pages
+0 Vote Up -0Vote Down

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...]
Last 10 Variable Day Outs
+0 Vote Up -0Vote Down

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

Day Outs:

  [Read more...]
Variable's Day Out #10: innodb_file_per_table
+0 Vote Up -0Vote Down

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...]
Variable's Day Out #9: long_query_time
+0 Vote Up -0Vote Down

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...]
Variable's Day Out #8: innodb_additional_mem_pool_size
+0 Vote Up -0Vote Down

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...]
Showing entries 1 to 10 of 16 6 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.