https://pinboard.in/u:jm/public/ recent bookmarks from jm 2025-12-15T11:59:46+00:00 https://andyatkinson.com/avoid-uuid-version-4-primary-keys jm - UUID v4s increase latency for lookups, as they can’t take advantage of fast ordered lookups in B-Tree indexes - For new databases, don’t use gen_random_uuid() for primary key types, which generates random UUID v4 values - UUIDs consume twice the space of bigint - UUID v4 values are not meant to be secure per the UUID RFC - UUID v4s are random. For good performance, the whole index must be in buffer cache for index scans, which is increasingly unlikely for bigger data. - UUID v4s cause more page splits, which increase IO for writes with increased fragmentation, and increased size of WAL logs - For non-guessable, obfuscated pseudo-random codes, we can generate those from integers, which could be an alternative to using UUIDs - If you must use UUIDs, use time-orderable UUIDs like UUID v7 ]]> postgres rails databases sql mysql uuids indexing primary-keys keys lookup storage random https://pinboard.in/ https://pinboard.in/u:jm/b:c1aa91942c42/ 2024-11-26T12:22:48+00:00 https://github.com/wesql/wesql jm mysql s3 object-storage storage databases sql https://pinboard.in/ https://pinboard.in/u:jm/b:b01e04d0ad70/ 2023-05-17T09:04:10+00:00 https://engineering.fb.com/2023/05/16/data-infrastructure/mysql-raft-meta/ jm mysql ops replicas replication raft distributed-systems meta https://pinboard.in/ https://pinboard.in/u:jm/b:bc755bcb61b1/ 2023-04-12T08:28:02+00:00 https://plaid.com/blog/exploring-performance-differences-between-amazon-aurora-and-vanilla-mysql/ jmbecause Aurora MySQL primary and replica instances share a storage layer, they share a set of undo logs. This means that, for a REPEATABLE READ isolation level, the storage instance must maintain undo logs at least as far back as could be required to satisfy transactional guarantees for the primary or any read replica instance. Long-running replica transactions can negatively impact writer performance in Aurora MySQL—finally, an explanation for the incident that spawned this investigation. The same scenario plays out differently in vanilla MySQL because of its different model for undo logs. Vanilla MYSQL: there are two undo logs – one on the writer, and one on the reader. The performance impact of an operation that prevents the garbage collection of undo log records will be isolated to either the writer or the reader. Aurora MySQL: there is a single undo log that is shared between the writer and reader. The performance impact of an operation that prevents the garbage collection of undo log records will affect the entire cluster. ]]> aurora aws mysql performance databases isolation-levels https://pinboard.in/ https://pinboard.in/u:jm/b:06b1342ee8c4/ 2021-09-29T09:09:08+00:00 https://github.blog/2021-09-27-partitioning-githubs-relational-databases-scale/ jm github mysql architecture database https://pinboard.in/ https://pinboard.in/u:jm/b:8c92e3000937/ 2021-05-28T08:57:02+00:00 https://github.com/nocodb/nocodb jm airtable database sql mysql nocodb spreadsheets ui web https://pinboard.in/ https://pinboard.in/u:jm/b:61352fd4e436/ 2019-10-10T15:55:49+00:00 https://www.flamingspork.com/projects/libeatmydata/ jm fsync linux performance mysql testing https://pinboard.in/ https://pinboard.in/u:jm/b:a876035ccbb5/ 2019-04-08T09:39:48+00:00 https://github.com/ankane/strong_migrations jm Strong Migrations detects potentially dangerous operations in [Rails database] migrations, prevents them from running by default, and provides instructions on safer ways to do what you want. ]]> database migrations rails releases ops databases mysql ruby gems https://pinboard.in/ https://pinboard.in/u:jm/b:11703c588753/ 2019-01-16T10:08:18+00:00 https://www.dropbox.com/s/47xbjrkni9bx0g3/aurora2.pdf?dl=0 jmOne of the more novel differences between Aurora and other relational databases is how it pushes redo processing to a multi-tenant scale-out storage service, purpose-built for Aurora. Doing so reduces networking traffic, avoids checkpoints and crash recovery, enables failovers to replicas without loss of data, and enables fault-tolerant storage that heals without database involvement. Traditional implementations that leverage distributed storage would use distributed consensus algorithms for commits, reads, replication, and membership changes and amplify cost of underlying storage. In this paper, we describe how Aurora avoids distributed consensus under most circumstances by establishing invariants and leveraging local transient state. Doing so improves performance, reduces variability, and lowers costs. ]]> papers toread aurora amazon aws pdf scalability distcomp state sql mysql postgresql distributed-consensus https://pinboard.in/ https://pinboard.in/u:jm/b:db64811fffaf/ 2018-02-02T10:39:00+00:00 https://aws.amazon.com/about-aws/whats-new/2018/02/amazon-aurora-parallel-query-is-available-for-preview/ jmParallel Query improves the performance of large analytic queries by pushing processing down to the Aurora storage layer, spreading processing across hundreds of nodes. With Parallel Query, you can run sophisticated analytic queries on Aurora tables with an order of magnitude performance improvement over serial query processing, in many cases. Parallel Query currently pushes down predicates used to filter tables and hash joins.  ]]> parallel aurora amazon mysql sql performance joins architecture data-model https://pinboard.in/ https://pinboard.in/u:jm/b:035caa846548/ 2017-03-16T23:18:20+00:00 https://erikbern.com/2017/03/15/the-eigenvector-of-why-we-moved-from-language-x-to-language-y.html jm statistics programming languages golang go mysql coding https://pinboard.in/ https://pinboard.in/u:jm/b:bc481ec8d1b8/ 2017-02-14T10:42:03+00:00 https://medium.com/making-instapaper/instapaper-outage-cause-recovery-3c32a7e9cc5f#.39bn4xjas jmWithout knowledge of the pre-April 2014 file size limit, it was difficult to foresee and prevent this issue. As far as we can tell, there’s no information in the RDS console in the form of monitoring, alerts or logging that would have let us know we were approaching the 2TB file size limit, or that we were subject to it in the first place. Even now, there’s nothing to indicate that our hosted database has a critical issue. ]]> limits aws rds databases mysql filesystems ops instapaper risks https://pinboard.in/ https://pinboard.in/u:jm/b:70ad5bf26582/ 2017-02-03T11:00:23+00:00 https://github.com/square/shift jm databases mysql sql migrations ops square ddl percona https://pinboard.in/ https://pinboard.in/u:jm/b:fe923a6ce9c1/ 2017-01-30T17:59:25+00:00 https://engineering.pinterest.com/blog/evolving-mysql-compression-part-2 jm compression deflate zlib pinterest hacks mysql https://pinboard.in/ https://pinboard.in/u:jm/b:8d4780ed5c89/ 2016-12-12T11:21:30+00:00 https://www.percona.com/live/europe-amsterdam-2015/sites/default/files/slides/PL_AMS_Unicode_Booking169_v3.pdf jm utf-8 utf8mb4 mysql storage databases slides booking.com character-sets https://pinboard.in/ https://pinboard.in/u:jm/b:12c8fe17e0d5/ 2016-11-01T11:59:27+00:00 https://www.percona.com/blog/2016/02/11/measuring-docker-io-overhead/ jm docker percona overhead mysql deployment performance ops containers https://pinboard.in/ https://pinboard.in/u:jm/b:c324d2498cee/ 2016-07-27T09:47:20+00:00 https://eng.uber.com/mysql-migration/ jm database mysql postgres postgresql uber architecture storage sql https://pinboard.in/ https://pinboard.in/u:jm/b:bb13fe501b54/ 2016-06-14T09:19:08+00:00 https://aws.amazon.com/blogs/aws/new-cross-region-read-replicas-for-amazon-aurora/ jmCreating a read replica in another region also creates an Aurora cluster in the region. This cluster can contain up to 15 more read replicas, with very low replication lag (typically less than 20 ms) within the region (between regions, latency will vary based on the distance between the source and target). You can use this model to duplicate your cluster and read replica setup across regions for disaster recovery. In the event of a regional disruption, you can promote the cross-region replica to be the master. This will allow you to minimize downtime for your cross-region application. This feature applies to unencrypted Aurora clusters. ]]> aws mysql databases storage replication cross-region failover reliability aurora https://pinboard.in/ https://pinboard.in/u:jm/b:4bc9688d386b/ 2016-04-13T10:49:47+00:00 http://stackoverflow.com/questions/303994/log-all-queries-in-mysql/20485975#20485975 jm stackoverflow mysql rds logging ops https://pinboard.in/ https://pinboard.in/u:jm/b:a80b2ac6db7f/ 2016-03-25T15:48:55+00:00 http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins jm sql joins mysql reference database https://pinboard.in/ https://pinboard.in/u:jm/b:da83d142d2e3/ 2016-03-24T13:21:13+00:00 http://prometheus.io/blog/2016/03/23/interview-with-life360/ jm'Prometheus has been known to us for a while, and we have been tracking it and reading about the active development, and at a point (a few months back) we decided to start evaluating it for production use. The PoC results were incredible. The monitoring coverage of MySQL was amazing, and we also loved the JMX monitoring for Cassandra, which had been sorely lacking in the past.' ]]> metrics monitoring time-series prometheus testimonials life360 cassandra jmx mysql https://pinboard.in/ https://pinboard.in/u:jm/b:c4405c9925a7/ 2016-01-06T12:42:41+00:00 https://blog.engineyard.com/2011/5-subtle-ways-youre-using-mysql-as-a-queue-and-why-itll-bite-you jm database mysql queueing queue messaging percona rds locking sql architecture https://pinboard.in/ https://pinboard.in/u:jm/b:5081114f1b88/ 2015-09-14T06:02:17+00:00 http://eev.ee/blog/2015/09/12/dark-corners-of-unicode/ jmI’m assuming, if you are on the Internet and reading kind of a nerdy blog, that you know what Unicode is. At the very least, you have a very general understanding of it — maybe “it’s what gives us emoji”. That’s about as far as most people’s understanding extends, in my experience, even among programmers. And that’s a tragedy, because Unicode has a lot of… ah, depth to it. Not to say that Unicode is a terrible disaster — more that human language is a terrible disaster, and anything with the lofty goals of representing all of it is going to have some wrinkles. So here is a collection of curiosities I’ve encountered in dealing with Unicode that you generally only find out about through experience. Enjoy. ]]> unicode characters encoding emoji utf-8 utf-16 utf mysql text https://pinboard.in/ https://pinboard.in/u:jm/b:f032051210c0/ 2015-07-21T10:20:42+00:00 http://blog.mihasya.com/2015/07/19/thoughts-evoked-by-circleci-outage.html jm database-is-not-a-queue mysql sql databases ops outages postmortems https://pinboard.in/ https://pinboard.in/u:jm/b:689da69282e3/ 2015-04-16T15:19:35+00:00 http://engineering.pinterest.com/post/116038532184/learn-to-stop-using-shiny-new-things-and-love jm mysql storage databases reliability pinterest architecture https://pinboard.in/ https://pinboard.in/u:jm/b:ee79bcd2c695/ 2015-03-30T16:55:14+00:00 http://highscalability.com/blog/2015/3/30/how-we-scale-vividcortexs-backend-systems.html jm time-series tsd storage mysql sql baron-schwartz ops performance scalability scaling go https://pinboard.in/ https://pinboard.in/u:jm/b:fe014fc1ee1b/ 2015-03-25T15:17:01+00:00 https://github.com/barsoom/devbook/blob/master/deploy_without_downtime/README.md jm activerecord fail rails mysql sql migrations databases schemas releasing https://pinboard.in/ https://pinboard.in/u:jm/b:be719470c073/ 2015-03-11T10:15:28+00:00 http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/ jmAnother core problem we’ve faced is one of the fundamental features of MongoDB (or any other schemaless storage engine): the lack of a schema. The lack of a schema may sound interesting, and in some cases it can certainly have its benefits. However, for many the usage of a schemaless storage engine leads to the problem of implicit schemas. These schemas aren’t defined by your storage engine but instead are defined based on application behaviour and expectations. Well, don't say we didn't warn you ;)]]> mongodb mysql postgresql databases storage schemas war-stories https://pinboard.in/ https://pinboard.in/u:jm/b:da312f02b590/ 2015-03-09T23:08:39+00:00 https://github.com/soundcloud/lhm jm The basic idea is to perform the migration online while the system is live, without locking the table. In contrast to OAK and the facebook tool, we only use a copy table and triggers. The Large Hadron is a test driven Ruby solution which can easily be dropped into an ActiveRecord or DataMapper migration. It presumes a single auto incremented numerical primary key called id as per the Rails convention. Unlike the twitter solution, it does not require the presence of an indexed updated_at column. ]]> migrations database sql ops mysql rails ruby lhm soundcloud activerecord https://pinboard.in/ https://pinboard.in/u:jm/b:7319dc67d62e/ 2014-12-09T16:42:44+00:00 http://www.xaprb.com/blog/2014/12/08/eventual-consistency-simpler-than-mvcc/ jmSince I am not ready to assert that there’s a distributed system I know to be better and simpler than eventually consistent datastores, and since I certainly know that InnoDB’s MVCC implementation is full of complexities, for right now I am probably in the same position most of my readers are: the two viable choices seem to be single-node MVCC and multi-node eventual consistency. And I don’t think MVCC is the simpler paradigm of the two. ]]> nosql concurrency databases mysql riak voldemort eventual-consistency reliability storage baron-schwartz mvcc innodb postgresql https://pinboard.in/ https://pinboard.in/u:jm/b:0dddeb567400/ 2014-12-09T16:40:38+00:00 http://smalldatum.blogspot.ie/2014/11/aurora-for-mysql-is-coming.html?showComment=1416563079921 jm mysql databases aurora aws ec2 sql storage transactions replication https://pinboard.in/ https://pinboard.in/u:jm/b:b6f304df35af/ 2014-12-05T22:53:40+00:00 http://smalldatum.blogspot.ie/2014/11/aurora-for-mysql-is-coming.html?showComment=1416603086179 jm via:highscalability mysql aurora failover fault-tolerance aws replication quorum https://pinboard.in/ https://pinboard.in/u:jm/b:ae310d17fac7/ 2014-11-28T16:47:33+00:00 https://code.facebook.com/posts/1499322996995183/solving-the-mystery-of-link-imbalance-a-metastable-failure-state-at-scale/ jmFacebook collocates many of a user’s nodes and edges in the social graph. That means that when somebody logs in after a while and their data isn’t in the cache, we might suddenly perform 50 or 100 database queries to a single database to load their data. This starts a race among those queries. The queries that go over a congested link will lose the race reliably, even if only by a few milliseconds. That loss makes them the most recently used when they are put back in the pool. The effect is that during a query burst we stack the deck against ourselves, putting all of the congested connections at the top of the deck. ]]> architecture debugging devops facebook layer-7 mysql connection-pooling aggregation networking tcp-stack https://pinboard.in/ https://pinboard.in/u:jm/b:c71d54137cff/ 2014-05-08T16:53:32+00:00 http://www.brunton-spall.co.uk/post/2014/05/06/database-migrations-done-right/ jmThe rule is simple. You should never tie database migrations to application deploys or vice versa. By minimising dependencies you enable faster, easier and cleaner deployments. A solid description of why this is a good idea, from an ex-Guardian dev.]]> migrations database sql mysql postgres deployment ops dependencies loose-coupling https://pinboard.in/ https://pinboard.in/u:jm/b:2e8db5bfa149/ 2014-04-28T11:35:23+00:00 http://aws.amazon.com/solutions/case-studies/hailo/ jm hailo cassandra ubuntu mysql rds aws ec2 haproxy architecture https://pinboard.in/ https://pinboard.in/u:jm/b:210362e2cd0c/ 2014-01-13T15:52:02+00:00 https://tech.dropbox.com/2014/01/outage-post-mortem/ jm dropbox outage postmortems upgrades mysql https://pinboard.in/ https://pinboard.in/u:jm/b:33ceb4b91aab/ 2013-10-10T14:22:13+00:00 https://www.facebook.com/notes/facebook-engineering/linkbench-a-database-benchmark-for-the-social-graph/10151391496443920 jmHowever, the gold standard for database benchmarking is to test the performance of a system on the real production workload, since synthetic benchmarks often don't exercise systems in the same way. When making decisions about a significant component of Facebook's infrastructure, we need to understand how a database system will really perform in Facebook's production workload. [....] LinkBench addresses these needs by replicating the data model, graph structure, and request mix of our MySQL social graph workload. Mentioned in a presentation from Peter Bailis, http://www.hpts.ws/papers/2013/bailis-hpts-2013.pdf]]> graph databases mysql facebook performance testing benchmarks workloads https://pinboard.in/ https://pinboard.in/u:jm/b:2f062c07a82a/ 2013-09-14T21:31:31+00:00 http://www.theregister.co.uk/2013/09/12/google_mariadb_mysql_migration/ jmWhen we asked Sallner to quantify the scale of the migration he said, "They're moving it all. Everything they have. All of the MySQL servers are moving to MariaDB, as far as I understand." By moving to MariaDB, Google can free itself of any dependence on technology dictated by Oracle – a company whose motivations are unclear, and whose track record for working with the wider technology community is dicey, to say the least. Oracle has controlled MySQL since its acquisition of Sun in 2010, and the key InnoDB storage engine since it got ahold of Innobase in 2005. [...] We asked Cole why Google would shift from MySQL to MariaDB, and what the key technical differences between the systems were. "From my perspective, they're more or less equivalent other than if you look at specific features and how they implement them," Cole said, speaking in a personal capacity and not on behalf of Google. "Ideologically there are lots of differences." So -- AWS, when will RDS offer MariaDB as an option?]]> google mysql mariadb sql open-source licensing databases storage innodb oracle https://pinboard.in/ https://pinboard.in/u:jm/b:d069159fa948/ 2013-07-09T09:01:05+00:00 http://highscalability.com/blog/2013/7/8/the-architecture-twitter-uses-to-deal-with-150m-active-users.html jmTwitter is primarily a consumption mechanism, not a production mechanism. 300K QPS are spent reading timelines and only 6000 requests per second are spent on writes. * their approach of precomputing the timeline for the non-search case is a good example of optimizing for the more frequently-exercised path. * MySQL and Redis are the underlying stores. Redis is acting as a front-line in-RAM cache. they're pretty happy with it: https://news.ycombinator.com/item?id=6011254 * these further talks go into more detail, apparently (haven't watched them yet): http://www.infoq.com/presentations/Real-Time-Delivery-Twitter http://www.infoq.com/presentations/Twitter-Timeline-Scalability http://www.infoq.com/presentations/Timelines-Twitter * funny thread of comments on HN, from a big-iron fan: https://news.ycombinator.com/item?id=6008228]]> scale architecture scalability twitter high-scalability redis mysql https://pinboard.in/ https://pinboard.in/u:jm/b:5bddc42e545c/ 2013-04-16T21:28:43+00:00 https://engineering.groupon.com/2013/mysql/mysql-buffer-pool-warming/ jm via:dave-doran mysql databases warm-spares spares failover groupon percona replication https://pinboard.in/ https://pinboard.in/u:jm/b:6c770a73f651/ 2013-04-15T21:17:02+00:00 http://highscalability.com/blog/2013/4/15/scaling-pinterest-from-0-to-10s-of-billions-of-page-views-a.html jma [Cassandra-style] Cluster Management Algorithm is a SPOF. If there’s a bug it impacts every node. This took them down 4 times. yeah, so, eek ;)]]> clustering sharding architecture aws scalability scaling pinterest via:matt-sergeant redis mysql memcached https://pinboard.in/ https://pinboard.in/u:jm/b:94eb7274d2de/ 2013-03-20T13:23:38+00:00 http://www.percona.com/doc/percona-playback/plugins/tcpdump/index.html jm tcpdump production load-testing testing staging tee networking netcat percona replay mysql https://pinboard.in/ https://pinboard.in/u:jm/b:cf3d15109c17/ 2013-03-19T23:04:16+00:00 http://codeascraft.etsy.com/2013/03/19/the-perils-of-sql_mode/ jm mysql input corrupt invalid validation coding databases sql testing tcpdump percona pt-query-digest tee https://pinboard.in/ https://pinboard.in/u:jm/b:4e37ee84990a/ 2013-03-14T14:20:51+00:00 http://www.solowizard.com/ jmMany teams use chef to manage their production machines, but developers often build their development boxes by hand. SoloWizard makes it painless to create a configurable chef solo script to get your development machine humming: mysql, sublime text, .bash_profile tweaks to OS-X settings - it's all there!]]> osx chef mac build-out ops macosx deployment developers desktops laptops mysql rabbitmq activemq nginx https://pinboard.in/ https://pinboard.in/u:jm/b:c9a860892e4c/ 2013-03-06T09:44:53+00:00 https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932 jmSome ALTER TABLE statements take too long form the perspective of some MySQL users. The fast index create feature for the InnoDB plugin in MySQL 5.1 makes this less of an issue but this can still take minutes to hours for a large table and for some MySQL deployments that is too long.   A workaround is to perform the change on a slave first and then promote the slave to be the new master. But this requires a slave located near the master. MySQL 5.0 added support for triggers and some replication systems have been built using triggers to capture row changes. Why not use triggers for this? The openarkkit toolkit did just that with oak-online-alter-table. We have published our version of an online schema change utility (OnlineSchemaChange.php aka OSC). ]]> facebook mysql sql schema database migrations ops alter-table https://pinboard.in/ https://pinboard.in/u:jm/b:f2bd9f37b00f/ 2012-12-12T13:28:36+00:00 http://codeascraft.etsy.com/2012/04/20/two-sides-for-salvation/ jm database etsy mysql replication schema availability downtime https://pinboard.in/ https://pinboard.in/u:jm/b:3662d0721dbe/ 2012-05-10T21:16:34+00:00 http://saucelabs.com/blog/index.php/2012/05/goodbye-couchdb/ jm couchdb mysql nosql databases storage percona via:peakscale https://pinboard.in/ https://pinboard.in/u:jm/b:9c3fd9393941/ 2012-04-19T20:19:22+00:00 http://www.gamasutra.com/view/news/168799/Scale_Something_How_Draw_Something_rode_its_rocket_ship_of_growth.php jm scale scalability draw-something games haproxy mysql membase couchbase https://pinboard.in/ https://pinboard.in/u:jm/b:227a490d3262/ 2011-12-19T21:49:50+00:00 http://highscalability.com/blog/2011/12/19/how-twitter-stores-250-million-tweets-a-day-using-mysql.html jm mysql twitter scalability gizzard innodb performance database https://pinboard.in/ https://pinboard.in/u:jm/b:bf5a9b9e5b85/ 2011-08-23T11:00:54+00:00 http://highscalability.com/bunch-great-strategies-using-memcached-and-mysql-better-together jm memcached caching mysql performance scalability via:jeffbarr https://pinboard.in/ https://pinboard.in/u:jm/b:010b32939fd2/ 2011-02-05T21:50:36+00:00 http://www.philwhln.com/quoras-technology-examined?ref=reddit jm quora python nginx tornado comet mysql memcached thrift haproxy aws pylons via:nelson https://pinboard.in/u:jm/b:0e6e81529af5/ 2010-10-28T13:48:16+00:00 http://www.maatkit.org/doc/synopsis jm sysadmin db mysql replication maatkit dba https://pinboard.in/u:jm/b:19a8d702c591/ 2010-09-29T15:48:24+00:00 http://jcole.us/blog/archives/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/ jm linux memory mysql optimization performance swap tuning vm numa swap-insanity swapping https://pinboard.in/u:jm/b:6aad45b4a7bc/ 2010-06-23T20:03:19+00:00 http://www.blue74.com/?p=25 jm mongodb mysql nosql rant stability beta https://pinboard.in/u:jm/b:bc58a76d21be/