MyMySQL Community
Community homeVersions & features Indexing & tuning High availability

MySQL Community · Ten MySQL anti-patterns — how many are you guilty of?

250
Myr/mysql·posted by kernel_panic·2 days agoOpen source

Ten MySQL anti-patterns — how many are you guilty of?

Most MySQL articles stop at "how to use it" and never cover "when not to use it". This is an attempt at the second half.

Order of investigation, by return on effort: 1. Check downstream latency first — usually it is not your problem 2. Then pool hit rate and wait-queue length 3. Only then GC and allocation 4. Suspect the framework last

Worth noting: the official docs do cover this, just in a very inconspicuous spot. I only found it reading the source comments, where the author explains the reasoning — roughly "so that it degrades into predictable behaviour in extreme cases".

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.

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.

92 comments

92 comments

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

486
Zzhu_zongOP·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.

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

474
Cchen_dev·just now

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

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

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

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

321
Zzhu_zongOP·2 days agoedited

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.

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

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

212
Sswoole_lee·28 minutes 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.

268
Bbob_chen·28 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".

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

158
Cchen_devOP·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.

6
Ddev_zhouMod·5 hours 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.

49
Rran_bo·2 days ago

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

142
Hhuang_ke·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.

48
Mmike_xu·5 hours agoedited

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

104
Llinlin·3 minutes ago

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

447
Aalice_dev·2 days agoLevel 6

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.

139
Wwinter·2 days agoLevel 6

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

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

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

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

9
Lli_mingOP·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.

302
Nnikic·2 days ago

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

44
Kkite·2 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.

318
Lli_ming·2 days agoeditedLevel 6

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

352
Llinlin·1 hour agoLevel 6

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.

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

116
Zzhou_yi·3 minutes agoedited

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

291
Wwinter·2 days ago

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

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

4
Zzhou_yi·2 days agoLevel 6

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

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

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

112
Sslow_queryOP·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.

163
Mmike_xu·3 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.

197
Rran_bo·just nowedited

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

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

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

54
Sswoole_lee·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.

99
Ddev_zhou·2 days agoedited

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

7
Rrase·5 hours 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.

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

423
Zzhou_yi·28 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.

121
Kkernel_panic·2 hours ago

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

32
Lli_ming·2 days ago

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

2
Kkernel_panicOP·3 minutes ago

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

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

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

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

99
NnikicOP·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.

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

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

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

68
Cchen_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.

89
Cchen_dev·2 days agoedited

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.

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

412
Cchen_dev·2 days ago

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

19
Zzhou_yi·3 minutes ago

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

47
Bbob_chen·28 minutes agoedited

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

32
Sslow_query·5 hours ago

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

25
Sslow_query·2 days ago

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

15
Ttang_haoMod·12 minutes ago

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

346
Hhuang_ke·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.

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

17
Zzhu_zong·just nowedited

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.

251
Rran_bo·2 days 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.

103
WwinterOP·12 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".

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

192
Rran_boOP·2 days ago

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

27
LlinlinOP·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.

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

14
Bbob_chen·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

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.

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

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

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

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

13
Kkernel_panic·1 hour ago

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

8
Rrase·2 days ago

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

5
Bbob_chen·2 days ago

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

3
Wwinter·2 days ago

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

94
Rran_boOP·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".

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

361
WwinterOP·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
Zzhu_zong·3 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.

2
Rrase·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
Bbob_chen·2 days ago

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

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

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

71

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