Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 42 Next 12 Older Entries

Displaying posts with tag: Features (reset)

MySQL 5.7.3: Deep dive into 1mil QPS with InnoDB & Memcached
Employee_Team +2 Vote Up -0Vote Down

As you probably already know, in MySQL 5.7.3 release, InnoDB Memcached reached a record of over 1 million QPS on a read only load. The overview of the benchmark and testing results can be seen in an earlier blog by Dimitri. In this blog, I will spend sometime on the detail changes we have made to achieve this number.

First thanks to Facebook's Yoshinori with his bug#70172 that brought our attention to this single commit read only load test. We have been focussing on operation with large batch size. This bug prompted us to do a series of optimization on single commit read only queries and these optimizations eliminate almost all major bottlenecks from the InnoDB Memcached plugin itself.

  [Read more...]
Redo Logging in InnoDB
Employee_Team +3 Vote Up -0Vote Down

Introduction

InnoDB is a general-purpose storage engine that balances high reliability and high performance. It is a transactional storage engine and is fully ACID compliant, as would be expected from any relational database. The durability guarantee provided by InnoDB is made possible by the redo logs.

This article will provide an overview of the redo log subsystem or log subsystem of InnoDB. We will look at the following details:

  • The global log system object, which provides access to important data structures and information.
  • The mini-transaction (mtr), using which all redo log records are created.
  • The global in-memory log buffer (or just log buffer), into which the redo logs are written to from the mini
  [Read more...]
My MySQL bugs and feature requests
+1 Vote Up -0Vote Down

My MySQL bugs is a list I recently created and intend to keep up to date with issues I have seen.

Benchmarking the Performance Impact of Foreign Keys in MySQL Cluster 7.3 GA
+2 Vote Up -0Vote Down



FOREIGN KEYs in MySQL Cluster is a big step forward. It is now possible to run enterprise software with NDB Cluster as the storage backend. Over the years, the lack of FOREIGN KEYs have been one of the most limiting pieces of functionality. Who wants to fiddle with TRIGGERs or recode applications to enforce data integrity?
But finally, it is here. It is implemented natively at the Data Node level, where NDB stores its data. It is well known that FOREIGN KEYs come with an overhead. E.g., when writing a record into a child table, the existence must be checked in the parent table. Since data is distributed across multiple Data Nodes, the child record and parent record may be on different nodes or shards (Node Groups). Hence there is extra work to be done in terms of internal triggers and network communication, the



  [Read more...]
Playing hid-and-seek with databases
+4 Vote Up -0Vote Down
As far as I know there isn't a well accepted set of best practices for MySQL, but there are many best practices known and used by most MySQL DBA's. One of those best practices is that the datadir must not be equal to a mountpoint; it has to be a subdirectory of a mountpoint. I learned this the hard way a few years ago when I used a NetApp Filer via NFS as a data directory. The NetApp filer exposed the snapshots via a .snapshot directory. A database in MySQL is a directory, so MySQL thought that the .snapshot directory was a database. This resulted in some issues with our monitoring scripts, so we had to create a mysql_data directory and move all the databases to that directory.

For other setups directories like lost+found, .zfs, etc. gave similar issues.

In MySQL 5.6 a new feature was introduced to make it possible to make some databases hidden. To do



  [Read more...]
MySQL 5.5's new features
+0 Vote Up -0Vote Down
The recently released MySQL 5.6 gets a lot of attention, but for those who are still on 5.5 there is also good news: There are two new features in 5.5.

The first feature is that there are more INFORMATION_SCHEMA tables for InnoDB. This means that it's possible to 'see' what's in the buffer pool. It also makes it possible to get more information about the LRU list.

From the 5.5.28 changelog:
InnoDB: Certain information_schema tables originally introduced in MySQL 5.6 are now also available in MySQL 5.5 and MySQL 5.1: INNODB_BUFFER_PAGE, INNODB_BUFFER_PAGE_LRU, and INNODB_BUFFER_POOL_STATS. (Bug #13113026)

This is in the "Bugs Fixed" section instead of the "Functionality Added or Changed" section, which is a bit weird in my opinion.

The second feature is a








  [Read more...]
MySQL 5.6 vs. MariaDB 10.0
+6 Vote Up -8Vote Down

A high-level comparative overview of the features

With the recent GA release of MySQL 5.6, there have been a lot of questions about where MariaDB stands with regards to MySQL 5.6.  SkySQL will of course support both as Patrik explained in his recent blog post, but there are many questions about the technical differences. Rasmus from Monty Program gave a detailed view on MariaDB 10.0 here but I thought it would be beneficial to share a comparison table of the two. MariaDB recently released a benchmark including various versions of both MariaDB and MySQL (as did DmitriK from Oracle), but this post will focus solely on

  [Read more...]
Repeatable Read Isolation Level in InnoDB - How Consistent Read View Works
Employee_Team +5 Vote Up -0Vote Down
This article discusses about the approach taken by InnoDB Storage Engine of MySQL to provide the repeatable read isolation level.  First, an example is presented to demonstrate the two different designs that are possible.  Then the design used in InnoDB is presented followed by a short discussion about the advantages and disadvantages of this design choice.  As part of this discussion, we also present a performance optimization done in MySQL 5.6. 

An Example Scenario

I used MySQL 5.5 for this purpose.  Let us create the following tables t1 and t2 in the test database that is available by default. Even though the default storage engine in MySQL 5.5 is InnoDB, I explicitly specify it for clarity.  

mysql> use test;
mysql>



  [Read more...]
Online ALTER TABLE in MySQL 5.6
Employee_Team +1 Vote Up -0Vote Down
This is the low-level view of data dictionary language (DDL) operations in the InnoDB storage engine in MySQL 5.6. John Russell gave a more high-level view in his blog post April 2012 Labs Release – Online DDL Improvements.

MySQL before the InnoDB Plugin

Traditionally, the MySQL storage engine interface has taken a minimalistic approach to data definition language. The only natively supported operations were CREATE TABLE, DROP TABLE and RENAME TABLE. Consider the following example:

CREATE TABLE t(a INT);
INSERT INTO t VALUES (1),(2),(3);
CREATE INDEX a ON t(a);
DROP TABLE t;

The CREATE INDEX statement would be executed roughly as follows:

CREATE TABLE temp(a INT, INDEX(a));
INSERT INTO
  [Read more...]
New Enhancements for InnoDB Memcached
Employee_Team +0 Vote Up -0Vote Down

In MySQL 5.6, we continued our development on InnoDB Memcached and completed a few widely desirable features that make InnoDB Memcached a competitive feature in more scenario. Notablely, they are

1) Support multiple table mapping

2) Added background thread to auto-commit long running transactions

3) Enhancement in binlog performance

 Let’s go over each of these features one by one. And in the last section, we will go over a couple of internally performed performance tests.

Support multiple table mapping

In our earlier release, all InnoDB Memcached operations are mapped to a single InnoDB table. In the real life, user might want to use this InnoDB Memcached features on different tables. Thus being able to support access to different table at run time, and having different



  [Read more...]
InnoDB Compression Improvements in MySQL 5.6
Employee_Team +1 Vote Up -0Vote Down

MySQL 5.6 comes with significant improvements for the compression support inside InnoDB. The enhancements that we'll talk about in this piece are also a good example of community contributions. The work on these was conceived, implemented and contributed by the engineers at Facebook. Before we plunge into the details let us familiarize ourselves with some of the key concepts surrounding InnoDB compression.

  • In InnoDB compressed pages are fixed size. Supported sizes are 1, 2, 4, 8 and 16K. The compressed page size is specified at table creation time.
  • InnoDB uses zlib for compression.
  • InnoDB buffer pool will attempt to cache compressed pages like normal pages. However, whenever a page is actively used by a transaction, we'll always have the uncompressed version of the page as well i.e.: we can have a page in


  [Read more...]
Optimizing neighbor flush behavior
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 16, 2012 by Yasufumi Kinoshita.

The performance of flush_list flushing of InnoDB decides the basic performance for modifying workloads. So, it is important to optimize the flush behavior. In this post we’ll consider how to optimize the neighbor-flushing behavior.

Factor 1: Characteristics of storage

Depending on the characteristics of your storage’s throughput for write IO, you can term your storage as either “write amount bound” or “write times bound”. The minimum unit of the InnoDB datafile is page size (16KB or less). And InnoDB attempts to combines them in a single IO up to 1 extent (1MB) maximum, if they are contiguous.

<one HDD>

  [Read more...]
New flushing algorithm in InnoDB
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 13, 2012 by Inaam Rana.

In MySQL labs release April 2012 we have reworked the flushing heuristics in InnoDB. In this post I’ll give an overview of what we have changed and the various configuration variables we have introduced to fine tune the flushing algorithm. If you are interested in finding out how our new flushing algorithm fares in benchmarks you can get these details in Dimitri’s well-explained blog here.

Flushing means writing dirty pages to disk. I have explained in some detail about

  [Read more...]
InnoDB persistent stats got a friendly UI
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 11, 2012 by Vasil Dimov.

After introducing InnoDB persistent statistics in MySQL 5.6, in this April Labs release we have dressed it up in a nice UI and refactored the internals a bit to make the code more elegant and straight-forward.

The persistent stats are now controlled globally and can also be overridden at table level, should any table require a different behavior.

Global

The server global flag –innodb-stats-persistent (boolean) now controls whether all InnoDB tables use persistent statistics or not. Keep in mind that if a table is using persistent stats then its statistics

  [Read more...]
Memcached With SASL Support
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 11, 2012 by Jimmy Yang.

In this April MySQL Lab release, we’ll provide you a more robust and release-ready InnoDB Memcached Engine with a few enhancements. The most notable addition is the SASL support, which gives users the capability to protect their MySQL database from unauthenticated access through memcached clients. In this blog, I will walk you through steps of getting this option enabled.

Background Info:
SASL stands for “Simple Authentication and Security Layer”, which is a Standard for adding authentication support to connection-based protocols. Memcached added SASL support starting its 1.4.3 release. And here is a

  [Read more...]
April 2012 Labs Release – Online DDL Improvements
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 11, 2012 by John Russell.

This feature is a continuation of the “Fast Index Creation” feature introduced in Fast Index Creation in the InnoDB Storage Engine. Now you can perform other kinds of DDL operations on InnoDB tables online: that is, with minimal delay for operations on that table, and without rebuilding the entire table. This enhancement improves responsiveness and availability in busy production

  [Read more...]
InnoDB transportable tablespaces
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 10, 2012 by Sunny Bains.

The Problem

In the past, users were unable to take full advantage of the FLUSH TABLES WITH READ LOCK statement. InnoDB simply ignored the flush to disk part. If the table did not have any dirty pages in the InnoDB buffer that weren’t synced to disk (due to sheer luck) then it was safe to copy the .ibd file to another location. Also, the restore was not without its limitations and complications. The .ibd file could not be copied over to another server because InnoDB during import did not fix

  [Read more...]
Choose the Location of your InnoDB File-per-Table Tablespace
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on April 10, 2012 by Kevin Lewis.

The April 2012 InnoDB labs release introduces a new feature in InnoDB that allows you to choose the location of specific tables.  For example, you can place critical tables onto an SSD drive while leaving the system tablespace on a hard drive.  Conversely, you can store you primary database files on an SSD and put a seldom used but very large archive or reference table on a larger cheaper hard drive.

Innodb now makes use of the following existing syntax in MySQL ;

CREATE TABLE  . . .  DATA DIRECTORY = ‘absolute path of data directory’;

CREATE TABLE  . . .  PARTITION . . . DATA DIRECTORY = ‘absolute path of data directory’;

  [Read more...]
Better scaling of read-only workloads
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on Dec 20, 2011 by Sunny Bains.

The problem and its cause

There have been several complaints over the years about InnoDB’s inability to scale beyond 256 connections. One of the main issues behind this scalability bottleneck was the read view creation that is required for MVCC (Multi Version Concurrency Control) to work. When the user starts a transaction this is what InnoDB does under the hood:

  • Create or reuse a transaction instance – usually it is reused, the transactions are reused from a pool (trx_sys_t::mysql_trx_list).
  • Initialize the transaction start time and assign a rollback segment
  • Append the transaction to an

  [Read more...]
InnoDB Full-Text Search is in MySQL 5.6.4
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on Dec 20, 2011 by Jimmy Yang.

InnoDB full-text search (FTS) is finally available in MySQL 5.6.4 release. The feature has been on trial through MySQL’s summer lab release, thus we had several blogs covering the feature. In this blog, we will leave the “how to” part of the feature to those blogs, and focus on some important characteristics of this new feature, so you will have a better understanding when trying on the feature.

The InnoDB Full-text Index as an Inverted Index

When comes to the basic design, InnoDB takes a traditional way to implementation the full-text index, which is a so called “Inverted Index”. It composes of a set of auxiliary “index tables” that stores the

  [Read more...]
InnoDB 5.6.4 supports databases with 4k and 8k page sizes
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on Dec 20, 2011 by Kevin Lewis.

In the 5.6.4 release it is now possible to create an InnoDB database with 4k or 8k page sizes in addition to the original 16k page size. Previously, it could be done by recompiling the engine with a different value for UNIV_PAGE_SIZE_SHIFT and UNIV_PAGE_SIZE. With this release, you can set –innodb-page-size=n when starting mysqld, or put innodb_page_size=n in the configuration file in the [mysqld] section where n can be 4k, 8k, 16k, or 4096, 8192, 16384.

The support of smaller page sizes may be useful for certain storage media such as SSDs. Performance results can vary depending on your data schema, record size, and read/write ratio. But this provides you more options to optimize your

  [Read more...]
InnoDB Memcached with Binlog Capability
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on Oct 3, 2011 by Jimmy Yang.

In our earlier Labs release, we presented a MySQL NoSQL solution through InnoDB Memcached Daemon Plugin (see earlier Calvin’s and my posts). That earlier release allows the Memcached Plugin directly interacting with InnoDB, completely bypassing MySQL optimizer and QP layers. It differs with another popular MySQL NoSQL solution, HandlerSocket, by skipping even the Handler API layer and directly access the InnoDB through InnoDB APIs. Thus, in theory, it would be simpler and more efficient.

However, there is one

  [Read more...]
InnoDB Full-Text Search Tutorial
Employee_Team +0 Vote Up -0Vote Down

Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by John Russell.

The InnoDB full-text search capability is an exciting feature. The full-text search itself is generally useful to have in an RDBMS. If an application is using all InnoDB tables except for one that is used for full-text searches, now that last table can be switched to InnoDB. If putting the full-text data in a MyISAM table led to scalability problems, duplication, or a less-than-ideal schema design, now those issues can be addressed.

In this post, I’ll take you through some of the basics of setting up and querying an InnoDB FULLTEXT search index. I’ll leave the scalability and performance aspects to

  [Read more...]
Improve InnoDB thread scheduling
Employee +0 Vote Up -0Vote Down
Introduction

Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by Sunny Bains.

InnoDB has had the thread concurrency management code for some years now. Most will be familiar with the three configuration variables associated with this feature:
  • innodb_thread_concurrency
  • innodb_concurrency_tickets

  •   [Read more...]
    Allow UNDO logs to reside in their own tablespace
    Employee_Team +0 Vote Up -0Vote Down

    Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by Sunny Bains.

    Introduction

    The InnoDB  UNDO entries reside in a special system table called the UNDO log. This log is made up of several segments. These segments are called rollback segments. A segment in InnoDB is similar to what a file would be in a file system,e.g., user tables and indexes are also stored as separate segments within the same tablespace,  only their format is different. In that sense there is nothing special about InnoDB UNDO logs. This feature allows storing of the UNDO log across several tablespaces.

    Purpose

    UNDO logs  contain the before image of modified records. There are two

      [Read more...]
    Shortened warm-up times with a preloaded InnoDB buffer pool
    Employee_Team +0 Vote Up -0Vote Down

    Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by Vasil Dimov.

    Are you running an InnoDB installation with a many-gigabytes buffer pool(s)? Does it take too long before it goes back to speed after a restart? If yes, then the following will be interesting to you.

    In the latest MySQL 5.6 Labs release we have implemented an InnoDB buffer pool(s) dump and load to solve this problem.

    The contents of the InnoDB buffer pool(s) can be saved on disk before MySQL is shut down and then read in after a restart so that the warm up time is drastically shortened – the buffer pool(s) go to the state they were before the server restart! The time needed for that is roughly the time needed to read data from disk that is about the size of the

      [Read more...]
    Overview and Getting Started with InnoDB FTS
    Employee_Team +0 Vote Up -0Vote Down

    Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by Jimmy Yang.

    This Summer’s lab release includes an important feature of InnoDB – InnoDB Fulltext Search. This feature would greatly enhance InnoDB’s capability in Text search and retrieving. Since the feature is designed for our transactional storage engine, its underlying architecture design and implementation are completely different with those of MyISAM. So it is worth to give a brief technology review of this feature, familiarize users with some important concepts in the InnoDB FTS so that they can better utilize this feature.

    There are a few other posts on the subject. John Russell will give a brief tutorial on the InnoDB fulltext search command and syntax. I will also discuss some

      [Read more...]
    Difference between InnoDB FTS and MyISAM FTS
    Employee_Team +0 Vote Up -0Vote Down

    Note: this article was originally published on http://blogs.innodb.com on July 25, 2011 by Jimmy Yang.

    With this lab release, InnoDB now has its own Fulltext Search (FTS) support. People might wonder how different is this FTS comparing with that of MyISAM. From the usage standpoint, they seem very similar with no apparent difference. However, in the underlying FTS engine, the difference is huge, since two are built on different storage engines with totally different design. So in some aspect such as DML, it might be difficult to make it an apple to apple comparison. However, we do try to list and explain the difference, so to make it easier for user to understand our design and make the transition to use InnoDB FTS easier.

    The Design:

    The most significant

      [Read more...]
    Rewriting the Creating a CRUD Application with PHP tutorial cycle: help wanted!
    Employee +0 Vote Up -0Vote Down

    I've been reworking the Creating a CRUD Application with PHP tutorials. The new version should be ready for 7.0 Beta 2 release. In the new version:

    • I replaced mysql functions with mysqli functions.
    • With the help of Christoper Jones, I've added OCI8 versions of the code for connecting to Oracle Database
    • I've fixed some old bugs and improved the tutorials overall.

    In the course of this work, two things have come to my attention. These are two things that you can help with.

    • The title Creating a CRUD Application... is not very appealing.
    • The sample CSS used for Lesson 8 is terrible.

    So first, can anyone suggest a new title? We have one


      [Read more...]
    Rewriting the Creating a CRUD Application with PHP tutorial cycle: help wanted!
    Employee +0 Vote Up -0Vote Down

    I've been reworking the Creating a CRUD Application with PHP tutorials. The new version should be ready for 7.0 Beta 2 release. In the new version:

    • I replaced mysql functions with mysqli functions.
    • With the help of Christoper Jones, I've added OCI8 versions of the code for connecting to Oracle Database
    • I've fixed some old bugs and improved the tutorials overall.

    In the course of this work, two things have come to my attention. These are two things that you can help with.

    • The title Creating a CRUD Application... is not very appealing.
    • The sample CSS used for Lesson 8 is terrible.

    So first, can anyone suggest a new title? We have one


      [Read more...]
    Showing entries 1 to 30 of 42 Next 12 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.