1

How do I monitor the performance of the following SQL in-depth?

INSERT INTO rating (account_id, product_id, rating)
    VALUES (133236, 107, 0), (133236, 513, 1), (133236, 575, 2), (133236, 677, 3)
    ON DUPLICATE KEY UPDATE rating=VALUES(rating)

EXPLAIN is next to useless for this statement. Slow query log is turned on, but I don't expect this to be that slow (InnoDB row-locking)

But I have no way to be sure. I'm interested in both the time taken to complete, but also any nasty bugs or side-effects I may not have considered. Is is possible this could make the entire table unavailable?

It is wrapped in a transaction which is rolled-back if the query fails (application code). Really looking for a deep-dive around this simple code.

Background

So I'm currently tinkering with a greenfield toy application, and because I am mildly lazy, I want to use the following.

In my application, all the values are bound parameter values, rather than hard-coded like in the above example. I've got a way to generate as many placeholders as needed and then send in lists.

How do I Instrument monitoring / performance on that query, and others where I am simplifying my application code database interaction in this way?

My Goal is hopefully reducing unnecessary querying, deferring to the DBMS the engine of state. But I'm not inferring zero-cost or magic, and I'd love to know where I can look, to monitor such decisions, so that I can feed back on my own experiments.

The most viable alternatives seems to be

  • attempt to insert new records, then attempt to update existing records
  • delete all records for each account and insert new ones
  • pre-insert all product user-combo's ignoring collisions, then issue update for all.

Rows will never be deleted from the ratings table (I didn't pick the name of the columns or tables BTW). It's like an ever-growing hash-map where account_id and product_id are single-value across all transactions for ratings of products, storing a canonical "latest rating" for an account and product combo.

I did consider doing one record at a time, but that just seems inefficient.

My main concern with two separate insert and updates is that, accounting for changes to the table become a problem, I think my insert bypasses.

I also have my application code wrapped in a try/catch block, which begins a transaction and attempts to roll-back on error, checking if the connection is viable before issuing rollback.

One other oddity, I am currently working around is that if a customer ordered product-A on 4 occasions, and I were to allow 4 input values for the single product. I don't know what would happen. For-now I'm restricting updates to single-order for my own sanity.

Notes

Handler method linked by @RickJames

FLUSH STATUS;  -- zero out most of SESSION STATUS (non existent on really old versions of MySQL)
INSERT INTO rating (account_id, product_id, rating)
    VALUES (42, 107, 0), (42, 513, 1), (42, 575, 2), (42, 677, 3)
    ON DUPLICATE KEY UPDATE rating=VALUES(rating);
SHOW SESSION STATUS LIKE 'Handler%';

This produces the following in a table with all records existing (most collisions)

# Variable_name, Value
Handler_commit, 1
Handler_delete, 0
Handler_discover, 0
Handler_external_lock, 0
Handler_icp_attempts, 0
Handler_icp_match, 0
Handler_mrr_init, 0
Handler_mrr_key_refills, 0
Handler_mrr_rowid_refills, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 4
Handler_read_last, 0
Handler_read_next, 0
Handler_read_prev, 0
Handler_read_retry, 0
Handler_read_rnd, 0
Handler_read_rnd_deleted, 0
Handler_read_rnd_next, 0
Handler_rollback, 0
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_tmp_delete, 0
Handler_tmp_update, 0
Handler_tmp_write, 0
Handler_update, 0
Handler_write, 4

27 Handlers

I Then deleted the rows and ran an insert without ON DUPLICATE clause

# Variable_name, Value
Handler_commit, 1
Handler_delete, 0
Handler_discover, 0
Handler_external_lock, 0
Handler_icp_attempts, 0
Handler_icp_match, 0
Handler_mrr_init, 0
Handler_mrr_key_refills, 0
Handler_mrr_rowid_refills, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 0
Handler_read_last, 0
Handler_read_next, 0
Handler_read_prev, 0
Handler_read_retry, 0
Handler_read_rnd, 0
Handler_read_rnd_deleted, 0
Handler_read_rnd_next, 0
Handler_rollback, 0
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_tmp_delete, 0
Handler_tmp_update, 0
Handler_tmp_write, 0
Handler_update, 0
Handler_write, 4

27 Handlers

Next I tried clearing the session. DELETING all rows, then just doing an insert...

FLUSH STATUS;  -- zero out most of SESSION STATUS (non existent on really old versions of MySQL)
DELETE FROM rating WHERE account_id = 42 AND product_id IN(107, 513, 575, 677);
INSERT INTO rating (account_id, product_id, rating)
    VALUES (42, 107, 0), (42, 513, 1), (42, 575, 2), (42, 677, 3);
SHOW SESSION STATUS LIKE 'Handler%';

The results were as follows

# Variable_name, Value
Handler_commit, 2
Handler_delete, 4
Handler_discover, 0
Handler_external_lock, 0
Handler_icp_attempts, 0
Handler_icp_match, 0
Handler_mrr_init, 0
Handler_mrr_key_refills, 0
Handler_mrr_rowid_refills, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 4
Handler_read_last, 0
Handler_read_next, 4
Handler_read_prev, 0
Handler_read_retry, 0
Handler_read_rnd, 0
Handler_read_rnd_deleted, 0
Handler_read_rnd_next, 0
Handler_rollback, 0
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_tmp_delete, 0
Handler_tmp_update, 0
Handler_tmp_write, 0
Handler_update, 0
Handler_write, 4

27 handlers...

Then I tried again, knowing inserts without the ON UPDATE would lead to problems.

# Variable_name, Value
Handler_commit, 0
Handler_delete, 0
Handler_discover, 0
Handler_external_lock, 0
Handler_icp_attempts, 0
Handler_icp_match, 0
Handler_mrr_init, 0
Handler_mrr_key_refills, 0
Handler_mrr_rowid_refills, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 0
Handler_read_last, 0
Handler_read_next, 0
Handler_read_prev, 0
Handler_read_retry, 0
Handler_read_rnd, 0
Handler_read_rnd_deleted, 0
Handler_read_rnd_next, 0
Handler_rollback, 1
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_tmp_delete, 0
Handler_tmp_update, 0
Handler_tmp_write, 0
Handler_update, 0
Handler_write, 1

27 handlers...

Here is an interesting-ish case. Just updates. So first requiring rows to exist. It's an edge similarly to inserting records where none exist.

FLUSH STATUS;  -- zero out most of SESSION STATUS (non existent on really old versions of MySQL)
UPDATE rating
    SET rating = (case when product_id = 107 then 3
                        when product_id = 513 then 2
                        when product_id = 575 then 1
                        when product_id = 677 then 0
                    end)
    WHERE account_id = 42;
SHOW SESSION STATUS LIKE 'Handler%';

This is interesting both because the CASE statement would need to be generated, a bit like the current values, and I've not tested this using application code.

# Variable_name, Value
Handler_commit, 1
Handler_delete, 0
Handler_discover, 0
Handler_external_lock, 0
Handler_icp_attempts, 0
Handler_icp_match, 0
Handler_mrr_init, 0
Handler_mrr_key_refills, 0
Handler_mrr_rowid_refills, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 1
Handler_read_last, 0
Handler_read_next, 4
Handler_read_prev, 0
Handler_read_retry, 0
Handler_read_rnd, 0
Handler_read_rnd_deleted, 0
Handler_read_rnd_next, 0
Handler_rollback, 0
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_tmp_delete, 0
Handler_tmp_update, 0
Handler_tmp_write, 0
Handler_update, 4
Handler_write, 0

At this point I begin to think it's the right column values summed, not the number of rows in the handlers (they are all 27)

9 | insert with on duplicate update
5 | insert (ideal case only insert needed)
2 | failed insert
18 | delete then insert
10 | bulk single-query updates

So it seems that from the handler method. Upserts are half as costly as in a single request with two queries deleting, then inserting (impressive)

With 24 rows

49  |   2n+1 INSERT ON DUPLICATE KEY UPDATE (single field)
49  |   UPDATE (requires all rows to exist)
75  |   DELETE, then insert
25  |   INSERT (requires no records)
8
  • 1
    "bound parameter values" -- What client? Stored Proc? Java? PDO? Other?
    – Rick James
    Mar 15 at 17:32
  • 1
    "checking if the connection is viable before issuing rollback" -- If the connection dropped out before COMMIT, the transaction was automatically ROLLBACKd.
    – Rick James
    Mar 15 at 17:35
  • Thanks @Rick James. Would that be part of the core MySQL C bindings, or just Sane client behavior? Do you know where I can learn more about client / server internals like that? (re-posted due to typo in name)
    – MrMesees
    Mar 19 at 12:01
  • Rerun those Handle counts with at least 10 rows. 4 is too short to really see anything.
    – Rick James
    Mar 20 at 5:05
  • just did 24 rows. Doesn't seem that out of sorts
    – MrMesees
    Mar 20 at 22:02
1

Short answer: IODKU (as you have) does it all in one, efficient, SQL; Use it.

Long answer:

Please tighten up the statement of the problem. As I read it...

There is a list of 'things', uniquely identified by account_id + product_id and a value, rating.

Choices:

  • IODKU (aka Upsert, essentially a blend of INSERT and UPDATE), like your example, is optimal for updating the for new ratings (for given account and product) or adding tuple new tuple. Ane that statement does I either the INSERT or UPDATE in a single statement. This seems to be what you need; was there a problem with it? IODKU does require in your case that the pair accunt_id and product_id be the PRIMARY KEY or a UNIQUE key; that way it can correctly decide between UPDATE (if the row exists) or INSERT (if it is new). (This should address "to allow 4 input values for the single product; I don't know what would happen". Namely; it depends on what PK or Unique key you have.)
  • REPLACE is especially bad since it is two somewhat independent steps: DELETE and INSERT.
  • SELECT, then INSERT/UPDATE -- several lines of client code; IODKU blends the two together. Internally, INSERT and UDPATE do something like SELECT to find where the row is, or should be, on the disk.
  • DELETE first -- that's akin to REPLACE

There is no "multi-row UPDATE". However, IODKU provides that functionality and it is not too kludgy.

In InnoDB, note that IODKU, as with many multi-row statement, is all-or-none (even without BEGIN and COMMIT around it). If for some reason, you need it to do some of the rows but avoid locking on others, there is no option for such. The IGNORE option lets INSERT skip rows that are already in the table (according to the PK or a unique key).

Correction to the previous paragraph. MySQL 8.0 and MariaDB have SELECT ... NOWAIT and SKIP LOCKED. I am not fluent in these options; they might be useful to you.

In my opinion, the best tool for peering into the inner workings is via the "Handler%" values in SHOW SESSION STATUS;. They are a bit tricky to get. Here is a discussion of such: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts It counts the actual number of reads/writes (whether cached or not). It is useful for comparing two techniques -- fewer reads/writes, means probably faster.

Statements have overhead. So fewer statements usually means faster code overall. A specific metric: Using multi-row INSERTs runs at a speed approaching 10 times as fast as one row per INSERT.

"checking if the connection is viable before issuing rollback" -- If the connection is lost, a ROLLBACK is done for you. A spurious ROLLBACK is harmless. Do not enable "auto-reconnect", it defeats the auto-rollback-on-disconnect. Do check for errors (or try-catch) after each SQL.

5
  • I Think we've both struggled to understand each other. IODKU is what you're calling the pattern. I take it, this is an initialism? I know upsert, but IODKU... it seems unnecessarily gate-keep-y and opaque. I understand the compound primary key could be shortened to key but is that not too much brevity? I'm 100% up for amending to make my ask more clear, less ramble.
    – MrMesees
    Mar 17 at 6:43
  • Sorry. IODKU is an acronym: "Insert On Duplicate Key Update". "Upsert" is another name for the same statement type. A PRIMARY KEY is a KEY, but there is one per table and it must be unique. INDEX == KEY. My use of "key" was to discuss more generically the composite (account_id, product_id) in your table. Sorry for the confusion.
    – Rick James
    Mar 17 at 15:58
  • Hi Rick, I've edited the statement to be more clear about what I am looking for. While I really appreciate your time explaining your own initialism and the basics of SQL terminology to me. I was trying to highlight that I will try to rewrite, that key isn't good enough. I believe the fact this is a compound primary key may be important; and that although I appreciate your time, you've not answered the question.
    – MrMesees
    Mar 19 at 5:08
  • 1
    @MrMesees - Apologies. I overhauled my Answer and added a bunch to it.
    – Rick James
    Mar 19 at 5:37
  • Added results from handlers.We follow the same logic RE: batch operations versus singular.
    – MrMesees
    Mar 19 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.