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

Displaying posts with tag: partitioning (reset)

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...]
Best Practices for Partitioned Collections and Tables in TokuDB and TokuMX
+0 Vote Up -1Vote Down

In my last post, I gave a technical explanation of the performance characteristics of partitioned collections in TokuMX 1.5 (which is right around the corner) and partitioned tables in relational databases. Given those performance characteristics, in this post, I will present some best practices when using this feature in TokuMX or

  [Read more...]
Understanding the Performance Characteristics of Partitioned Collections
+0 Vote Up -0Vote Down

In TokuMX 1.5 that is right around the corner, the big feature will be partitioned collections. This feature is similar to partitioned tables in Oracle, MySQL, SQL Server, and Postgres. A question many have is “why should I use partitioned tables?” In short, it’s complicated. The answer depends on your workload, your schema, and your database of choice. For example, this Oracle related post states “Anyone with un-partitioned databases over 500 gigabytes is courting disaster.” That’s not true for

  [Read more...]
MySQL 5.7.4 now supports Transportable Tablespaces for InnoDB Partitions.
Employee_Team +2 Vote Up -0Vote Down
When InnoDB transportable tablespaces was introduced in 5.6 it did not support partitions since it could be accomplished in combination with EXCHANGE PARTITION. To make it easier to use transportable tablespaces for partitioned tables we added support for DISCARD/IMPORT TABLESPACE for partitioned tables as well as single partitions in 5.7.4.To get a consistent full copy of the tablespace files one use FLUSH TABLES <list of tables> FOR EXPORT
# Flush all dirty pages to the tablespaces and write a .cfg file per tablespace

  [Read more...]
Fun with Bugs #29 - important bug fixes in MySQL 5.6.16
+3 Vote Up -0Vote Down
As you should already know, Oracle had released MySQL 5.6.16 officially on January 31, 2014. You can find all the details in official release notes. In this post I decided to concentrate on important fixes for bugs reported by community in 4 most important categories: InnoDB, replication, partitioning and optimizer.

Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB tables stored in two (again, InnoDB) tables in mysql database may cause various problems, not only bad plans based on outdated statistics there. One of these problems seems solved in 5.6.16, but note that the bug report itself

  [Read more...]
Percona XtraDB Cluster – A Drop-in-place Clustering Solution for MySQL
+0 Vote Up -0Vote Down

Emphasis on clustering solutions comes up quite a lot when talking to customers about High Availability. The reason is because clustering is supposed to provide an easier solution for maintaining high availability and so that you do not have to rely on other tools and techniques outside of the database server. I thought it would be good to share the gist of many of my discussions around clustering, in the form of a blog post. So here I will be doing a high-level comparison between MySQL NDB Cluster and Percona XtraDB Cluster.

The post Percona XtraDB Cluster – A Drop-in-place Clustering Solution for MySQL appeared first on ovais.tariq.

Persistent statistics and partitions
+1 Vote Up -0Vote Down
Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:
  • Create and utilize table partitioning
  • Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.
mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
-> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
-> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name |
+----+--------+
| 4 | test04

















  [Read more...]
Running out of Disk Space? Move innodb-tables to another partition (with MySQL 5.6)
+0 Vote Up -0Vote Down

Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.

Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.

First get the 15 biggest tables:

root@mysqlserver [(none)]> set global innodb_stats_on_metadata=0; select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY
  [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...]
    The MERGE storage engine: not dead, just resting…. or forgotten.
    +0 Vote Up -0Vote Down

    Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE.

    The MERGE engine was basically a multiplexer down to a number of MyISAM tables. They all had to be the same, there was no parallel query execution and it saw fairly limited use. One of the main benefits was that then you could actually put more rows in a MyISAM table than your “files up to 2/4GB” file system allowed. With the advent of partitioning, this really should have instantly gone away

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