In case you were wondering, after seeing this self-defined tutorial, backing up MySQL 5.1
partitioned tables does not require any particular additional
technology.
This tutorial presents MySQL 5.1 in a short paragraph, and then
starts talking about a commercial backup application, as if it
were the only way of backing up partitioned tables.
The title, for sure, is misleading. Backing Up MySQL
Partitioned Tables. Reading such a title, readers expect
either a list of possible solutions, or being presented with the
only existing way of dealing with this task. Instead, the only
instructions that you get is how to use the above mentioned tool,
which is definitely not the only way of getting the job done. It
is also doubly misleading because, since partitioning is a new
technology, the casual reader may think "oh? What's peculiar
with …
A feature which hasn't been so public about the
implementation
of partitioning is the support for atomicity of many ALTER
TABLE
statements using partitioned tables.
This atomicity exists for
ALTER TABLE ADD PARTITION ....
ALTER TABLE REORGANIZE PARTITION ...
ALTER TABLE DROP PARTITION ...
ALTER TABLE COALESCE PARTITION
Given that partitioning often works with very large tables
it
was desirable to have a higher level of security for ALTER
TABLE
of partitioned tables. To support this a DDL log was
implemented.
This DDL log will in future versions be used also for many
other
meta data statements. The DDL log will record all files
added,
renamed and dropped during an ALTER TABLE command as above.
The design is done in such a way that the ALTER TABLE will
either
fail and then all temporary files will be removed (even in …
|
I talked several times about partitioning usability. In my many tests of
partitioning I found myself in need of generating list of
partitions for a given range. I made the Partition Helper to scratch this particular itch, i.e. making partitions reasonably quickly and without thinking too much. |
The Partition Helper is a Perl script that converts some …
[Read more]With the delivery of MySQL 5.1 GA, our release policy sees a couple of changes in relation to that of MySQL 5.0.
First, initially we will deliver both MySQL Enterprise Server and MySQL Community Server in parallel. The bug fixes to the initial MySQL 5.1 GA releases will thus be delivered also in binary form for both MySQL Community Server and MySQL Enterprise Server.
Second, after a period of time, we will revert to the different release schedule for MySQL Enterprise Server and MySQL Community Server that we have been using. MySQL Enterprise Server will have more frequent binary releases. However, at this point, we do not have an exact date or a release number, when the first Enterprise only release will happen.
Third, we have done away with odd and even numbering. Whenever MySQL …
[Read more]MySQL 5.1 is here! It’s announced! And it’s time for the overall winner, Position 1 in the MySQL 5.1 Use Case Competition.
1. Greg Haase (Lotame Solutions Inc., Elkridge, Maryland, USA): Using Partitioning and Event Scheduler to Prune Archive Tables. See Greg’s DevZone article, and his blog.
Thanks and congratulations, Greg! I absolutely hope you are in a position to take advantage of your free MySQL Conference & Expo 2009 Pass, including a dinner with MySQL …
[Read more]
I saw Monty's latest rant and I told myself "here we
go again." I blogged before about deaf developers, and
here's another symptom of the same disease.
This is another case of a developer, a brilliant one, mind you,
one of the brightest in the open source arena, but a developer
nonetheless, who has a non-user oriented mindset and is
inflicting his view on the users.
I will stay clear of the reasons. Why the co-founder of MySQL
decides to spoil the party on release day and ambushes his
colleagues with a truckload of blame is beyond my
understanding.
What concerns me is the patronizing plea that Monty uses against
the users. "Don't use MySQL 5.1"? Really? What about the ones who
have already been using it for …
With all due respect to Monty (and I mean that — much respect is due), I have some serious issues with his portrayal of the 5.1 release. I hate to make my first entry on Planet MySQL about a controversy, but he encouraged people to blog about their experience with 5.1, so that’s what I’ll do here.
Overall Quality
As a long time user, I am very confident that the quality of 5.1 GA far exceeds that of the initial 5.0 GA release (5.0.15). In fact, I would go further and suggest that the MySQL organization has if anything been too conservative about declaring 5.1 GA.
It’s obviously true that there are still many bugs open. However no software is bug free, especially not those with codebase as large as MySQL. So the question is not if they are bug free, but are the …
[Read more]
Hello, blog! Long time without posting. Here I am again.
My attention was caught a few days ago by a contest posted by
Tokutek.
This is a company that makes a proprietary engine for MySQL. In
their contest, they claim that it takes 20 days
to insert 1 billion records into a InnoDB table, and that MyISAM
is not even up to the task. In their example, InnoDB inserts 404
rows per second and MyISAM died after a few days of inserting 93
rows per second.
It looks farfetched to me. Surely you can do better than
that.
Now, I don't have a quad 3.16Ghz Xeon server with 16GB RAM
available like the one used by Tokutek, and so I tried with a
modest quad 2.7Ghz AMD, 8GB RAM server. The problem is mostly the
same. Once your index becomes bigger than the available RAM, you
are in the glue. The only …
With the GA announcement of MySQL 5.1 coming up, we have picked the winners in the MySQL 5.1 Use Case Competition.
To keep you in suspense, let me first announce those on positions 5 to 10:
5. Fourat Zouari (TriTUX.com, Tunis, Tunisia): Using Partitioning for Data Warehousing. See Fourat’s DevZone article, and his blog entry from May 2008.
6. Ryan Thiessen (Big Fish Games, Seattle, …
[Read more]
A few days ago I was describing a common grievance when using
partitions.
When you care at a table, like the following
CREATE TABLE t1 ( d DATE )
PARTITION by range (to_days(d))
(
partition p001 VALUES LESS THAN (to_days('2001-01-01'))
, partition p002 VALUES LESS THAN (to_days('2001-02-01'))
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);
Then you have the problem of finding out the original values.
SHOW CREATE TABLE doesn't help.
show create table t1\G
*************************** 1. row
***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY
RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851)
ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE
= MyISAM, PARTITION p003 VALUES LESS …