-
Announcing VillageSQL Server 0.0.7
We are pleased to announce the launch of VillageSQL Server 0.0.7. You now have a License to Query (please note this post contains lots of James Bond 007 database puns). In this release, the mysql-8.4 build tracks MySQL Server 8.4.11, the mysql-9.7 build tracks MySQL Server 9.7.2, and the percona-8.4 build carries Percona Server 8.4.11-11.
Highlights of this release include the following:
The server can now describe a custom index, store its data, report it, and run a nearest-neighbor search through it. This is the foundation of custom indexing and vector search we demoed at Percona Live Amsterdam 2026.
CLONE now checks that the recipient has your extensions before it copies anything.
An extension can give every connection its own copy of a setting.
Every bundled extension, and the server itself, runs under Valgrind and ThreadSanitizer every night.
Custom indexes defined by extensions
MySQL gives you B-tree, R-tree, hash, and full-text indexes, and that list is fixed. If you want a vector index, you need to wait for a future MySQL release that ships one, and MySQL Community Server doesn't ship one in any version. VillageSQL Server custom indexes let you define an index yourself as an extension. The extension supplies the index type, the storage, the build, and the search, and the server treats the result as a first-class index. This release lays the foundation for that (the Quantum of SQL).
SHOW CREATE TABLE renders a custom index as DDL, with its type, the distance profile for each column (hnsw_cosine here), and the WITH parameters the extension defined:
KEY `idx_title` (`title_vec` `vsql_vector`.`hnsw_cosine`) USING EXTENDED(`vsql_vector`.`hnsw`) WITH (`ef_construction` = 64, `m` = 8),
KEY `idx_body` (`body_vec` `vsql_vector`.`hnsw_l1`) USING EXTENDED(`vsql_vector`.`hnsw`),
The server doesn't interpret m or ef_construction on its own. It stores them because the extension asked it to, and prints them back as DDL you can replay. SHOW INDEX and INFORMATION_SCHEMA.STATISTICS now report the extension's index type name. The server also stores the data a custom index needs. A vector column lives outside the row, in storage the extension manages, and InnoDB now indexes it through a reference, so you can index a column far wider than the usual key-length limit.
The server can also plan a search through a custom index. The query shape is a distance function over the indexed column in ORDER BY, with a LIMIT. The server routes that query to the extension's index instead of sorting the whole table. The classic optimizer reports the access type as index_distance, and EXPLAIN FORMAT=TREE prints Custom index distance scan on idx_title. The hypergraph optimizer picks the same plan.
The server runs the whole scan: it picks the index, asks the extension for the nearest entries, and fetches each row by the primary key the index stored. That means the index has to store the primary key. The vsql-vector-pk extension does, and its HNSW index answers these queries end to end on this release. It is a variant of vsql-vector whose index stores the primary key alongside each entry.
The remaining work on custom indexing is index maintenance. On a table with a custom index, the server currently refuses an UPDATE that changes the primary key or a column in any index, ordinary indexes included, and an ALTER TABLE that changes the primary key. It also refuses a DELETE (DELETE Another Day) against an index that identifies its entries by its own handles rather than by row key, because the server can't yet tell the index which entry to drop. The HNSW indexes in vsql-vector and vsql-vector-pk both work this way, so neither extension supports DELETE yet. Other ALTER TABLE operations, including a full table rebuild, keep the index.
At Percona Live in Amsterdam on September 9 we demoed vector search in MySQL on a development build. An HNSW index defined entirely by the vsql-vector extension answered a nearest-neighbor query from the graph instead of scanning the table, and we measured recall against the same rows. The metadata, the storage, the DDL, and the server side of that scan all ship in this release.
CLONE now checks for your extensions
CLONE INSTANCE copies a running server's data into another server. We extended its compatibility checks to cover extensions, alongside the existing checks for plugins, character sets, and configuration. Extension bundles (.veb files) live outside the data directory, so a clone doesn't copy them, but the system table that records which extensions are installed is copied.
We check the donor's extension list during the handshake, before any data moves. Every extension installed on the donor has to be present in the recipient's veb_dir with a matching version and a matching .veb checksum, which is the same bar the server applies to its own extensions at startup.
This check matters beyond clone. An extension can define a column type whose stored bytes mean nothing without the extension that wrote them. The extension is part of the server's state, so every operational path that copies, moves, or upgrades a server has to account for it. Clone is the first path we covered. Backup and restore, upgrades across codebases, and dependencies between extensions come next.
Extension functions can compute their own return type
The vsql_tvector example extension defines TVECTOR(N), a fixed-width vector type. A function that joins a TVECTOR(3) and a TVECTOR(4) end to end should return a TVECTOR(7). The server's built-in type rules can't produce that answer: they copy one argument's parameters onto the return value, and they reject a call whose arguments disagree.
An extension can now register a function with bind_and_check_types, and the server calls that hook while it resolves the statement:
make_func<&tvector_concat>("tvector_concat")
.bind_and_check_types<&tvector_concat_bind>()
The hook receives each argument's kind, the type parameters already resolved for it, and the literal text when an argument is a string constant. It returns the type parameters for the result, optionally new parameters for each argument, or an error. The hook replaces the built-in rules for that function, so a call the server would have rejected is allowed and the return type is the one the extension computed.
vsql_tvector gains 7 functions that use it, covering a computed return dimension, 2 that fix the dimension and inherit the element type, 1 that inherits the dimension and fixes the element type, 2 reductions to a scalar, and an accessor. You can use these as examples when building your own extension.
Per-session extension settings
An extension can declare per-session variables through the new vsql::session_var capability. Every connection holds its own value, SET SESSION changes it, and the value the extension declared is the global default. INT and STR are supported. Extension system variables, which hold one value for the whole server, work as they did before.
The vector work is why this exists. HNSW search has a dial that trades recall against speed, and that choice belongs to the query, so vsql-vector declares the dial per session:
SET SESSION vsql_vector.ef_search = 100;
For vsql-vector, the default is 40, and a value outside 1 to 65536 is clamped to the nearest bound. Turn it up and the search reads more of the graph, returns better answers, and takes longer. One connection can run an analytics query at 400 while every other connection stays at the default. A function reads the calling session's value through int_var(). The capability is preview, so it needs vsql_allow_preview_extensions=ON.
System variables with a fixed list of values
For Your Enums Only. A vsql::sys_var variable can now be an enum: a fixed list of names, stored as an index. The server validates the name at SET time and rejects anything off the list, so the extension no longer has to check a free-form string after the fact.
Statement events identify the client
The Spy Who Logged Me. The vsql::preview::statement_event capability now reports the client process id, the connector library, and the application name, each NULL when the client didn't send it. If you're trying to be The Man with the Golden Query and writing per-query telemetry, that's what attributes a slow query to the service that ran it.
Breaking changes
DDL that the server used to accept can now be rejected. The server validates type parameters before it hands them to an extension, and for the TYPE, TYPE(N), and TYPE('k=v,...') forms it rejects a duplicate parameter name, a nameless parameter, and a parameter with no value. After the extension's resolve_params callback returns, the server also rejects a rewrite that dropped a parameter. Adding parameters and changing values is still legal. Check your DDL and your extensions against these rules before you upgrade.
The preview authentication capability changed too. auto_grant is gone, replaced by roles_mode, which tells the server whether to activate, grant, or sync the roles an identity provider supplies. An authentication extension built against the 0.0.6 preview needs a source change to compile. vsql-oauth2 already has, and its own vsql_oauth2.auto_grant setting still exists.
Strengthening testing
Tomorrow Never Deadlocks. Every night we run the unit tests, the full VillageSQL test suite, and every bundled extension under both Valgrind memcheck and ThreadSanitizer, so the server and the extensions you get with a release have been through both. We also used gcov to find code the suites never reached and wrote tests for it, and moving our CI runners to Ubuntu 26.04 turned up undefined behavior in our type-parameter parsing along with an upstream bug in the Group Replication plugin.
New extensions
Three extensions joined this cycle:
vsql-duckdb (From Amsterdam with Select): query Parquet, CSV, and JSON files from an ordinary MySQL connection, without exporting the data first or standing up a second query engine. It embeds DuckDB in the server process and reads the files where they already live, in an s3:// bucket or behind an https:// URL. Reading files off the server's own disk works too, once you turn on vsql_duckdb.allow_local_files. It's a port of the PostgreSQL extension pg_duckdb, narrowed to what the extension framework supports today. It uses preview capabilities, so it needs vsql_allow_preview_extensions=ON, and you build it from source. It also needs a server newer than 0.0.6, so this is the first release it runs on.
vsql-vector (Dr. No-Scan): the SVECTOR vector type, stored as packed floats in InnoDB pages through the column storage interface, the distance functions to go with it, and the HNSW index type the demo ran on. Its index does not store a row reference, so on this release the server builds and keeps it current on INSERT but cannot answer a query through it. It needs vsql_allow_preview_extensions=ON, and you build it from source.
vsql-vector-pk (The Vector Who Loved Me): the same SVECTOR type and HNSW index, with the primary key stored alongside each entry. That is what the nearest-neighbor scan described above needs, so this is the variant that answers queries on this release. A server runs either vsql-vector or vsql-vector-pk, never both. It needs vsql_allow_preview_extensions=ON, and you build it from source.
What's ahead
The next release is more hardening and production readiness. We're testing VillageSQL against popular ORMs, the JDBC and ODBC connectors, the Windows client, and backup and restore. We're building performance tests for a server running extensions, so that when someone asks what an extension costs in terms of performance we have a number instead of an opinion. Custom indexes carry on in parallel, on the query side and the write side, until an extension's index runs end to end on a released server without the preview flag.
Special thanks
The native RPM and DEB packaging in this release came from Evgeniy Patlan at Percona. The packages take over an existing MySQL install (On Her Majesty's Secret Server) rather than sitting beside one, sharing its binary names, data directory, systemd unit, and port, and the build dependencies resolve on EL8, EL9, EL10, Ubuntu 24.04, Ubuntu 26.04, Debian 12, and Debian 13. No workflow publishes them yet, so the release artifacts are unchanged for now, but the sources and the build scripts are in the tree.
There's more in this release than fits here. The release notes carry the per-PR detail. Our roadmap is published on GitHub, and we'd like to hear what you're building and what we should work on next, either on GitHub or on Discord. If you want to support VillageSQL, please star the repo.
-
PHP và MySQL: 7 câu hỏi dev mới hay hỏi nhất
Khi bắt đầu làm một website có tính năng động, gần như lúc nào bạn cũng sẽ phải đụng đến PHP và MySQL. Đây là phần lập trình xử lý logic và phần lưu trữ dữ liệu. Bài viết này tổng hợp top 7 câu hỏi thường gặp về PHP và MySQL mà người mới hay hỏi nhất.
PHP là phần xử lý logic chạy trên máy chủ. MySQL là nơi lưu trữ dữ liệu dưới dạng các bảng có liên kết với nhau. Chức năng đăng nhập, giỏ hàng, danh sách bài viết hay sản phẩm phần lớn là kết quả của việc hai thành phần này phối hợp.
Chúng tôi nhận thấy người mới thường hỏi gần như cùng một nhóm câu hỏi. Dưới đây là phần giải đáp cụ thể. Nếu bạn đang tự học, làm đồ án, hoặc là chủ shop nhỏ đang tự dựng website, phần này sẽ giúp bạn có nền tảng vững hơn.
PHP và MySQL khác nhau thế nào, có cần học cả hai không?
PHP là ngôn ngữ lập trình, dùng để viết các quyết định trong chương trình. Ví dụ: kiểm tra mật khẩu có đúng hay không, tính tổng tiền giỏ hàng, hay quyết định hiển thị nội dung nào cho người truy cập.
MySQL là hệ quản trị cơ sở dữ liệu, chuyên giữ dữ liệu và trả về đúng thứ bạn yêu cầu. Nếu chỉ làm trang giới thiệu tĩnh, ít thay đổi, bạn chưa cần MySQL. Khi có người đăng nhập hoặc bài viết cần thêm, sửa, xóa, bạn cần chỗ lưu dữ liệu đó. Với phần lớn dự án, học cả hai là cần thiết.
Nên chọn phiên bản PHP nào cho dự án hiện tại?
Tiêu chí quan trọng nhất là phiên bản đó còn được nhà phát triển vá lỗi bảo mật hay không. Khi phiên bản ngừng hỗ trợ, lỗ hổng mới sẽ không còn bản sửa chính thức. Tiêu chí thứ hai là thư viện bạn định dùng có tương thích với phiên bản đó không.
Danh sách phiên bản được hỗ trợ thay đổi theo thời gian, nên hãy kiểm tra trên trang chính thức của PHP trước khi quyết định. Đừng chỉ tin một bài viết cũ, kể cả bài này chỉ là điểm khởi đầu để bạn đối chiếu.
Đừng quên kiểm tra hosting. Một số gói giá rẻ chỉ hỗ trợ phiên bản khá thấp. Kiểm tra trước khi cài đặt sẽ tránh phiền phức khi nâng cấp về sau.
Kết nối PHP với MySQL sao cho an toàn?
Có hai cách kết nối phổ biến: PDO và MySQLi. PDO làm việc được với nhiều loại cơ sở dữ liệu, còn MySQLi chỉ dành cho MySQL. Với người mới, chúng tôi thường gợi ý PDO vì kiến thức này dễ mang sang dự án khác.
Quan trọng hơn cả là không ghép trực tiếp dữ liệu người dùng nhập vào câu lệnh SQL. Hãy dùng câu lệnh chuẩn bị sẵn (prepared statement) để tách lệnh khỏi dữ liệu. Thông tin kết nối như tên đăng nhập và mật khẩu cơ sở dữ liệu nên đặt trong tệp cấu hình nằm ngoài thư mục công khai.
Vì sao SQL Injection nguy hiểm và phòng tránh ra sao?
SQL Injection là kiểu tấn công mà kẻ xấu nhập mã SQL vào các ô nhập liệu như ô tìm kiếm hoặc form đăng nhập. Nếu chương trình ghép thẳng chuỗi đó vào truy vấn mà không kiểm tra, dữ liệu có thể bị đọc, sửa hoặc xóa.
Cách xử lý cốt lõi là dùng prepared statement và kiểm tra kiểu dữ liệu đầu vào. Ô nhập số lượng chỉ nhận số, còn ô email cần kiểm tra đúng định dạng. Bên cạnh đó, tài khoản kết nối cơ sở dữ liệu chỉ nên có những quyền thật sự cần thiết.
Thiết kế bảng MySQL cho shop nhỏ thế nào cho gọn?
Với shop nhỏ, bạn nên tách riêng bảng người dùng, bảng sản phẩm và bảng đơn hàng. Các bảng liên kết với nhau qua khóa ngoại, ví dụ mỗi đơn hàng trỏ về đúng một người mua. Cách này giúp tránh lặp thông tin và giúp việc sửa đổi chỉ cần làm ở một chỗ.
Đừng chuẩn hóa quá mức khi quy mô còn nhỏ. Thiết kế quá phức tạp sẽ làm việc viết truy vấn khó hơn. Chuẩn hóa vừa phải là đủ, và khi cửa hàng mở rộng bạn có thể điều chỉnh tiếp.
Website chậm vì truy vấn MySQL, nên xử lý từ đâu?
Điều đầu tiên là đừng đoán mò. Hãy bật slow query log để ghi lại các truy vấn chạy chậm, rồi xem xét từng câu. Thường thì thêm chỉ mục (index) cho các cột xuất hiện trong mệnh đề WHERE hoặc JOIN sẽ giúp cải thiện.
Một lỗi phổ biến là dùng SELECT * khi chỉ cần vài cột. Chỉ chọn đúng cột cần thiết cũng góp phần cải thiện tốc độ. Hãy đo lại sau mỗi lần chỉnh sửa để biết hiệu quả thực tế, vì kết quả phụ thuộc vào lượng dữ liệu và cấu hình máy chủ.
So sánh PDO, MySQLi và mysql_ cho người mới
Khi tìm tài liệu cũ, bạn sẽ gặp cả ba cách này. Dưới đây là tóm tắt nhanh:
mysql_: cách cũ, đã ngừng sử dụng và không hỗ trợ prepared statement. Không nên dùng cho dự án mới.
MySQLi: hỗ trợ prepared statement, phù hợp khi dự án chỉ dùng MySQL.
PDO: hỗ trợ prepared statement tốt và dùng được với nhiều hệ quản trị, thường là lựa chọn dễ mang theo kiến thức nhất cho người mới.
Quan trọng hơn là chọn một cách, dùng đúng và giữ nhất quán trong toàn bộ dự án.
Câu hỏi thường gặp
PHP và MySQL có miễn phí cho dự án thương mại không?
Cả hai đều là mã nguồn mở, bạn có thể dùng cho dự án kinh doanh. Tuy nhiên, công cụ đi kèm hoặc dịch vụ hosting có thể tính phí, nên hãy đọc kỹ giấy phép từng thành phần.
Hosting giá rẻ có chạy tốt PHP và MySQL không?
Đa số hosting phổ thông đều hỗ trợ PHP và MySQL. Cần để ý phiên bản PHP và giới hạn tài nguyên. Với website nhỏ thường đủ dùng, khi lượng truy cập tăng bạn sẽ cần nâng cấp gói.
Nên học MySQL trước hay PHP trước?
Người hoàn toàn mới nên học PHP căn bản trước để thấy dữ liệu được lấy ra và hiển thị như thế nào, từ đó hiểu MySQL nhanh hơn.
Nếu chưa biết bắt đầu từ đâu, hãy chọn bài toán nhỏ như trang đăng nhập hoặc danh sách sản phẩm rồi tự làm thử. Để làm quen công cụ quản trị, bạn có thể đọc tổng quan về hệ quản trị cơ sở dữ liệu MySQL và MySQL Workbench là gì và vì sao nên cài đặt. Nếu cần đơn vị tư vấn để dựng website đúng chuẩn, bạn có thể tham khảo dotnetguru.org.
The post PHP và MySQL: 7 câu hỏi dev mới hay hỏi nhất appeared first on DBAhire.
-
How fast is vector search inside MySQL?
Benchmarks · HNSW vector search
MyVector adds approximate nearest-neighbor search to stock MySQL — no separate vector database. We put it on identical hardware across three MySQL versions, added MariaDB’s native vector type as a reference, and measured build time, throughput, latency, and recall on the same data.
askdba/myvector · live dashboard at bench.myvector.online
548peak ANN queries/sec (MySQL 9.7), ~1.8 ms median
0.95best recall@10 (MariaDB 12.3) once tuned
4×engines, one 50k×128 workload, identical 4 vCPU / 24 GB slices
Why benchmark this at all
Vector search usually means standing up a dedicated database next to your primary store, then keeping the two in sync. MyVector takes the other path: it lives inside MySQL as UDFs and stored procedures, builds an HNSW index over a column of vectors, and answers nearest-neighbor queries in SQL. The obvious question is whether that convenience costs you speed or accuracy.
To answer it honestly we needed a level field. Every engine runs in its own container on one Ampere A1 host, each pinned to the same 4 vCPU / 24 GB slice, loading the same 50,000 synthetic 128-dimensional vectors (seeded, so every engine indexes identical data), and builds an HNSW index with the same parameters (M=16, ef_construction=200, L2 distance). Three of the four are MySQL running MyVector — versions 8.4, 9.7 and 26.7. The fourth is MariaDB 12.3, which ships its own native VECTOR type, included as a cross-engine reference point.
What each number means
Five measurements, each isolating one thing:
Index build — wall-clock seconds to build the HNSW graph over all 50k rows.
Insert throughput — rows/sec written into a live, indexed table.
ANN QPS & latency — approximate nearest-neighbor queries per second through the index, and their median response time.
Recall@10 — of the true 10 nearest neighbors (found by exact brute-force search), how many the approximate search actually returns, averaged over held-out queries that were never inserted. 1.0 is perfect; 0.82 means it finds ~8 of the real 10.
ef_search sweep — the accuracy/throughput trade-off: searching more of the graph raises recall but lowers QPS.
Results
The headline is throughput. On identical A1 hardware, all three MySQL versions clear ~530–550 approximate queries per second at ~1.8 ms median — roughly 500× faster than the exact brute-force scan (~1 QPS) they fall back to without an index. 8.4, 9.7 and 26.7 land within a few percent of each other, so moving to a newer MySQL costs nothing here.
Approximate nearest-neighbor throughput at the default operating point (ef_search=200). Higher is better. All four engines on identical Ampere A1 hardware.
Recall@10 at the same operating point. The three MyVector builds land at 0.82; MariaDB 12.3 reaches 0.948 — it trades some speed for accuracy in how it walks the graph.
The real picture: accuracy vs throughput
A single QPS number hides the trade-off. The ef_search knob lets each query decide how hard to look: low values are fast but miss neighbors, high values are accurate but slow. Plotting recall against QPS across that sweep is the honest comparison — up and to the right is better.
Each point is one ef_search setting (10 → 200). MariaDB’s curve sits higher on recall at a given speed; the MySQL/MyVector curves reach higher raw QPS. All share the same synthetic ground truth.
EngineBuild (s)Insert QPSANN QPSANN p50 (ms)Recall@10
Exact brute-force KNN measured ~1 QPS (~700 ms) on every engine — the baseline the index is beating.
The MariaDB surprise
The first MariaDB run looked grim: 29 ANN QPS, a 583-second build, recall aside. Taken at face value it was 15× behind MySQL. That gap was too large to be a real engine difference, so we dug in.
The cause was one default. MariaDB caches its HNSW graph in memory up to mhnsw_max_cache_size, which ships at 16 MB — but the index for this dataset is ~27 MB. The graph didn’t fit, so every search and the whole online build re-read nodes from disk. MyVector sidesteps this by loading its entire index into memory, so the default pitted a disk-bound MariaDB against an in-memory MyVector.
Sizing the cache above the index changed everything:
MariaDB 12.3, same data and hardware, cache at its 16 MB default vs sized above the index. ANN throughput rose ~9×; build dropped from 583 s to 92 s; recall improved from 0.85 to 0.95. (Search alone, measured in isolation, was ~15× faster.)
The lesson, not the leaderboard
Properly configured, MariaDB’s native vector search is competitive with MyVector on speed and ahead on recall. The takeaway isn’t a winner — it’s that a single cache default can make one engine look an order of magnitude slower than it is. Benchmarks are only as fair as their configuration, which is why the setup here is in the open.
Takeaways
Native MySQL vector search is fast. ~530–550 QPS at single-digit-millisecond latency on 4 modest cores, ~500× over brute force — without a second database to run.
Newer MySQL isn’t slower. 8.4, 9.7 and 26.7 land within a few percent of each other; there’s no regression moving forward.
MariaDB 12.3 holds its own once its HNSW cache fits the index — higher recall, comparable throughput.
Configuration is part of the result. The most interesting finding wasn’t a speed number; it was a default that quietly handicapped an engine 15×.
Every number here is reproducible. The live, interactive dashboard is at bench.myvector.online, and the benchmark harness and raw results live in github.com/askdba/myvector under scripts/bench-dashboard. Workload: 50,000 × 128-d vectors, L2 distance, HNSW M=16 / ef_construction=200, held-out queries for recall, each engine pinned to 4 vCPU / 24 GB on Ampere.
-
MySQL Online DDL "Duplicate Key" Optimization
When you rebuild a table with MySQL’s Online DDL, the operation can fail partway through with a Duplicate Entry error:
1
2
mysql> alter table tt add c3 int, algorithm=inplace;
ERROR 1062 (23000): Duplicate entry '1' for key 'tt.uk_c2'
This is a long-standing, well-known problem — it has existed ever since Online DDL was introduced in MySQL 5.6, and it appears across several bug reports:
BUG#76895 — Adding new column OR Drop column causes duplicate PK error
BUG#77572 — The bogus duplicate key error in online ddl with incorrect key name
BUG#98600 — Optimize table fails with duplicate entry on UNIQUE KEY
BUG#104626 — Remove failure of Online ALTER because concurrent Duplicate entry
BUG#76895 was the first to report it. It called the problem a Duplicate PRIMARY KEY error, but that label was itself a bug: a separate 5.6 issue, BUG#77572, printed PRIMARY KEY in the message when it should have said UNIQUE KEY. Once BUG#77572 was fixed (in 5.6.28 and 5.7.10), the message correctly read Duplicate UNIQUE KEY — and BUG#98600 was filed later for what was really the same underlying problem.
MySQL closed BUG#76895 as Not a Bug, treating the error as an unavoidable side effect of how Online DDL works. But “not a bug” is of little consolation to a DBA: a failed DDL derails a carefully planned change and often pushes it to the next maintenance window. That inconvenience is what eventually produced BUG#104626 — this time filed as a feature request asking for a real fix.
So what actually causes this error, why does MySQL consider it working as intended, and can you do anything about it? Answering all three means looking at how Online DDL works internally.
How Online DDL Works
To keep a table writable while it’s being altered, Online DDL splits the job in two: a full copy of the rows that already exist, and an incremental replay of everything that changes while that copy runs. It copies the existing rows into the new table (when you’re adding an index there’s no new table — the rows go straight into the new index). The copy takes time, and DML keeps running throughout, so every change made along the way is captured to be applied afterward. Two things make this work:
A precise cut-off in time, so the full copy takes only the rows that existed before it and nothing after.
An incremental log that captures every change made after the cut-off, so replaying it brings the new table fully up to date.
Distinguishing Full Data from Incremental Changes
The full data lives in the clustered B+Tree, and Online DDL reads it by scanning that tree. Since DML runs during the scan, the tree also holds changes made after the cut-off, so Online DDL needs a way to separate the original rows from those later changes. It does this with InnoDB’s MVCC (multi-version concurrency control).
MVCC lets a transaction read from a consistent snapshot of the data — one that isn’t affected by changes other transactions make while it runs. The mechanism behind it is the read view: when one is created, it records the IDs of all currently active transactions, then uses that list to decide which version of each row the transaction may see. If a row is modified and committed after the read view was created, the view can still reach the earlier version through the undo log, even once the new version is sitting in the clustered index.
That is exactly what Online DDL relies on to set the boundary of the full copy: it opens a read view at one precise moment, and the copy reads only what that view can see. Whatever concurrent DML changes afterward — no matter when it commits — stays invisible to the copy.
The Three Phases of Online DDL
InnoDB runs an Online DDL in three phases, each holding a different level of Metadata Lock (MDL) to control precisely what concurrent DML is allowed to do.
Prepare
Prepare runs under MDL_EXCLUSIVE, which blocks every read and write on the table. That brief “writes-stopped” window is the cut-off between full and incremental data. During it, InnoDB:
Creates a consistent read view via trx_assign_read_view().
Sets up the row log (the incremental log) on the target index and marks the index ONLINE_INDEX_CREATION. From then on, each insert, update, or delete on that index’s B+tree checks the flag to decide whether it also needs to go into the row log.
Both steps happen under MDL_EXCLUSIVE, which guarantees two things:
The read view and the row log line up perfectly, so together they cover the existing rows and every later change with no gap.
It shuts out any in-flight transaction. Without the exclusive lock, a transaction could have modified the table but not yet committed at the moment of the cut-off. The row log isn’t installed yet, so its change goes unlogged; and because it’s still uncommitted, the read view marks it active and the full copy can’t see it either. The change would then be in neither the copy nor the log, and would be lost. MDL_EXCLUSIVE guarantees no such transaction is running, so the read view and row log meet at a clean boundary: everything committed before it belongs to the full copy; everything after it is captured by the row log.
Once Prepare finishes, the lock is downgraded from MDL_EXCLUSIVE to the shared MDL_SHARED_UPGRADABLE.
Execute
During Execute, Online DDL holds MDL_SHARED_UPGRADABLE, so concurrent DML runs normally. This is where the full copy happens, along with the first pass of incremental replay — and it is by far the longest phase, the one throughout which the table stays writable. Every DML on the table is recorded in the row log.
Commit
For Commit, the DDL upgrades the lock from MDL_SHARED_UPGRADABLE back to MDL_EXCLUSIVE, blocking all reads and writes again. Under that lock it replays whatever row log is left and commits the DDL transaction. Because Execute already replayed most of the log, there is little left to do here, so the exclusive lock is held only briefly.
The exclusive lock in this phase also guarantees two things:
Every DML from the Execute phase that touched the table has committed.
No new DML can start, so no new row log is produced.
In short, Online DDL is not fully online — it blocks DML during Prepare and Commit. And because a metadata lock is held for the life of a transaction, a long-running transaction that is in progress when the DDL tries to acquire MDL_EXCLUSIVE will block it — and while the DDL waits, it blocks other DML too, leaving the table effectively unavailable.
The Full Copy
The full copy runs during Execute, under MDL_SHARED_UPGRADABLE, with concurrent DML allowed.
It uses row_merge_read_clustered_index() to scan the old table’s clustered index in ascending PRIMARY KEY order, checking each row’s visibility with MVCC as it goes. Only rows visible to the read view are copied into the new table; delete-marked rows are skipped.
Thanks to MVCC, even when concurrent DML rewrites a row mid-scan, the copy still sees the version as of the read view — reconstructed from the undo log if necessary. What it copies is always one consistent snapshot, unaffected by concurrent activity.
Recording the Incremental Log
The row log records page-level changes to a B+tree, and it behaves differently in the two kinds of DDL:
Rebuilding the table: it logs only changes to the clustered B+tree — INSERT, UPDATE, and DELETE — and on replay each entry is applied to every index.
Adding an index: it logs only changes to the new index’s B+tree — INSERT and DELETE, never UPDATE. Since the index is still being built, these operations are only written to the row log and never actually applied to the B+tree — the opposite of a table rebuild, where every existing index on the old table is maintained in real time.
One detail here is worth paying attention to: when an entry is logged. Because the row log tracks B+tree changes, an entry is written only after the B+tree operation succeeds. As the figure shows, for a table rebuild the entry is logged after the row is inserted into the clustered B+tree; for an added index it is logged at the point the row would go into the secondary index's B+tree — except that, for an added index, nothing is actually inserted, and only the log entry is written.
What Happens on a DML Rollback
A DML operates on the clustered B+tree first, then the secondary indexes. So what happens if the secondary-index step fails?
The row-log entry remains — it isn’t cleaned up. It records one B+tree operation, and that operation really did happen.
The statement then rolls back. Rolling back replays the change in reverse against the B+tree (driven by the undo log), and that reverse operation is logged too. So a failed INSERT leaves two row-log entries — a ROW_T_INSERT followed by a ROW_T_DELETE (below). Applied in order, the two cancel out, exactly as a rollback should.
A failed statement is just one way to reach this state; every rollback works the same way, including an explicit ROLLBACK from the user.
Replaying the Incremental Log
The row log is replayed twice:
The first pass runs in Execute, right after the full copy. The lock is still MDL_SHARED_UPGRADABLE, so DML keeps running and keeps adding to the log. The purpose of this pass is to replay as much of the log as possible now, so the exclusive lock in Commit is held for as short a time as possible.
The second pass runs in Commit, under MDL_EXCLUSIVE, with no new log being produced. It handles the small remainder, and once it is done the new table matches the old one exactly.
Both passes read the log in write order, one entry at a time. For a table rebuild, each entry is applied to every index on the new table; for an added index, only to the index being built.
What Actually Causes the Duplicate Entry
Recall the rollback case: even when an INSERT encounters a Duplicate Entry on a unique index, it still leaves two row-log entries behind (below).
Replaying the first entry, ROW_T_INSERT, does exactly what the original INSERT did: insert a row into the clustered B+tree, then add an entry to each index. When it reaches the unique index, it encounters the same Duplicate Entry — this time during replay, which is what fails the whole DDL.
Reproducing It
1
2
3
4
5
CREATE TABLE t1 (
c1 INT AUTO_INCREMENT PRIMARY KEY,
c2 INT,
UNIQUE KEY (c2)
) ENGINE=InnoDB;
Run these statements in three separate sessions:
1
2
3
4
5
6
7
8
9
# Session 1
BEGIN;
INSERT INTO t1 VALUES(NULL, 1);
# Session 2
ALTER TABLE t1 ENGINE = InnoDB;
# Session 3
INSERT INTO t1 VALUES(NULL, 1);
Now commit Session 1, and both Session 2’s ALTER and Session 3’s INSERT fail with Duplicate Entry:
1
2
3
4
5
6
7
# Session 2
mysql> ALTER TABLE t1 ENGINE = InnoDB;
ERROR 1062 (23000): Duplicate entry '1' for key 't1.c2'
# Session 3
mysql> INSERT INTO t1 VALUES(2, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 't1.c2'
The example relies on the MDL behavior described above:
Session 1’s INSERT takes an MDL_SHARED_WRITE lock on t1 and holds it until it commits.
Session 2’s ALTER needs MDL_EXCLUSIVE for its prepare phase, so it is blocked by Session 1.
Session 3’s INSERT wants MDL_SHARED_WRITE, but is now blocked by Session 2.
You can observe this in performance_schema.metadata_locks — the first row below is Session 1’s lock.
When Session 1 commits, Session 2 acquires MDL_EXCLUSIVE, finishes Prepare, and downgrades to MDL_SHARED_UPGRADABLE. That lock doesn’t conflict with MDL_SHARED_WRITE, so Session 3 runs — and since c2 = 1 already exists, it fails with a Duplicate key error. That failure leaves a row-log entry, and replaying it is what makes Session 2’s ALTER fail in turn.
How to Avoid It
Once the mechanism is clear, I would call this a bug — which is why we fixed it in AliSQL. On community MySQL, the only option is to prevent DML from encountering Duplicate Entry while a DDL is running.
A common trigger is a table with an auto-increment primary key that has also gained a unique index. The application inserts a row, lets MySQL assign the auto-increment key, and — if the first INSERT is slow — retries the same statement from another session. That is perfectly sensible on its own: the unique index guarantees only one of the two can succeed. But a single failing INSERT is enough to make the DDL fail. So while a DDL is in progress, increasing the retry timeout is a practical way to avoid the problem.
The Fix in AliSQL
AliSQL takes a straightforward approach: when replay encounters a Duplicate Entry, ignore it.
Real Duplicates vs. False Ones
There is one caveat: not every Duplicate Entry is safe to ignore.
Online DDL has a genuine real duplicate case: the DDL introduces a new uniqueness constraint (a new or changed primary key, or a new UNIQUE index) while the existing data already contains duplicates. That new constraint isn’t enforced during the DDL, so concurrent DML can keep adding duplicates — and here the DDL must fail. Ignoring it would silently break the guarantee the user asked for.
But if the Duplicate Entry occurs on a unique index that already existed on the table, it is always a false duplicate and safe to skip. That is the common case, and the one AliSQL optimizes.
Cleaning Up the Rollback Entry
As we saw, a failed DML leaves two row-log entries. For an INSERT:
1
2
<ROW_T_INSERT, pk1, ...>
<ROW_T_DELETE, pk1>
On replay, the insert into the unique index fails and we ignore it — but the insert into the primary-key B+tree did succeed. When the second entry (ROW_T_DELETE) is replayed, deleting from the primary key is fine; deleting from the unique index is not, because nothing was ever inserted there, so the record can’t be found and the replay fails.
So whenever we skip a Duplicate Entry on a pre-existing unique index, we have to record it and skip the matching operation when the rollback’s row log arrives later, as shown below:
UPDATE is more complex. When an UPDATE changes a secondary-index column, the secondary index performs two operations:
Delete the old entry.
Insert the new one.
The replay fails at step 2, so on the rollback we cannot simply skip the whole index — we skip step 1 but still perform step 2:
With this handling, AliSQL eliminates the Duplicate Entry failures that Online DDL should never have raised in the first place.
Conclusion
Online DDL can fail with a Duplicate Entry, and because it is nondeterministic — it depends on the timing of concurrent DML — it is hard to rule out entirely, and each failure can disrupt a planned change. The failure happens in just a few steps: a concurrent DML encounters a Duplicate Entry, that error is carried through Online DDL’s row log, and replaying the log fails the DDL. AliSQL breaks this chain by ignoring Duplicate Entry errors that occur on an already-existing unique index, so Online DDL is no longer aborted by an error that did not originate from the DDL itself.
-
Thread Pool in Percona Server and MySQL (Part 2)
Thread Pool in Percona Server and MySQL (Part 2)
The previous Part 1 focused on explaining the idea of thread pool, configuration options and analysis of thread pool efficiency in Percona Server for MySQL 9.7.1-1 against the base line, which is the same server without thread pooling.
We recommend reading the post with Part 1 before proceeding with reading Part 2 in the current post.
MySQL 26.7.0 with and without thread pool:
For better understanding of Percona Server 9.7.1-1 and MySQL 26.7.0 / 9.7.2 thread pool performance against each other we will start with a brief look at MySQL on its own.
The test runs did not find any significant differences in the performance of the thread pool between MySQL Server 26.7.0 and 9.7.2:
Graph 1 – Comparison of MySQL 26.7.0 and 9.7.2 [ INTERACTIVE GRAPH ][ TABLE ]
As can be seen on the Graph 1 – the variation in performance between the versions is minimal. Configurations without thread pool go extremely close to each other, same as with the thread pool.
Lines that correspond to similar configurations converge to the same values across all server buffer tiers (2G / 12G / 32G) and across thread pool parameter ranges (thread_pool_size=[10/20/40/80/120/160] and thread_pool_oversubscribe=[2/3/4]).
NOTE: MySQL 26.7.0 is very slightly faster than 9.7.2 in the high connection threads count.
Therefore, to avoid the crowded graphs the data and performance comparisons will be presented for MySQL Version 26.7.0 only. The results for MySQL 9.7.2 can be viewed on the interactive graph page on GitHub.
Graph 2 – MySQL with 2G buffer [ INTERACTIVE GRAPH ][ TABLE ]
The thread pool implementation behaves differently in MySQL Server 26.7.0. Although it does not bring a huge performance boost as it was in Percona Server, the buffer pool for a high number of client connection threads (640 / 1280 / 2560) helps to keep TPS performance better than without the thread pool. Especially with 2560 connections the pooled TPS is three times the number of non-pooled TPS: 371 and 122 respectively.
The selection of the optimal value for thread_pool_size is important. The value itself (10) looks the same as it was with Percona Server. In the same way, higher values for thread_pool_size (20 / 40 / 80 / 120 / 160) do less TPS improvements.
Unlike Percona Server the oversubscription variations do not impact TPS much when innodb_buffer_pool_size=2G. However, thread_pool_oversubscribe=2 does slightly better than 3 or 4.
Graph 3 – MySQL with 12G buffer [ INTERACTIVE GRAPH ][ TABLE ]
Increasing buffer pool (innodb_buffer_pool_size=12G) brings the overall TPS performance up, but thread pool makes less impact at the highest number of connections: 1273 with thread pool and 718 without (TPS is improved by 77%).
With the fully buffered data set (innodb_buffer_pool_size=32G) MySQL thread pool configuration managed to achieve better TPS performance than non-pooled configuration.
Graph 4 – MySQL with 32G buffer [ INTERACTIVE GRAPH ][ TABLE ]
The pooled configuration yielded 18,975 TPS against 17,011 for non-pooled server configuration, which is 11.6% win for the thread pool.
The p95 latency in MySQL had a very similar characteristic to Percona Server with thread pool reducing the latency from 759ms down to 153 ms.
Comparison of Percona Server 9.7.1-1 for MySQL and MySQL Server 26.7.0:
Finally, we arrived at the direct TPS comparison.
Let’s start with innodb_buffer_pool_size=2G:
For the comparison we will pick the best performing thread pool configurations for Percona Server and MySQL Server along with baseline (thread pool disabled) configurations. It was established before that thread_pool_size=10 works best for both servers (TP 10 on the graph), but the oversubscription 4 (os4 on the graph) works better for Percona Server while the optimal oversubscription for MySQL Server is 2 (os2 on the graph).
Graph 5 – Percona Server and MySQL with 2G buffer [ INTERACTIVE GRAPH ][ TABLE ]
MySQL Server starts well, winning with 40 connections, but we do not consider thread pool efficiency to be good with low number of connections.
At 2560 connections mark the Percona Server implementation of the thread pool helps it to produce more TPS than MySQL server by a factor of 6.2 (2,304 against 371 TPS).
Graph 6 – Percona Server and MySQL with 12G buffer [ INTERACTIVE GRAPH ][ TABLE ]
Again, at a lower number of connections (40 and 80) MySQL Server goes faster than Percona Server.
Moving to the high number of connections (2560) with innodb_buffer_pool_size=12G the Percona thread pool performs better than MySQL.
On the next graph corresponding to innodb_buffer_pool_size=32G, the best performing configurations are different for Percona Server (thread_pool_size=160/thread_pool_oversubscribe=2) and MySQL Server (thread_pool_size=10/thread_pool_oversubscribe=2). It looks that in Percona Server increasing innodb_buffer_pool_size requires increasing thread_pool_size for the thread pool to work optimally.
Graph 7 – Percona Server and MySQL with 32G buffer [ INTERACTIVE GRAPH ][ TABLE ]
In the tier of fully buffered data (innodb_buffer_pool_size=32G) both servers performance was close, but MySQL Server was slightly faster. When it comes to the thread pool, MySQL is a clear winner with 19,054 TPS against 15,519 TPS from Percona Server (22% difference).
With regard to p95 the Percona Server showed slightly better (lower) latency than MySQL server in 2G and 12G buffer tiers. However, with 32G the MySQL Server is ahead of Percona Server:
Graph 8 – p95 latency in Percona Server and MySQL with 32G buffer [ INTERACTIVE GRAPH ][ TABLE ]
With disabled thread pool the latencies for both Percona Server and MySQL land close to each other with MySQL being slightly better (shorter).
When thread pool is enabled the p95 latency in MySQL is notably better (by ~25%) with 177ms against 235ms in Percona server.
Conclusion
Despite sharing the base server code, the thread pool implementations in Percona Server for MySQL and MySQL are very different. When the data set is too big to be fully buffered the Percona Server thread pool shows better performance compared to MySQL Server. However, MySQL Server and its thread pool wins with the configurations when data is fully buffered. Also, in the fully buffered scenario the p95 latency in MySQL Server is slightly shorter than in Percona Server.
Another important observation is that the thread pool inside Percona Server for MySQL is very sensitive to the number of thread groups (thread_pool_size) and the number of active threads in the same group (thread_pool_oversubscribe). These options had the biggest impact on the performance and setting optimal values is important to reach the full potential of the thread pool.
With MySQL Server change of these parameters has less dramatic effect. Also, the noticeable benefit of thread pooling in case of MySQL is visible only on high connection numbers such as 1280/2560 while Percona Server implementation does it much earlier with 80+ connections.
The post Thread Pool in Percona Server and MySQL (Part 2) appeared first on Percona.
|
