What are some good tips for a developer trying to design a scalable MySQL database?

Question

As the question states, I am a developer, not a DBA. I have experience with designing good ER schemas and am fairly knowledgeable about normalization and good schema design. I have also worked with data warehouses that use dimensional modeling with fact tables and dim tables.

However, all of the database-driven applications I've developed at previous jobs have been internal applications on the company's intranet, never receiving "real-world traffic". Furthermore, at previous jobs, I have always had a DBA or someone who knew much more than me about these things.

At this new job I just started, I've been asked to develop a public-facing application with a MySQL backend and the data stored by this application is expected to grow very rapidly. Oh, and we don't have a DBA. Well, I guess I am the DBA. ;)

As far as designing a database to be scalable, I don't even know where to start. Does anyone have any good tips or know of any good educational materials for a developer who has been sort of shoved into a DBA/database designer role and has been tasked with designing a scalable database to support an application like this? Have any other developers been through this sort of thing? What did you do to quickly become good at this role?

I've found some good slides on the subject here but it's hard to glean details from slides. Wish I could've attended that guy's talk.

I also found a good blog entry called 5 Ways to Boost MySQL Scalability which had some good information, though some of it was over my head.

tl;dr

I just want to make sure the database doesn't have to be completely redesigned when it scales up, and I'm looking for tips to get it right the first time. The answer I'm looking for is a "list of things every developer should know about making a scalable MySQL database so your application doesn't perform like crap when the data gets huge".

Answer 1

I think the usual rules apply:

• Keep tables small (don't waste space unnecessarily).
• Don't query for more information than you need.
• If you use ORMs, beware of common pitfalls such as the N+1 problem.
• Stay away from troublesome operators (e.g. Like '%Smith%').
• Design your indexes intelligently and make sure they cover the majority of the uses (here is a decent, if light, treatment of indexes).
• Remember that set-based querying is usually far superior in terms of performance than iterating through the data.
• Know when to denormalise data for performance reasons.
• Cache whatever can be cached (economically) to relieve the DB.

Of course, vertical scaling can only get you so far, and then you might have to look into horizontal scaling. Having said that, a good single-DB design can still take you very far - as far as I know, StackOverflow is still running a single DB instance. If you think you will need to handle far more data than that, consider sharding (or alternative DBs) early on.

Answer 2

The scalability highly depends on database schema design, and much more less depending on database performance.

I'd suggest following scenario of creating good database schema.

1. Normalize schema as much as possible.
2. Then, if you have any link tables - duplicate all keys through target tables.
3. Now - slightly denormalize all tables in order to get faster reports.

Answer 3

• Select by PK whenever possible;
• Keep tables small enough for all indices to fit in the memory. If that is not possible shard vertically. Should be easy enough if some of the tables are rarely or never used together;
• Avoid JOINs whenever possible, but on the other hand don't fall into 1+N trap;
• Avoid SELECT *, especially if any of the columns is a LOB (like TEXT). LOBs are never cached in memory, they are always fetched from HDD;
• Have dedicated slaves used for interactive queries, and separate ones for slow, complicated, aggregate report queries;
• Use InnoDB (default in current MySQL);
• Use slaves for SELECTs, master for INSERT/UPDATE/DELETEs;
• Batch similar queries, eg. if you have an insert on each page view, don't do database immediately, put it in cache and later on insert with multiple values instead;

Answer 4

I found that the defaults for MySQL are very, very conservative. This means you can run it out of the box on really old hardware, but that it's slow until you configure it. I went through these steps (just hacking on it really):

1. Determine what engine you will use (Isam, the default, does not support transactions)
2. Go through all the available options and read the docs
3. Adjust options upwards as much as possible

Not every option matters, but this covers most of the ground. Secondly, realize that alot of the back end is IO bound by your hard drive (tmp files, reading/writing large chunks of data) so pointing your mysql-data and /tmp folder to a SSD drive has a big boost in performance. That is easily done through the magic of symlinks.

Oh, and make sure that the DB schema supports and encourages good queries. Recognize that SQL is set based and you should use UUIDs.