I am about to launch a new web application using PHP/MySQL.
I want to have scalability as a consideration (not an after thought) and am currently tinkering with MySQL NDB.

Is it worthwhile starting out with just one replica (1 x NDBD) and adding nodes as the project expands or just stick with MyISAM and when I buy my second server for load balancing/DR then move to NDB?

Does NDB work ok across RAID setups?

I would avoid putting too much effort into scaling the infrastructure upfront until you know you will need it.

Reason I say this is MySQL cluster is transparent to clients connecting to it. Your application would never know it is connecting to a MySQL server or a cluster so my recommendation would be to start off with a normal database until you need it. You can squeeze a lot of performance out of MySQL with the right backend (MyISAM vs InnoDB), proper indicies, appropriate column schemas (ie: don't go using bigints as PK's) and fine tuned queries.

What you need to do is analyse how your database is going to be used. For instance is the application high write / low read, high write / high read, low write / high read. Knowing the behaviour will determine how you are to scale in the future. MySQL cluster may be an option in the future or you could look into master/slave replication, partitioning etc.

I would also recommend you check out some of the talks Brad Fitzpatrick from Danga made about scaling the LiveJournal backend. They are some of the most informative talks out there about real-world problems and how a large site dealt with them.

Starting with version 7.0.x, you can add additional storage nodes online (previously you had to backup and restore the entire cluster to do that), so you could add nodes as required. It certainly works with RAID, and higher IO can help with the performance of writing to the transaction/checkpoint logs. For production usage, it's generally recommended to have at least 3 separate servers, two for the server/data nodes and one for the management/arbitrator node.

I'd sort of disagree with Elliot A. I've had a lot of experience with MySQL Cluster/NDB in production environments, and it's significantly different enough from the other storage engines that it would probably be useful to consider the ramifications of it's usage from the get-go if you have definite plans to migrate to it at some point. For instance, at this point, complex joins aren't really performing very well, so it's best to avoid those (though there is work being done to optimize those types of queries in the storage nodes themselves instead of passing a ton of data to the server nodes for the join processing). Doing strict third form normalization isn't necessarily performant, and you find after researching various high volume environments (not just MySQL Cluster) that in many cases they started de-normalizing their database design for scalability reasons. If you start to think of those issues now, you can avoid having to make as many major schema or architectural changes later.

I would also recommend you check out some of the talks Brad Fitzpatrick from Danga made about scaling the LiveJournal backend. They are some of the most informative talks out there about real-world problems and how a large site dealt with them.

Sounds interesting...got a link?

Thanks for that I'm just on fliiby.com at the moment and there are some great mysql documents related to what I need. Although it is points like you just said re: myisam vs innodb that confuse me... in what situation is right?

I'm setting up a profile based website.

The primary key will be an integer and be the main identifier of the individual user (or row), this field has relationships to other tables, such as images that user has or whether user X has added user Y as a friend.
But finding information on whether to set this as a varchar(99) or BIGINT is quite lacking (come on MySQL AB!) and what database type to store all these as.
Also when I've got like 1 million people on there... how will this affect performance. I don't mind in the early stages buying more ram or whether to cope with that but be nice to know

In fact do you guys know of any (UK preferred) companies that specialise in schema optimisation? Obviously rather not post my DB structure on the public net but if someone could give me a hand I would love you forever.

It's this bit of an application roll out that I'm struggling with... the coding.. meh.. i can just about do.. this side of things *whoosh*

I'd check with these guys...they're behind the MySQL performance blog and parterned with Monty on the MariaDB project. A lot of the staff there are former Sun developers for MySQL:

http://www.percona.com/

You also might want to keep in check that the NDB engine doesn't support a lot of the things that you might be used to with InnoDB/MyISAM. Foreign keys, for example - or autoincrement ids.

Burhan,

Unless I'm misinterpreting what your saying, but NDB absolutely supports auto-increment fields (and has ever since I can remember, and I started using it with version 4.x back in 2005), though you are correct about foreign key constraints not being supported (though you can work around this using triggers or programmatically in your app).