2

I'm managing an enterprise web application that uses Amazon's RDS as our database server. Our architecture is such that when a user signs up for a new account we create a brand new database for them in RDS.

The database has around 63 tables, most of which have indexes, and almost all have foreign key constraints. Currently we're creating each new database by executing a large set of sql statements. This takes on average 57 seconds.

Does anyone have any ideas for how I could do this quicker or more efficiently?

3
  • 2
    This doesn't sound scalable at all. Every user gets their own database? How large could your user base potentially get? Nov 9, 2011 at 16:45
  • 1
    I absolutely agree! We expect to have 100's of thousands of user accounts! Unfortunately the architecture was already put in place before I took over the project. I'm going to see if I can get permission to re-write it but for now this is what I'm stuck with.
    – Mel Green
    Nov 9, 2011 at 22:21
  • Fair enough. Legacy architecture is a pain. Nov 10, 2011 at 3:35

3 Answers 3

2

You might want to try and execute the query as a "bulk transaction" to skip the verification checks. Be warned, this can create duplicate indexes & break consistency checks if you're not careful.

7
  • I do wrap the entire set of queries in a transaction. However I believe that CREATE TABlE and CREATE INDEX (which is about half of the statements) implicitly end a transaction. Do you know a way to prevent them from doing that?
    – Mel Green
    Nov 8, 2011 at 22:38
  • Honestly, when doing "CREATE TABLE" I don't believe there are any consistency checks beyond the "does X table already exist?"... Are you populating the tables before creating the indexes or after? Have you done any sort of performance analysis to find out where the "slow-bits" are?
    – TheCompWiz
    Nov 9, 2011 at 14:19
  • You might also want to look at using the "LOAD DATA INFILE" method of fleshing out your database? ... just build a blank system... do a "SELECT ... INTO OUTFILE" and then load the file directly into the database. I don't believe there is any faster/more efficient method. You could also do mysqldump/mysqlimport perhaps...
    – TheCompWiz
    Nov 9, 2011 at 14:21
  • The process I'm working with doesn't populate any data into these tables. It simply creates them, adds their indexes, and then creates the foreign key relationships. The amount of time each of these 3 parts take to execute is pretty equal with the index creations running slightly faster.
    – Mel Green
    Nov 9, 2011 at 22:23
  • 1min+ to do nothing but create tables/indexes with 0 data? ... either your box has insufficient memory... slow disk IO... or some random who-knows-what. I've created hundreds of tables with various indexed fields/relations between the tables... and NEVER had it more than a few seconds (5-10)... I can't even imagine what you're doing. As far as rapid-deployment goes... how much do the mysql instances vary? Perhaps you can simply build the db once... then take the db offline... copy the data files and then re-attach them for each new instance.
    – TheCompWiz
    Dec 8, 2011 at 14:23
1

There is no way to increase this speed for example up to 5 sec. I think that you have to use some queue logic and create such BD in the background. For customers this will be just: "DB creation in progress"

1
  • Yeah, I've already got the experience set up for the user, letting them know that the database is being created. It works, but a minute seems like a long time to make the user wait =\
    – Mel Green
    Nov 8, 2011 at 22:37
0

Thank you to everyone for the suggestions, I did ultimately come up with a pretty good solution and wanted to share it here for future readers!

Ultimately I was able to tweak our database architecture, combining/removing some tables, which simplified the overall design and reduced the time required to run the creation script. I also re-wrote our creation script so that it would perform one query per table instead of three queries (one to create the table, second altering the table to add the indexes, third creating the foreign keys). These two things did give a big improvement on running the script, it went from around 50 seconds on average to 8 seconds!

Another thing that we did which makes a big difference for the user experience was to create a service that runs independently on one of our servers that will do nothing but create new databases that sit empty waiting for a new user sign-up. When a user signs up their account is simply assigned to the next available empty database. We've set it up so that the DB creator service won't get more than 100 ahead of what our actual account needs are. This design has decreased the time the user waits when signing up to 1 or 2 seconds. It all feels a little "hacky" but ultimately it's a huge improvement!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .