5 ms·
Ingesting MySQL data at scale – Part 1
- pbreit 10y agoCurious if Postgres would be up to the task here?
- Thaxll 10y agoWho do people feel the need to mention Postgres on every post talking about MySQL... It reminds me the same problem with Linux and *BSD.
- infamouscow 10y agoWhat drives me mad is when people claim they choose PostgreSQL over MySQL because data integrity concerns, yet they run PostgreSQL on Linux as opposed to FreeBSD or Illumos using ZFS.
- dijit 10y agoPostgres' MVCC is almost like copy-on-write by itself, so anything more than a journal is overkill in any realistic scenarios. Personally we tried ZFS with PostgreSQL and we managed to fill the WAL drive before the vacuum could clear it out with our update rate. So we went back to XFS on the more commonly adopted (in our company); CentOS
- deleted 10y ago[deleted]
- EGreg 10y agoBSD > Linux Postgres > Mysql (in most cases) But larger learning curve
- dump121 10y agoThey are two competing open source databases, knowing strength/weakness of each is always good. So unless the answer is `we rock, you suck`, it would be a useful info. for lots of people
- threeseed 10y agoPostgreSQL has been mentioned on EVERY database post for years now. And it always involve an endless amount of condescension in particular directed at those that god forbid choose to use a NoSQL database. Everything seems to get turned into a competition despite the fact that the world have moved away from the centralised, vertically scaled database towards a more heterogenous landscape.
- spudlyo 10y agoSadly at present Postgres has absolutely awful mysqldump support (It's not even on their roadmap) so I doubt it would be a good fit for Pinterest's environment.
- dijit 10y agois that a joke? of course they wont support mysqldump, but there is pg_dump, which does the same sort of thing except you can dump to binary and compress it on the way without pipes/forks.
- spudlyo 10y agoWait, hold the phone, are you telling me by migrating to Postgres I can shave a handful of system calls off my long-running i/o bound logical backups!? Oh Postgres, is there anything you can't do better than MySQL?
- dijit 10y agoNo I'm telling you that `mysqldump` will not support postgres the same way that a diesel truck will not take petrol.
- spudlyo 10y agoI'm glad you cleared that up, because for a moment there I thought your comment was advocating for Postgres in a thread ostensibly about MySQL by pointing out that pg_dump has baked-in compression; an optimization which could potentially shave milliseconds off a process that the article mentioned can take over 12 hours on large tables. So, putting aside the issue of Postgres not supporting mysqldump for the moment, there is also the issue of Postgres having poor support for the InnoDB on-disk format, and practically non-existent support for the MySQL client protocol. The more I think about this, the more I realize just how unsuitable Postgres is for Pinterest's current environment.
- dreaminvm 10y ago
- jasode 10y agoI notice you often ask that question on MySQL threads.[1][2] Here's the recurring pattern: if it's a single-instance database, either PostgreSQL or MySQL will work. However, if it is sharded database with multi-master replication, the overwhelming industry preference is MySQL instead of PostgreSQL. When it's just a single-instance db, a compelling case can be made for PostgreSQL because of features such as stricter type checking and stronger stored procedure language. However for sharded databases, there have been several high-profile case studies of migrations from PostgreSQL to MySQL including Etsy and Uber. I can't think of any major company doing the reverse of migrating a multi-master db from MySQL to PostgreSQL.[3] From an operational standpoint, the replication of Postgre dbs is fragile compared to MySQL. Instagram is a famous example of a large website using PostgreSQL but keep in mind that they did(do) master-slave instead of master-master. Reddit on Postgre is another example configured as master-slave. As for this particular thread about Pinterest, they use master-master and I've seen no evidence that Postgres+3rd party tools is superior for that scenario. If you disagree with the above assessment, it would be helpful if you explain how PostreSQL is equivalent or better than MySQL for multi-master db replication architectures. If you just repeatedly ask, "is Postgres good for this?", the replies don't seem to give you the answers you're looking for. If you have a strong position, you should state it so it guides further discussion. [1]https://news.ycombinator.com/item?id=10087412 https://news.ycombinator.com/item?id=10087412 [2]https://news.ycombinator.com/item?id=10926854 https://news.ycombinator.com/item?id=10926854 [3] when Uber migrated from MySQL to PostgreSQL in 2013, it was still a single-instance to single-instance migration. They did it to take advantage of PostGIS features. The subsequent 2016 operational difficulties of multi db replication pushed them back to MySQL.
- olavgg 10y agoI cannot understand the fuzz about multi-master replication, it either involves dirty/phantom reads, or it is awfully slow. Wikipedia has a lot of examples of these issues https://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Non-repeatable_reads https://en.wikipedia.org/wiki/Isolation_%28database_systems%... There should be a book about the dark side of multi-master replication at scale, about what can you trade away and still be happy with. Preferring either PostgreSQL or MySQL for this is just avoiding the technical complexity that multi-master replication is.
- netcraft 10y ago> Every day we collect more than 100 terabytes of data from MySQL databases that is an amazing amount of data - I dont use pinterest but had no idea they were that large.
- firasd 10y agoThey have over 100 million MAUs so even 1000 UTF-8 characters for each each active user (email, first name, last name, location, tokens, other stuff) would be up to 0.4 TB. Then add all the inactive users, and pins, tags, friend connections, and all the other data they'll process for recommendations/discovery.
- delta1 10y agoMAU == Monthly Active User for those that don't know the acronym like me
- spudlyo 10y agoI found this article very unsatisfying due to the lack of key details. This reads like a blog post tailored for an audience of folks with pointy hair. As far as I can tell, they went from Hadoop mappers pulling logical backups from the DBs using python and mysqldump to ... Hadoop mappers pulling logical backups from S3, which were pushed there by scripts running on the DBs, probably still using mysqldump. Although I have no idea, since there are no details. Are these backups physical? Logical? How is the 12 hour big table problem solved by this approach? Why was there a limitation on the number of mappers usable by the old approach? And what of the old system's DB failover problem? Nothing that can't be solved with a script that is failover aware! Nice. No reason to have dedicated ingestion slaves that aren't, you know, master candidates, when you have scripts that can restart themselves.
- shenli3514 10y agoIf there is a RDMS with great scale capacity, it will save the effort of data migration. Spark/Hadoop could read data from the RDMS directly.