MyMySQL Community
Community homeVersions & features Indexing & tuning High availability

MySQL Community · I built a small tool with MySQL — code is open, feedback welcome

474
Myr/mysql·posted by zhou_yi·5 minutes agoJobs

I built a small tool with MySQL — code is open, feedback welcome

Some background first. Our setup is MySQL plus three downstream services, seven figures of daily requests, peaking around nine in the evening.

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.

We also fixed monitoring along the way: replaced average-based alerts with percentiles and split them per endpoint. False alerts dropped by about seventy percent and the on-call rotation visibly cheered up.

On trade-offs, my view is this: if nobody on the team owns this area long-term, do not introduce a second mechanism. With two coexistence you first have to work out which one is even in play when things break, and that costs far more than the performance you saved.

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.

100 comments

100 comments

M
Ddev_zhou·2 days ago

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

508
Oops_wang·5 hours ago

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

455
Mmike_xuOP·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.

382
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.

403
Llinlin·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.

363
Cchen_dev·12 minutes 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.

33
Nnikic·2 days ago

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

84
RraseOP·3 minutes 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".

286
Mmike_xu·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.

408
Zzhou_yi·2 days ago

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

218
Wwinter·just now

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".

210
Sslow_query·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.

169
Kkernel_panic·2 days ago

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

199
Ttang_hao·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.

336
Cchen_dev·2 days ago

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

196
Wwinter·2 days ago

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

94
Cchen_devOPMod·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.

30
Llinlin·2 days ago

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

20
Oops_wang·2 days 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.

218
Sswoole_leeOP·2 days ago

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

104
Oops_wangOP·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.

222
Aalice_dev·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.

1
Ddev_zhouOP·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.

428
Zzhou_yi·12 minutes 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.

324
Nnikic·1 hour 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.

74
Ddev_zhou·28 minutes agoedited

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.

8
Hhuang_keOP·just now

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

443
Bbob_chen·just now

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.

308
Llinlin·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.

111
Sswoole_lee·1 hour 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.

251
Mmike_xu·2 days ago

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

193
Ttang_hao·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.

152
Hhuang_ke·just now

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

119
Lli_ming·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.

41
Ttang_hao·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.

115
Zzhu_zong·12 minutes ago

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

102
Sslow_query·12 minutes ago

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

95
Zzhou_yi·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
Zzhu_zong·28 minutes 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.

127
Zzhou_yi·2 days agoedited

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

58
Zzhu_zongOP·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".

23
Hhuang_ke·yesterdayedited

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.

77
Kkernel_panic·2 days ago

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

210
Mmike_xuOP·2 days ago

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

32
Mmike_xuOP·12 minutes 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.

14
Kkernel_panic·just now

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.

451
Ddev_zhouOP·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.

248
Ddev_zhou·1 hour 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.

279
Rrase·12 minutes 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.

466
Mmike_xu·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.

332
RraseOP·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.

29
Mmike_xu·2 days agoLevel 6

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.

35
Lli_mingMod·1 hour agoLevel 6

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.

27
Hhuang_ke·28 minutes ago

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

353
WwinterOP·2 days 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.

117
LlinlinMod·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".

175
Kkite·1 hour ago

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

171
Bbob_chen·1 hour 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.

410
Ttang_hao·just now

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.

69
Kkernel_panic·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.

132
NnikicMod·12 minutes 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.

345
Sslow_queryOP·2 days ago

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

159
Sslow_query·12 minutes agoedited

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.

64
Oops_wangOP·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.

2
Kkite·2 days ago

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

52
Hhuang_ke·2 days ago

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

201
Zzhou_yi·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.

45
Mmike_xu·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.

219
Ddev_zhou·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.

21
Kkite·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.

8
Ddev_zhou·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.

3
Mmike_xu·2 days ago

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

148
Ddev_zhou·12 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.

42
Zzhou_yi·28 minutes ago

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

38
Hhuang_ke·12 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.

34
Lli_ming·2 days ago

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

10
Bbob_chen·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.

28
Rran_bo·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.

25
Mmike_xuMod·2 hours 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.

21
Oops_wang·1 hour ago

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

8
Sslow_query·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.

260
Lli_ming·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.

95
Oops_wangOP·12 minutes 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.

49
Lli_ming·3 minutes 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".

276
Sslow_query·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.

129
Oops_wangOP·2 days ago

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

69
NnikicMod·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.

123
Mmike_xu·2 days 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.

19
Ddev_zhou·2 days 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.

435
Sslow_query·2 days agoeditedLevel 6

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

394
Zzhu_zong·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.

21
Sswoole_lee·2 days 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.

2
Rran_boOP·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.

325
Rrase·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.

78
Kkernel_panic·2 hours ago

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

5
Wwinter·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.

1
Rrase·12 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.

1
Bbob_chen·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.

501
Aalice_dev·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.

429
Rrase·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.

1

This is the post detail page /en/c/mysql/post/p8. 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 →