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 756 Next 30 Older Entries

Displaying posts with tag: innodb (reset)

Fun with Bugs #19 - waiting for MySQL 5.6.13 and some real fun?
+0 Vote Up -0Vote Down
I feel like MySQL 5.6.12 was released ages ago, while in reality it was on June 3, less than 2 months ago. No wonder I feel so, after writing several posts about bugs fixed and not fixed in it... Anyway, we still have to wait for MySQL 5.6.13 for a week or even two probably and in the meantime I decided to write new post for this series based on good old idea of making a digest of my recent bugs-related posts at Facebook. I know, it's boring and annoying (same as waiting for the release of 5.6.13).

Let's start with Bug #69846 - "ICP does not work on UNIQUE indexes". Based on my quick tests presented there I'd say that ICP (

  [Read more...]
How InnoDB works with transactions and auto recovery
+0 Vote Up -0Vote Down
How InnoDB work with transactions: When any transaction will be completed with COMMIT,  InnoDB will write those changes in InnoDB Buffer Pool. After that InnoDB will run some background operations like checkpoint.  Checkpoint is the most important operation which will Continue reading →   [Read more...]
Comparing MongoDB, MySQL, and TokuMX Data Layout
+2 Vote Up -0Vote Down

A lot is said about the differences in the data between MySQL (http://www.mysql.com/) and MongoDB. Things such as “MongoDB is document based”, “MySQL is relational”, “InnoDB has a clustering key”, etc.. Some may wonder how TokuDB, our MySQL storage engine, and TokuMX, our MongoDB product, fit in with these data layouts. I could not find anything describing the differences with a simple google search, so I figured I’d write a post explaining how things compare.

So who are the players here? With MySQL, users are likely familiar with two storage engines: MyISAM, the original default up until MySQL 5.5, and

  [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...]
Fun with Bugs #15 - Recent News and Hawthorne Effect Studies
+0 Vote Up -0Vote Down
Let me present a quick review of new and recently verified MySQL bug reports (mostly in 5.6.12, but not only). Surely I have to start with this request that many my colleagues had already mentioned in their blogs:

Bug #69558 - Put *all* know bugs into the public bug tracking system at bugs.mysql.com. We may argue on how and when this should apply to "security" bugs, but automatic bi-directional replication (even if delayed) with Oracle internals bugs database is what I was also asking for since we were forced to use it. Click on "Affects Me" button there and let's hope that some day Oracle will publish list of bugs that affect most of community users and may even try to take this into account while making decisions.

I have good news for everybody who was following MySQL 5.6



  [Read more...]
Fun with Bugs #14 - InnoDB in MySQL 5.6
+2 Vote Up -1Vote Down
InnoDB improvements in MySQL 5.6 are well known. One of the key reasons to upgrade to MySQL 5.6 for most users is to get the benefits of improved performance, scalability, new monitoring features and fulltext indexes support in InnoDB.

Is there anything to double check before assuming that InnoDB in MySQL 5.6 is just better than any older version for any practical purposes? Let's review known public InnoDB-specific bug reports. Here is my "Top 10" list, as of MySQL 5.6.12, starting with most recent reports:

  • Bug #69424  - maybe I miss something (I am not the only one though), but I see no way to continue using raw devices (on Linux at least) to store InnoDB data. You had working raw device in 5.5.32, then you upgrade to 5.6.12 and just can not start MySQL any more.




  •   [Read more...]
    Comprehensive How-To for Enabling the Standard InnoDB Plugin in MariaDB and MySQL
    +4 Vote Up -0Vote Down

    I’m always switching back-and-forth between the 2 different InnoDB flavors in MariaDB – XtraDB+ and the standard InnoDB plugin, so I thought I’d simply post all of the various combinations in a single place. (And then I cover enabling the InnoDB Plugin in MySQL, since it’s an option in 5.1.) [Addition: Thanks to Andrew and Sergei for the tips on shortening plugin-load=. The changes are reflected below.]

    Note: Below is for Windows. For Linux, simply change “.dll” to “.so” where appropriate.

    MariaDB 10.0:

    Do not add anything, as the standard InnoDB plugin is the current default (as of 10.0.3, although I do anticipate this changing in the near future, and I’ll update the post accordingly when that happens).

    MariaDB 5.5:

    # Enable the 2 below to disable XtraDB+ and enable the standard InnoDB
      [Read more...]
    Fun with Bugs #10 - recently reported bugs affecting MySQL 5.6.12
    +0 Vote Up -0Vote Down
    MySQL 5.6.12 is available to community for more than a week already, so people started to test and use it. And, no wonder, new bug reports started to appear. Let's concentrate on them in this issue.

    I'd like to start with a funny one.  Bug #69413 had scared some of my Facebook readers to death, as we see kernel mutex mentioned clearly in the release notes for 5.6.12. What, kernel mutex comes back again? No, it's just a result of null merge and, probably, copy/paste from the release notes for 5.5.32.

    It seems recent bug reports for 5.6.12 are mostly related to small details



      [Read more...]
    Easy SELECT COUNT(*) with split()
    +0 Vote Up -0Vote Down

    The two conservative ways of getting the number of rows in an InnoDB table are:

    • SELECT COUNT(*) FROM my_table:
      provides with an accurate number, but makes for a long running transaction which take ages on large tables. Long transactions make for locks
    • SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='my_schema' AND TABLE_NAME='my_table', or get same info via SHOW TABLE STATUS.
      Gives immediate response, but the value can be way off; it can be two times as large as real value, or half the value. For query execution plans this may be a "good enough" estimation, but typically you just can't trust it for your own purposes.

    Get a good estimate using chunks

    You can get a good estimate by calculating the total number of rows in steps. Walk the table 1,000 rows



      [Read more...]
    Converting compressed InnoDB tables to TokuDB 7.0.1
    +1 Vote Up -0Vote Down

    Or: how to make it work in TokuDB version 7.0.1. This is a follow up on a discussion on the tokudb-user group.

    Background

    I wanted to test TokuDB's compression. I took a staging machine of mine, with production data, and migrated it from Percona Server 5.5 To MariaDB 5.5+TokuDB 7.0.1. Migration went well, no problems.

    To my surprise, when I converted tables from InnoDB to TokuDB, I saw an increase in table file size on disk. As explained by Tim Callaghan, this was due to TokuDB interpreting my compressed table's "KEY_BLOCK_SIZE=4" as an instruction for TokuDB's page size. TokuDB should be using 4MB block size, but thinks it's being instructed to use

      [Read more...]
    Introduction to Transaction Locks in InnoDB Storage Engine
    Employee_Team +5 Vote Up -0Vote Down

    Introduction

    Transaction locks are an important feature of any transactional storage engine. There are two types of transaction locks – table locks and row locks. Table locks are used to avoid a table being altered or dropped by one transaction when another transaction is using the table. It is also used to prohibit a transaction from accessing a table, when it is being altered. InnoDB supports multiple granularity locking (MGL). So to access rows in a table, intention locks must be taken on the tables.

    Row locks are at finer granularity than table level locks, different threads can work on different parts of the table without interfering with each other. This is in contrast with MyISAM where the entire table has to be locked when updating even unrelated rows. Having

      [Read more...]
    Bulk insert into tables in sorted order to avoid deadlocks
    +2 Vote Up -0Vote Down

    Shard-Query inserts data into a “coordinator” table when answering queries.   When there is a GROUP BY on the original query, the coordinator table contains a UNIQUE KEY over the GROUP BY attributes.   Shard-Query uses INSERT .. ON DUPLICATE KEY UPDATE in combination with bulk insert (insert into … values (),(),() ) when inserting into the table.

    For what would normally be efficiency sake, Shard-Query sends queries to the shards using ORDER BY NULL which disables the filesort operation. Of course, this often results in the rows being sent back from the shards in random order.

    Because the results are in random order, the bulk insertion that the worker does into the coordinator table can deadlock with other worker threads when using InnoDB or TokuDB as the coordinator table. Right now I’ve just been using MyISAM for the

      [Read more...]
    Optimizing MIN and MAX MySQL Functions
    +1 Vote Up -0Vote Down

    MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:

    CREATE TABLE `history` (
       `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
       `u_id` int(10) unsigned NOT NULL,
       `cn_id` int(10) unsigned NOT NULL,
       `f_id` int(10) unsigned NOT NULL
       PRIMARY KEY (`h_id`)
     ) ENGINE=InnoDB

    If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:

    mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G
     *************************** 1. row ***************************
                id: 1
       select_type: SIMPLE
             table: history
      [Read more...]
    Shard-Query 2.0 performance on the SSB with InnoDB on Tokutek’s MariaDB distribution
    +2 Vote Up -0Vote Down
    Scaling up a workload to many cores on a single host

    Here are results for Shard-Query 2.0 Beta 1* on the Star Schema Benchmark at scale factor 10.  In the comparison below the “single threaded” response times for InnoDB are the response times reported in my previous test which did not use Shard-Query.

    Shard-Query configuration

    Shard-Query has been configured to use a single host.  The Shard-Query configuration repository is stored on the host.  Gearman is also running on the host, as are the Gearman workers.  In short, only one host is involved in the testing.

    The

      [Read more...]
    TokuDB vs Percona XtraDB using Tokutek’s MariaDB distribution
    +1 Vote Up -0Vote Down

    Following are benchmark results comparing Tokutek TokuDB and Percona XtraDB at scale factor 10 on the Star Schema benchmark. I’m posting this on the Shard-Query blog because I am going to compare the performance of Shard-Query on the benchmark on these two engines. First, however, I think it is important to see how they perform in isolation without concurrency.

    Because I am going to be testing Shard-Query, I have chosen to partition the “fact” table (lineorder) by month. I’ve attached the full DDL at the end of the post as well as the queries again for reference.

    I want to note a few things about the results:
    First and foremost, TokuDB was configured to use quicklz compression (the default) and InnoDB compression was not used. No tuning of TokuDB was performed, which means it will use up to 50% of memory by

      [Read more...]
    MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency
    +1 Vote Up -0Vote Down

    This blog post is part two in what is now a continuing series on the Star Schema Benchmark.

    In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using only the InnoDB storage engine.  In my testing I discovered that innodb_old_blocks_time had an effect on performance of the benchmark.  There was some discussion in the comments and I promised to follow up with more SSB tests at a later date.

    I also promised more low concurrency SSB tests when Peter blogged about the importance of performance at low concurrency.

    The SSB
    The SSB tests a

      [Read more...]
    Calculating the InnoDB free space - part 2
    +0 Vote Up -0Vote Down
    This is part 2, you can find part 1 here.

    So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

    The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
    This is Bug #36312.

    Example:
    mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM









      [Read more...]
    Calculating the InnoDB free space
    +2 Vote Up -0Vote Down
    Recently someone asked my if it's possible to find the total free space within InnoDB. I thought this would be very easy as the INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we could just use SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES couldn't we?
    &nbsp
    So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
    &nbsp
    The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
    &nbsp
    If innodb_file_per_table was always disabled then this query probably reports the correct free space:
    SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;

    This is







      [Read more...]
    The EXAMPLE storage engine
    +2 Vote Up -0Vote Down

    The Example storage engine is meant to serve mainly as a code example of the stub of a storage engine for example purposes only (or so the code comment at the start of ha_example.cc reads). In reality however, it’s not very useful. It likely was back in 2004 when it could be used as a starting point for starting some simple new engines (my guess would be that more than a few of the simpler engines started from ha_example.cc).

    The sad reality is the complexity of the non-obviousness of the bits o the storage engine API you actually care about are documented in ha_ndbcluster.cc, ha_myisam.cc and ha_innodb.cc. If you’re doing something that isn’t already done by one of those three engines: good luck.

    Whenever I looked at ha_example.cc I always wished there was something

      [Read more...]
    The ARCHIVE Storage Engine
    +5 Vote Up -0Vote Down

    I wonder how much longer the ARCHIVE storage engine is going to ship with MySQL…. I think I’m the last person to actually fix a bug in it, and that was, well, a good number of years ago now. It was created to solve a simple problem: write once read hardly ever. Useful for logs and the like. A zlib stream of rows in a file.

    You can actually easily beat ARCHIVE for INSERT speed with a non-indexed MyISAM table, and with things like TokuDB around you can probably get pretty close to compression while at the same time having these things known as “indexes”.

    ARCHIVE for a long time held this niche though and was widely and quietly used (and likely still is). It has the great benefit of being fairly lightweight – it’s only about 2500 lines of code (1130 if

      [Read more...]
    Delayed row-based replication with large tables lacking a primary key
    +0 Vote Up -0Vote Down
    I configure all our master databases to use row-based binary logging where I work. In my opinion it is a much safer option than statement-based replication. The advantages and disadvantages of both types of MySQL replication are detailed in the online documentation here. You can't view the events a slave is applying directly with 'show processlist' but by issuing 'show open tables where in use' you can detect what table is receiving the attention of the SQL thread. If you need more information the mysqlbinlog command must be used to decode the slaves relay logs or masters binary logs.

    Our developers often change a lot of rows with a single update statement. This usually results in some reasonable replication lag on downstream

      [Read more...]
    Presenting at tomorrow’s Effective MySQL Meetup (New York City)
    +0 Vote Up -1Vote Down

    At tomorrow’s Effective MySQL Meetup, I’ll be presenting “Fractal Tree Indexes : Theory and Practice (MySQL and MongoDB).” The meetup is at 6:30pm Tuesday, May 14, 2013, and will be held at Alley NYC in New York City.

    I’ll give an overview on how Fractal Tree® indexes work, and then get into specific product features that Fractal Trees enable in MySQL and MongoDB.  Some benchmarking and customer use-cases will be discussed, but my intent is for this to be a deep technical dive.  Several Tokutek Engineers will also be on hand, so bring any questions you’ve got.

    I hope to see you there!

    Percona XtraBackup 2.1.0 ‘release candidate’ for MySQL available for download
    +0 Vote Up -0Vote Down

    Percona is glad to announce the release of Percona XtraBackup 2.1.0-rc1 on May 7, 2013. Downloads are available from our download site here. For this RC release, we will not be making APT and YUM repositories available, just base deb and RPM packages

    This is an Release Candidate quality release and is not intended for production. If you want a high-quality, generally available release, the current stable version should be used (currently

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