MyMySQL Community
Community homeVersions & features Indexing & tuning High availability

MySQL Community · Building an internal platform with MySQL from scratch: architecture and decision log

572
Myr/mysql·posted by winter·3 hours agoOpen source

Building an internal platform with MySQL from scratch: architecture and decision log

Short version: MySQL needs almost no tuning at small and medium scale — the point where it starts to hurt is much further out than most people assume. Full measurements below.

-- The query that broke: a full scan over 20M rows.
-- A composite index took P99 from 1.8s down to 42ms.
SELECT id, title, created_at
  FROM posts
 WHERE community_id = ?
   AND status = 1
 ORDER BY score DESC
 LIMIT 20;

One last trap: in container environments remember to adjust the memory-related parameters in step. Otherwise the host limit and the process expectation disagree, and the symptom is intermittent, unreproducible failure.

The first thing was to collapse the variables. We were changing config and upgrading the version at the same time, and afterwards nobody could say which change caused what. We rolled back to moving one variable at a time, re-ran three times, and only then did the curve settle. Tedious, but not skippable.

What genuinely surprised me was the tail. The average looked great while P99 jumped by an order of magnitude past some threshold. The cause was not MySQL itself but our upstream connection reuse — the load test traffic was too clean and hid the long-tail requests.

73 comments

73 comments

M
Zzhu_zong·yesterday

I see point 3 differently. The trade-off depends on your read/write ratio: read-heavy with little writing means caching actually widens the inconsistency window.

438
Cchen_dev·2 days ago

We have run this in production for two years without hitting it. That said, we never reached this scale, so our experience is not really evidence here.

91
Ttang_hao·2 days ago

Thanks for sharing real numbers — far more useful than the articles that only cover concepts.

430
Lli_ming·3 minutes ago

We have run this in production for two years without hitting it. That said, we never reached this scale, so our experience is not really evidence here.

362
Zzhou_yi·12 minutes ago

Saved. I am reworking this area this week — this saves a lot of wrong turns.

366
Zzhu_zongOPMod·1 hour ago

This is not a MySQL problem, it is a usage problem. The docs say this API is not thread-safe and you must lock around it yourself.

226
Bbob_chen·2 days ago

This matches what we see in production. We only hit it past 3k QPS; the earlier load tests showed nothing — the test traffic was too clean, with no long-tail requests.

154
Sswoole_lee·2 days ago

There is actually a simpler fix that needs no architecture change: move this check up to the gateway and the problem disappears. The cost is one extra lookup at the gateway.

155
Oops_wang·2 days ago

A question: what changes in a container with a 512Mi memory limit? That is how we run it in production.

30
Zzhu_zong·2 days ago

One counter-example: below MySQL 7.4 the semantics of that code are different, so do not copy it verbatim. We got burned in staging and rolled back once.

49
Wwinter·2 days ago

A question: what changes in a container with a 512Mi memory limit? That is how we run it in production.

143
KkiteOP·2 days ago

I see point 3 differently. The trade-off depends on your read/write ratio: read-heavy with little writing means caching actually widens the inconsistency window.

39
Aalice_dev·2 days ago

This matches what we see in production. We only hit it past 3k QPS; the earlier load tests showed nothing — the test traffic was too clean, with no long-tail requests.

118
Oops_wang·just now

There is actually a simpler fix that needs no architecture change: move this check up to the gateway and the problem disappears. The cost is one extra lookup at the gateway.

114
Nnikic·3 minutes ago

I see point 3 differently. The trade-off depends on your read/write ratio: read-heavy with little writing means caching actually widens the inconsistency window.

2
Zzhu_zong·3 minutes ago

Can you give a minimal reproduction? I ran it locally for ten minutes and could not reproduce on macOS with the latest version.

14
Kkite·5 hours ago

Sharing our numbers, 8 cores 16GB, same scenario:

| Concurrency | P50 | P99 |
|---|---|---|
| 200 | 12ms | 88ms |
| 500 | 31ms | 340ms |

P99 clearly collapses at 500 concurrency, which lines up with your knee point.

39
Kkernel_panic·3 minutes ago

I see point 3 differently. The trade-off depends on your read/write ratio: read-heavy with little writing means caching actually widens the inconsistency window.

37
Wwinter·2 days ago

I just read the MySQL source — the author actually explains the reasoning in a comment, roughly "so that it degrades into predictable behaviour in extreme cases".

26
Sslow_query·2 days agoeditedLevel 6

This is not a MySQL problem, it is a usage problem. The docs say this API is not thread-safe and you must lock around it yourself.

1
Kkite·2 days ago

This is not a MySQL problem, it is a usage problem. The docs say this API is not thread-safe and you must lock around it yourself.

2
Hhuang_ke·2 days agoLevel 6

Worth learning from this debugging approach. We went straight at the logs and took a much longer route.

110
Kkite·5 hours agoLevel 6

One counter-example: below MySQL 7.4 the semantics of that code are different, so do not copy it verbatim. We got burned in staging and rolled back once.

1
Kkernel_panic·2 days ago

There is actually a simpler fix that needs no architecture change: move this check up to the gateway and the problem disappears. The cost is one extra lookup at the gateway.

6
KkiteOP·2 days ago

I see point 3 differently. The trade-off depends on your read/write ratio: read-heavy with little writing means caching actually widens the inconsistency window.

151
Rrase·2 days ago

This is not a MySQL problem, it is a usage problem. The docs say this API is not thread-safe and you must lock around it yourself.

343
Lli_ming·2 days ago

Worth learning from this debugging approach. We went straight at the logs and took a much longer route.

207
Lli_ming·2 days ago

Has anyone run a controlled experiment? I did, reducing it to a single variable, and the difference was 4% — within noise. So I suspect the main cause is something else.

76
Rran_boOP·2 days ago

Agreeing with the above. One addition: with this option enabled the GC count in your metrics doubles, so adjust the alert threshold at the same time or it will keep firing.

5
Nnikic·2 hours ago

One counter-example: below MySQL 7.4 the semantics of that code are different, so do not copy it verbatim. We got burned in staging and rolled back once.

109
Nnikic·3 minutes ago

Saved. I am reworking this area this week — this saves a lot of wrong turns.

88
Rrase·12 minutes ago

Agreeing with the above. One addition: with this option enabled the GC count in your metrics doubles, so adjust the alert threshold at the same time or it will keep firing.

444
KkiteOP·5 hours agoedited

Can you give a minimal reproduction? I ran it locally for ten minutes and could not reproduce on macOS with the latest version.

12
Zzhu_zong·2 days ago

I just read the MySQL source — the author actually explains the reasoning in a comment, roughly "so that it degrades into predictable behaviour in extreme cases".

2
Rrase·3 minutes ago

Can you give a minimal reproduction? I ran it locally for ten minutes and could not reproduce on macOS with the latest version.

26
Rran_bo·just now

This is not a MySQL problem, it is a usage problem. The docs say this API is not thread-safe and you must lock around it yourself.

83
Kkite·5 hours ago

This matches what we see in production. We only hit it past 3k QPS; the earlier load tests showed nothing — the test traffic was too clean, with no long-tail requests.

513
Kkernel_panic·2 hours agoedited

There is actually a simpler fix that needs no architecture change: move this check up to the gateway and the problem disappears. The cost is one extra lookup at the gateway.

83
Aalice_dev·2 days ago

There is actually a simpler fix that needs no architecture change: move this check up to the gateway and the problem disappears. The cost is one extra lookup at the gateway.

32
Oops_wang·2 days ago

A question: what changes in a container with a 512Mi memory limit? That is how we run it in production.

72
Mmike_xu·2 days ago

Saved. I am reworking this area this week — this saves a lot of wrong turns.

66
Sswoole_leeOP·yesterday

Has anyone run a controlled experiment? I did, reducing it to a single variable, and the difference was 4% — within noise. So I suspect the main cause is something else.

3
Bbob_chenOP·2 days ago

We have run this in production for two years without hitting it. That said, we never reached this scale, so our experience is not really evidence here.

2
Zzhu_zong·12 minutes agoedited

Worth learning from this debugging approach. We went straight at the logs and took a much longer route.

30
Oops_wang·1 hour agoedited

This matches what we see in production. We only hit it past 3k QPS; the earlier load tests showed nothing — the test traffic was too clean, with no long-tail requests.

120
Cchen_devOP·2 hours ago

I just read the MySQL source — the author actually explains the reasoning in a comment, roughly "so that it degrades into predictable behaviour in extreme cases".

5
Nnikic·12 minutes ago

Thanks for sharing real numbers — far more useful than the articles that only cover concepts.

337
Rran_boMod·2 days ago

Has anyone run a controlled experiment? I did, reducing it to a single variable, and the difference was 4% — within noise. So I suspect the main cause is something else.

184
Zzhu_zong·5 hours ago

A question: what changes in a container with a 512Mi memory limit? That is how we run it in production.

120
Sswoole_lee·1 hour ago

Agreeing with the above. One addition: with this option enabled the GC count in your metrics doubles, so adjust the alert threshold at the same time or it will keep firing.

3
Lli_ming·2 days agoedited

Sharing our numbers, 8 cores 16GB, same scenario:

| Concurrency | P50 | P99 |
|---|---|---|
| 200 | 12ms | 88ms |
| 500 | 31ms | 340ms |

P99 clearly collapses at 500 concurrency, which lines up with your knee point.

38
Rran_bo·2 days ago

Can you give a minimal reproduction? I ran it locally for ten minutes and could not reproduce on macOS with the latest version.

253
Sswoole_lee·2 days ago

Has anyone run a controlled experiment? I did, reducing it to a single variable, and the difference was 4% — within noise. So I suspect the main cause is something else.

11
Rran_bo·2 days agoedited

Sharing our numbers, 8 cores 16GB, same scenario:

| Concurrency | P50 | P99 |
|---|---|---|
| 200 | 12ms | 88ms |
| 500 | 31ms | 340ms |

P99 clearly collapses at 500 concurrency, which lines up with your knee point.

11
Zzhou_yi·just now

We have run this in production for two years without hitting it. That said, we never reached this scale, so our experience is not really evidence here.

8
Aalice_dev·2 days ago

Thanks for sharing real numbers — far more useful than the articles that only cover concepts.

8
Sslow_query·2 days ago

Thanks for sharing real numbers — far more useful than the articles that only cover concepts.

8
Aalice_dev·2 days ago

Has anyone run a controlled experiment? I did, reducing it to a single variable, and the difference was 4% — within noise. So I suspect the main cause is something else.

26
Mmike_xu·2 days ago

I just read the MySQL source — the author actually explains the reasoning in a comment, roughly "so that it degrades into predictable behaviour in extreme cases".

6
Llinlin·2 days ago

Worth learning from this debugging approach. We went straight at the logs and took a much longer route.

3
Zzhu_zong·yesterday

Agreeing with the above. One addition: with this option enabled the GC count in your metrics doubles, so adjust the alert threshold at the same time or it will keep firing.

1
Lli_ming·2 days ago

One counter-example: below MySQL 7.4 the semantics of that code are different, so do not copy it verbatim. We got burned in staging and rolled back once.

437
Ddev_zhou·2 days ago

Can you give a minimal reproduction? I ran it locally for ten minutes and could not reproduce on macOS with the latest version.

255
Hhuang_ke·just now

We have run this in production for two years without hitting it. That said, we never reached this scale, so our experience is not really evidence here.

331
Rran_bo·2 days agoedited

This matches what we see in production. We only hit it past 3k QPS; the earlier load tests showed nothing — the test traffic was too clean, with no long-tail requests.

316
Kkernel_panic·2 days ago

Saved. I am reworking this area this week — this saves a lot of wrong turns.

2
Zzhu_zong·2 days ago

A question: what changes in a container with a 512Mi memory limit? That is how we run it in production.

26
Zzhou_yi·2 days agoedited

Sharing our numbers, 8 cores 16GB, same scenario:

| Concurrency | P50 | P99 |
|---|---|---|
| 200 | 12ms | 88ms |
| 500 | 31ms | 340ms |

P99 clearly collapses at 500 concurrency, which lines up with your knee point.

1
Bbob_chen·yesterdayedited

Saved. I am reworking this area this week — this saves a lot of wrong turns.

214
Aalice_dev·2 days ago

One counter-example: below MySQL 7.4 the semantics of that code are different, so do not copy it verbatim. We got burned in staging and rolled back once.

1
Aalice_devMod·28 minutes ago

Agreeing with the above. One addition: with this option enabled the GC count in your metrics doubles, so adjust the alert threshold at the same time or it will keep firing.

31
Zzhu_zong·2 days ago

Thanks for sharing real numbers — far more useful than the articles that only cover concepts.

260
Wwinter·2 days ago

Worth learning from this debugging approach. We went straight at the logs and took a much longer route.

1

This is the post detail page /en/c/mysql/post/p7. Posts and comments are generated deterministically from a seeded PRNG, so the same post always renders the same content and the link can be shared, reloaded and indexed. In production this page reads MySQL for the post, Redis for hot-post caching, and fetches the whole comment tree in a single query on the path column.

See the database schema →