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 中文
Showing entries 1 to 30 of 90 Next 30 Older Entries

Displaying posts with tag: partitioning (reset)

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 TokuDB. Note that these best practices are designed for TokuMX and TokuDB only, which

  [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 TokuDB or TokuMX. Nevertheless,

  [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.
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...]
    MySQL 5.6 features for NoSQL, Big Data and the Cloud
    +2 Vote Up -0Vote Down

    Download PDF Presentation

    At the recent MySQL Tech Tour Events in New York and Boston I gave a presentation on MySQL integration with NoSQL,Big Data and the Cloud.

    This covered discussion on topics including:

    • Memcached API for InnoDB
    • InnoDB Online Alter
    • InnoDB Full Text Search (FTS)
    • Partitioning inprovements for import/export
    • SSD Optimisations
    • Replication Improvements
    • And much more …
    Don't forget to check your partitions!
    +3 Vote Up -1Vote Down
    As it's the end of the year it might be time to check your partition definitions.

    If you forget to add a new partition in time partitions with no MAXVALUE might start to throw errors:
    mysql> create table nye (`event_id` int not null auto_increment,`edate` year(4) not null, description varchar(200), 
        -> primary key(`event_id`,`edate`)) 
        -> partition by range( edate ) (
        -> partition p2010 VALUES LESS THAN (2011), 
        -> partition p2011 VALUES LESS THAN (2012), 
        -> partition p2012 VALUES LESS THAN (2013) );
    Query OK, 0 rows affected (0.04 sec)

    mysql> INSERT INTO nye(edate,description) VALUES('2010','twenty ten'); 
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO nye(edate,description)







      [Read more...]
    common_schema 1.2: security, partition management, processes, QueryScript goodies
    +1 Vote Up -0Vote Down

    common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies:

    • Account blocking
    • Security audit
    • RANGE partition management
    • Slave status
    • Better blocking and idle transaction management
    • QueryScript goodies:
      • echo, report
      • while-otherwise statement; foreach-otherwise statement
      • Better variable scope handling
      • Complete support for variable expansion
      • Transaction support within QueryScript
    • More summary info and SQL statements in processlist-related views

    A closer look at these follows:

    Account blocking

    A new view called

      [Read more...]
    Successfully automate MySQL systems using MySQL Replication and Partitioning
    +0 Vote Up -0Vote Down
    A Pattern for a Newly Hired DBA? I don’t think this experience is unique. It has been shared repeatedly among those starting a job as a DBA (database administrator) at a new company, especially when the organization has never had a dedicated DBA. The conversation usually goes something like this: – “Welcome aboard <insert name here>! Here [...]   [Read more...]
    Your magical RANGE partitioning maintenance query
    +0 Vote Up -0Vote Down

    If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the "next" partition? How and when do you drop your older partitions?

    Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):

    mysql> SELECT * FROM sql_range_partitions \G
    *************************** 1. row
      [Read more...]
    Scale Up, Partitioning, Scale Out
    +1 Vote Up -0Vote Down
    On the 8/16 I conducted a webinar titled: "Scale Up vs. Scale Out" (http://www.slideshare.net/ScaleBase/scalebase-webinar-816-scaleup-vs-scaleout):


    ScaleBase Webinar 8.16: ScaleUp vs. ScaleOut from ScaleBase
    The webinar was successful, we had many attendees and great participation in questions and answers throughout the session and in the end. Only after the webinar it only occurred to me that one specific graphic was missing from the webinar deck. It was occurred to me after answering



      [Read more...]
    The Query Cache and Partitions
    +3 Vote Up -0Vote Down

    Like others we were not satisfied with the fix for a bug in MySQL which caused the query cache and partitioning to not work reliably together. The bug, in simple terms, was that if the query cache was enabled and you used partitioned tables and if a partitioned table was using a transactional engine like InnoDB or XtraDB, the query cache could, under certain circumstances, return incorrect results.

    Returning incorrect results is a definite, high-priority bug. However, the upstream fix was to disable all caching of queries from partitioned tables. We wanted a better solution

      [Read more...]
    Heads up! No more query cache for partitioned tables as of MySQL 5.5.23.
    +9 Vote Up -0Vote Down

    A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me.

    As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables!

    This is a "fix" for bug #53775.

    At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository.

    read more

    Overlooked MySQL 5.6 New Features – WL#5217
    Employee +7 Vote Up -0Vote Down

    There’s a lot of great new features in MySQL 5.6 DMRs – almost too many to keep track of.  And while a lot of (justified) attention is given to the headline-grabbing features of 5.6 (memcached APIs! global transaction ids! improved PERFORMANCE_SCHEMA!), I’m often curious about the new features that don’t make as big a splash.  I thought I would look at one such new feature – WorkLog #5217.  I’m not telling you what this WorkLog is yet; test your knowledge of 5.6 features by seeing if you can figure it out from the following scenario.  Imagine the following table data:

    mysql> SELECT * FROM p;
    +------+------+
    | a    | b    |
    +------+------+
    |    1 |    1 |
    |   11 |    1 |
    |   21 |    1 |
    +------+------+
    3 rows in set (0.00 sec)

    OK, try to write an UPDATE statement that

      [Read more...]
    Benchmarking single-row insert performance on Amazon EC2
    +0 Vote Up -0Vote Down

    I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.

    OK, let’s start off with the configuration details.

    Configuration

    First of all let me describe the EC2 instance type that I used.

    EC2 Configuration

    I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.

    High-Memory Quadruple Extra Large
      [Read more...]
    MySQL Partitioning and its Confusing Syntax
    +0 Vote Up -2Vote Down

    While looking at partitioning I recently made a mistake which I guess can happen to others. Often this is due to not fully reading the documentation or scanning it too quickly and misunderstanding what’s being said.

    So this post is to complain about the MySQL partitioning syntax and to warn others who may easily make the same mistake without realising.

    First we probably need to ask why we are partitioning a table in the first place. The main reasons for this are I think:

    • to improve query performance
    • to reduce individual .ibd file sizes for large tables (if using innodb_file_per_table)

    In my case I wanted to do both. I had a several tables which store a large number of rows (batches of data) based on an incremental batch number. One of these tables was around 40 GB and had about 500,000,000 rows in it.  When processing

      [Read more...]
    Proper handling of insert-mostly, select-recently datasets
    +3 Vote Up -0Vote Down
    Some kinds of large tables such as chat messages, blog entries, etc have the following characteristics.

    * huge number of records, huge data and index size
    * insert and select mostly
    * select from only recent data
    * select by secondary index (i.e. user_id)
    * secondary index entries are inserted in random order

    What are optimal ways to handle these tables? The below single large table does not perform well.
    CREATE TABLE message (
    message_id BINGINT UNSIGNED PRIMARY KEY,
    user_id INT UNSIGNED,
    body VARCHAR(255),
    ...
    created DATETIME,
    INDEX(user_id)
    ) ENGINE=InnoDB;

    The cause of poor performance is a secondary index on user_id. user_id is inserted in random order. Index size grows, and sooner or later it will exceed RAM size. Once index size on user_id exceeds RAM size,















      [Read more...]
    Partitioning the WordPress Comments Table
    Employee +4 Vote Up -0Vote Down

    WordPress sites can get big. Really big. When you’re looking at a site of Cheezburger, Engadget or Techcrunch proportions, you get hundreds of comments per post, on dozens of posts per day, which adds up to millions of comments per year.

    In order to keep your site running in top condition, you don’t want to be running queries against tables with lots of rarely accessed rows, which is what happens with most comments – after the post drops off the front page, readership drops, so the comments are viewed much less frequently. So, what we want to do is remove these old comments from the primary comment table, but keep them handy, for when people read the archives.

    Enter partitioning.

    The idea of MySQL partitioning is that it splits tables up into multiple logical tablespaces, based on your criteria. Running a query on a single partition of a

      [Read more...]
    Replication and sandbox talks on the road - San Francisco, Santa Clara, Orlando
    +3 Vote Up -1Vote Down
    In a few weeks, I will be on the road, for an intense set of presentations in the USA.

    San Francisco, April 7th

    I will start the tour at the San Francisco MySQL User Group. On April 7, at 6pm I will talk about Advanced MySQL replication for the masses. This talk will explore topics such as bi-directional replication, multiple sources, parallel replication, seamless failover, with the help of Tungsten replicator.




    Santa Clara, April 11, 12

    The MySQL conference starts on Monday, April 11th, with several three-hour tutorials.
    • April 11, 9:00am








      [Read more...]
    MySQL Partitioning: A Flow Chart
    +2 Vote Up -4Vote Down

    In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.

    Here goes:

  • Spindle contention? In other words, are you partitioning in order to spread your query work load across many disks? I’ve yet to see a compelling
  •   [Read more...]
    Partitioning, Free Lunches, & Indexing, Part 2
    +0 Vote Up -0Vote Down

    Review

    In part one, I presented a very brief and particular view of partitioning. I covered what partitioning is, with hardly a mention of why one would use partitioning. In this post, I’ll talk about a few use cases often cited as justification for using partitions.

    Lots of disks → Lots of partitioning of tables

    One use case for justifying partitions is that each partition can be placed on a separate disk to avoid spindle contention. I have to say that on this one, I agree with Kevin Burton, who makes the point that if you want to distribute I/O load across several disks, you can use a RAID configuration on the disks. In this case, he says that partitioning is not

      [Read more...]
    Data Warehousing Best Practices: Comparing Oracle to MySQL pt 2
    +5 Vote Up -3Vote Down

    At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

    See part 1 for the introduction and talking about power and hardware. This part will go over the 2nd “P”, partitioning. Learning about Oracle’s partitioning has gotten me more interested in how MySQL’s partitioning works, and

      [Read more...]
    Using MySQL Partitioning Instead of MERGE Tables
    +8 Vote Up -0Vote Down

    One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

    First, a simple table, not partitioned (yet):

    use test;
    DROP TABLE IF EXISTS my_part;
    CREATE TABLE IF NOT EXISTS my_part (
      id int NOT NULL,
      creationDate datetime NOT NULL,
      PRIMARY KEY (id,creationDate)
    ) ENGINE=InnoDB;
    

    In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.

    Now, add the partition definition. This can be done in the


      [Read more...]
    Welcome googleCL
    +7 Vote Up -0Vote Down
    I am writing this blog post with Vim, my favorite editor, instead of using the online editor offered by blogger. And I am uploading this post to my Blogger account using Google CL a tool that lets you use Google services from the command line.
    I am a command line geek, and as soon as I saw the announcement, I installed it in my laptop. The mere fact that you are reading this blog post shows that it works.
    GoogleCL is an apparently simple application. If you install it on Mac using macports you realize how many dependencies it has and how much complexity it gives under the hood.
    Using


      [Read more...]
    I wrote a new tool that runs aggregation queries over MySQL sharded databases using Gearman.
    +2 Vote Up -1Vote Down
    I created a new tool this week:
    http://code.google.com/p/shard-query

    As the name Shard-Query suggests, the goal of the tool is to run a query over multiple shards, and to return the combined results together as a unified query. It uses Gearman to ask each server for a set of rows and then runs the query over the combined set. This isn't a new idea, however, Shard-Query is different than other Gearman examples I've seen, because it supports aggregation.

    It does this by doing some basic query rewriting based on the input query.

    Take this query for example:
    select c2, 
           sum(s0.c1), 
           max(c1) 
     from t1 as s0 
     join t1 using (c1,c2) 
     where c2 = 98818 
     group by c2;
    


    The tool will split this up into two queries.

    This first query will be sent to each shard. Notice











      [Read more...]
    Two quick performance tips with MySQL 5.1 partitions
    +13 Vote Up -1Vote Down
    While I was researching for my partitions tutorial, I came across two hidden problems, which may happen often, but are somehow difficult to detect and even more difficult to fix, unless you know what's going on, and why. I presented both cases during my tutorial, but there were no pictures to convey the mechanics of the problem. Here is the full story.

    TO_DAYS() prunes two partitions instead of one


    If you are partitioning by date, chances are that you are using TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you

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