MySQL Forums
Forum List  »  Partitioning

Re: horizontal partitioning on multiple nodes
Posted by: Mikael Ronström
Date: October 20, 2007 02:51AM


Sounds like you need a simple table in MySQL Cluster.
CREATE TABLE t (a int not null primary key, b blob) engine = ndb;

This should be sufficient. It sounds to me like you're not interested
in replication and high availability so thus you use NoOfReplicas = 1
in config file of NDB.

You set-up the cluster to use e.g. 6 machines of 64G each and place
6 ndbd nodes, one on each machine. You need to set DataMemory to a
very high number to use most of the memory. Also IndexMemory need to
be set to accomodate the hash index for the tables.

This table will be purely memory-based but will checkpoint to disk.
You can have any number of MySQL Servers and they will all see the
same data, so MySQL will ensure that routing to the proper node is
done for reading and writing.

If you need more control over data distribution you can use a
partitioning over the table, the default is hashing on the primary key.

If you decide that actually not all of the blob's are so hot then you
can add TABLESPACE ts STORAGE DISK to the CREATE TABLE and the blob
will be disk-based. Then you need to decrease the DataMemory and set
the DiskPageBufferMemory much higher (this is the Page cache for the
blob objects and you probably want a very big one). In this case BLOB's
are not certain to be in memory but very likely if they are hot.

Rgrds Mikael

Nathan Hoult Wrote:
> We have a need for a 250 gig table with only 2
> columns, key and binary blob. We will be doing 99%
> selects but will be mostly random access. We have
> looked at a number of commercial solutions, but
> found that none of them are fast enough and
> provide more than we need (don't 'have' to have
> complex queries, multi index...). We have come to
> the conclusion that disk latency are the limiting
> factor. We would be willing to buy 250gigs worth
> of memory in multiple 64gig 64bit nodes, and solid
> state drives when they are supported by our
> hardware provider (2 years?). I have been
> searching with Google for around 16 hours, but the
> closest thing I have found is how to cluster MySQL
> that mirrors the data on multiple nodes. I also
> found how to make MySQL 5.1 do horizontal
> partitioning (lead me here), but it seems to just
> be disk based partitioning.
> In summery was wondering if MySQL can:
> - In memory engine that supports BLOBS (so far my
> searching has said no, has that changed?). Disk
> based that after a while of selecting, will all be
> cached in memory is an option.
> - Use a hash or range to put the data on other
> nodes, any node I insert to should put it on the
> right node, and any node I select from should get
> it from the right node. (aka: client does not need
> to managed the distribution)
> - if a node has a problem, some way to block
> inserts/selects until the node is restored. It
> should maintain the data through a reboot, a hard
> drive loss could require a cluster reload.
> We currently have an in house solution that does
> not support live updates and causes us problems,
> sadly it currently beats every commercial
> distributed Database system we have tested by 4x
> speed or more. We are trying to avoid another in
> house solution as we have to support it.
> Thanks in advance.

Options: ReplyQuote

Written By
Re: horizontal partitioning on multiple nodes
October 20, 2007 02:51AM

Sorry, you can't reply to this topic. It has been closed.

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.