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

Displaying posts with tag: innodb (reset)

Innodb transaction history often hides dangerous ‘debt’
+1 Vote Up -0Vote Down

In many write-intensive workloads Innodb/XtraDB storage engines you may see hidden and dangerous “debt” being accumulated – unpurged transaction “history” which if not kept in check over time will cause serve performance regression or will take all free space and cause an outage. Let’s talk about where it comes from and what can you do to avoid running into the trouble.

Technical Background: InnoDB is an MVCC engine which means it keeps multiple versions of the rows in the database, and when rows are deleted or updated they are not immediately removed from the database but kept for some time – until they can be removed. For a majority of OLTP workloads they can be removed seconds after the change actually took place. In some cases though they might need to be kept for a long period of time

  [Read more...]
InnoDB: Supporting Page Sizes of 32k and 64k
Employee_Team +1 Vote Up -0Vote Down

In the new InnoDB lab release we support page sizes of 32k and 64k. This gives users even more choices on the page size, allowing you to further customize InnoDB for your particular workload.

There are some things worthy of note related to this new feature:

  • The extent size changes when the innodb_page_size is set 32k or 64k.
    The extent size is 2M for 32k page sizes, and 4M for 64k page sizes (the extent size is 1M for 4k, 8k, and 16k page sizes). If we do not enlarge the extent size then we will have too many extent headers on the allocation bitmap page, and the bitmap page will overflow.
  • The

  •   [Read more...]
    Recover orphaned InnoDB partition tablespaces in MySQL
    +0 Vote Up -0Vote Down

    A few months back, Michael wrote about reconnecting orphaned *.ibd files using MySQL 5.6. I will show you the same procedure, this time for partitioned tables. An InnoDB partition is also a self-contained tablespace in itself so you can use the same method described in the previous post.

    To begin with, I have an example table with a few orphaned partitions and we will reconnect each partition one by one to the original table.

    mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t1 G
    *************************** 1. row ***************************
    Table: t1
    Create Table: CREATE TABLE `t1` (
    [...]
    KEY `h_date` (`h_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50100 PARTITION BY RANGE (year(h_date))
    (PARTITION p0 VALUES LESS THAN
      [Read more...]
    InnoDB General Tablespaces – Preview
    Employee_Team +1 Vote Up -0Vote Down

    The new InnoDB Labs release contains the ability to create and use independent multi-table general tablespaces.

    This feature will provide a way to group tables together into tablespaces at a location and filename of your choosing.  Tables using row formats of Redundant, Compact, and Dynamic can be combined together into the same general tablespace. Compressed tables with the same key_block_size can also be combined together.

    The SQL syntax for creating an empty general tablespaces is:

    CREATE TABLESPACE `tblspace_name` ADD DATAFILE 'tablespace.ibd' [FILE_BLOCK_SIZE=n];

    The filename can contain an


      [Read more...]
    MySQL compression: Compressed and Uncompressed data size
    +1 Vote Up -0Vote Down

    MySQL has information_schema.tables that contain information such as “data_length” or “avg_row_length.” Documentation on this table however is quite poor, making an assumption that those fields are self explanatory – they are not when it comes to tables that employ compression. And this is where inconsistency is born. Lets take a look at the same table containing some highly compressible data using different storage engines that support MySQL compression:

    TokuDB:

    mysql> select * from information_schema.tables where table_schema='test' G
    *************************** 1. row ***************************
    TABLE_CATALOG: def
    TABLE_SCHEMA: test
    TABLE_NAME: comp
    TABLE_TYPE: BASE TABLE
    ENGINE: TokuDB
    VERSION: 10
      [Read more...]
    Testing the Fastest Way to Import a Table into MySQL (and some interesting 5.7 performance results)
    +2 Vote Up -0Vote Down

    As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I

      [Read more...]
    New InnoDB Memcached Functionality in 5.7 Lab Release
    Employee_Team +1 Vote Up -0Vote Down

    InnoDB Memcached’s read only query performance in 5.7 has already achieved a remarkable 1.1 million QPS record. Now, the read only query bottleneck shifts to the memcached client itself. Thus anything that can batch the queries and shorten the query strings helps.

    Multiple Get

    In the new 5.7 InnoDB Lab Release, we add support for the “multi-get” option within InnoDB Memcached. This functionality enables users to send multiple keys in a single “get” command. In this way, for client-server communication, the package size is reduced as multiple keys are package within a single “get” call. For InnoDB, it

      [Read more...]
    InnoDB Native Partitioning – Early Access
    Employee_Team +0 Vote Up -0Vote Down

    The InnoDB labs release includes a snapshot of the InnoDB Native Partitioning feature.

    To better understand why we implemented this, we need to start with some background on tables, storage engines, and handlers. In MySQL an open instance of a table has a handler object as an interface to the table’s storage engine. For a partitioned table there is a main table handler that implements the partitioning feature, but for storage, each partition has its own handler. This worked fairly well, but the more partitions you had the more overhead from the per partition handlers. So to remove this overhead for partitioned InnoDB tables we’re introducing Native Partitioning support! This means a new InnoDB partitioning aware handler, so that we have a single handler object for a partitioned table and not one handler object per

      [Read more...]
    Visualizing the impact of ordered vs. random index insertion in InnoDB
    +3 Vote Up -0Vote Down

    [This post refers to innodb_ruby version 0.9.11 as of October 2, 2014.]

    Many DBAs know that building indexes in “random” order (or really any order that greatly differs from ordered by key) can be much less efficient. However, it’s often hard to really understand why that is. With the “-illustrate” visualization modes available in innodb_ruby, it’s possible to quite easily visualize the structure of indexes. The space-lsn-age-illustrate mode to innodb_space allows visualization of all pages in a space file by “LSN age”, generating something like a heatmap of the space file based on how recently each page was modified.

    (Note that a

      [Read more...]
    Online Truncate of InnoDB UNDO Tablespaces
    Employee_Team +1 Vote Up -0Vote Down

    We have received a lot of requests from our user community regarding the ability to  truncate UNDO tablespaces (‘truncate’ here means that the size of the tablespace is reset back to what it was when it was first created). We are happy to say that we’ve now been able to implement this anticipated feature.

    Introduction

    The InnoDB UNDO tablespace(s) host rollback segments that hold rollback information related to database changes. This information is used to rollback a transaction and to retrieve the previous version of a record that has been updated or deleted for multi-version concurrency control (MVCC). Once a transaction is committed, InnoDB will discard the related UNDO log records. UNDO log records for updates or deletes will be kept around as long as there exists an open transaction that may access older versions of the records. When all

      [Read more...]
    Showing entries 1 to 10 of 773 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.