Making MySQL Better at GitHub

https://github.com/blog/1880-making-mysql-better-at-github

by samlambert • 12 years ago

195 138 12 years ago

github.com

bluedino• 12 years ago

> now featuring a massively updated DB cluster with SSDs and 10GBs networking!

Any further details on the DB servers, SSDs that were used, and the 10Gb networking infrastructure?

_RPM• 12 years ago

"5am beer" Is it normal to stay in the office all night? (CS undergraduate, never had full time job)

samlambert14• 12 years ago

I think all of us where at home during this. I certainly was.

imbriaco2• 12 years ago

I was too. And I had that beer when we finished. ;)

drstewart• 12 years ago

No, but it's also not normal to migrate your production database servers to new datacenters. And when you do, you want to do this in a way that will minimally impact your users (i.e. after hours).

showerst• 12 years ago

Not at any decently functioning company, but during a huge operation like a data center migration or a core technology change, having all hands on deck during non-business hours is fairly normal.

15characterlimi2• 12 years ago

Migrations like this don't happen often, and being on-site would be common when you are running in a physical data center rather than the cloud. When we switched backend DB systems we did it from home.

angersock2• 12 years ago

Not at a company worth working at, no.

That sort of thing should be the extreme exception--anything else is just burning out employees.

pmontra• 12 years ago

I worked for a telco years ago. We had separate development and operations teams. Operations worked on 3 turns of 8 hours, 7x24. They took care of every installation (pre-production and production) and they usually did them in the night shift. The staff was rotated over the months and there was no particular employer burn out.

By the way, one full time equivalent (that is, a hypothetical person working 24 hours a day all year long) equalled to five real people. That is, if they wanted ten people to be always available they had to hire fifty. You can easily understand why these arrangements are not common for Internet companies. Furthermore telcos have different requirements. Devops wasn't there yet and I wonder if it is accepted by management even now. My bets are against it.

I also wonder if companies like Google, Amazon and Facebook are organized in that way too.

roskilli• 12 years ago

If you are on call/scheduled for the deployment you may be required from time to time to come online and deploy in off-peak times. This type of maintenance should be extremely rare - in that it requires making your service entirely unavailable, most deployments should be able to be done in a rolling manner and not affect ongoing service availability.

Also usually you are on call every so-often so if your team/company happens to do this often it's not always you performing the deployment.

In regards to "being in the office": most people will do this from home after sleeping most of the night and waking up to an alarm and then coming in a little later the next day. There are a few hardcore ones out there that prefer to pull an all-nighter and do so from the office, although my mileage has been those are quite few and far between.

toomuchtodo3• 12 years ago

In regards to "being in the office": most people will do this from home after sleeping most of the night and waking up to an alarm and then coming in a little later the next day. There are a few hardcore ones out there that prefer to pull an all-nighter and do so from the office, although my mileage has been those are quite few and far between.

DevOps/Admin here! I prefer the all-nighter, but I've almost always (in 14 hours) done it from home unless physical hardware had to be moved (i.e. forklifted datacenter to datacenter).

AYBABTME• 12 years ago

Experience tells me an allnighter would be a poor idea, as sleep deprivation is just as harmful to cognitive abilities than being drunk. Especially in the times between 5AM and 8AM, which I've found affects individuals the most.

If something goes wrong with your database, you'll be in a shitty condition to make fast and sound judgement calls.

One could say that not everybody's the same, but in those conditions I think that's not true. I've trained extensively under sleep deprived conditions in the military, where we were continually asked to make quick decisions in harsh conditions. Everybody's bad when sleep deprived and everybody's especially shitty at 5AM.

toomuchtodo3• 12 years ago

During massive migrations, yes. Doesn't count if you're working remote though, you're already at home.

ojbyrne• 12 years ago

If you read carefully, it seems that it was 5am Saturday, not through the night, and it looks like it was done by 7:15am. The beer was just labelled with the start of the process.

nathan_f77• 12 years ago

It's not normal, but this was an exceptional situation.

nodesocket2• 12 years ago

Does GitHub use Percona or MariaDB? We (https://commando.io) switched to Percona, mainly to use their Xtrabackup feature which can do streaming type backups without bringing MySQL to its knees. Also, Percona supports an awesome custom configuration option:

    thread_handling=pool-of-threads

http://www.percona.com/doc/percona-server/5.5/performance/th...

samlambert14• 12 years ago

I have tested thread pooling with our workload and it didn't go so well. Also

Note This feature implementation is considered BETA quality.

nodesocket2• 12 years ago

Interesting, love to see your results some day. How are you doing backups? I assume you are not just using mysqldump?

15characterlimi2• 12 years ago

Are you using Percona XtraDB Cluster?

nrbafna• 12 years ago

Do you some figures around improvement from changing thread_handling to a pool instead of one per thread?

The queries on my db server, fit the documented use case for this feature - lots of short live queries.

morgo5• 12 years ago

The analogy I would use for thread-pool is to insert a waiter in front of the chefs in the kitchen.

It doesn't make sense for all workloads, but I have found thread pool to be useful in cases where application servers can overload database servers (either via misconfigured connection pooling, or no pooling).

An example where it might make less sense: a dedicated worker queue running in N threads connecting to MySQL.

morgo5• 12 years ago

It is worth mentioning that commercial distributions of MySQL feature pool-of-threads as well. Percona Server features what is essentially a re-implementation: http://dev.mysql.com/doc/refman/5.5/en/faqs-thread-pool.html

Thaxll2• 12 years ago

Why won't you use the Oracle version? atm MariaDB isn't a replacement for the stock version of MySQL.

toomuchtodo3• 12 years ago

MariaDB is a drop-in replacement for stock MySQL. It's performance is actually better than MySQL.

https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibi...

sjwright8• 12 years ago

Our experience switching from Oracle's MySQL to MariaDB was a solid improvement in performance -- particularly on certain queries where MariaDB's superior query optimizations kick in.

We've also migrated many tables to TokuDB storage engine, and seen phenomenal improvements in performance and scaling. It's so good we were able to de-partition and de-archive our largest tables with no performance penalty.

If you haven't tried MariaDB yet, try it.

If your database is reasonably large (10GB+) and you haven't tried TokuDB yet, TRY IT.

walden42• 12 years ago

Why not just run backups on a replica suited specifically for that purpose?

gatehouse• 12 years ago

They mentioned that their new config has a delayed replica. Can anyone comment on how useful this actually is?

I do snapshots + binlogs so I can do a point-in-time recovery to any time in the last month. So obviously a delayed replica would be a faster way to recover from human error at the MySQL prompt. But it would still require human intervention which is slow and can't really be automated. On the other hand, presumably a process already exists to bring a replica up from scratch, and that could be done and paused at a certain point. So it seems like a lot of extra effort and hardware for a really narrow and constrained benefit.

Anyone running a delayed replica -- is this wrong? Has it been used ever? often? Worth it?

samlambert14• 12 years ago

It is certainly helpful for grabbing bits of data that you want quickly, it is also another line of defense for DR.

If we make a change and want to quickly compare against an older version of the data delayed slaves are super handy.

We can also restore to any point in time with binlogs.

morgo5• 12 years ago

Delayed replicas is a MySQL 5.6 feature (and can be emulated in previous versions).

You are correct in that the main use-case is fast recovery from human error. But as a DBA, I can tell you that accidents like this cover 90% of disasters :)

sjwright8• 12 years ago

We use a delayed replica for our off-site backups. It's a little bit of safety, plus it comes in handy for sanity checking previous state after making big global changes.

SEJeff2• 12 years ago

Wait, you mean relational databases actually can scale? Say it ain't so! - end sarcasm

Personally, I've always been wowed at what youtube does with mysql. See the entire vitess[1] project for an idea. Thanks github for writing this up though, very neat.

[1] https://github.com/youtube/vitess

threeseed3• 12 years ago

You do realise that most of people using MySQL at that scale aren't using it as a relational store ? They are sharding in the application layer and using it as a dumb key-value store.

No one would argue that MySQL's database engines can't scale. But you could argue that the relational model doesn't scale.

samlambert14• 12 years ago

I think at massive scale a lot of models break down, and I would agree sharding is the key to huge scale.

I often see "MySQL doesn't scale" posts which simply isn't true. I just wish people would stick with it longer and iron out their problems.

jeffdavis2• 12 years ago

"But you could argue that the relational model doesn't scale."

How would one make such an argument? The relational model is simply a combination of logic and set theory used for manipulating data. It's orthogonal to scalability concerns.

opendais• 12 years ago

I'm tired of the "lol lets mock NoSQL fanbois" behavior on HN. You fail to realize you are acting exactly like the people you are mocking.

Generally the use case for "scale" with NoSQL isn't that MySQL isn't technically capable. It is a cost/benefit for a specific use case.

For instance, if you are storing counters that are purely tracked via key/value ... MySQL is a terrible choice from a server-cost-to-performance-perspective.

BradRuderman• 12 years ago

I would love to know more about the specific setup they use? Do they use snowflake or something for sharding?

stonewhite• 12 years ago

I thought this 'GitHub making MySQL better' was more along the lines of 'Facebook making PHP better'. But impressive nevertheless.

electrum• 12 years ago

Facebook, Google, LinkedIn, and Twitter making MySQL better: http://webscalesql.org/

derengel• 12 years ago

Talking out of ignorance, but why is MySQL so dominant in big companies? why do these companies choose MySQL over Postgresql? I see people bashing MySQL all the time because of Oracle, but technically, is MySQL less capable than Postgres? Would it be wrong to start a new business on MySQL?

samlambert14• 12 years ago

WebscaleSQL is a great project.

coops3• 12 years ago

It would be interesting if someone from Github could discuss why they chose to do this migration by taking the whole site offline and doing the migration all at once. Did anyone investigate if this could be done without taking the site offline?

samlambert14• 12 years ago

Doing this online would of been very tricky while maintaining 100% consistency. We perform major infrastructure changes often without ever having to take the site offline. In this case and at this time it was unavoidable.

I feel 13 minutes of maintenance at 5am PST was a good trade off for the benefits we gained.

coops3• 12 years ago

Can you go into more detail regarding the prohibitive consistency issues? How do you maintain consistency in steady-state (ie. not during migrations?) Also, how do you make the call as to whether to bring your site down vs. attempting a live migration?

why-el• 12 years ago

I think its a smart decision, given the nature of the product. An off time of 14 minutes on a Saturday very early morning is a price they were willing to pay to make this a one time operation with no (actually reduced) risks of losing data consistency and other pitfalls that come with a live migration.

ck23• 12 years ago

Did you remember to move the undo log outside of ibdata1 ?

http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespac...

Because in mysql, ibdata1 can never shrink, only grow.

And that's how you end up with massive ibdata1 files that cannot be managed.

Oh and undo can only be moved outside of ibdata when mysql is being initialized, not afterwards.

innodb has some serious flaws

http://www.percona.com/blog/wp-content/uploads/2010/04/InnoD...

lmz• 12 years ago

Can you actually shrink the undo logs once you move them out of ibdata1? From your link:

Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces.

The most important part to control ibdata1 growth is innodb_file_per_table (default now).

ck23• 12 years ago

No you cannot shrink them but more importantly they do not have to be cached.

The rest of ibdata1 has to be cached for performance.

Impossible to isolate the caching needs unless undo is moved outside and 99% of servers out there have probably not been setup with external undo logs because most admin do not know of this limitation after it has been configured.

Only way around this is to rebuild the entire database, loads of fun.

vtal2• 12 years ago

couldn't you use innodb_file_per_table and not worry about the huge ibdata1 file?

ck23• 12 years ago

No file_per_table does not change anything about the unlimited growth of the undo log in the ibdata1 file.

tsuraan• 12 years ago

Is there a document on how GitHub uses MySQL? Does it actually store git objects (either as binaries or in a logical equivalent with foreign keys, etc), or is the database just used for higher-level things, like users, organizations, etc?

technoweenie• 12 years ago

We use MySQL with ActiveRecord primarily (with a few exceptions). So that's all Users, Repositories, Issues, etc.

Git data gets stored with Git on separate file servers.

EGreg• 12 years ago

I say it's not fully shipped until it's distributed

DigitalSea• 12 years ago

Anyone else disappointed the post didn't go into all that much detail? Scaling databases is hard if you're not privileged enough to have access to large pools of money to hire good DBA's.

Interestingly, I would love to see companies like Github opening up their database schemas to the public with mock data. Scaling is one aspect, but the best thing you can do in the beginning is to create a solid schema (normalise, denormalise...) it would be interesting to see what Github uses and why. Still awesome to see MySQL being the choice most large companies like Github choose in the face of new and untested NoSQL databases like MongoDB.

seabrookmx3• 12 years ago

new and untested NoSQL databases

MongoDB is new and untested? What rock do you live under?

It is new compared to many of the veteran Relational databases like SQL Server (and I don't mean that in a bad way), but it is a proven technology used by many. See http://www.mongodb.com/who-uses-mongodb

karlick88• 12 years ago

Unfortunately, many of those who used mongodb regretted the decision

Mongodb is a good db but it is not good for many use cases that people are using them for. There are a lot of companies that realized the mistake and are actively migrating off it. I know we are planning a "Off-Mongo party" with another company once we both manage to migrate off

ayrx• 12 years ago

Calling it new and untested is a bit too much. It's probably more accurate to describe it as being tested and found unworthy for most use cases.

calinet64• 12 years ago

Or better, more solid relational options like Postgres...

Honestly, is there any reason to use MySQL over Postgres at this point? Or is it sort of six of one half a dozen of the other as long as the data model is decent?

btown• 12 years ago

MySQL historically had more support for HA/clustering than Postgres. Recently, there's been a lot of progress on integrating Postgres clustering into the core, to the point where it's mature, but perhaps not as battle-tested. Not a reason to choose MySQL for a startup, I think, but if you've got a cluster working on MySQL and a clear understanding of its pitfalls, there's no real reason to switch to Postgres.

markwatson• 12 years ago

We chose MySQL where I work only because it's easier to hire people with a lot of MySQL knowledge. Technical reasons aren't the only ones when deciding what software/framework/libraries to use.

vtal2• 12 years ago

1. Monitoring and administration tools for MySQL are (better?) more polished. 2. WAY easier to find MySQL DBA's vs. PG DBA's 3. More resources in general around MySQL. Whatever problem/issue you have it's out there already.

otterley• 12 years ago

One minor reason I noticed recently is that you can't change Postgres' listen(2) backlog. This can have a significant impact on responsiveness.

d4mi3n• 12 years ago

Tooling. While I agree psql tends to be more performant, when you're dealing with groups of people having good tools (and good documentation for those tools) trumps many considerations.

As an example, the organization I work at is considering a move to psql, but our main barrier is a lack of good DB clients that are accessible to people who aren't software developers. The best we're aware of as far as DB GUIs for Postgres is pgAdmin, whereas if we're talking MySQL you have things like MySQL workbench, SQLPro, and a myriad of other applications available for whatever your operating system of choice is.

samlambert14• 12 years ago

There are a lot of reasons IMO. MySQL has a proven track record for stability and performance powering huge sites. I would say MySQL has a nicer replication story as well.

In general MySQL is a lot more widely used with a greater pool of knowledge out there.

collyw• 12 years ago

MySQL is like PHP, its installed almost everywhere, and requires less set up. I did have one problem recently where MySQL turned out to be a better solution, as it has a native bitcount operation, and a larger set of numerical types.

erkkie3• 12 years ago

After Postgres got replication out of the box I'm pretty sure it's just inertia and the fact that it's installed everywhere keeping mysql usage up. Postgres is so much nicer when dealing with day-to-day things.

Eg disabling connections per database using sql and having built-in query stats (http://www.postgresql.org/docs/9.4/static/pgstatstatements.h...) just made me smile.

ksec• 12 years ago

I thought it was more to do with Github is based on Rails, and MySQl is the default choice.

Yes, I really wish DHH and the Basecamp team would move to Postgre , therefore moving the community to it.

cwyers3• 12 years ago

INSERT IGNORE and REPLACE are two pretty good reasons, in my opinion. Postgres also doesn't have real table partitioning. Yes, you can sorta kinda hack around the first with stored procs. And yes, you can do something that looks a lot like partitioned tables using table inheritance. And yes, Postgres now has replication support, if you don't mind using only row-based replication (MySQL lets you choose between row-based and statement-based replication) among other tradeoffs.

So yeah, Postgres is a better relational database than MySQL, if you ignore all the things MySQL does better. Another great thing about Postgres is that whenever a site like Hacker News gets a thread about MySQL, you get a bunch of people asking why you aren't using Postgres instead, and then whenever you try and answer the question you get a bunch of Postgres users to tell you you're wrong or that the features you care about don't matter (or that they're hard to implement, which... this is my problem why?) or that Postgres really has replication that's as good as MySQL's this time we pinky swear. So most MySQL users get a first impression of Postgres' user community that is quite frankly rather unfavorable.

Oh, and MySQL has a lot more third-party documentation, tooling and support available.

sjwright8• 12 years ago

I was planning to move to Postgres, until TokuDB went open source.

threeseed3• 12 years ago

Scaling databases is EASY if you pick a database designed for it. I've personally managed Cassandra, HBase and Riak on 30+ node clusters for companies. Almost no issues.

It shows how little you know about real world scalability when you actually suggest MySQL over something like Cassandra. It is a nightmare to scale unless you are sharding in your application layer.

Xorlev2• 12 years ago

It can be easier, but saying it's ever easy belies the bevy of issues that can (and will) always crop up.

samlambert14• 12 years ago

The problem with going into too much detail is that it can become a list of recommendations that might not even apply to different people's infrastructure.

Most of the time MySQL optimization is workload specific.

shamsulbuddy3• 12 years ago

Recently i have got the task to work on a 10 GB and 12 Million rows table to make it work with complex aggregation queries like SUM or COUNT.

Server - Ubuntu 14.0.4 Mysql - Percona XtraDB Cluster 5.6 RAM - 16 GB CPU - 8 Cores - 2.95 Ghz SSD - 100 GB

I tried many options --

1) Increased the innodb_buffer_pool_size to 8GB on a 16GB RAM machine -- It helped but nothing magical here 2) Add few more Keys on Date based columns and force the users in front end to select at least one date range column -- Seen some performance gain here 3) Tried MyISAM engine -- I would say in present days MyISAM is history since Innodb itself is pretty much comparable to MyISAM -- so didn't see amy much performance gain .. one disadvantage was that loading of this 12 Million rows took ages .. and also "SELECT table_rows FROM information_schema.tables" had hanged , so i was not able to to figure out that how many rows have been loaded in my Table. 4) Finally I tried Partitioning the Table based on a DATE column -- Massive Performance gain .. If the user has selected a date range which falls under one or two partitions , then you can get the results very fast , even if it falls in many partitions still the performance is acceptable .. Useful ref - http://www.slideshare.net/datacharmer/mysql-partitions-tutor... -- remember that its Mandatory that you add that date column as part of Primary Key if you are Partitioning on that date column .

--- For Ref -- my Innodb settings

# INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 6G bulk-insert-buffer-size = 256M innodb_thread_concurrency = 16

Xorlev2• 12 years ago

If it truly was only 10GB of data, something was seriously wrong with your table, schema, or query.

shamsulbuddy3• 12 years ago

Do you think that 10GB data is easily to manage .. what all common practices do you suggest . any examples with use case

sjwright8• 12 years ago

My suggestion is to try TokuDB storage engine on that table. Our (admittedly text heavy) workload got order of magnitude scalability improvements and a dramatic fall in query times.

shamsulbuddy3• 12 years ago

I tried Tokudb as well and below are the stats comparison for complex SUM and COUNT query. Please note that the number of rows and size is exactly the same and there is a partition on a Date column

Table size 10GB and rows 12 Million

Also note that I am using the default settings of Tokudb

With Tokudb - 1 min 41.38 sec With Innodb - 58.17 sec

So almost double.

molixiaoge• 12 years ago

get it

revskill2• 12 years ago

Event Sourcing could help scale database infinitely. But why Github not using it ?

samlambert14• 12 years ago

How do you scale the event store?

revskill2• 12 years ago

It's easy to support sharding because there is one table needed to scale based on one column (AggregateId).

_pmf_• 12 years ago

A 20 point manual checklist for performing critical infrastructure updates does not sound very DevOpsy to me.

MaulingMonkey• 12 years ago

Obligatory http://xkcd.com/1205/ . Checklists are an insanely super cheap way to provide a repeatable process. They're not sexy, perhaps, but they're damn useful.

There are three factors here for me:

I automate things if it will save me time. I automate things if it will provide necessary reliability to the process. I automate some things which annoy me to do manually, even if I can't justify it on either of those basis.

The second one is tricky. When things go pear shaped in new and interesting ways 10 points into your automatic scripts, do they correctly and automatically recover? Mine generally don't. I'm going to react better to things going pear shaped.

But my coworkers don't necessarily have the same attention to detail with my checklist. This can be because they're not as familiar with the tools, or simply don't believe the rigor justified, or necessary.

Making MySQL Better at GitHub

Related Stories