Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database.

We're having a single MySQL db, and besides many really small tables, we have two tables with approx 500k-1.5m rows.

Currently, when performing DDL queries (adding new columns to these tables), we have to deal with quite a long execution time (around 4-5 mins).

I started to search for online schema migration tips and found the following:

Although these tactics are worth investigating, I started to feel unconfortable at all: I don't think, that tables with such a low number of rows have to use such advanced methods. (What about really huge tables then?)

So I was wondering, if anyone of you have tips regarding the mysql.cnf itself, or other performance tips for the ddl queries?

share|improve this question
    
I encourage you to read Aaron Brown's answer to the "mysql change schema on the fly" that you provided. Creating a second MySQL instance as a replica of your production environment can offer the flexibility to make a number of major changes (schema changes, MySQL configuration changes, even major version upgrades) with minimal downtime. –  Nathan Jolly Feb 26 at 6:03
    
Are the tables InnoDB? And, are you using MySQL 5.5? –  Michael - sqlbot Feb 26 at 12:05
    
@Michael-sqlbot: yes, all tables are innoDB and we're using MySQL 5.5 –  Johannes N. Feb 26 at 12:49

1 Answer 1

up vote 2 down vote accepted

I think that Percona's pt-online-schema-change might be a better solution for you as it can do an ALTER with very little downtime. It does, however, require adding a TRIGGER to the table.

Runnig DDL on your table will lead you into rebuilding your table, mysql is a row based engine so you will suffer the index rebuild this can be quite expensive depending on your engine, table complexity is factor as well.

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.