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

Displaying posts with tag: innodb (reset)

How does InnoDB behave without a Primary Key?
+3 Vote Up -0Vote Down

This afternoon, Arjen Lentz and I were discussing InnoDB’s behavior without a declared PRIMARY KEY, and the topic felt interesting enough and undocumented enough to warrant its own short post.

Background on InnoDB clustered keys

In The physical structure of InnoDB index pages I described how “Everything is an index in InnoDB”. This means that InnoDB must always have a “cluster key” for each table, which is normally the PRIMARY KEY. The manual has this to say in Clustered and Secondary Indexes:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered

  [Read more...]
MySQL Architect at Oracle
Employee +9 Vote Up -3Vote Down
I have worked as an architect in the MySQL/NDB world for more than 20 years and I am still working at Oracle and I like it here. Given all the FUD spread about MySQL I thought it might be a good idea to spread the word about all the great things we're doing to MySQL at Oracle.

#1 We are working on improving modularity in MySQL code base
In the early days of MySQL the MySQL development had serious issues with its development model. It was a model designed for a small code base. I used to work at Ericsson which is developing telecom switches that have systems with tens of millions lines of code. Such large systems require modularity. The Ericsson switches was developed with modularity built into the programming language already since the 70's. Even with this modularity a second level of modularity was required. The learnings from this reengineering project


  [Read more...]
MySQL Triggers with Logging
+0 Vote Up -0Vote Down

Somebody asked why you can’t implement MySQL triggers that write information when you want to stop the DML statement, like autonomous procedures in Oracle. The question was a surprise but I didn’t find anything on it, so here’s how you can do it. This is more or less like an autonomous process by leveraging both the InnoDB and MyISAM engine’s behaviors. This post leverages an earlier explanation of MySQL Triggers.

  • First you create a MyISAM table, which is a persistent store that auto commits when you’re other InnoDB tables can be transactionally dependent. Here’s a simple MyISAM logger table.
  • CREATE TABLE logger
    ( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    , logger_event      VARCHAR(50)
    ,
      [Read more...]
    A few notes on InnoDB in MySQL 5.7.1
    +7 Vote Up -0Vote Down

    I’ve started poking around the MySQL 5.7.1 source tree (although just from tarball as I don’t see a BZR tree yet). I thought I’d share a few thoughts:

    • InnoDB temporary tables. Not REDO logged. What does this mean? It’s a huge step in removing the dependency on MEMORY and MyISAM engines for temporary tables used in query execution. With InnoDB temporary tables there is no reason for MEMORY engine to continue to exist, there is absolutely no way in which it is better.
    • InnoDB temp tables aren’t insert buffered
      This probably doesn’t really matter as you’re not going to be doing REDO logging for them (plus things are generally short lived)… but it could be a future area for performance improvement
    • The

      [Read more...]
    Galera pre-deployment check
    +0 Vote Up -0Vote Down

    One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable.

    • Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for InnoDB-internal reasons).
    • We want to know about FULLTEXT indexes. With recent InnoDB versions also supporting FULLTEXT we need to check not just whether a table has such an index, but actually which engine it is.
    • Spatial indexes. While both InnoDB and MyISAM can deal with spatial datatypes (POINT, GEOMETRY, etc), only MyISAM has the spatial indexes.

    Naturally, checking a schema in the server is more effective than going through other sources and possibly missing bits. On the downside, the only viable way to get this info out of MariaDB

      [Read more...]
    Fresh dogfood: Migrating to InnoDB fulltext search on bugs.mysql.com
    Employee +2 Vote Up -0Vote Down

    Even frequent visitors to bugs.mysql.com can sometimes miss the little note in the bottom right corner of each page:

    Page generated in 0.017 sec. using MySQL 5.6.11-enterprise-commercial-advanced-log

    That text changed this past weekend, going from MySQL Enterprise 5.6.10 to 5.6.11.  But more importantly, the collection of MyISAM tables which support the bugs system were also converted to InnoDB.  There’s a little story to tell here about eating this particular helping of dogfood which also amplifies changelog comments, so here it is:

    We like to keep bugs.mysql.com on a current release of MySQL, and

      [Read more...]
    How to recover table structure from InnoDB dictionary
    +2 Vote Up -0Vote Down

    To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from some old backup.

    A new tool sys_parser can recover the table structure from InnoDB dictionary.

    Why do we need a new tool anyway? It is absolutely critical to have an accurate table definition to ensure a successful recovery. Even an unnoticeable difference like NULL or NOT NULL can shift all

      [Read more...]
    Book review: Instant InnoDB
    +0 Vote Up -0Vote Down
    Instant Innodb, by Matt Reid

    This book does a good job of explaining the InnoDB internals. I have found particularly useful the section where it describe in detail all the server variables affecting InnoDB. Although these variables are also in the MySQL manual, some of them have never been explained to me as thoroughly as this book as done.

    The title claims that it is a InnoDB reference. If is more than that, as the reference part id covered in three chapters. The rest of the book gives useful advice on maintenance, monitoring, and troubleshooting.

    Data Organization in InnoDB
    Employee_Team +5 Vote Up -1Vote Down

    Introduction

    This article will explain how the data is organized in InnoDB storage engine. First we will look at the various files that are created by InnoDB, then we look at the logical data organization like tablespaces, pages, segments and extents. We will explore each of them in some detail and discuss about their relationship with each other. At the end of this article, the reader will have a high level view of the data layout within the InnoDB storage engine.

    The Files

    MySQL will store all data within the data directory. The data directory can be specified using the command line option –data-dir or in the configuration file as datadir. Refer to the Server Command Options for complete details.

    By default, when InnoDB is initialized, it creates 3

      [Read more...]
    InnoDB: A journey to the core: At the MySQL Conference
    +2 Vote Up -0Vote Down

    Next week is the Percona Live MySQL Conference and Expo 2013.

    Davi Arnaut and I are co-presenting InnoDB: A journey to the core, based on my InnoDB blog series by the same name. We will (fairly quickly) cover InnoDB’s storage formats as described in those posts, but in an interactive format. There will be some new material that hasn’t been blogged yet (mostly stuff that is more difficult to explain or has been incompletely described in innodb_diagrams). Most importantly, Davi and I will be available for questions, and hopefully some of the InnoDB

      [Read more...]
    10 Newer Entries Showing entries 121 to 130 of 759 10 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.