Skip to main content

Performance

Benchmarks pending for V5V5 基准测试待补充

The benchmark numbers below were recorded against PostGraphile V4 and are included for historical context only. We plan to refresh these figures for V5. You should run your own benchmarks for production planning.

以下基准测试数据均基于 PostGraphile V4 测得,仅作历史参考。我们计划后续更新 V5 的测试数据;在进行生产环境容量规划时,请务必自行运行基准测试。

On a Digital Ocean compute-optimized droplet with 8GB of RAM, running PostGraphile, PostgreSQL and the benchmarking software all through Docker, PostGraphile running in cluster mode over 4 vCPUs can handle 3250 requests per second for the following simple query:

在一台配备 8GB 内存的 DigitalOcean 计算优化型云主机(Droplet)上,将 PostGraphile、PostgreSQL 以及压测工具全部通过 Docker 同机运行,PostGraphile 以 4 vCPU 集群模式运行时,面对下面这个简单查询每秒可处理 3250 次请求:

query tracks_media_first_20 {
allTracksList(first: 20) {
trackId
name
}
}

For a more complex 3-level query, it can handle about 1450 requests per second:

面对更复杂的 3 层嵌套查询,每秒仍可处理约 1450 次请求:

query albums_tracks_genre_some {
allAlbumsList(condition: { artistId: 127 }) {
artistId
title
tracksByAlbumIdList {
trackId
name
genreByGenreId {
name
}
}
}
}

And for a very heavy query such as the following one, it can still serve 550 requests per second from a single server, all while maintaining sub-50ms 95th percentile latency:

即便面对下面这种非常繁重的深度查询,单台服务器依然能达到每秒 550 次请求的吞吐量,且 P95 延迟始终保持在 50ms 以内:

query prisma_deeplyNested {
allAlbumsList(condition: { artistId: 127 }) {
albumId
title
tracksByAlbumIdList {
trackId
name
genreByGenreId {
name
}
}
artistByArtistId {
albumsByArtistIdList {
tracksByAlbumIdList {
mediaTypeByMediaTypeId {
name
}
genreByGenreId {
name
}
}
}
}
}
}

To read about how PostGraphile's performance compares to that of Prisma, and how to validate the results for yourself, check out this post on Medium.

如果想了解 PostGraphile 与 Prisma 的性能对比以及如何自行复现测试结果,可以阅读 Medium 上的这篇文章。

How is it so fast?为什么能这么快?​

We leverage graphile-build's look-ahead features when resolving a GraphQL request so that a single root level query, no matter how nested, is compiled into just one SQL query. PostgreSQL has an excellent query planner which optimises and executes this query for us, avoiding the need for multiple round-trips to the database and thus solving the N+1 problem that is found in many GraphQL APIs.

在解析 GraphQL 请求时,我们利用 graphile-build 的 look-ahead(前瞻解析)特性,将单个根级查询(无论嵌套多深)直接编译为仅仅一条 SQL 语句。随后交给 PostgreSQL 强大的查询规划器进行优化与执行,从而避免多次往返数据库的开销,从根本上解决了许多 GraphQL API 常见的 N+1 查询问题。

For example the following query would be compiled into one SQL statement:

例如,下面这段查询会被编译成一条 SQL 语句:

{
allPosts {
edges {
node {
id
title
author: userByAuthorId {
...UserDetails
}
comments {
text
author: userByAuthorId {
...UserDetails
recentComments {
date
post: postByPostId {
title
author {
...UserDetails
}
}
text
}
}
}
}
}
}
}

fragment UserDetails on User {
id
username
bio: bioByUserId {
preamble
location
description
}
}

How can I improve performance of my PostGraphile API?如何进一步提升 PostGraphile API 的性能?​

Chances are that any performance issues you have are coming from your database schema, so standard PostgreSQL optimisation techniques apply. Here's a few things you might want to try:

绝大多数性能瓶颈其实都源自数据库 schema 本身,因此标准的 PostgreSQL 调优手段同样适用。你可以尝试以下几个方向:

  • Throw more RAM at your database server

    为数据库服务器增加内存

  • Make sure your database server is using an SSD

    确保数据库服务器使用 SSD 固态硬盘

  • Make sure you have added the correct type of database indexes in the correct places (references, filters, order-by)

    确保在关键位置(外键引用、过滤条件、排序字段)建立了类型恰当的数据库索引

  • Note: making a column a reference to a foreign key does not add an index to that column, so for example User.postsByAuthorId will be slow unless you've manually added an index to posts.author_id

    注意:将某列设为外键引用不会自动为该列创建索引。例如,如果没有手动给 posts.author_id 建索引,User.postsByAuthorId 查询就会变慢

  • VACUUM your database tables

    定期对数据库表执行 VACUUM

  • Check your RLS policies aren't too expensive, consider optimising them

    检查 RLS 策略是否存在高开销操作,必要时加以优化

  • Optimise your computed column functions

    优化计算列(computed column)函数的执行效率

  • Consider security definer on functions to bypass RLS (but make sure you add your own auth checks!)

    考虑为函数使用 security definer 以绕过 RLS(但务必在函数内自行编写鉴权校验!)

  • Use the envvar DEBUG=graphile-build-pg:sql to show the SQL statements that are being executed; e.g. DEBUG=graphile-build-pg:sql postgraphile

    设置环境变量 DEBUG=graphile-build-pg:sql 打印实际执行的 SQL 语句,例如 DEBUG=graphile-build-pg:sql postgraphile

If you need help optimising your PostgreSQL database or PostGraphile API, please get in touch.

如果你在优化 PostgreSQL 数据库或 PostGraphile API 方面需要专业支持,欢迎联系我们。