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 中文
Previous 30 Newer Entries Showing entries 91 to 120 of 733 Next 30 Older Entries

Displaying posts with tag: innodb (reset)

Benchmarking Percona Server TokuDB vs InnoDB
+2 Vote Up -0Vote Down

After compiling Percona Server with TokuDB, of course I wanted to compare InnoDB performance vs TokuDB.
I have a particular workload I’m interested in testing – it is an insert-intensive workload (which is TokuDB’s strong suit) with some roll-up aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), so it will produce all good amount of reads.

A few words about the hardware: I am going to use new the Dell PowerEdge R420 with two Intel(R) Xeon(R) CPU E5-2450 0 @ 2.10GHz, 48GB of RAM and SATA SSD: Kingston HyperX 3K 240 GB.

Workload: I will use two different schemas. The first schema is from sysbench, and


  [Read more...]
MariaDB/MySQL: Performances of COUNT()
+0 Vote Up -0Vote Down

Versione italiana

How fast is COUNT() execution? Well, it depends from the Storage Engine.

Try to create an Aria or MyISAM table, INSERT some data, and execute an EXPLAIN similar to the following:

MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM test.t1;
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |
  [Read more...]
Percona XtraBackup 2.0.7 for MySQL available for download
+0 Vote Up -0Vote Down

Percona XtraBackup 2.0.7 was released May 6.

Percona is glad to announce the release of Percona XtraBackup 2.0.7 for MySQL on May 6, 2013. Downloads are available from our download site here and Percona Software Repositories. Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

This release is the current GA (Generally Available) stable release in the 2.0

  [Read more...]
InnoDB Tidbit: The doublewrite buffer wastes 32 pages (512 KiB)
+2 Vote Up -0Vote Down

In my ongoing quest to completely understand InnoDB’s data storage, I came across a quite small and inconsequential waste, which is nevertheless fun to write about. I noticed the following block of pages which were allocated very early in the ibdata1 system tablespace but apparently unused (unnecessary lines removed from output):

$ innodb_space -f ibdata1 space-page-type-regions

start       end         count       type                
13          44          32          ALLOCATED           

Background on the doublewrite buffer

Most people using InnoDB have heard of the “doublewrite buffer”—part of InnoDB’s page flushing strategy. The doublewrite buffer is used as a “scratch area” to write (by

  [Read more...]
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...]
    MySQL: Every detail matters
    +0 Vote Up -0Vote Down
    Some bugs can have a high impact if it causes data corruption, security issues or simply causes MySQL to crash. But what about small bugs and small usability issues?

    This entry from the MySQL 5.6.8 release notes is an interesting one:
    InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of


      [Read more...]
    Instant InnoDB
    +1 Vote Up -0Vote Down
    I've reviewed the Instant InnoDB book. It's a good starting point if you're learning about InnoDB, which is used for most MySQL installations.
    As a consultant I've seen many situations where some basic InnoDB setting (buffer pool size, log file size, file per table) can have a huge effect on the performance of the database and make it easier to manage.

    The website of the book can be found here.
    Converting InnoDB Tables to MySQL Cluster
    Employee +0 Vote Up -0Vote Down

    If you're considering using MySQL Cluster, you might be tempted to try it out on one of your existing databases. MySQL Cluster 7.3 (available as a development release at the time of this post) supports foreign keys, so it seems sensible to try migrating an InnoDB database with foreign keys to MySQL Cluster.

    What won't work

    For this example, I'm using the world_innodb.sql script available from the MySQL sample databases. It's a simple database with three tables: Country, City, and CountryLanguage. Both City and CountryLanguage have foreign keys referencing the Country table's Code field.

    So, you might try something like this:

    ALTER TABLE Country
      [Read more...]
    InnoDB bugs found during research on InnoDB data storage
    +4 Vote Up -0Vote Down

    During the process of researching InnoDB’s storage formats and building the innodb_ruby and innodb_diagrams projects discussed in my series of InnoDB blog posts, Davi Arnaut and I found a number of InnoDB bugs. I thought I’d bring up a few of them, as they are fairly interesting.

    These bugs were largely discoverable due to the innodb_space utility making important internal information visible in a way that it had never been visible in the past. Using it to examine production tables provided many leads to go on to find the bugs responsible. When we initially looked at a graphical plot of free space by page produced from innodb_space data, we were

      [Read more...]
    How InnoDB accidentally reserved only 1 bit for table format
    +2 Vote Up -0Vote Down

    The MySQL 5.5 (and 5.6) documentation says, in Identifying the File Format in Use:

    “… Otherwise, the least significant bit should be set in the tablespace flags, and the file format identifier is written in the bits 5 through 11. …”

    This is incorrect for any version due to a bug in how the tablespace flags were stored (which caused only 1 bit to be reserved, rather than 6). This was all re-worked in MySQL 5.6, so someone obviously noticed it, but the documentation has been left incorrect for all versions, and the incorrect and misleading code has been left in MySQL 5.5. I filed MySQL Bug #68868 about the documentation.

    File formats and  [Read more...]

    Awesome to see the MySQL Ecosystem Flourishing
    +6 Vote Up -0Vote Down

    I just wanted to take a moment and thank, notice, what ever you want to call it, but share some love with all those and all things MySQL.

    I read the post Let’s Celebrate MySQL 5.6 GA! – MySQL Community Reception by Oracle by Oracle MySQL Group and it got me to thinking of how proud I am of (and proud to be a part of) the whole MySQL Ecosystem.

    We *should* all celebrate MySQL 5.6 GA! I well remember the 3.22 and 3.23 days, and there were many folks before me already using MySQL!!!

    I love to see how it has continued to grow, the ecosystem and all things MySQL, that is. MySQL is better than ever. MariaDB is better than ever. Percona Server is better than ever. You have great Support options with MySQL/Oracle, SkySQL/MariaDB, and Percona as well – not to mention numerous others. I

      [Read more...]
    MySQL 5.6 – InnoDB Memcached Plugin as a caching layer
    +0 Vote Up -0Vote Down

    A common practice to offload traffic from MySQL 5.6 is to use a caching layer to store expensive result sets or objects.  Some typical use cases include:

    • Complicated query result set (search results, recent users, recent posts, etc)
    • Full page output (relatively static pages)
    • Full objects (user or cart object built from several queries)
    • Infrequently changing data (configurations, etc)

    In pseudo-code, here is the basic approach:

    data = fetchCache(key)
    if (data) {
      return data
    }
    data = callExpensiveFunction(params)
    storeCache(data, key)
    return data

    Memcached is a very popular (and proven) option used in production as a caching layer.  While very fast, one major potential shortcoming of memcached is that it is not persistent.  While a common design

      [Read more...]
    How to Build MariaDB 10.0 on Windows from Source Code
    +2 Vote Up -0Vote Down

    I just wanted to share my steps for building MariaDB 10.0 on Windows (10.0.1 in this case):

    cd maria-10.0.1
    mkdir bld
    cd bld
    cmake .. -G "Visual Studio 10"
    cmake --build . --config relwithdebinfo --target package

    That’s it! The zip file is created right there in bld/.

    Hope this helps.

     

    Enabling the Verbose InnoDB Lock Monitor in MariaDB and Percona Server for XtraDB+ and XtraDB
    +0 Vote Up -0Vote Down

    I enabled the InnoDB Lock Monitor in my MariaDB 5.5 instance (using XtraDB+ as the InnoDB – which is the default in MariaDB) and noticed that while the SHOW ENGINE INNODB STATUS was being logged to the error log, it wasn’t logging the “additional” lock information – it just looked like the plain ‘ole INNODB STATUS.

    Long story short, Percona added a new variable so one has better control over what gets logged:

    innodb_show_verbose_locks

    If off (default), then the InnoDB Lock Monitor logs the normal INNODB STATUS, and if enabled, then it logs it with the extended lock information.

    They also created another variable that goes along with this one (and the InnoDB Lock Monitor), which is:

    innodb_show_locks_held

    This

      [Read more...]
    MySQL Workshops: Chicago & London this April
    +0 Vote Up -0Vote Down

    Percona’s Kenny Gryp leads a recent MySQL workshop

    Percona will be in Chicago and London the week of April 8th delivering two 2-day MySQL workshops. For our MPB readers, we are offering a 15% discount. Just use MPB15A when purchasing your tickets to one or both MySQL workshops.

      [Read more...]
    INNODB_SYS_FIELDS vs INNODB_SYS_COLUMNS
    +0 Vote Up -0Vote Down

    In MySQL 5.6 we have two new INFORMATION_SCHEMA tables for InnoDB that are likely going to cause confusion: INNODB_SYS_FIELDS and INNODB_SYS_COLUMNS. You may think these are likely to just be aliases of each other in order to make your life easier. However…

    These are not the same thing. The INNODB_SYS_FIELDS table is all about key columns (fields) of InnoDB indexes, while INNODB_SYS_COLUMNS is about actual columns. This is even more confusing as within the MySQL source code, there is the Field set of objects that manipulate fields (columns) in a row.

    Blegh. I’m glad it’s Friday.

    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...]
    InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!
    +4 Vote Up -1Vote Down

    InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

    This is part 2 in a 3 part series. In part 1, we took a quick look at some initial configuration of InnoDB full-text search and discovered a little bit of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two things will happen; either the results returned from a MyISAM FTS query will be exactly identical to the same query when performed against InnoDB data, OR that the results returned by InnoDB FTS will somehow be

      [Read more...]
    InnoDB Full-text Search in MySQL 5.6 (part 1)
    +0 Vote Up -0Vote Down

    I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many customers for whom the prudent move would be a migration to InnoDB, but due to their use of MyISAM FTS, the idea of a complete or partial migration was, for one reason or another, an impractical solution. So, when FTS for InnoDB was first announced, I thought this might end up being the magic bullet that would help these sorts of customers realize all of the benefits that have been engineered into InnoDB over the past few years and still keep their FTS capability without having to make any significant code

      [Read more...]
    [updated] Free book February returns – Get a copy of the InnoDB Quick Reference Guide
    +0 Vote Up -0Vote Down

    This month is a special month. It’s not because of President’s Day or even the exciting day where we revel in groundhogs. No, this month is special because the free book give-away is happening again. This is where you, the reader, gets to win something free for doing nothing more than posting a comment saying that you want a copy of my recently published book – The InnoDB Quick Reference Guide from Packt Publishing. The book is a great reference for DBAs, PHP, Python, or Perl programmers that integrate with MySQL and want to learn more about the InnoDB database engine.

    So, all you have to do is post a comment here saying that you want a copy and write out a single (or more) sentence about how you use InnoDB in your

      [Read more...]
    About MySQL 5.6
    +14 Vote Up -2Vote Down

    I am very excited and thrilled to use the latest release of MySQL 5.6 in production. This is probably the most notable and innovative release in many years, if not ever.

    During the last year, we had the chance to work with many new features and test the fixes to old issues. To be honest, I was expecting to have MySQL 5.6 GA before now, and I even wagered with my colleague Francisco that it would be out before the end of 2012. It was nothing special, just a beer in the Santa Clara Hyatt lounge. Unfortunately for me, MySQL 5.6 is now in GA and given that it happened in 2013, I lost the bet and now have to pay for that beer. But I have also lost the full list of things that we saw as relevant, interesting, or really innovative for MySQL.

    So I took a step back, took some time, and reviewed what Oracle delivered in this new MySQL release.

    Short premise

    Oracle developer teams

      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 733 Next 30 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.