Showing entries 1 to 10 of 102
10 Older Entries »
Displaying posts with tag: partitioning (reset)
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]
Sample employees database migrated to GitHub

It's migration time. There was another project that I use often and was still in Launchpad. The Sample Employees Database is now on GitHub, under the same license it had before (CC A-SA 3).

Figure 1 - Employees database
This database is interesting because it is not too small (like Sakila) and not too big. It has enough data to allow you to test in a non trivial way.
Installation and testInstalling the database is easy:

$ git clone https://github.com/datacharmer/test_db.git
$ cd test_db
$ mysql < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO …
[Read more]
Circular Partitioning scheme for a fully Automated Archival / Purge process with Events

Partitioning The advantage of partitioning is that we can drop / truncate the partitions which is real quick and done in seconds. It does lock the table (metadata lock) during this operation but only for few seconds. That duration depends on the number of pages in use in the buffer pool. With MySQL 5.6 it is possible to exchange the partitions , which helps in moving archived data to another table with similar structure.
   
Fully Automated archival / purge 

Consider a fast growing table which is partitioned to help the queries and help quick archival/purge. The queries if specify the partitioning filter can limit the search to relevant partitions and can considerably reduce the query time. Purging old data to reclaim the storage space can be readily achieved by truncating the old partitions. Archival has multiple techniques but with MySQL 5.6 exchanging the partitions make archival a real …

[Read more]
Fun with Bugs #36 - Bugs fixed in MySQL 5.6.25

Two days ago Oracle had released MySQL 5.6.25, so it's time to check what bugs reported by MySQL Community are fixed there. As usual, I'll mention both a bug reporter and engineer who verified the bug. Please, pay attention to fixes in replication and partitioning - if you use these features (or queries to INFORMATION_SCHEMA with a lot of complex tables in your database), please, consider upgrading ASAP.

The following InnoDB related bugs were fixed:

  • Bug #69990 - CREATE_TIME and UPDATE_TIME are wrong for partitioned tables. Finally this bug reported by my colleague Justin Swanhart and verified by Umesh (almost …
[Read more]
Showing entries 1 to 10 of 102
10 Older Entries »