Showing entries 61 to 70 of 113
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: partitioning (reset)
Partitioning with non integer values using triggers

Looking at Bug#47310, which is a feature request that I hear frequently when I talk about partitions, I wrote a comment, suggesting triggers to work around the limitation.
The reason for the limitation is that allowing arbitrary functions for partitioning was too complex and it was provoking crashes and other unpleasant side effects (see the discussion under bug#18198).
But if you use a trigger, the resulting column is a plain integer, and many of the side effects disappear. The drawback is that you need to add a column to your table, and you need to use that column when searching for data. With that in mind, you can implement the workaround quite easily.

USE test;

user_id int(10) NOT NULL,
[Read more]
Enterprise Software as a Service (SaaS) and Partitioning


Partitioning, new with MySQL 5.1, has complicated interactions with queries and indexes.  If one isn’t careful it is easy to degrade performance.   For example, select queries that go with that grain (queries where partition elimination occurs) can be much quicker, but select queries that go against that grain can be much slower.   Queries that go against the grain must query each partition, so for a table with 12 partitions,  one query against that  table can result in 12 queries, one against each of the partitions.  An example of this would be a query against a month partitioned table that is looking to see how much activity a product had in the past 12 months. 

The ideal partitioning scheme would be a system where all queries only needs to access data from one partition.  This describes enterprise software deployed as a service where multiple enterprise tenants all …

[Read more]
partitioning + auto_increment is buggy!

Folks, I just want to warn you how buggy partitioned tables with negative values are. Never use negative values for InnoDB auto_increment columns!!!!

[Read more]
MySQL Sandbox and Spider at FrOSCon and OpenSQLCamp

FrOSCon and the OpenSQLCamp are about to start.
I am packing for Sankt Augustin, where I will attend the fourth edition of FrOSCon and the second OpenSQLCamp. I will have two sessions, Sharding for the masses, about the Spider storage engine and MySQL Sandbox 3, about one of my favorite tools.

The program is very rich. There will be several tracks in the main event and in the associated conferences. If …

[Read more]
Why you don’t want to shard.

Note: This blog post is part 1 of 4 on building our training workshop.

The Percona training workshop will not cover sharding. If you follow our blog, you'll notice we don't talk much about the subject; in some cases it makes sense, but in many we've seen that it causes architectures to be prematurely complicated.

So let me state it: You don't want to shard.

Optimize everything else first, and then if performance still isn't good enough, it's time to take a very bitter medicine. The reason you need to shard basically comes down to one of …

[Read more]
Partition by column_list ready for alpha testers

I got time to spend on a really old worklog I completed coding
already october 2005. I blogged about it in July 2006 and
interestingly enough it's still the second most read blog
entry on my blog (probably related to search engines in some

I have merged it with the azalea tree (this is an internal code
name for our development tree, name is likely to change). This
tree contains subquery optimisations, Batched join and some more

I have fixed a whole bunch of bugs that always shows up in early
code. The code quality is still alpha but at least you won't find
10 bugs per hour :)

you can find the launch pad tree for this code.

There are two important additions made possible by this tree. …

[Read more]
Sharding for the masses: the spider storage engine

In my previous article about the Spider storage engine, I made some tests and I saw that the engine has potential. I did also identify some problems, which were promptly addressed by the author. I have looked at the latest version (0.12), and the results are very much encouraging.

Installing the Spider storage engine is not trivial but not extremely difficult either. My previous article has a step-by-step …

[Read more]
MySQL University: Boosting Performance With MySQL 5.1 Partitioning

This Thursday (June 4th, 14:00 UTC), Giuseppe Maxia will give a MySQL University session on Boosting Performance With MySQL 5.1 Partitioning. Giuseppe is leading the Community team at MySQL and has done various MySQL University sessions before.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session …

[Read more]
MySQL University - Boosting performance with partitions

Mark your calendars: A MySQL University session about Boosting performance with MySQL 5.1 will take place on Thursday, June 4th at 13:00 UTC ( 8am CDT (Central) / 9am EDT (Eastern) / 14:00 BST / 15:00 CET / 17:00 MDT (Moscow) / 18:30 IST (India))
The session will be conducted through DimDim, a system that allows you to follow the audio and visuals of a presentation from your browser, without any additional settings.

Attendance is free. Please follow the instructions given in the …

[Read more]
Partitioning Presentation from MySQL Conference

It appears that my presentation isn’t available at the conference site. I’ve added it below.   Sorry for the delay. 

Without some context that I talked about at the conference the presentation may not make sense.

Partitioning creates a grain in the table.  Select queries that go with that grain can be quicker, at times much faster, but select queries that go against that grain can be slower, at times much slower. 

An example is a table that is partitioned by date and has an orderId primary key.  Queries that select data by date will either be almost as fast (partitioning can add a slight overhead) as the non-partitioned table to much faster.   But queries that don’t query by date will have to query all the partitions.  A table partitioned 12 times, one partition for each month, results in 12 index partitions.  As the indexes are partitioned by date, a query by the …

[Read more]
Showing entries 61 to 70 of 113
« 10 Newer Entries | 10 Older Entries »