MySQL Cluster distributes rows amongst the data nodes in a
cluster, and also provides data replication. How does this work?
What are the trade offs?
Table fragments
Tables are 'horizontally fragmented' into table fragments each
containing a disjoint subset of the rows of the table. The union
of rows in all table fragments is the set of rows in the table.
Rows are always identified by their primary key. Tables with no
primary key are given a hidden primary key by MySQLD.
By default, one table fragment is created for each data node in
the cluster at the time the table is created.
Node groups and Fragment replicas
The data nodes in a cluster are logically divided into Node
groups. The size of each Node group is controlled by the
NoOfReplicas parameter. All data nodes in a Node group store the
same data. In other words, where the NoOfReplicas parameter is
two or greater, each …
When MySQL AB bought Sun Microsystems in 2008 (or did Sun buy
MySQL?), most of the MySQL team merged with the existing Database
Technology Group (DBTG) within Sun. The DBTG group had been busy
working on JavaDB, Postgres and other DB related projects as well
as 'High Availability DB' (HADB), which was Sun's name for the
database formerly known as Clustra.
Clustra originated as a University research project which spun
out into a startup company and was then acquired by Sun around
the era of dot-com. A number of technical papers describing
aspects of Clustra's design and history can be found online, and it is in many ways similar to Ndb
Cluster, not just in their shared Scandinavian roots. Both are
shared-nothing parallel databases originally aimed at the
Telecoms market, supporting high availability and horizontal
scalability. Clustra has an impressive feature set and …
This project was announced a year or so ago by Antony Curtis who
used to work for MySQL AB. Having met Antony a few times I was
intrigued to see what he was up to. The quote on the OpenQuery website
describes it well :
The Open Query GRAPH engine (OQGRAPH) is a computation
engine allowing hierarchies and more complex graph
structures to be handled in a relational fashion. In a nutshell,
tree structures and friend-of-a-friend style searches can now be
done using standard SQL syntax, and results joined onto other
tables.
That sounds cool, and it's the first time I've heard of a MySQL
'Computation engine' plugin. Delving further into the manual gives
some insight, and there's some unexpected twists there :
- OQG is a storage engine, but data stored is not persistent w.r.t. server crashes. …
Most of the time we think of SQL queries as being executed at a
point in time and generating a single definitive result, but huge
efficiency gains are available when data changes are tracked and
derived views are partially updated as needed rather than being
fully recomputed periodically. MySQL has support for views on
tables, but there is currently no support for materialized views.
While thinking about this topic I decided to have another look at
Justin Swanhart's Flexviews tool and it's definitely a cool
MySQL based project.
Flexviews is an open source set of non-intrusive addons to MySQL
enabling materialized views to be defined and maintained as the
underlying tables are changed. If you're not sure what a
materialized view is or why they can be useful then I recommend
reading the intro on the Flexviews site. I was particularly
impressed by the documented support for …
My wife – a good web designer with 6 years of experience with web design, HTML and CSS is looking for a job. Here is some information about her:
- Her web site (of course she is the one who designed it)
- Her portfolio
- Her resume
- Her LinkedIn profile
We’re physically located in Toronto, Canada, but she has a great experience of working remotely too. So, if you need a web designer or a junior web designer, feel free to contact Tanya.
…
[Read more]
Most software people are aware of the ACID
acronym coined by Jim Gray. With the growth of the web and open
source, the scaling and complexity constraints imposed on DBMS
implementations supporting ACID are more visible, and new (or at
least new terms for known) compromises and tradeoffs are being
discussed widely. The better known NoSQL
systems are giving insight by example into particular choices of
tradeoffs.
Working at MySQL, I have often been surprised at the variety of
potential alternatives when implementing a DBMS, and the number
of applications which don't need the full set of ACID letters in
the strictest form. The original MySQL storage engine, MyISAM
is one of the first and most successful examples of an 'ACID
remix'. The people …
It may seem hard to believe, but I have seen DECIMAL(31,0) in action on a production server. Not just in one column, but in 15 columns just in the largest 4 tables of one schema. The column was being used to represent a integer primary or foreign key column.
In a representative production instance (one of a dozen plus distributed production database servers) the overall database footprint was decreased from ~10 GB to ~2 GB, a 78% saving. In total, 15 columns across just 4 tables were changed from DECIMAL(31,0) to INT UNSIGNED.
One single table > 5GB was reduced to under 1GB (a 81% saving). This being my record for any GB+ tables in my time working with the MySQL database.
Had this server for example had 4GB of RAM, and say 2.5GB allocated to the innodb_buffer_pool_size, this one change moved the system from requiring more consistent disk access (4x data to memory) to being able to store all data in memory. Tests showed …
[Read more]
MySQL Cluster is the name given to one or more
MySQL Server processes, connected to an Ndb Cluster database.
From the point of view of the MySQL Server processes, the Ndb
Cluster is a Storage Engine, implementing transactional
storage of tables containing rows. From the point of view of the
Ndb Cluster database, the MySQL Server processes are API nodes,
performing DDL and DML transactions on tables stored in the
cluster. Both exist independently – Ndb Cluster can be used
without attached MySQL Server processes, but almost all users of
Ndb Cluster connect at least one MySQL Server for DDL and
administration.
Ndb stands for Network DataBase. This is a telecoms phrase where
Network usually refers to a fixed or wireless telephone network,
rather than the …