List:Cluster« Previous MessageNext Message »
From:<list Date:February 3 2011 2:55pm
Subject:Re: cluster-plain Digest 2 Feb 2011 12:03:32 -0000 Issue 811
View as plain text  
On Thu, 03 Feb 2011 12:17:10 +0100, Oli Sennhauser
<oli.sennhauser@stripped> wrote:
> Hello Michael,
> 
>> foreign keys are not available when using MySQL Cluster as the storage
>> engine for a table (InnoDB is the only storage engine that supports
>> them).
>> While not ideal there is a workaround that will work for some
>> applications -
>> written up here....
>>
http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.ht
>> ml (basically using stored procedures to enforce the referential
>> integrity).
>>
>>
>> Please excuse my lack of mysql knowledge, as I have not worked with the
>> software for very long, but we are evaluating mysql cluster for our
>> deployment of a mysql server farm and have run into a major snafu with
>> support for foreign key support.   I ran into this problem when
>> installing
>> Funambol (a SyncML server) which builds it's mysql database
automatically
>> and with a large number of indexes on multiple tables.  Is there a way
to
>> enable support for the foreign keys, or are we pretty much out of luck?
> 
> First of all you have to be aware that MySQL Cluster has only little to 
> do with normal MySQL. So your MySQL knowledge would not really help much

> here.
> MySQL Cluster is a specialized product (especially for Telcos) and is 
> NOT a general purpose database.
> 
> This has the effect that you typically will not get the expected results

> when you try it out and you will be disappointed. As long as you run the

> system not at its limits it might work fine but you will not reach the 
> peak performance without explicitly tune your application.
> 
> Funambol looks to me like such a general purpose application expecting a

> "normal" relational database as a back-end.
> 
> Now it comes to the question, why do you consider MySQL Cluster? Is it 
> HA or is it performance. When you are interested in performance then I 
> am pretty sure we can achieve the solution with a normal MySQL database 
> (you do not want to rewrite and redesign the whole Funambol, don't you?)
> 
> When it is about HA and low performance we can consider MySQL Cluster or

> we can think about active/passive fail-over clustering which is 
> typically the used solution in such cases.
> 
> Just a little site information: InnoDB is NOT the only Storage Engine 
> which knows about Foreign Key constraints. PBXT knows about it as well, 
> and possibly a few others as well...
> 
> If you are interested we can assist you in a Architecture and Design 
> Engagement or a MySQL Cluster Jump-start.
> 
> Best regards,
> Oli

Oli,

Thanks for the information, much appreciated.  A little history, we have
been working with MySQL itself for a number of years as our database for
our ISP services such as web hosting and user management.  You are correct
in assuming that we are looking for an HA solution, which needs to serve
two major purposes.  First being the database for a redesigned user
management system, and secondly the database from which our clusters of
postfix and dovecot machines do their authentication queries.  To give you
an idea of how much load we are expecting, we currently server 1.1 million
POP3 logins and 350K IMAP4 logins per day, and deliver an average of 250K
messages per day.  Currently we run all these systems using hashed files,
and have not tested a MySQL server or cluster to that capacity.  

Over the course of the last week I have read much of the MySQL cluster
documentation, and I do agree that it is not suitable for large numbers of
complex joins (hopefully SPJ will have an impact on that) or acting as a
database for applications that expect a standard mysql server.  Looking at
our needs I know the vast majority of queries (from postfix and dovecot)
will be simple primary key lookups, while our user management system will
have a couple of data mining tasks that will require some more complex
queries.  We are planning to design a four data node cluster with two
management nodes, which will act as the primary database for the dovecot
and postfix authentication lookups.  We then will ship the database via
replication to a slave server running MySQL server where our new user
management system will have the ability to execute complex joins and other
data mining tasks.  

I do know that one of my options is to configure one write server
replicating to as many read only slaves as needed, but this does not offer
the true high availability and no single points of failure model we hope to
achieve.  Another possibility is looking at a third party software (or
hardware) solution such as Clustrix or scaledb.

As for Funambol, I have abandoned the software for a lighter SyncML
server, or possibly writing our own.

Thanks again,

Michael

Thread
Re: cluster-plain Digest 2 Feb 2011 12:03:32 -0000 Issue 811list3 Feb
  • Re: cluster-plain Digest 2 Feb 2011 12:03:32 -0000 Issue 811Mikael ronström3 Feb
    • Re: cluster-plain Digest 2 Feb 2011 12:03:32 -0000 Issue 811david3 Feb
      • Re: cluster-plain Digest 2 Feb 2011 12:03:32 -0000 Issue 811Johan Andersson3 Feb