Migrating 1200 databases from MySQL to Postgres • Hacker News Board

Re: Migrating 1200 databases from MySQL to Postgres

#11

Post by koolba » Wed, Aug 16, 2017, 2:12 PM UTC

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…

Alternatively did the application speed up after it was normalized? A normalized data set can be substantially smaller which may allow the entire data set to fit in memory.

Also, it's officially "PostgreSQL" or informally "Postgres". Not "PostGres".

Re: Migrating 1200 databases from MySQL to Postgres

#12

Post by shoo » Wed, Aug 16, 2017, 2:14 PM UTC

cntlzw wrote: post #7

zimpenfish wrote: post #4

Earlier quoted context omitted.

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

How concurrent is that? Can't imagine how writing to the file is even achieved.

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

Re: Migrating 1200 databases from MySQL to Postgres

#13

Post by fermuch » Wed, Aug 16, 2017, 2:16 PM UTC

aidos wrote: post #8

I went through a similar process a few years ago (4k dbs into a single db, all mysql). You'll get something working fairly quickly and then spend days debugging data anomalies that have crept in over the years. We ended up with a frankenstein mixture of bash scripts, a sequence of sql transformations, some python and some php to decode some of the data that had been stored in pickled / php encoded packages. It's not…

It truly sounds like a nightmare. How much time did it took to move everything to psql, and how long until it was stable enough to not keep an eye on it?

Re: Migrating 1200 databases from MySQL to Postgres

#14

Post by ishi » Wed, Aug 16, 2017, 2:20 PM UTC

cntlzw wrote: post #10

noir_lord wrote: post #2

Would love to do this to the monstrosity I inherited in new job but it's a sispheyan task. One day.

One time I saw a database, with a single table, with a single text column that contained the old database.

It's databases all the way down...

Re: Migrating 1200 databases from MySQL to Postgres

#15

Post by JelteF » Wed, Aug 16, 2017, 2:25 PM UTC

One of the main things I ran into when migrating from MySQL to postgres was that the default text columns (TEXT, VARCHAR, etc) behave differently when searching. MySQL matches text case insensitive, while postgres matches it case sensitive. This resulted in searches for content suddenly not returning data. Luckily there's an official CITEXT extension for postgres [1], which matches text case insensitive and adds the correct indices for doing so efficiently.

[1] https://www.postgresql.org/docs/9.1/static/citext.html

Re: Migrating 1200 databases from MySQL to Postgres

#16

Post by toomuchtodo » Wed, Aug 16, 2017, 2:26 PM UTC

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?

This is a special level of hell.

Re: Migrating 1200 databases from MySQL to Postgres

#17

Post by aidos » Wed, Aug 16, 2017, 2:31 PM UTC

fermuch wrote: post #13

aidos wrote: post #8

I went through a similar process a few years ago (4k dbs into a single db, all mysql). You'll get something working fairly quickly and then spend days debugging data anomalies that have crept in over the years. We ended up with a frankenstein mixture of bash scripts, a sequence of sql transformations, some python and some php to decode some of the data that had been stored in pickled / php encoded packages. It's not…

It truly sounds like a nightmare. How much time did it took to move everything to psql, and how long until it was stable enough to not keep an eye on it?

To clarify, it all stayed on mysql — just pulled everything into a single db.

I can't remember exactly but the whole project was done in a real hurry, about 4 weeks I think. It included a rewrite of the application / website (about 20k lines of backend code, html and js / css), migration of all the data, creation of new transcoders, migration of a lot of data asset/video data from another server.

No idea how we got through it all in retrospect, but I had to. A few days after we finished I got married and went off on my honeymoon, so I guess I decided it was stable enough the day I released it...! :-)

Re: Migrating 1200 databases from MySQL to Postgres

#18

Post by berns » Wed, Aug 16, 2017, 2:35 PM UTC

One thing that Postgres lacks is accent insensitive collations. Having clients with databases in Spanish, this is one of the reasons I wouldn't consider migrating to Postgres. I know I can use the unaccent extension, but I consider it a poor substitute to proper collations. I guess this isn't a problem for most people because it's never mentioned.

Re: Migrating 1200 databases from MySQL to Postgres

#19

Post by pasta » Wed, Aug 16, 2017, 2:36 PM UTC

JelteF wrote: post #15

One of the main things I ran into when migrating from MySQL to postgres was that the default text columns (TEXT, VARCHAR, etc) behave differently when searching. MySQL matches text case insensitive, while postgres matches it case sensitive. This resulted in searches for content suddenly not returning data. Luckily there's an official CITEXT extension for postgres [1], which matches text case insensitive and adds the…

"MySQL matches text case insensitive"

This is of course only when you set a case insensitive collation (_ci).

But I agree this can be a problem because case insensitive collations are used a lot in MySQL.

Also other collations can be a trouble. For example the collation that returns results for both 'ß' and 'ss' (German)

Re: Migrating 1200 databases from MySQL to Postgres

#20

Post by zimpenfish » Wed, Aug 16, 2017, 2:47 PM UTC

cntlzw wrote: post #7

zimpenfish wrote: post #4

Earlier quoted context omitted.

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

How concurrent is that? Can't imagine how writing to the file is even achieved.

"Badly". At least once a week someone managed to overwrite a change another client had just made. When I left, they were running a cron to copy it every minute as a "quick restore" when it happened.