DEV Community: CYN

Long Live MySQL: Kudos to the Ecosystem Innovators

CYN — Sun, 15 May 2022 09:20:48 +0000

Author: Ed Huang (Co-Founder & CTO at PingCAP)

This post is the second part in our series. You can find the first part here: **Long Live MySQL: Good Old MySQL Should Be Rejuvenated**.

I am a firm believer in MySQL and its ecosystem. As I said in my previous article, the MySQL ecosystem is still thriving with a lot of innovations in the space, and MySQL applications and developers are still one of the mainstays of the database market. The question is: do we need a more modernized MySQL? Absolutely yes.

In this article, I’ll illustrate the robustness and progressiveness of MySQL by sharing some of the key players in the more modern MySQL ecosystem.

Forks of MySQL: MariaDB and Percona as examples

MARIADB

There is no talking about MySQL alternatives without mentioning MariaDB, an enterprise MySQL fork driven by the open source community. Open source factors aside, MariaDB has a lot to offer both as a technology and a product.

To cope with increasing data volume, MariaDB provides MaxScale, a simple proxy-like component that supports query routing, read-write separation, and automatic failover based on the MySQL Binlog. Scaling with MaxScale is more about sharding data vertically than horizontally. If a user’s application works with large tables, the horizontal sharding workload must be completed in the application layer, which is intrusive to the application.

As the boundaries blur between real-time analytics and online transactions in modern workloads, MariaDB introduced ColumnStore to speed up queries for Online Analytical Processing (OLAP). ColumnStore supports direct reads and writes and was introduced as a MySQL new storage engine. However, currently there isn’t a way for ColumnStore tables to be interconverted with those of InnoDB, MySQL’s default storage engine. The way I see it, the performance of column storage for writing is different from that of row storage in terms of both throughput and latency. Users need to know clearly which workloads are suitable for running on ColumnStore. To achieve a Hybrid Transactional and Analytical Processing (HTAP) experience, ColumnStore must be used with MaxScale to route Online Transactional Processing (OLTP) and OLAP requests to the corresponding table or database at the MaxScale layer. When users design their schema, they must take these requirements into account. Many next generation database companies, not just MariaDB, are noticing this trend to process both OLTP and OLAP workloads.

PERCONA

Percona is a key long-term supporter of the MySQL ecosystem and a recognized expert in MySQL operation and maintenance. They have contributed many well-received tools to the MySQL community, such as pt-online-schema-change and pt-query-digest. Percona’s business model is to package these tools with MySQL, introduce other practical functions based on the MySQL kernel, and release it as a community distribution with commercial support.

The differences between Percona Server for MySQL and MySQL is easy to find on Percona’s official website. Here I will highlight two points that I think are interesting:

MyRocks. MyRocks is a MySQL storage engine based on RocksDB, an embeddable, persistent key-value store. Percona MyRocks implements Percona Server for MySQL. RocksDB is a log structured merge (LSM) tree implementation. Compared to B-tree, LSM tree has a data structure that caters more to modern hardware, has a better compression ratio, and is easier to implement. In addition, LSM-Tree is built with the idea of tiered storage. If you want to implement a cloud-native storage engine and make full use of different storage media on the cloud, tiered storage is an ideal design. MyRocks would be a great addition to MySQL for modern hardware and cloud infrastructure.
XtraDB Cluster. Percona integrates ProxySQL as a routing layer and then uses Galera to achieve high availability of storage nodes in a distributed architecture. However, like MariaDB, the most suitable scenarios for XtraDB Cluster are the vertical sharding of tables and databases, read-write separation, and node-level auto-failover. XtraDB Cluster is not a distributed database that supports horizontal scaling.

Percona has supported the ClickHouse community for quite a while. In some ways, this support makes up for the real-time analytical capabilities of MySQL. As businesses become more real-time in the digital transformation era, the mass MySQL user groups hope that the MySQL ecosystem can provide real-time analytics capabilities.

Middleware on top of MySQL: Vitess as an example

When faced with the challenge of massive data, MariaDB and Percona Server choose vertical sharding and read-write separation through proxy. For large single tables, however, horizontal sharding is a better option with less intrusion on the application. The application only needs to specify the sharding key, and then a routing layer decides which shard it should store. The routing layer is transparent to the application. One example of this proxy design is Vitess, a SQL middleware which turns MySQL into a fast, scalable, and highly-available distributed database. Vitess was open-sourced after being widely used at YouTube and has become the mainstream solution for MySQL sharding. However, its limitations are also obvious. For example, a sharding proxy is not a database, but a routing layer for requests. This means that Vitess does not support complex queries, which is a tricky issue for distributed transactions across shards. The failover and service levels heavily rely on the underlying MySQL mechanisms. Of course, the existing MySQL operation and maintenance experience can still be leveraged.

Initial HTAP practitioners: SingleStore as an example

The predecessor of SingleStore is MemSQL. Its early positioning was as a distributed OLAP database that supports massively parallel processing (MPP), with an all-in-memory design. Later, this solution added disk storage support and was renamed SingleStore. SingleStore supports a row-based storage format for OLTP workloads. However, the default storage engine is ColumnStore, which indicates that it focuses more on OLAP workloads. Because SingleStore can handle both workloads in the same database, it can be classified as an HTAP database. Since then, many databases in the MySQL ecosystem have enhanced their OLAP capabilities.

There are two types of nodes in the SingleStore architecture: aggregator nodes and leaf nodes. The leaf node stores table data shards. Some SQL operators are pushed down to the leaf node to accelerate computing. SingleStore is also one of the few OLAP databases that follow codegen technology routes, in which execution plans are compiled into machine code and cached locally for reuse. Other OLAP databases, such as ClickHouse and MonetDB, adopt vectorization to develop faster analytical query engines by making efficient utilization of CPU cache.

Because SingleStore is compatible with the MySQL protocol, it is easier to adopt and more cost effective. Overall, I think it has a good market and growth strategy.

RDS on public cloud: Amazon Aurora as an example

Amazon Aurora is a significant database innovation. Aurora does not use a shared-nothing architecture like Spanner. Instead, it is optimized based on a stand-alone RDBMS. Aurora keenly seized the following characteristics of long-tail users on the cloud:

High expectations for elasticity and relatively low requirements for scalable storage capacity
Higher scalability requirements for data reads than writes
Mostly RDBMS users because it’s 100% compatible with MySQL

Aurora also leverages the capabilities of cloud infrastructure in a smart way: the throughput of shared storage is basically the same as that of local disks. Shared storage for a database means that the computing node can become stateless, thereby realizing the separation of computing and storage. This is possible because computing and storage have different hardware requirements. For example, to scale the read requests, Aurora simply adds a new computing node of the read replica. The process does not involve much data migration effort except for the cache warm-up time. This is also a key prerequisite for serverless implementation.

In addition, Aurora’s shared storage does not mean just running InnoDB on a distributed file system. It represents the design concept of “The log is database.” In fact, as long as the Redo log is secured, the data is secured. The storage uses Redo log records to build the page image on demand. Compared with traditional Binlog replication, this method has reduced log serialization, network transmission load, and the write amplification caused by applying the binlog. This significantly improves the write performance when compared with a standalone database. As a cloud provider, AWS offers a complete cloud hosting service for Aurora.

The problem with Aurora is that it is essentially a standalone database. Even with its modern design, if Aurora is faced with large data volume or write scalability issues, it may still need to be supplemented by sharding middleware like Vitess or distributed SQL databases like TiDB. Additionally, Aurora supports parallel scanning for some types of computation on the read replica, but its main focus still seems to be on traditional OLTP workloads.

Next-Gen distributed SQL: TiDB as an example

TiDB is one of the next-gen distributed SQL databases that has been active in recent years. It was initially inspired by Google Spanner and F1. Currently, there are several distributed SQL projects that are active in the community: CockroachDB (CRDB), YugabytesDB, and TiDB. CRDB and YugabytesDB are PostgreSQL compatible; however, of the three, TiDB is the only one that is MySQL compatible.

TiDB is not an altered fork of the MySQL code. Instead, it was developed from scratch with a more modern design. TiDB adopts a typical shared-nothing design, which ensures its elastic scalability. The TiDB architecture has two layers: the top is the stateless SQL layer responsible for connection management, SQL parsing and optimization, and distributed execution plan generation. The bottom layer is TIKV, a distributed key-value storage layer. TiKV supports ACID transactions, and it stores the data. The SQL layer converts data in relational models such as tables, rows, and indexes into key-value pairs and stores them in TiKV. The advantage of this design is that it is easier to achieve elastic distribution in a key-value model than in a relational model. This is because in the relational model, there are too many concepts: there are tables, there are databases, there are many columns in a row, and that means there are countless kinds of sharding strategies. However, for the key-value model, it is obvious to follow the range of key (or hash of keys). I described the details in the blog post, Building a Large-scale Distributed Storage System Based on Raft. TiDB adopts Raft, a modern consensus algorithm, to achieve high availability and horizontal scalability on a distributed architecture. In addition, since the SQL layer is stateless with no storage dependency, it is convenient to scale the connection layer and the computing layer independent of each other.

Unlike SingleStore, TiDB’s positioning in the early days was as more of an OLTP database, which was designed to handle high-frequency transactions with low-latency and high-concurrency. When MySQL users are faced with scalability challenges, if they are up for sharding and are looking for a simpler, less intrusive distributed database that supports elastic scaling, TiDB could be a good option.

In the past two years, while the boundaries between OLTP and OLAP are blurring, more users sought a simpler database. Building on the existing distributed framework, we implemented TiFlash, a distributed columnar storage engine to make TiDB a true HTAP database. Thanks to TiDB’s decoupled design, the SQL computing layer forwards the OLAP request to the columnar replica on TiFlash. Users do not need to care about the synchronization of data replicas between the column storage and the row storage, and OLTP and OLAP workloads are resolved within the same architecture. I will not explain the technical details here, but if you’d like more information on TiFlash’s design, see the TiDB team’s paper, TiDB: A Raft-based HTAP Database.

It is not easy for users to maintain a complex distributed system in a production environment, and it is even more complicated with the various tools built into modern databases. As mentioned earlier, more users are moving to the cloud, where managed services can shield these complexities. TiDB’s fully managed service is also available on AWS and CCP, which makes an important difference.

However, there is a lot of legacy code in MySQL, after all it’s been around for more than 20 years and was mostly designed around MyISAM, a non-transactional storage engine. Some of that idiosyncrasy doesn’t transfer well into a fully distributed database architecture. To fully take advantage of the capabilities of the distributed architecture, TiDB’s SQL optimizer and executor are designed as a distributed environment computing engine. Therefore, some behaviors may be different from that in stand-alone MySQL.

Conclusion

MySQL users now have a greater choice with more scalable, innovative distributed architectures. There is no shortage of innovation in the MySQL ecosystem, and some of these projects are even leading the database industry in terms of technical progress. These are exciting times and the future is bright.

Long live MySQL.

This post is the second part in our series. You can find the first part here: *Long Live MySQL: Good Old***** MySQL Should Be Rejuvenated**.

Long Live MySQL: Good Old MySQL Should Be Rejuvenated

CYN — Fri, 13 May 2022 01:25:34 +0000

Author: Ed Huang (Co-Founder & CTO at PingCAP)

This post is the first part in our series. You can see the second part **here: *Long Live MySQL: Kudos to the Ecosystem Innovators***.

Imagine you’re an application developer, and your manager tells you to prototype a new application in a week. What would you choose for your database? Probably MySQL.

MySQL is ubiquitous

Many engineers love MySQL. DB-Engines reports MySQL is the second most popular database; however, some other databases, such as SQLite and IBM Db2, are decreasing in popularity.

From the 73,317 responses of Stack Overflow’s 2021 Developer Survey, MySQL ranked as the most popular database.

MySQL is also popular when it comes to hiring. I searched for jobs on Indeed with the keywords of top used databases, and there were 31,245 jobs returned for “mysql.” Job openings that require a “mysql” skillset are the second highest among the top databases.

MySQL is ubiquitous. In fact, at PingCAP, MySQL was one of the few databases a lot of our engineers knew how to use before they joined PingCAP. I was one of them.

My 20 years with MySQL

I used MySQL for the first time when I was a high school student in 2003. Back then, I wanted to develop a web application (Online Judge) with PHP for programming competition hobbyists and help them run their code and check whether it was correct. To store the data users submitted, I needed a database. MySQL was my best choice because it was so straightforward and user friendly. I could use MySQL client to write SQL and operate the database in PHP directly and then Apache rendered the dynamic page results.

In 2012, I joined a startup company whose infrastructure was based on MySQL. I was one of the few infrastructure engineers. The company business grew so fast, its data volume soon spiked beyond MySQL’s single-machine capacity. So I began to seek solutions. For example, I used MySQL binlog for highly available primary and secondary layers and applied horizontal sharding. When I left that company, its database cluster had more than 30 shards which could hold dozens of terabytes. For me, solving these issues was a very fulfilling experience.

Because of these MySQL-related experiences, I had the opportunity to meet many excellent engineers who had similar memories working with MySQL. In 2015, when I decided to start my own business and build a brand new database, MySQL was the first ecosystem that popped into my mind.

Why so many people love MySQL

MySQL’s popularity is unquestionable, although its earlier versions were not as advanced as what we see today. The secret sauce behind its popularity is that MySQL is simple and easy to use. Many user-friendly features like Binlog were introduced to MySQL early as well. As a result, the number of MySQL users grew dramatically in the following three decades. In addition, many users gave their feedback on using MySQL or contributed to MySQL directly, all of which led to a more prosperous MySQL ecosystem. In its heyday, MySQL became a natural choice for many businesses because:

Many engineers are using it, making a large talent pool available for database companies.
It is open-sourced, making it easy to obtain and use.
You could find the answers to almost any MySQL question on the internet.
It has a vibrant ecosystem. A large number of open source projects, such as WordPress and phpMyAdmin, heavily relied on MySQL and made it even more prevalent.

Things have changed

During the past 20 years, the world has been changing rapidly and we have witnessed many significant changes in technology such as the explosion of the mobile internet and today’s digital transformation. Our application scenarios have changed enormously as well. What are these changes? Let’s explore them from a data perspective.

Data volume increased exponentially, making distributed technology a must for almost all modern databases. NoSQL databases do a very good job of building a distributed architecture. They provide transparent scalability while maintaining consistent usage, so that developers can scale in and out without worrying about application change. However, NoSQL databases provide users with an even more limited set of functions than SQL databases. This was not a big problem in the early days of Web 2.0 because although data volume increased, business models were relatively simple. Take Twitter for example: in its earliest versions, it provided nothing more than a giant shared note service within a limit of 140 characters.

If enterprises plan to build complicated applications, especially those that require strong consistency and complex association analysis such as JOIN and GROUP BY, SQL is still their best option. Unfortunately, traditional relational databases including MySQL and PostgreSQL made few innovations for a long time even though they embraced distributed technology. They developed their databases for a single machine, making sharding the only mainstream solution to deal with distributed requirements. Recently, however, we have seen some new database projects that can be called real distributed SQL databases.

At first, data models evolved from complicated to simple, and then became even more complicated. This evolution comes with the significant change in technology like the mobile internet explosion. The then-prevalence of NoSQL and sharded databases made data modeling subject to a limited set of functions. For a while, data application modeling had to be simplified to key-value pairs, documents, and other flexible and simple models.

However, when the low-hanging fruit is picked, the really hard problems get trickier. As more industries such as financial services and e-commerce begin their digital transformation to accelerate their online business, the databases’ application scenarios are becoming more complicated. So, the relational model plus SQL is almost always our best database solution. In the last 10 years, we have seen many attempts to model data with new domain-specific languages (DSL)—GraphQL is one example—but I think none of them can shake the dominant place of a relational model plus SQL combination. Google described this situation succinctly in its research paper “F1: A Distributed SQL Database That Scales”:

"When we sought a replacement for Google’s MySQL data store for the AdWords product, that option was simply not feasible: the complexity of dealing with a non-ACID data store in every part of our business logic would be too great, and there was simply no way our business could function without SQL queries."

The demand has been increasing for stronger consistency and real-time transactional processing and analytics. In the early days, MySQL’s default storage engine was MyISAM, but eventually InnoDB took its place. I think one of the main differences is that InnoDB supports strongly consistent transactions. The more consistent semantics the database layer provides, the less complexity the application layer has to bear. This is especially true when you are dealing with failovers. If the database does not support strong consistency, it is difficult to guarantee data integrity when performing a disaster recovery. This is not acceptable in most financial scenarios.

In the last decade, there have been many innovations in database management technologies. However, one obvious trade off is the separation of Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP). Streaming technologies such as Apache Kafka, Apache Storm, and Apache Spark have emerged to form the so-called “lambda architecture,” which combines the two systems and eliminates the latency of extract, transform, load (ETL). While this approach works in many situations, the architectural complexity introduced has been a tough nut to crack. It is also difficult to keep the data consistent on this head-scratching technology stack. This has caused great challenges for many businesses that require real-time insights at the minute or even millisecond level. Due to the technical barriers, companies might have to suppress their business needs and turn to solutions that are more traditional but easier to tame. As data volume and the demand of real-time analytics have risen in recent years, OLTP and OLAP are on the track of integration.

The way data is generated and consumed is becoming multifaceted. In the past, writing to a database was simple: the client connected directly to the DB through SQL statements. If you take a closer look at how data enters the DB today, you can see that the upstream is much more diversified; it could be a message queue or batch writing from an ETL job. The challenge for batch writing is that users normally care more about the throughput performance than whether the ACID isolation requirements are met. In such scenarios, traditional transaction mechanisms such as locking would be unnecessary. On the other hand, the dramatic rise in real-time requirements lead to the need for data change synchronization to a downstream message queue for consumption. Change Data Capture (CDC) has become a mainstream implementation for modern databases. For example, MySQL has Binlog, Oracle has GoldenGate, and PG has pg replication.

Data quality is increasingly required and the scope of observability extends from the execution panel to the data panel. Twenty years ago, most of our focus was on simple database system metrics such as queries per second (QPS), transactions per second (TPS), latency, and query plan level. With the dramatic increase in data volume and the emergence of distributed databases, we are not satisfied with basic system information; we want to observe how data is accessed, and from that, gain business insights. Take TiDB’s KeyVisualizer as an example. It reflects the distribution of data access in real time so that you can tell at a glance which data is frequently accessed and what are the characteristics of the access.

TiDB’s KeyVisualizer

However, these observability dimensions are still limited to the database system itself. Recently, a new trend has started to emerge: data observability, which tries to measure the quality of data. Anomalo is a good example, and I think this will be a very promising direction for database products.

The hardware infrastructure is changing dramatically. As the cloud is becoming more prevalent, managed services are becoming the mainstream. When MySQL was released, there were no SSDs or 20G NICs. The advances in hardware have alleviated many problems. For example, B+Tree solved the problem of the slow reads and writes of traditional mechanical disks; however, this is not an issue with modern NVMe SSDs. With wide adoption of modern cloud environments, shared storage solutions on the cloud, such as AWS EBS, perform as well, if not better, than local disks. This will profoundly change the design of database storage engines. On the other hand, database vendors and cloud providers can offer fully managed experience on the cloud, so that users won’t have to worry about operations and maintenance and can focus on the business development. This is also a reasonable business model for vendors providing database services on the cloud.

Is good old MySQL outdated?

Having witnessed all those changes happening in the technology world, I could not help but think: is MySQL outdated? As a database kernel developer, I am more than concerned that the contribution from Oracle to MySQL is dwindling.

The growth trend of the number of MySQL contributors

MySQL’s commits trend during past 3 years

I don’t know what’s going on inside Oracle, and it would be great for them to make an explanation to the community.

Even so, I still love MySQL and its ecosystem. The MySQL ecosystem will not go away. MySQL applications and developers are still the foundations of the database market. But do we need a more modern MySQL? Absolutely yes. In my next post, I will share with you some major players in the modern MySQL ecosystem. Stay tuned.

This post is the first part in our series. You can see the second part here: *Long Live MySQL: Kudos to the Ecosystem Innovators***.

Explore Deep in 4.6 Billion GitHub Events

CYN — Wed, 04 May 2022 23:19:08 +0000

4.6 billion is literally an astronomical figure. The richest star map of our galaxy, brought by Gaia space observatory, includes just under 2 billion stars. What does a view of 4.6 billion GitHub events really look like? What secrets and values can be discovered in such an enormous amount of data?

Here you go: OSSInsight.io can help you find the answer. It’s a useful insight tool that can give you the most updated open source intelligence, and help you deeply understand any single GitHub project or quickly compare any two projects by digging deep into 4.6 billion GitHub events in real time. Here are some ways you can play with it.

Compare any two GitHub projects

Do you wonder how different projects have performed and developed over time? Which project is worthy of more attention? OSSInsight.io can answer your questions via the Compare Projects page.

Let’s take the Kubernetes repository (K8s) and Docker’s Moby repository as examples and compare them in terms of popularity and coding vitality.

Popularity

To compare the popularity of two repositories, we use multiple metrics including the number of stars, the growth trend of stars over time, and stargazers’ geographic and employment distribution.

Number of stars

The line chart below shows the accumulated number of stars of K8s and Moby each year. According to the chart, Moby was ahead of K8s until late 2019. The star growth of Moby slowed after 2017 while K8s has kept a steady growth pace.

The star history of K8s and Moby

Geographical distribution of stargazers

The map below shows the stargazers’ geographical distribution of Moby and K8s. As you can see, their stargazers are scattered around the world with the majority coming from the US, Europe, and China.

The geographical distribution of K8s and Moby stargazers

Employment distribution of stargazers

The chart below shows the stargazers’ employment of K8s (red) and Moby (dark blue). Both of their stargazers work in a wide range of industries, and most come from leading dotcom companies such as Google, Tencent, and Microsoft. The difference is that the top two companies of K8s’ stargazers are Google and Microsoft from the US, while Moby’s top two followers are Tencent and Alibaba from China.

The employment distribution of K8s and Moby stargazers

Coding vitality

To compare the coding vitality of two GitHub projects, we use many metrics including the growth trend of pull requests (PRs), the monthly number of PRs, commits and pushes, and the heat map of developers’ contribution time.

Number of commits and pushes

The bar chart below shows the number of commits and pushes submitted to K8s (top) and Moby (bottom) each month after their inception. Generally speaking, K8s has more pushes and commits than Moby, and their number grew stably until 2020 followed by a slowdown afterwards. Moby’s monthly pushes and commits had a minor growth between 2015 and 2017, and then barely increased after 2018.

The monthly pushes and commits of K8s (top) and Moby (bottom)

Number of PRs

The charts below show the monthly and accumulated number of PRs of the two repositories. As you can see, K8s has received stable and consistent PR contributions ever since its inception and its accumulated number of PRs has also grown steadily. Moby had vibrant PR submissions before late 2017, but started to drop afterwards. Its accumulated number of PRs reached a plateau in 2017, which has remained the case ever since.

The monthly and accumulated PR number of K8s (top) and Moby (bottom)

Developers’ contribution time

The following heat map shows developers’ contribution time for K8s (left) and Moby (right). Each square represents one hour in a day. The darker the color, the more contributions occur during that time. K8s has many more dark parts than Moby, and K8s’ contributions occur almost 24 hours a day, 7 days a week. K8s definitely has more dynamic coding activities than Moby.

Heat map of developers’ contribution time of K8s (left) and Moby (right)

Taken together, these metrics show that while both K8s and Moby are popular across industries world-wide, K8s has more vibrant coding activities than Moby. K8s is continuously gaining popularity and coding vitality while Moby is falling in both over time.

Popularity and coding vitality are just two dimensions to compare repositories. If you want to discover more insights or compare other projects you are interested in, feel free to visit the Compare page and explore it for yourself.

Of course, you can use this same page to deeply explore any single GitHub project and gain the most up-to-date insights about them. The key metrics and the corresponding changes are presented in a panoramic view. More in-depth analytics such as code changes by PR size groups and PR lines are also available. Explore it for yourself and you’d be surprised. Have fun.

Panoramic view of key GitHub metrics (K8s as an example)

Total PR number each month/PR groups (K8s as an example)

The number of lines of code change each month (K8s as an example)

Key open source insights

OSSInsight.io does more than explore or compare repositories. It gives you historical, real-time, and custom open source insights. In this section, we’ll share some key insights in open source databases and programming languages. If you want to gain insights in other areas, you can explore the Insights page for yourself.

Note: If you want to get those analytical results by yourself, you can execute the SQL commands above each chart on TiDB Cloud with ease following this 10-minute tutorial.

Rust: the most active programming language

Rust was first released in 2012 and has been among the leading programming languages for 10 years. It has the most active repository with a total of 103,047 PRs at the time of writing.

SELECT 
/*+ read_from_storage(tiflash[github_events]), MAX_EXECUTION_TIME(120000) */
    programming_language_repos.name AS repo_name,
    COUNT(*)      AS num
FROM github_events
         JOIN programming_language_repos ON programming_language_repos.id = github_events.repo_id
WHERE type = 'PullRequestEvent'
  AND action = 'opened'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10

PR numbers of the leading programming languages

Go: the new favorite and the fastest growing programming language

According to OSSInsight.io, 10 programming languages dominate the open source community. Go is the most popular with 108,317 stars, followed by Node and TypeScript. Go is also the fastest growing language in popularity.

WITH repo_stars AS (
    SELECT
        /*+ read_from_storage(tiflash[github_events]) */
        repo_id,
        ANY_VALUE(repos.name) AS repo_name,
        COUNT(distinct actor_login) AS stars
    FROM github_events
         JOIN programming_language_repos repos ON repos.id = github_events.repo_id
    WHERE type = 'WatchEvent'
    GROUP BY 1
), top_10_repos AS (
    SELECT
        repo_id, repo_name, stars
    FROM repo_stars rs
    ORDER BY stars DESC
    LIMIT 10
), tmp AS (
    SELECT
        /*+ read_from_storage(tiflash[github_events]) */
        event_year,
        tr.repo_name AS repo_name,
        COUNT(*) AS year_stars
    FROM github_events
         JOIN top_10_repos tr ON tr.repo_id = github_events.repo_id
    WHERE type = 'WatchEvent' AND event_year <= 2021
    GROUP BY 2, 1
    ORDER BY 1 ASC, 2
), tmp1 AS (
    SELECT
        event_year,
        repo_name,
        SUM(year_stars) OVER(partition by repo_name order by event_year ASC) as stars
    FROM tmp
    ORDER BY event_year ASC, repo_name
)
SELECT event_year, repo_name, stars FROM tmp1

The star growth trends of leading programming languages

Microsoft and Google: the top two programing languages contributors

As world-renowned high-tech companies, Microsoft and Google take the lead in open source language contributions with a total of 1,443 and 947 contributors respectively at the time of writing.

SELECT
    /*+ read_from_storage(tiflash[github_events]), MAX_EXECUTION_TIME(120000) */
    TRIM(LOWER(REPLACE(u.company, '@', ''))) AS company,
    COUNT(DISTINCT actor_id)                 AS num
FROM
    github_events github_events
    JOIN programming_language_repos db ON db.id = github_events.repo_id
    JOIN users u ON u.login = github_events.actor_login
WHERE
    github_events.type IN (
        'IssuesEvent', 'PullRequestEvent','IssueCommentEvent',
        'PullRequestReviewCommentEvent', 'CommitCommentEvent',
        'PullRequestReviewEvent'
    )
    AND u.company IS NOT NULL
    AND u.company != ''
    AND u.company != 'none'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

Companies who contribute the most to programing languages

Elasticsearch draws the most attention

Elasticsearch was one of the first open source databases. It is the most liked database with 64,554 stars, followed by Redis and Prometheus. From 2011 to 2016, Elasticseasrch and Redis shared the top spot until Elasticsearch broke away in 2017.

WITH repo_stars AS (
    SELECT
        /*+ read_from_storage(tiflash[github_events]) */
        repo_id,
        ANY_VALUE(repos.name) AS repo_name,
        COUNT(distinct actor_login) AS stars
    FROM github_events
         JOIN db_repos repos ON repos.id = github_events.repo_id
    WHERE type = 'WatchEvent'
    GROUP BY 1
), top_10_repos AS (
    SELECT
        repo_id, repo_name, stars
    FROM repo_stars rs
    ORDER BY stars DESC
    LIMIT 10
), tmp AS (
    SELECT
        /*+ read_from_storage(tiflash[github_events]) */
        event_year,
        tr.repo_name AS repo_name,
        COUNT(*) AS year_stars
    FROM github_events
         JOIN top_10_repos tr ON tr.repo_id = github_events.repo_id
    WHERE type = 'WatchEvent' AND event_year <= 2021
    GROUP BY 2, 1
    ORDER BY 1 ASC, 2
), tmp1 AS (
    SELECT
        event_year,
        repo_name,
        SUM(year_stars) OVER(partition by repo_name order by event_year ASC) as stars
    FROM tmp
    ORDER BY event_year ASC, repo_name
)
SELECT event_year, repo_name, stars FROM tmp1

The star growth trend of leading databases

China: the number one fan of open source databases

China has the most open source database followers with 11,171 stargazers of database repositories, followed by the US and Europe.

select upper(u.country_code) as country_or_area, count(*) as count, count(*) / max(s.total) as percentage
from github_events
use index(index_github_events_on_repo_id)
left join users u ON github_events.actor_login = u.login
join (
    -- Get the number of people has the country code.
    select count(*) as total
    from github_events
    use index(index_github_events_on_repo_id)
    left join users u ON github_events.actor_login = u.login
    where repo_id in (507775, 60246359, 17165658, 41986369, 16563587, 6838921, 108110, 166515022, 48833910, 156018, 50229487, 20089857, 5349565, 6934395, 6358188, 11008207, 19961085, 206444, 30753733, 105944401, 31006158, 99919302, 50874442, 84240850, 28738447, 44781140, 372536760, 13124802, 146459443, 28449431, 23418517, 206417, 9342529, 19257422, 196353673, 172104891, 402945349, 11225014, 2649214, 41349039, 114187903, 20587599, 19816070, 69400326, 927442, 24494032) and github_events.type = 'WatchEvent' and u.country_code is not null
) s
where repo_id in (507775, 60246359, 17165658, 41986369, 16563587, 6838921, 108110, 166515022, 48833910, 156018, 50229487, 20089857, 5349565, 6934395, 6358188, 11008207, 19961085, 206444, 30753733, 105944401, 31006158, 99919302, 50874442, 84240850, 28738447, 44781140, 372536760, 13124802, 146459443, 28449431, 23418517, 206417, 9342529, 19257422, 196353673, 172104891, 402945349, 11225014, 2649214, 41349039, 114187903, 20587599, 19816070, 69400326, 927442, 24494032) and github_events.type = 'WatchEvent' and u.country_code is not null
group by 1
order by 2 desc;

The geographical distribution of open source database stargazers

OSSInsight.io also allows you to create your own custom insights into any GitHub repository created after 2011. You’re welcome to visit the Insights page to explore more.

Run your own analytics with TiDB Cloud

All the analytics on OSSInsight.io are powered by TiDB Cloud, a fully-managed database as a service. If you want to run your own analytics and get your own insights, sign up for a TiDB Cloud account and try it for yourself with this 10-minute tutorial.

Contact us

Do you find OSSInsight.io useful and fun to work with? Do you have any question or feedback to share with us? Feel free to file an issue on GitHub or follow us on Twitter to get the latest information. You’re also welcome to share this insight tool with your friends.