Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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".

share|improve this question
    
That "5 Ways" post does list some of the basic starting points in this area, and I'd really recommend getting up to speed with those -- you say some of it was over your head, but I will bet if you ask specific questions here/DBA SE/wherever applicable, you'll get it. Seriously. –  jcmeloni Mar 23 '12 at 14:04
    
I just now found this question and after reading some of the answers I'm wondering if I should even be worrying about scalability at this point. –  CFL_Jeff Mar 23 '12 at 14:31
    
Monolithic MySQL installations, no matter how great the underlying hardware is, will be bound by I/O (in most cases) or by CPU. There are many techniques how to scale, none of them involve a single server with single DB installation. One of the "tricks" is to have MySQL replicate to a few slaves and then you direct reads to the slaves. Since there are more slaves, it's irrelevant which one you choose for reading the data, so you can have a load balancer of sort that chooses which MySQL slave instance to read from. This is one of techniques that scales reads. –  N.B. Mar 23 '12 at 14:49

4 Answers 4

up vote 1 down vote accepted

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.

share|improve this answer

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.
share|improve this answer

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.

share|improve this answer
    
Just a note: InnoDB is the default table type as of MySQL 5.5.5 –  jcmeloni Mar 23 '12 at 14:09
1  
@jcmeloni and stable is MySQL 5.5.22 which most people pull with apt-get –  Spencer Rathbun Mar 23 '12 at 14:12
  • 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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.