Hi,
Actually primary key transactions was the original design target for the clustering
solution in MySQL so your needs fits very well into the MySQL Cluster capabilities.
1 million transactions per hour translates into about 300 per second.
We have had benchmarks of user applications where they have made
that amount of queries where each query was selecting on a primary key
using an IN clause (thus in reality returning around 100-200 records based
on primary key access). So the performance requirement is not a problem
for MySQL Cluster. Also MySQL Cluster is scalable in this type of scenario so
adding MySQL Servers and Data nodes increases the number of requests the
cluster can handle. I would estimate a normal 2 data node system to be able
to handle at least a thousand (or even a few thousand) simple query lookups
(dependent on the record size) per second or many millions per hour.
Updates are not a problem either, a primary key update performs a read based
on primary key followed by an update in the cluster. An update has to update
all replicas and perform some extra communication. The cost of an update is
around 3 times the cost of a read and thus the total cost of a SQL UPDATE is
around 4 times the cost of a SELECT.
For a 50 GB system with two replicas the total DataMemory needs to be 2 * 50 GB
plus the overhead. If we assume that this becomes 128 GB then the total DataMemory
in all data nodes needs to be 128 GB. So a cluster of 8 data nodes with 16 GBytes of
DataMemory per data node would provide that.
Rgrds Mikael
Peter Santos wrote:
> Dear all,
> I'm hoping to get some feedback about using
> MySQL's clustering solution. I've spent many years
> administering
> Oracle databases, but I only have 2 weeks of
> experience on MySQL... so please bare with me!
>
> Here is the problem:
> - We have a web application where users browse
> their data. A typical session consists of 1
> SELECT and/or 1
> UPDATE based on a primary key. So the
> transactions are short and quick. We expect the
> read/write ratio
> to be 70/30 ...since many users will SELECT
> their data, but never UPDATE it. This seems
> simple, but the problem is
> that we experience very high peaks in load.
> We may get 1 million SELECT's per hour for 4-5
> hrs straight at any time
> during the day followed by 30% updates.
> Currently we have Oracle handling this
> volume, but we are looking for another solution to
> handle these
> short OLTP type transactions in a different
> system since our core business is not an OLTP
> environment
> .... hence my inquiry into Clustering.
>
> Since MySQL doesn't support multi-master
> replication, clustering seems to be the only other
> option. Also, the delay
> between Master/Slave replication make single
> Master replication not an option.
>
> I've read the documentation on Clustering and
> I'm just not sure it it will work. The goal here
> is to support my
> short dml transactions in large volumes. It
> seems to me that clustering is more HA, and may or
> may not sustain
> the short bursts in load that we are looking
> for.
> The data that I would need to put in a
> cluster could also be anywhere between 10GB and
> 50GB ..so how do I
> scale? ( We cannot wait for storing NDB data
> to disk, so MySQL 5.1 is not an option).
>
> Also I'm still a bit confused about the
> architecture and how much memory would need to be
> on each sql node, and
> data/ndb storage nodes. (I'm looked at the
> formula, but didn't understand it... ie
> NumberofReplicas? .)
>
> TIA
> --peter
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com