Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Migrating 1200 databases from MySQL to Postgres

psyenix.blogspot.com

Post reply on HN
Print view View original

51–60 of 85 posts

Re: Migrating 1200 databases from MySQL to Postgres

#51

Post by junkilo »

TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

Having used both from the prototyping stage to very large scale my answer: It doesn't matter.

They are both plenty feature complete to work well for relational data type problems. Grab the one you have more exposure to and you'll be fine.

That said, I'm still using mysql-innodb (latest percona) because the built-in replication has had more time to bake and its concurrency model (think 100k qps+) is plain better. For further reading see:

https://eng.uber.com/mysql-migration/

Re: Migrating 1200 databases from MySQL to Postgres

#53

Post by noir_lord »

K-Wall wrote: post #30
shoo wrote: post #12

Earlier quoted context omitted.

maybe there is an email thread shared between all clients for acquiring / releasing the write lock on the xml file

Semaphore as a service Slack Chat bot.

Seen that.

Shared drive with excel files, people would shout at each other to 'release the lock'

Re: Migrating 1200 databases from MySQL to Postgres

#54

Post by collyw »

tkyjonathan wrote: post #49

I'm not against the idea of using postgres, but I have a sneaky suspicion, that you could have gotten that 30% speed benefit from MySQL, if you did some performance work on it. Or in other words, I don't feel that migrating from one relational database to another similar relational database was worth that much trouble.

Thats interesting, as my dev experience would allow me to tune queries better in Postgres (the visual query planner makes it easier and there are more join types), while tuning them through configuration on either database would involve a lot more trail and error reading. I would more likely know what to do by migrating.

Re: Migrating 1200 databases from MySQL to Postgres

#55

Post by seanharr11 »

I wrote an open source tool that migrates between any 2 relational databases.

https://github.com/seanharr11/etlalchemy

This includes automatic migration of the schema, constraints, indexes and obviously data. It leverages fast bulk loading tools like mysqlimport, and PostgreSQL's COPY FROM. It doesn't need any config. Just plug and play.

The tool uses SQLAlchemy to abstract column types, and handles various column type conversions between any flavor of SQL.

The one thing it needs are tests, and scalability support for tables bigger than a few GB!

Re: Migrating 1200 databases from MySQL to Postgres

#57

Post by gaius »

Amezarak wrote: post #35
SOLAR_FIELDS wrote: post #5

One thing author did not touch on: since the tables were denormalized-ish in the original MySQL DB, did his application lose significant performance by having to perform the joins for every single query in the renormalized PostGres instance? Or were the DB's small enough and indexed properly enough so it didn't really matter? Might have been worthy of testing this to see if it was worth it to materialize certain view…

At least in my experience, joins in 100s of GB databases are not a performance problem. I've always been somewhat baffled by the claims I see pretty regularly that joins are a major performance concern. Was this a big deal in the past?

Was this a big deal in the past?

In MySQL, yes. It lacked the concept of foreign keys.

Re: Migrating 1200 databases from MySQL to Postgres

#58

Post by sigi45 »

I like the approach and think something like this is fun but i don't understand the businsess case at all.

I would have migrated MySQL 5.5 to 5.6 first, than cleaned the shit out of it, normalized everything, added a few indezes and suddently realize that there is not much left to do.

I like to work with postgresql but if you have 1,2k dbs which are more or less not claned up and crappy, what are they used anyway?

Re: Migrating 1200 databases from MySQL to Postgres

#59

Post by deathanatos »

TekMol wrote: post #28

Is it an universally accepted truth now that it's generally better to use Postgres then to use MySql? Would anybody here use MySql for a new project? If so, why?

https://grimoire.ca/mysql/choose-something-else

That's essentially my reasons for preferring PostgreSQL.

Re: Migrating 1200 databases from MySQL to Postgres

#60

Post by jboy55 »

zimpenfish wrote: post #4
overcast wrote: post #3

Earlier quoted context omitted.

You should see the one here. An old flat file database monstrosity from the 80s, imported into oracle, complete with all of the duplicated data. It's the most non relational, relational database ever.

Would a database stored in an XML file shared between 60+ clients over NFS beat that for monstrositiness?

Lol, if it sent emails, I might know of such a system
Post reply on HN