Showing entries 31 to 40 of 113
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: partitioning (reset)
Fun with Bugs #14 - InnoDB in MySQL 5.6

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:

  1. 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. Check this bug for the details and maybe you'll find …
[Read more]
The MERGE storage engine: not dead, just resting…. or forgotten.

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 and been replaced by it. It wasn’t.

It is another MySQL feature that exists likely due to customer demand at the time. It’s not a complete solution by any means, PARTITIONING is way more complete and …

[Read more]
MySQL 5.6 features for NoSQL, Big Data and the Cloud

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!

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) VALUES('2011','twenty eleven');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO …
[Read more]
common_schema 1.2: security, partition management, processes, QueryScript goodies

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

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

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 ***************************
            table_schema: test
              table_name: city …
[Read more]
Scale Up, Partitioning, Scale Out

On the 8/16 I conducted a webinar titled: "Scale Up vs. Scale Out" (

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 several audience questions about "the difference between …

[Read more]
The Query Cache and Partitions

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 …

[Read more]
Heads up! No more query cache for partitioned tables as of MySQL 5.5.23.

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]
Showing entries 31 to 40 of 113
« 10 Newer Entries | 10 Older Entries »