28 ms·

Making MySQL Better at GitHub

195 points by samlambert 12y ago

tsuraan 12y 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 12y 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.
bluedino 12y 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 12y ago
"5am beer" Is it normal to stay in the office all night? (CS undergraduate, never had full time job)
- samlambert 12y ago
  I think all of us where at home during this. I certainly was.
  - imbriaco 12y ago
    I was too. And I had that beer when we finished. ;)
    - samlambert 12y ago
      So awesome.
- drstewart 12y 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 12y 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.
- 15characterlimi 12y 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.
- angersock 12y 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 12y 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.
    - angersock 12y ago
      Sounds like a sound way of managing it...as others have pointed out, occasional on-call duty or what have you is fine--it's the idea that staying at the office until 3 is "normal" and accepted as part of your equity payout is insane.
- roskilli 12y 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.
  - toomuchtodo 12y 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 12y 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.
    - wastedhours 12y ago
      The last sentence is interesting to me - I wonder if there are many sys/devops types who are morning people? I'm a developer and function much better at 5am than 11pm, but it'd be an interesting correlation if server-peeps were almost exclusively nightowls.
- toomuchtodo 12y ago
  During massive migrations, yes. Doesn't count if you're working remote though, you're already at home.
- ojbyrne 12y 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 12y ago
  It's not normal, but this was an exceptional situation.
nodesocket 12y ago
Does GitHub use Percona or MariaDB? We (https://commando.io 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/threadpool.html http://www.percona.com/doc/percona-server/5.5/performance/th...
- samlambert 12y 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.
  - nodesocket 12y ago
    Interesting, love to see your results some day. How are you doing backups? I assume you are not just using mysqldump?
    - samlambert 12y ago
      Both Xtrabackups and mysqldump for logical backups.
  - 15characterlimi 12y ago
    Are you using Percona XtraDB Cluster?
    - samlambert 12y ago
      Nope.
- nrbafna 12y 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.
gatehouse 12y 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?
- samlambert 12y 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.
- morgo 12y 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 :)
- sjwright 12y 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.
SEJeff 12y 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 https://github.com/youtube/vitess
- deleted 12y ago
  [deleted]
  - toomuchtodo 12y ago
    Didn't Google scale Adwords primarily with MySQL?
    - jeremycole 12y ago
      Yes, as well as Youtube, Twitter, and Facebook...
- threeseed 12y 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.
  - samlambert 12y 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.
  - jeffdavis 12y 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.
BradRuderman 12y ago
I would love to know more about the specific setup they use? Do they use snowflake or something for sharding?
stonewhite 12y ago
I thought this 'GitHub making MySQL better' was more along the lines of 'Facebook making PHP better'. But impressive nevertheless.
- electrum 12y ago
  Facebook, Google, LinkedIn, and Twitter making MySQL better: http://webscalesql.org/ http://webscalesql.org/
  - derengel 12y 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?
    - samlambert 12y ago
      > why do these companies choose MySQL over Postgresql? Even though it is not without its faults it is a reliable and high performing database, which is what is needed at scale. > is MySQL less capable than Postgres? Definitely not.
      - yangyang 12y ago
        > Definitely not. Have you used both professionally? Not being arsey, seriously interested.
    - fulafel 12y ago
      MySQL used to suck a lot more some years ago (tip of the iceberg: no transaction support!), the Oracle business is small potatoes compared to numerous earlier shortcomings that lead to data loss left and right. Inertia explains the current MySQL position reasonably well, a better question is how did it climb to its current position during its years of technical incompetence.
coops 12y 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?
- samlambert 12y 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.
  - coops 12y 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?
    - imbriaco 12y ago
      http://en.wikipedia.org/wiki/Split-brain_(computing) http://en.wikipedia.org/wiki/Split-brain_(computing)
      - coops 12y ago
        Yes, this is certainly a state you would desire to avoid. However, you didn't answer any of my questions.
- why-el 12y 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.
ck2 12y ago
Did you remember to move the undo log outside of ibdata1 ? http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html 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/InnoDB_int.png http://www.percona.com/blog/wp-content/uploads/2010/04/InnoD...
- lmz 12y 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).
  - ck2 12y 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.
- vtal 12y ago
  couldn't you use innodb_file_per_table and not worry about the huge ibdata1 file?
  - ck2 12y ago
    No file_per_table does not change anything about the unlimited growth of the undo log in the ibdata1 file.
EGreg 12y ago
I say it's not fully shipped until it's distributed
DigitalSea 12y 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.
- seabrookmx 12y 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 http://www.mongodb.com/who-uses-mongodb
  - karlick88 12y 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
    - threeseed 12y ago
      You are either being disingenuous or you're ignorant. Many of the companies that switched off MongoDB were growing and ended up moving up to databases like Cassandra. MongoDB is a great database from when you're starting to when you're mid sized. Cassandra destroys PostgreSQL in scalability but we don't say PostgreSQL is a crap database because of it.
      - kalleboo 12y ago
shamsulbuddy 12y 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-tutorial 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
- Xorlev 12y ago
  If it truly was only 10GB of data, something was seriously wrong with your table, schema, or query.
  - shamsulbuddy 12y ago
    Do you think that 10GB data is easily to manage .. what all common practices do you suggest . any examples with use case
    - 12y ago
molixiaoge 12y ago
get it
deleted 12y ago
[deleted]
deleted 12y ago
[deleted]
deleted 12y ago
[deleted]
revskill 12y ago
Event Sourcing could help scale database infinitely. But why Github not using it ?
- samlambert 12y ago
  How do you scale the event store?
  - revskill 12y ago
    It's easy to support sharding because there is one table needed to scale based on one column (AggregateId).
_pmf_ 12y ago
A 20 point manual checklist for performing critical infrastructure updates does not sound very DevOpsy to me.
- MaulingMonkey 12y ago
  Obligatory http://xkcd.com/1205/ 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.

195 points by samlambert 12y 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 12y 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.

bluedino 12y 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 12y ago

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

samlambert 12y ago
I think all of us where at home during this. I certainly was.
- imbriaco 12y ago
  I was too. And I had that beer when we finished. ;)
  - samlambert 12y ago
    So awesome.
drstewart 12y 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 12y 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.
15characterlimi 12y 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.
angersock 12y 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 12y 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.
  - angersock 12y ago
    Sounds like a sound way of managing it...as others have pointed out, occasional on-call duty or what have you is fine--it's the idea that staying at the office until 3 is "normal" and accepted as part of your equity payout is insane.
roskilli 12y 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.
- toomuchtodo 12y 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 12y 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.
  - wastedhours 12y ago
    The last sentence is interesting to me - I wonder if there are many sys/devops types who are morning people? I'm a developer and function much better at 5am than 11pm, but it'd be an interesting correlation if server-peeps were almost exclusively nightowls.
toomuchtodo 12y ago
During massive migrations, yes. Doesn't count if you're working remote though, you're already at home.
ojbyrne 12y 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 12y ago
It's not normal, but this was an exceptional situation.

nodesocket 12y ago

Does GitHub use Percona or MariaDB? We (https://commando.io 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/threadpool.html http://www.percona.com/doc/percona-server/5.5/performance/th...

samlambert 12y 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.
- nodesocket 12y ago
  Interesting, love to see your results some day. How are you doing backups? I assume you are not just using mysqldump?
  - samlambert 12y ago
    Both Xtrabackups and mysqldump for logical backups.
- 15characterlimi 12y ago
  Are you using Percona XtraDB Cluster?
  - samlambert 12y ago
    Nope.
nrbafna 12y 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.

gatehouse 12y 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?

samlambert 12y 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.
morgo 12y 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 :)
sjwright 12y 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.

SEJeff 12y 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 https://github.com/youtube/vitess

deleted 12y ago
[deleted]
- toomuchtodo 12y ago
  Didn't Google scale Adwords primarily with MySQL?
  - jeremycole 12y ago
    Yes, as well as Youtube, Twitter, and Facebook...
threeseed 12y 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.
- samlambert 12y 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.
- jeffdavis 12y 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.

BradRuderman 12y ago

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

stonewhite 12y ago

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

electrum 12y ago
Facebook, Google, LinkedIn, and Twitter making MySQL better: http://webscalesql.org/ http://webscalesql.org/
- derengel 12y 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?
  - samlambert 12y ago
    > why do these companies choose MySQL over Postgresql? Even though it is not without its faults it is a reliable and high performing database, which is what is needed at scale. > is MySQL less capable than Postgres? Definitely not.
    - yangyang 12y ago
      > Definitely not. Have you used both professionally? Not being arsey, seriously interested.
  - fulafel 12y ago
    MySQL used to suck a lot more some years ago (tip of the iceberg: no transaction support!), the Oracle business is small potatoes compared to numerous earlier shortcomings that lead to data loss left and right. Inertia explains the current MySQL position reasonably well, a better question is how did it climb to its current position during its years of technical incompetence.

coops 12y 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?

samlambert 12y 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.
- coops 12y 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?
  - imbriaco 12y ago
    http://en.wikipedia.org/wiki/Split-brain_(computing) http://en.wikipedia.org/wiki/Split-brain_(computing)
    - coops 12y ago
      Yes, this is certainly a state you would desire to avoid. However, you didn't answer any of my questions.
why-el 12y 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.

ck2 12y ago

Did you remember to move the undo log outside of ibdata1 ? http://dev.mysql.com/doc/refman/5.6/en/innodb-undo-tablespace.html 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/InnoDB_int.png http://www.percona.com/blog/wp-content/uploads/2010/04/InnoD...

lmz 12y 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).
- ck2 12y 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.
vtal 12y ago
couldn't you use innodb_file_per_table and not worry about the huge ibdata1 file?
- ck2 12y ago
  No file_per_table does not change anything about the unlimited growth of the undo log in the ibdata1 file.

EGreg 12y ago

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

DigitalSea 12y 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.

seabrookmx 12y 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 http://www.mongodb.com/who-uses-mongodb
- karlick88 12y 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
  - threeseed 12y ago
    You are either being disingenuous or you're ignorant. Many of the companies that switched off MongoDB were growing and ended up moving up to databases like Cassandra. MongoDB is a great database from when you're starting to when you're mid sized. Cassandra destroys PostgreSQL in scalability but we don't say PostgreSQL is a crap database because of it.
    - kalleboo 12y ago

shamsulbuddy 12y 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-tutorial 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

Xorlev 12y ago
If it truly was only 10GB of data, something was seriously wrong with your table, schema, or query.
- shamsulbuddy 12y ago
  Do you think that 10GB data is easily to manage .. what all common practices do you suggest . any examples with use case
  - 12y ago

molixiaoge 12y ago

get it

deleted 12y ago

[deleted]

deleted 12y ago

[deleted]

deleted 12y ago

[deleted]

revskill 12y ago

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

samlambert 12y ago
How do you scale the event store?
- revskill 12y ago
  It's easy to support sharding because there is one table needed to scale based on one column (AggregateId).

_pmf_ 12y ago

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

MaulingMonkey 12y ago
Obligatory http://xkcd.com/1205/ 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.