Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 121 to 130 of 763 10 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...]
    10 Newer Entries Showing entries 121 to 130 of 763 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.