Showing entries 1 to 10 of 105
10 Older Entries »
Displaying posts with tag: partitioning (reset)
When order of appearance of indexes matters in MySQL

Sometimes MySQL surprises you in ways you would have never imagined.

Would you think that the order in which the indexes appear in a table matters?
It does. Mind you, not the order of the columns - the order of the indexes.
MySQL optimizer can, in specific circumstances, take different paths, sometimes with nefarious effects.

Please consider the following table:

CREATE TABLE `mypartitionedtable ` (
  `HASH_ID` char(64) NOT NULL,
  `RAW_DATA` mediumblob NOT NULL,

[Read more]
What is the default sharding key in MySQL Cluster?

MySQL Cluster does an automatic sharding/partitioning to the tables across data nodes, enabling databases to scale horizontally to serve read and write-intensive workloads, but what is the default sharding key used in partitioning the data?
According to the recent update (Oct, 2016) of the MySQL Cluster white paper, primary key is the default sharding key:

By default, sharding is based on hashing of the primary key, which generally leads to a more even distribution of data and queries across the cluster than alternative approaches such as range partitioning.

However, that is not the case in all MySQL Cluster versions so far!
In this post, I’ll do some test cases on MySQL Cluster (of 4 datanodes) to confirm the default sharding key.

Testing on MySQL Cluster 7.2.26 …

[Read more]
Partitions number in MySQL Cluster

As stated in the MySQL Cluster documentation:

Partition.  This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster. Each node is responsible for keeping at least one copy of any partitions assigned to it (that is, at least one replica) available to the cluster.

According to my understanding for the previous paragraph, if we have a cluster of 6 datanodes we should have 6 partitions for each NDB table. I claim that this is not true for all cases – at least, after the introduction of ndbmtd (Multi-Threaded Daemon) in MySQL Cluster 7.2 .
In this post, I’ll do some …

[Read more]
MySQL Support Engineer's Chronicles, Issue #3

The original idea of this series was to publish one post per week, but it seems every other week I have some special topic that well deserves a dedicated post. Last week I had no time to complete my writing because of long (and, I hope, useful) Howto post on replacing corrupted partition using non-corrupted one from other server in replication setup. But I had links and notes collected in a draft that I am going to complete now.

First of all, during the previous week I had time to submit two more talks for the  …

[Read more]
How to Recover Corrupted InnoDB Partition Tablespace in Replication Setup

This week I've got a question that sounded basically like this:
"Is it possible to just copy the entire partition from the replicated server?"Let me share some background story. As it happens sometimes, user had a huge table with many partitions, let's say hundreds of gigabytes in size each, and one of them got unfortunately corrupted. It happened in a replication setup on master, but lucky they were, they had used innodb_file_per_table=1 and they had a slave that was more or less in sync with master. This allowed to reconfigure replication and continue to work, but the task remained to eventually put master back in use and get correct data in the corrupted partition. Let's assume that dumping and reloading data from one of instances in replication setup is not a desired option, as it will take too much time comparing to just copying the partition tablespace file. Hence the question above...
Side note: Let's assume …

[Read more]
MySQL Support Engineer's Chronicles, Issue #2

It's time to continue my new series that I've started 2 weeks ago. I'd like to start with a reminder that it's time to send your talks for "MySQL and Friends Devroom" at FOSDEM 2017 - the only MySQL-related event next year that I plan to attend in any case. It seems we have one more week to submit, but I've already filled in all the details for the "main" talk, "Understanding MyRocks locks and deadlocks". I'd like to apply my usual source code reading and gdb breakpoints approach in case if by the end of January, 2017 official documentation still misses important details. Official MySQL …

[Read more]
If enforcing data retention via DROP PARTITION, better watch your auto_increment counter

One of the main tasks that any DBA has to deal with is certainly data retention. Especially when your developers like to store logging and tracking information inside the database. This is not something that I would recommend (don't try it at home!) but when you're late to the party (read: someone had taken this path before you joined) , then you'll have to live with it.

Data retention in MySQL is usually applied by partitioning the table by RANGE on a selected column, usually containing the row creation timestamp. New partitions are created in advance for the coming days or weeks or months, and a script is used that will run nightly and drop partitions that are older than a set retention.

There are many tools that can be used to automate data retention; I have chosen the excellent pdb-parted, a nice and cozy perl script that you can find in the PalominoDB repository on GitHub (since PalominoDB is no longer in existence, …

[Read more]
Fun with Bugs #41 - Bugs Fixed in MySQL 5.7.11

I've just noted that Oracle had released new versions of MySQL on February 5, 2016 formally, so while these days I am mostly thinking about the ways to do support properly, remembering my colleagues and trying to understand some of RocksDB internals, it's time to postpone all these and write about bugs again. This time about some of the public bug reports from MySQL Community and Oracle engineers that were fixed by Oracle in MySQL 5.7.11.

As usual, I'll try to mention who had reported a bug and who verified it, as I think that names matter in MySQL world. I'll concentrate mostly on InnoDB, replication and optimizer bug reports, trying to highlight regressions clearly.

As usual, I prefer to start with InnoDB bugs:

[Read more]
MySQL Partition Manager is Open Source

At Yahoo, we manage a massive number of MySQL databases spread across multiple data centers.

We have thousands of databases and each database has many partitioned tables. In order to efficiently create and maintain partitions we developed a partition manager which automatically manages these for you with minimal pre configuration.

Today, we’re releasing MySQL Partition Manager. You can check out the code on GitHub.

We’re looking forward to interacting with the MySQL community and continue developing new features.

- MySQL Database Engineering Team, Yahoo

Fun with Bugs #40 - Bugs fixed in MySQL 5.7.10

MySQL 5.7.10, the first release after MySQL 5.7 GA announcement, appeared almost a week ago. It's interesting to check what bugs reported by MySQL Community were fixed in this version, hence this post (that had to wait during the weekdays busy with usual support work). As usual, I'll concentrate mostly on InnoDB, replication and optimizer-related bug fixes and highlight names of those who reported and verified each bug.

Let's start with InnoDB. The following bugs reported by MySQL Community users were fixed:

  • Bug #78623 - "Small tablespaces with BLOBs take up to 80 times more space in 5.7 than in 5.6". This serious regression was reported by my colleague Laurynas Biveinis and verified by Umesh well before 5.7.9 …
[Read more]
Showing entries 1 to 10 of 105
10 Older Entries »