3 ms·
In certain scenarios if you need to modify the schema for a table in MySQL it will lead to the entire table being locked, and for large tables this could lead t
by jschmitz28 8y ago
In certain scenarios if you need to modify the schema for a table in MySQL it will lead to the entire table being locked, and for large tables this could lead to a noticeable outage for users if you need to run queries on that table. One case I had where we faced this problem was changing the primary key for a table from 32 bit to 64 bit ints since we were running out of space. We used Percona's online schema change tool for handling this, which wrapped the creation of a new 'ghost' table (which has the target schema you want), rate limited writes from original table to ghost table, triggered writes from original table to ghost table as new writes came in, and finally a table rename from the ghost table back to the original table name in order to perform the full migration with no data loss or outage.
Sounds like this tool is doing something similar but avoiding the use of triggers for flexibility.
- analogmemory 8y agoAh ok! This sound like a great tool then. I have no need for it, but good one to star for a day when I might need it :)
- toomanybeersies 8y agoWe had to do something similar at my old job, but rather than migrating to a different schema, we were migrating our moderately sized DB (tens of gigabytes) from MySQL to Postgres. We dual wrote to both DBs while we copied the existing data to the new DB, then switched them over. I think we had less than 5 minutes of downtime all up.
- y4mi 8y agotens of GB is tiny though. most production systems are at least a few hundred gb, and the previously mentioned scaling problems from foreign keys and constraints are pretty nonexistent unless you're starting to push the boundaries of normal ACID DBs. i.e. a few TB of data with at least thousands of queries per second and lots of writes/updates
- manigandham 8y agoModern advice: always use 64 bit integer ids. If it's a small table, it won't matter. If it's a big table, you'll need them anyway.
- jschmitz28 8y agoYeah, this was a case of somebody (definitely not me, I would never!) generating a table in Rails, before Rails used 64 bit IDs on primary keys by default, and no one noticed before the table got really large. It looks like newer versions of Rails are doing it by default now: http://www.mccartie.com/2016/12/05/rails-5.1.html http://www.mccartie.com/2016/12/05/rails-5.1.html
- daigoba66 8y agoThat’s not always the best advice. Consider a table that you know will only have a few rows, but is referenced in one or more FK columns. If I use a 64bit integer when 32bit (or smaller!) is enough, I’m now using twice as much space for the FK column. If there are millions of rows, plus indexes, that can add up pretty fast.