wangbin579

3.1K posts

wangbin579

wangbin579

@wangbin579

Expert in tackling complex software challenges, with deep knowledge of TCP, MySQL and PostgreSQL kernels, and a passion for AI, history, math, and physics.

Beijing, China Присоединился Ekim 2012
131 Подписки17.8K Подписчики
Закреплённый твит
wangbin579
wangbin579@wangbin579·
A small mark on databases, but created with everything I’ve got. MySQL (Beginner): Explore MySQL → wangbin579.gumroad.com/l/mysql_course PostgreSQL (Intermediate): PostgreSQL Uncovered → wangbin579.gumroad.com/l/postgresql_c… MySQL (Intermediate): MySQL Uncovered → wangbin579.gumroad.com/l/mysql-intern… Advanced: MySQL & PostgreSQL Mastering → wangbin579.gumroad.com/l/mysql_and_po… Free Book: The Art of Problem-Solving → github.com/enhancedformys… Learn how databases really work, understand internals, tracing, and performance, and take your skills to the next level.
English
2
26
196
10.5K
wangbin579
wangbin579@wangbin579·
To truly understand MySQL, you need to understand how Postgres approaches the same problems.The intermediate MySQL course is expected to be completed before May 1.
wangbin579 tweet media
English
0
1
43
2.4K
wangbin579
wangbin579@wangbin579·
SQL that tests the optimizer can be found at github.com/winkyao/join-o…. It contains a large number of disastrous SQL cases for MySQL, and occasionally some SQL that runs slowly on Postgres. By combining these with our trace logs, it's easy to identify the root cause.
English
1
6
34
2.1K
alexeymakhotkin
alexeymakhotkin@alexeymakhotkin·
@wangbin579 What is that schema? Looks like IMDB, but I cannot find exact match.
English
1
0
0
130
wangbin579
wangbin579@wangbin579·
Be careful with complex SQL in MySQL. One bad plan can create a Cartesian product and bring your server down by consuming massive disk space. Postgres usually handles the same SQL much more safely.
wangbin579 tweet media
English
2
2
49
6.9K
wangbin579
wangbin579@wangbin579·
If you love database engineering, these ten papers are must-reads. Most are easy to follow, and you’ll learn a lot. Dive in and enjoy! Do some trace analysis, and you’ll get even more out of them.
wangbin579 tweet media
English
3
46
510
18.9K
wangbin579
wangbin579@wangbin579·
AI’s analytical ability is getting stronger and stronger. With sufficient and accurate trace data, it can analyze SQL execution very accurately. For those who truly love databases, this could be the ultimate enjoyment.
English
0
1
33
2K
wangbin579
wangbin579@wangbin579·
While MySQL surprisingly allows derived table merging when RAND() is used without a LIMIT, Postgres never merges in such situations.
wangbin579 tweet media
English
0
0
23
1.8K
wangbin579
wangbin579@wangbin579·
If you’re interested in learning more about the optimizer, you can read these papers.
wangbin579 tweet media
English
3
13
218
8.1K
wangbin579
wangbin579@wangbin579·
@jfg956 There are so many inconsistencies between comments and code, I'm just listing one.
English
0
0
0
508
wangbin579
wangbin579@wangbin579·
This is proof that MySQL's official code review isn't rigorous, because the comments and the code are inconsistent with each other.
wangbin579 tweet media
English
3
2
88
16.6K
wangbin579
wangbin579@wangbin579·
@sunbains The comment says returning true means the value is inserted, but the code returns false to mean the value is inserted.
English
0
0
0
911
Sunny Bains @TiDB
Sunny Bains @TiDB@sunbains·
@wangbin579 IIRC the server code used to have this convention that 0 is ok and non-zero is an error. Similar to OS some syscalls. It’s quite possible the “false” is true and true is “false” 🙂
English
1
0
6
1.5K
wangbin579
wangbin579@wangbin579·
Anyone can become an expert in query optimizers, and this paper is the best guide to get you started.
wangbin579 tweet media
English
4
56
445
14.1K
wangbin579
wangbin579@wangbin579·
If you want to become a database genius among your peers, you need to read this article. It offers invaluable insights that could mark the beginning of your breakthrough in the database field.
wangbin579 tweet media
English
3
89
988
46.6K
wangbin579
wangbin579@wangbin579·
@FranckPachot Just to clarify, I was looking at a case with a performance difference of several tens of times. The 300× case is a completely different issue. I didn’t realize they were separate problems and got them mixed up. Sorry about that.
English
0
0
1
18
wangbin579
wangbin579@wangbin579·
@FranckPachot This issue has nothing to do with statistics. It’s purely a difference in implementation details.
English
2
0
0
353
wangbin579
wangbin579@wangbin579·
Really, I’m not trying to upset Postgres fans, but I found a rare join case where MySQL runs 300 times faster than Postgres.
wangbin579 tweet media
English
10
3
87
13.7K
wangbin579
wangbin579@wangbin579·
@FranckPachot I tried to improve the statistics, but it didn’t work. The columns are correlated, and the estimated join selectivity is far from the actual value. As a result, the truly optimal join order has only a slightly higher cost than the chosen plan, based on trace analysis.
English
0
0
1
28
Franck Pachot
Franck Pachot@FranckPachot·
@wangbin579 I don't think so. Currently, the planner does not use extended statistics for selectivity estimation in table joins.
English
2
0
2
59
wangbin579
wangbin579@wangbin579·
The performance gap remains large no matter how you tweak the parameters, and I’ve already pinpointed the cause.
wangbin579 tweet media
Franck Pachot@FranckPachot

@wangbin579 Interesting. And same built/probe tables? Same join_buffer_size / work_mem as well?

English
1
0
20
3.6K
wangbin579
wangbin579@wangbin579·
@FranckPachot For this query, it seems very difficult to find a way to help the optimizer choose the optimal join order.
English
0
0
0
24

Открыть

@alexeymakhotkin @jfg956 @sunbains @FranckPachot @elonmusk @BarackObama @taylorswift13 @cristiano