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 中文
Previous 30 Newer Entries Showing entries 61 to 88

Displaying posts with tag: partitioning (reset)

MySQL 5.1 Use Case Competition: Position 1
Employee +0 Vote Up -0Vote Down

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,

  [Read more...]
Monty Rants or Yet Another Case Of Developers vs Users
+0 Vote Up -0Vote Down
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


  [Read more...]
Quality of 5.1 GA release
+0 Vote Up -0Vote Down

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,

  [Read more...]
Cracking the Tokutek contest with MySQL 5.1
+0 Vote Up -0Vote Down
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



  [Read more...]
MySQL 5.1 Use Case Competition: Positions 5 to 10
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
A quick usability hack with partitioning
+0 Vote Up -0Vote Down
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














  [Read more...]
Partitions usability I - blues and a preview
+0 Vote Up -0Vote Down
Pop quiz. Look at the picture below. Quick: can you tell me to which year and month partition P001 refers?


I couldn't say it without asking the database itself to revert the output of to_days:

select from_days(723180);
+-------------------+
| from_days(723180) |
+-------------------+
| 1980-01-01 |
+-------------------+

Just to make the pain more clear, you do something like this, clean and neat, and you think you have done a good job.

CREATE TABLE t1 ( d DATE )
PARTITION by range (to_days(d))
(

















  [Read more...]
New launchpad tree for PARTITION BY RANGE COLUMN_LIST(a,b)
Employee +0 Vote Up -0Vote Down
A colleague of mine at Sun/MySQL showed me how to get
statistics from my blog. This was an interesting read
of all statistics. I noted that there was a great
interest in partitioning related information and that
the new partitioning feature mentioned in my blog
2 years ago still attracts a lot of attention.

So I thought it was a good idea to blog a bit more
about what's going on in the partitioning
development. I decided to check out how easy it is
to externalize my development trees on launchpad.
It turned out to be really easy so I simply
put up the development tree for the new partitioning
feature which I described in my last blog.

Launchpad tree

I also externalized the Worklog entry for
















  [Read more...]
MySQL Partitioned Tables with Trigger enforced FK constraints
+0 Vote Up -0Vote Down
Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.All you need is an extra table and a couple triggers.The concept is fairly straight forward:Create a before insert trigger on the child table that validates the parent exists in the parent table.If there is no parent found, then insert a
Using partitions to create constraints
+0 Vote Up -0Vote Down
A devilish quiz by Scott Noyes has made me thinking about a side effect of partitioning.

The quiz

Given a table trickytable with a INT field, explain how these statements and results are possible:
INSERT IGNORE INTO trickyTable (id) VALUES (1);
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM trickyTable;
Empty set (0.00 sec)
There are no blackhole, federated, triggers, proxy.
I initially tried with the event scheduler, with an event that empties the table every second, but the insert results in 1 row to be inserted, while the quiz asked for 0 rows.

After a few minutes, I got it. The right answer was to use partitioning.

Let me show off a bit. :)
Table trickytable was created with partitions by range












  [Read more...]
Checking and repairing partitions in MySQL 5.1
+0 Vote Up -0Vote Down
Talking with users at OSCON 2008, the biggest fear with new features was table corruption with partitioning. Until recently, this fear was substantiated by bug#20129, which reported inability of repairing corrupted partitions. The initial "fix" for this bug was to disable the offending commands.
After a while, especially when the Support department complained loudly, the bug was reopen and a proper fix implemented. Now you can maintain a partition using ALTER TABLE ... CHECK/ANALYZE/OPTIMIZE/REPAIR PARTITION.
I did some experiment, using MySQL sandbox and the employees test database, which supports partitioning.
You need to download the latest MySQL 5.1 release (5.1.28), where the patch is available.
To load



  [Read more...]
Is MySQL-partitioning useful for very big real-life-problems?
+0 Vote Up -0Vote Down
Some months ago I helped out in another project in which they had some performance problems. They had a very big table and the index of the table was bigger than the table itself. As every change in the table causes MySQL to recalculate/reload the index, this can take some seconds for such big tables.
So, I thought it would be a good idea to split that big table into very small ones. This should reduce the overhead of reloading big indices and instead reload only very small parts. And the next thought was: Is it possible to use the "new" MySQL-Partitioning for that?
Continue reading "Is MySQL-partitioning useful for very big real-life-problems?"
MySQL Repair/Optimize Partition Errors
+0 Vote Up -0Vote Down

mysql 5.1 is nearing release, with the present release candidate 5.1.24.

The most important new feature, in my eyes, is the new partitioning capability. When I get some time, I will write up a more complete post on my experiences so far with 5.1 partitioning, but I am going to try to keep the turnover on posts a bit higher, and post smaller things on here more regularly.

Partitioning has the potential to make large tables in mysql manageable once again. This is music to the ears of anyone that has had the misfortune of having to learn, the hard way, about MyISAM’s often painfully slow “Repair by keycache” loading and repairing of large tables with unique keys. Add that to MyISAM’s propensity to table corruption, especially with large tables, and you have a ticking timebomb on many pre-5.1 servers out there. If you are

  [Read more...]
Horizontal Scaling with HiveDB
+0 Vote Up -0Vote Down

At the MySQL Conference & Expo 2008, Britt Crawford and Justin McCarthy, both from Cafepress.com, gave us a very interesting talk on scaling with HiveDB. I took a few notes (pasted below), their slides are online (warning: 6.1MB PDF), and if you’re after their abstract its available as well.

I also took a video of them (refer to Slide 12, for the IRC conversation):

The quick notes:

  • OLTP optimised (as it serves cafepress.com)
  • Cannot lock tables, or take it offline
  • Constant response time is more important than low latency (little slower query is ok, just
  [Read more...]
Speaking at the Conference
+0 Vote Up -0Vote Down
I had a great time speaking about partitioning at the MySQL conference this week.Special thanks go out to Mattias Jonsson as well for helping answer some of the questions afterwards. The room was packed - which makes me excited for the future of MySQL partitioning!I hope everyone got something out of the session, and I'd gladly welcome any comments (positive or constructive).I plan on doing some
MySQL Conference Liveblogging: Applied Partitioning And Scaling your (OLTP) Database System (Wednesday 11:55AM)
+0 Vote Up -0Vote Down
  • Phil Hilderbrand of thePlatform for Media, Inc presents
  • classic partitioning
    • old school - union in the archive tables
    • auto partitioning and partition pruning
    • great for data warehousing
    • query performance improved
    • maintenance is clearly improved
  • design issues in applying partitioning to OLTP (On-Line Transaction Processing)
    • often id driven access vs date driven access
    • 1 big clients could be 80% of the whole database, so there's a difficulty selecting partitioning schemes
  • partitioning is only supported starting from MySQL 5.1
  [Read more...]
MySQL: Getting Creative with Partitioning (Performance Results)
+0 Vote Up -0Vote Down
I decided to run some very basic performance test comparing the non-partitioned table with a primary key, and a partitioned table with a primary key and a unique constraint enforced via a secondary table explained in my previous post.Overall, it appears that with partitioning, as the data/rows scale, the inserts actually get faster :) This is what I would expect theoretically, so score one for
MySQL: Getting Creative with Partitioning
+0 Vote Up -0Vote Down
Lately, I've been trying to keep up with at least one of the MySQL Forums: Partitioning.It's a great way to keep on top of issues surrounding partitioning, and also get an idea of what people are trying to do with the new 5.1 feature. Richard came up with an interesting problem that I jumped into only to realize that I hadn't done my homework, and my initial suggestion wouldn't work at all due
Using Events to manage Table Partitioning by Date: wrap-up
+0 Vote Up -0Vote Down

As a follow up to the series of posts I've been making, I wanted to post what I ended up with.  Thanks to everyone who posted comments, your help was extremely useful. To recap, I have a working pair of events to add and remove partitions to this table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
)
PARTITION BY RANGE( TO_DAYS( logged ) ) (
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)

read more

Using Events to manage Table Partitioning by Date: wrap-up
+0 Vote Up -0Vote Down

As a follow up to the series of posts I've been making, I wanted to post what I ended up with.  Thanks to everyone who posted comments, your help was extremely useful. To recap, I have a working pair of events to add and remove partitions to this table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
)
PARTITION BY RANGE( TO_DAYS( logged ) ) (
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)

read more

Using Events to manage a Table Partitioned by Date: part 2
+0 Vote Up -0Vote Down

Thanks all for the help on getting my log_addpartition event running, dynamic SQL was just the ticket. 
I'm now focusing on writing the sister event: 'log_removepartition', which will find and purge partitions older than some time interval. The information schema comes in quite handy here, as I can query the PARTITIONS table to see all partitions.

read more

Using Events to manage a Table Partitioned by Date
+0 Vote Up -0Vote Down

I want to create a log table in 5.1 that is partitioned by day.  I want to roll-off old data and create new partitions for each day automatically.  Without writing a script and a cronjob, it seems like this should be possible with Events.  Let's start with the table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
)
PARTITION BY RANGE( TO_DAYS( logged ) ) (
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)
);

This seems pretty straight-forward:  I take my log entry time and convert it TO_DAYS and partition on that.  I have tomorrow's partition all ready to go, just in case I don't get around to adding it today.  Let's create an Event to do

  [Read more...]
Divide and be conquered?
+0 Vote Up -0Vote Down

Over the past four articles I've demonstrated cases where a denormalized data model is faster than a normalized one, but often not by that much, and a case where a normalized data model was a bit faster than a denormalized one.  My general conclusion was with today's optimizers one should target a normalized data model and then denormalize where it makes sense, even for reporting.  I'm not as much of a fan of the star schema, a heavily denormalized data model popularized by Ralph Kimball, as I used to be.  Star schemas are costly to build and maintain and the the time spent creating them can often be spent better on more productive optimizations, such as the creation of summary tables and better indexing.  I'm not saying the denormalization doesn't make sense in some cases, just that it doesn't make sense in all cases. 

Time

  [Read more...]
Proposals for MySQL Conference
+0 Vote Up -0Vote Down
Some of the bloggers on Planet MySQL are sharing their proposals for the upcoming 2008 MySQL Conference & Expo. Hereby, I follow their example and give you mine.

The first proposal I submitted is on PHPUnit and will showcase Michael Lively Jr.'s port of DbUnit:

Testing PHP/MySQL Applications with PHPUnit/DbUnit

In the last decade, PHP has developed from a niche language for adding dynamic functionality to small websites to a powerful tool making strong inroads into large-scale Web systems. Critical business logic





  [Read more...]
Partition pruning tip: Use comparisons of column, not of partitioning function value
+0 Vote Up -0Vote Down

A customer issue has drawn my attention to this this pecularity: if partitioning is done by the value of some function, then partition pruning module will make use of comparisons of the partitioning column(s), but not of comparisons of the value of the partitioning function. Here is an example:

CREATE TABLE t1 ( recdate  DATETIME NOT NULL, ... )
PARTITION BY RANGE( TO_DAYS(recdate) ) (
  PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-01-01') ),
  PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-02-01') ),
  ...
);
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE recdate='2007-01-15';
+----+-------------+-------+------------+------+-...
| id | select_type | table | partitions | type |
+----+-------------+-------+------------+------+-...
|  1 | SIMPLE      | t1    | p1         | ALL  |
  [Read more...]
MySQL: getting started for newbies
+0 Vote Up -0Vote Down
When MySQL first came out and started popping up all over the web in the e-commerce arena, if you were like me you looked at it as a wannabe. 'Call me in a few years when you grow up'. Yea, sometimes the Oracle in us goes to our heads a little to fast...MySQL has really gone mainstream in the last few years. Companies that never would have considered MySQL in the past for critical database
Thoughts on partitioning optimizations
+0 Vote Up -0Vote Down

I'm supposed to be working on subquery optimization but I can't get BUG#26630 out of my head.

The bug test case is small and easy to understand: looking at the EXPLAIN:

CREATE TABLE tbl_test( ... ) partition BY range(num) (...);
 
EXPLAIN partitions
SELECT * FROM tbl_co c STRAIGHT_JOIN tbl_test t WHERE t.num=c.num AND reg=8;
..+-------+-------------+------+---------------+------+---------+------+------+-------------+
  | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
..+-------+-------------+------+---------------+------+---------+------+------+-------------+
  | c     | NULL        | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using where |
  | t     | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   17 | Using where |
  [Read more...]
Partitioning with Dates in MySQL 5.1
+0 Vote Up -0Vote Down
Although I am still busy with writing my thesis paper, I do need a distraction from time to time. Since I started getting phpOpenTracker questions again recently, I decided to (slowly) start working on phpOpenTracker 2.0 again. phpOpenTracker 2.0 will be a complete rewrite, utilizing the features of PHP 5.2 and MySQL 5.1 (http://www.mysql.com/).

The biggest problem with phpOpenTracker 1.x is that its database schema is normalized. Even simple phpOpenTracker API calls result

  [Read more...]
Previous 30 Newer Entries Showing entries 61 to 88

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.