5

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?

Improve this question
3
  • 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, 2014 at 6:03
  • Are the tables InnoDB? And, are you using MySQL 5.5?
    – Michael - sqlbot
    Feb 26, 2014 at 12:05
  • @Michael-sqlbot: yes, all tables are innoDB and we're using MySQL 5.5
    – Johannes N.
    Feb 26, 2014 at 12:49

1 Answer 1

Reset to default
4

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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