mysql 5.6 - what's new?

   조회 7685  

mysql 5.6에서 가장 주목할 부분은 Security와 Performance라고 하는데,
그누보드 사용자로써 체감 가능한 Security issues는 없는거 같습니다.
성능개선은 ICP라는게 중요한 factor라고 하는데, 많이 기대 됩니다.
Inno DB는 5.5.x에서도 좋아졌지만, 5.6.x에서는 더 좋아지고 기본 DB가 되었습니다.
MyIsam은 기본 DB가 아닙니다.
- inno DB에서도 fulltext index가 가능해집니다. 그런데 쓸일이...
- adaptive flushing 알고리즘이 개선 되었다고 합니다. 이부분은 미묘한 설정이 필요한 것 같습니다.
Improvements to the algorithms for adaptive flushing make I/O operations more efficient and consistent under a variety of workloads.
- 이거는 읽어봐도 잘 모르겠다는... 뭔 말을 이렇게 어렵게 풀어쓴건지.
New optimizations apply to read-only transactions, improving performance and concurrency for ad-hoc queries and report-generating applications.
- 잘 만들었다는 말이겠죠?
InnoDB has several internal performance enhancements, including reducing contention by splitting the kernel mutex, moving flushing operations from the main thread to a separate thread, enabling multiple purge threads, and reducing contention for the buffer pool on large-memory systems.
memcache도 연결되고 한다지만, 가장 중요한거는 성능 입니다.
MySQL 5.5.x에서도 버젼마다 달라지는 성능에 놀라고 있는데, 5.6.x는 더 기대 됩니다.

1.4. What Is New in MySQL 5.6

This section summarizes what has been added to and removed from MySQL 5.6.

Added Features

The following features have been added to MySQL 5.6:
  • Security improvements. These security improvements were made:
    • MySQL now provides a method for storing authentication credentials encrypted in an option file named .mylogin.cnf. To create the file, use the mysql_config_editor utility. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to a MySQL server. mysql_config_editor writes the .mylogin.cnf file using encryption so the credentials are not stored as clear text, and its contents when decrypted by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable. For more information, see Section 4.6.6, “mysql_config_editor — MySQL Configuration Utility”.
    • MySQL now supports stronger encryption for user account passwords, available through an authentication plugin named sha256_password that implements SHA-256 password hashing. This plugin is built in, so it is always available and need not be loaded explicitly. For more information, including instructions for creating accounts that use SHA-256 passwords, see Section, “The SHA-256 Authentication Plugin”.
    • The mysql.user table now has a password_expired column. Its default value is 'N', but can be set to 'Y' with the new ALTER USER statement. After an account's password has been expired, all operations performed in subsequent connections to the server using the account result in an error until the user issues a SET PASSWORD statement to establish a new account password. For more information, see Section, “ALTER USER Syntax”.
    • MySQL now has provision for checking password security:
      • In statements that assign a password supplied as a cleartext value, the value is checked against the current password policy and rejected if it is weak (the statement returns an ER_NOT_VALID_PASSWORD error). This affects the CREATE USER, GRANT, and SET PASSWORD statements. Passwords given as arguments to the PASSWORD() and OLD_PASSWORD() functions are checked as well.
      • The strength of potential passwords can be assessed using the new VALIDATE_PASSWORD_STRENGTH() SQL function, which takes a password argument and returns an integer from 0 (weak) to 100 (strong).
      Both capabilities are implemented by the validate_password plugin. For more information, see Section, “The Password Validation Plugin”.
    • mysql_upgrade now produces a warning if it finds user accounts with passwords hashed with the older pre-4.1 hashing method. Such accounts should be updated to use more secure password hashing. See Section, “Password Hashing in MySQL”
    • Logging has been modified so that passwords do not appear in plain text in statements written to the general query log, slow query log, and binary log. See Section, “Passwords and Logging”.
    • START SLAVE syntax has been modified to permit connection parameters to be specified for connecting to the master. This provides an alternative to storing the password in the file. See Section, “START SLAVE Syntax”.
  • Changes to server defaults. Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. The motivation for these changes is to provide better out-of-box performance and to reduce the need for the database administrator to change settings manually. For more information, see Section, “Changes to Server Defaults”.
  • InnoDB enhancements. These InnoDB enhancements were added:
    • You can create FULLTEXT indexes on InnoDB tables, and query them using the MATCH() ... AGAINST syntax. This feature includes a new proximity search operator (@) and several new configuration options and INFORMATION_SCHEMA tables: See Section, “FULLTEXT Indexes” for more information.
    • Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section, “Online DDL for InnoDB Tables” for details.
    • You have more flexibility to move around the .ibd files files created in file-per-table mode to suit your storage devices and database servers. When creating a table, you can designate a location outside the MySQL data directory to hold the .ibd file, for example to put a busy table on an SSD device or a huge table on a high-capacity HDD device. You can export a table from one MySQL instance and import it in a different instance, without inconsistencies or mismatches caused by buffered data, in-progress transactions, and internal bookkeeping details such as the space ID and LSN. See Section, “Tablespace Management” for details.
    • You can now set the InnoDBpage size for uncompressed tables to 8KB or 4KB, as an alternative to the default 16KB. This setting is controlled by the innodb_page_size configuration option. You specify the size when creating the MySQL instance. All InnoDBtablespaces within an instance share the same page size. Smaller page sizes can help to avoid redundant or inefficient I/O for certain combinations of workload and storage devices, particularly SSD devices with small block sizes.
    • Improvements to the algorithms for adaptive flushing make I/O operations more efficient and consistent under a variety of workloads. The new algorithm and default configuration values are expected to improve performance and concurrency for most users. Advanced users can fine-tune their I/O responsiveness through several configuration options. See Section, “Improvements to Buffer Pool Flushing” for details.
    • You can code MySQL applications that access InnoDB tables through a NoSQL-style API. This feature uses the popular memcached daemon to relay requests such as ADD, SET, and GET for key-value pairs. These simple operations to store and retrieve data avoid the SQL overhead such as parsing and constructing a query execution plan. You can access the same data through the NoSQL API and SQL. For example, you might use the NoSQL API for fast updates and lookups, and SQL for complex queries and compatibility with existing applications. See Section 14.2.10, “InnoDB Integration with memcached for details.
    • Optimizer statistics for InnoDB tables are gathered at more predictable intervals and can persist across server restarts, for improved plan stability. You can also control the amount of sampling done for InnoDB indexes, to make the optimizer statistics more accurate and improve the query execution plan. See Section, “Persistent Optimizer Statistics for InnoDB Tables” for details.
    • New optimizations apply to read-only transactions, improving performance and concurrency for ad-hoc queries and report-generating applications. These optimizations are applied automatically when practical, or you can specify START TRANSACTION READ ONLY to ensure the transaction is read-only. See Section, “Optimizations for Read-Only Transactions” for details.
    • You can move the InnoDBundo log out of the system tablespace into one or more separate tablespaces. The I/O patterns for the undo log make these new tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. For details, see Section, “Separate Tablespaces for InnoDB Undo Logs”.
    • The InnoDBredo log files now have a maximum combined size of 512GB, increased from 4GB. You can specify the larger values through the innodb_log_file_size option.
    • The --innodb-read-only option lets you run a MySQL server in read-only mode. You can access InnoDB tables on read-only media such as a DVD or CD, or set up a data warehouse with multiple instances all sharing the same data directory. See Section, “Support for Read-Only Media” for usage details.
    • Several new InnoDB-related INFORMATION_SCHEMA tables provide information about the InnoDB buffer pool, metadata about tables, indexes, and foreign keys from the InnoDB data dictionary, and low-level information about performance metrics that complements the information from the Performance Schema tables.
    • InnoDB now limits the memory used to hold table information when many tables are opened.
    • InnoDB has several internal performance enhancements, including reducing contention by splitting the kernel mutex, moving flushing operations from the main thread to a separate thread, enabling multiple purge threads, and reducing contention for the buffer pool on large-memory systems.
    • InnoDB uses a new, faster algorithm to detect deadlocks. Information about all InnoDB deadlocks can be written to the MySQL server error log, to help diagnose application issues.
    • To avoid a lengthy warmup period after restarting the server, particularly for instances with large InnoDBbuffer pools, you can reload pages into the buffer pool immediately after a restart. MySQL can dump a compact data file at shutdown, then consult that data file to find the pages to reload on the next restart. You can also manually dump or reload the buffer pool at any time, for example during benchmarking or after complex report-generation queries. See Section, “Faster Restart by Preloading the InnoDB Buffer Pool” for details.
  • Partitioning. These table-partitioning enhancements were added:
    • The maximum number of partitions is increased to 8192. This number includes all partitions and all subpartitions of the table.
    • It is now possible to exchange a partition of a partitioned table or a subpartition of a subpartitioned table with a nonpartitioned table that otherwise has the same structure using the ALTER TABLE ... EXCHANGE PARTITION statement. This can be used, for example, to import and export partitions. For more information and examples, see Section 17.3.3, “Exchanging Partitions and Subpartitions with Tables”.
    • Explicit selection of one or more partitions or subpartitions is now supported for queries, as well as for many data modification statements, that act on partitioned tables. For example, assume a table t with some integer column c has 4 partitions named p0, p1, p2, and p3. Then the query SELECT * FROM t PARTITION (p0, p1) WHERE c < 5 returns only those rows from partitions p0 and p1 for which c is less than 5.
      The following statements support explicit partition selection:
      For syntax, see the descriptions of the individual statements. For additional information and examples, see Section 17.5, “Partition Selection”.
    • Partition lock pruning greatly improves performance of many DML and DDL statements acting on tables with many partitions by helping to eliminate locks on partitions that are not affected by these statements. Such statements include many SELECT, SELECT ... PARTITION, UPDATE, REPLACE, INSERT, as well as many other statements. For more information, including a complete listing of the statements whose performance has thus been improved, see Section 17.6.4, “Partitioning and Locking”.
  • Performance Schema. The Performance Schema includes several new features:
    • Instrumentation for table input and output. Instrumented operations include row-level accesses to persistent base tables or temporary tables. Operations that affect rows are fetch, insert, update, and delete.
    • Event filtering by table, based on schema and/or table names.
    • Event filtering by thread. More information is collected for threads.
    • Summary tables for table and index I/O, and for table locks.
    • Instrumentation for statements and stages within statements.
    • Configuration of instruments and consumers at server startup, which previously was possible only at runtime.
  • Replication and logging. These replication enhancements were added:
    • MySQL now supports transaction-based replication using global transaction identifiers (also known as GTIDs). This makes it possible to identify and track each transaction when it is committed on the originating server and as it is applied by any slaves.
      Enabling of GTIDs in a replication setup is done primarily using the new --gtid-mode and --disable-gtid-unsafe-statements server options. For information about additional options and variables introduced in support of GTIDs, see Section, “Global Transaction ID Options and Variables”.
      When using GTIDs it is not necessary to refer to log files or positions within those files when starting a new slave or failing over to a new master, which greatly simplifies these tasks.
      GTID-based replication is completely transaction-based, which makes it simple to check the consistency of masters and slaves. If all transactions committed on a given master are also committed on a given slave, consistency between the two servers is guaranteed.
      For more complete information about the implementation and use of GTIDs in MySQL Replication, see Section 16.1.3, “Replication with Global Transaction Identifiers”.
  • MySQL row-based replication now supports row image control. By logging only those columns required for uniquely identifying and executing changes on each row (as opposed to all columns) for each row change, it is possible to save disk space, network resources, and memory usage. You can determine whether full or minimal rows are logged by setting the binlog_row_image server system variable to one of the values minimal (log required columns only), full (log all columns), or noblob (log all columns except for unneeded BLOB or TEXT columns). See System variables used with the binary log, for more information.
Binary logs written and read by the MySQL Server are now crash-safe, because only complete events (or transactions) are logged or read back. By default, the server logs the length of the event as well as the event itself and uses this information to verify that the event was written correctly. You can also cause the server to write checksums for the events using CRC32 checksums by setting the binlog_checksum system variable. To cause the server to read checksums from the binary log, use the master_verify_checksum system variable. The --slave-sql-verify-checksum system variable causes the slave SQL thread to read checksums from the relay log.
MySQL now supports logging of master connection information and of slave relay log information to tables as well as files. Use of these tables can be controlled independently, by the --master-info-repository and --relay-log-info-repository server options. Setting --master-info-repository to TABLE causes connection information to be logged in the slave_master_info table; setting --relay-log-info-repository to TABLE causes relay log information to be logged to the slave_relay_log_info table. Both of these tables are created automatically, in the mysql system database.
In order for replication to be crash-safe, the slave_master_info and slave_relay_log_info tables must each use a transactional storage engine such as InnoDB. By default, both of these tables use MyISAM; this means that, prior to starting replication, you must change both of these tables to use a transactional storage engine if you wish for replication to be crash-safe. You can do this by means of the appropriate ALTER TABLE ... ENGINE=... statements. You should not attempt to change the storage engine used by either of these tables while replication is actually running.
mysqlbinlog now has the capability to back up a binary log in its original binary format. When invoked with the --read-from-remote-server and --raw options, mysqlbinlog connects to a server, requests the log files, and writes output files in the same format as the originals. See Section, “Using mysqlbinlog to Back Up Binary Log Files”.
MySQL now supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time. The default delay is 0 seconds. Use the new MASTER_DELAY option for CHANGE MASTER TO to set the delay.
Delayed replication can be used for purposes such as protecting against user mistakes on the master (a DBA can roll back a delayed slave to the time just before the disaster) or testing how the system behaves when there is a lag. See Section 16.3.9, “Delayed Replication”.
A replication slave having multiple network interfaces can now be caused to use only one of these (to the exclusion of the others) by using the MASTER_BIND option when issuing a CHANGE MASTER TO statement.
The log_bin_basename system variable has been added. This variable contains the complete filename and path to the binary log file. Whereas the log_bin system variable shows only whether or not binary logging is enabled, log_bin_basename reflects the name set with the --log-bin server option.
Similarly, the relay_log_basename system variable shows the filename and complete path to the relay log file.
MySQL Replication now supports parallel execution of transactions with multi-threading on the slave. When parallel execution is enabled, the slave SQL thread acts as the coordinator for a number of slave worker threads as determined by the value of the slave_parallel_workers server system variable. The current implementation of multi-threading on the slave assumes that data and updates are partitioned on a per-database basis, and that updates within a given database occur in the same relative order as they do on the master. However, it is not necessary to coordinate transactions between different databases. Transactions can then also be distributed per database, which means that a worker thread on the slave slave can process successive transactions on a given database without waiting for updates to other databases to complete.
Since transactions on different databases can occur in a different order on the slave than on the master, simply checking for the most recently executed transaction is not a guarantee that all previous transactions on the master have been executed on the slave. This has implications for logging and recovery when using a multi-threaded slave. For information about how to interpret binary logging information when using multi-threading on the slave, see Section, “SHOW SLAVE STATUS Syntax”.
Optimizer enhancements. These query optimizer improvements were implemented:
The optimizer now more efficiently handles queries (and subqueries) of the following form:
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;
That type of query is common in web applications that display only a few rows from a larger result set. For example:
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
The sort buffer has a size of sort_buffer_size. If the sort elements for N rows are small enough to fit in the sort buffer (M+N rows if M was specified), the server can avoid using a merge file and perform the sort entirely in memory. For details, see Section, “Optimizing LIMIT Queries”.
The optimizer implements Disk-Sweep Multi-Range Read. Reading rows using a range scan on a secondary index can result in many random disk accesses to the base table when the table is large and not stored in the storage engine's cache. With the Disk-Sweep Multi-Range Read (MRR) optimization, MySQL tries to reduce the number of random disk access for range scans by first scanning the index only and collecting the keys for the relevant rows. Then the keys are sorted and finally the rows are retrieved from the base table using the order of the primary key. The motivation for Disk-sweep MRR is to reduce the number of random disk accesses and instead achieve a more sequential scan of the base table data. For more information, see Section 8.13.10, “Multi-Range Read Optimization”.
The optimizer implements Index Condition Pushdown (ICP), an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is base row be read. ICP can reduce the number of accesses the storage engine has to do against the base table and the number of accesses the MySQL server has to do against the storage engine. For more information, see Section 8.13.4, “Index Condition Pushdown Optimization”.
- -

제목Page 3/9
2015-05   101597   불당
2014-05   121835   불당
2018-06   27046   불당
2015-11   65873   불당
2016-08   48105   불당
2013-02   15340   불당
2013-02   8465   불당
2013-02   10638   불당
2013-02   7069   불당
2013-02   7614   불당
2013-01   7751   불당
2013-01   6836   불당
2013-01   8258   불당
2013-01   5983   불당
2013-01   7415   불당
2012-12   7606   불당
2012-12   8407   불당
2012-11   7449   불당
2012-11   6611   불당
2012-11   6596   불당
2012-11   9178   불당
2012-11   8600   불당
2012-10   10753   불당
2012-10   11367   불당
2012-10   7686   불당