1

In MySQL 5.6, using InnoDB, I have a rather large table, call it A; (about 4 million rows) in which I need to update a field. The condition regarding what to update is held in another table, B; and the relation between the two is also held in another table, C; so I created my update statement with two joins, like so :

UPDATE A a JOIN (B b, C c) ON (conditions) SET a.myField = 'myValue'

Now I tested this statement on a dump of my target database, on a equivalent machine. Because I needed this dump for other purposes, I tested the thing using a transaction in order to rollback afterwards :

START TRANSACTION;
UPDATE [...]
ROLLBACK;

Which gave me pretty good performance (I think) for my update, about 17 seconds and 8 for the rollback.

My question is this: If I were to run this statement as is, not in a transaction, on my target database, would I obtain the same performance ? In other words, does using explicit transactions affect performance, and if yes, how ?

I tried to look the documentation on this but it is rather sparse on this topic.

14
  • Single query is always a hidden transaction.
    – Akina
    Sep 6, 2018 at 8:48
  • @Akina It is, so can I assume my query's execution time would be the same as my transaction's total execution time ? A naive question, I'll give you that. Sep 6, 2018 at 8:57
  • Yes and no in the same time. Explicit transaction, I think, must be slightly more expensive. But I am not sure it can be detected. You may test it...
    – Akina
    Sep 6, 2018 at 9:57
  • 17 seconds sounds quite a long time for an update of a few rows. SHOW CREATE TABLE {tablename} and EXPLAIN SELECT ... (a select query that looks like the UPDATE query you have without changing anything. Have you increased the innodb_buffer_pool_size and innodb_log_file_size from the defaults?
    – danblack
    Sep 7, 2018 at 0:20
  • In my experience I had 500-1000 insert queries and there were executed by php loop. It took 40-60 seconds. I put them in transaction and the time was 5-7 seconds for execution Sep 7, 2018 at 6:37

2 Answers 2

2

1: Using autocommit to automatically commit each transaction:

SET autocommit = ON;
UPDATE ...

2: Explicitly turning a single statement into a transaction. This is 'identical' to #1.

BEGIN;     (aka START TRANSACTION;)
UPDATE ...;
COMMIT;

3: Multiple statements in a transaction:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

4: Don't use this; some day you will forget to COMMIT:

SET autocommit = OFF;
UPDATE ...;
...
eventually: COMMIT;

5: Not advised. It can lead to slowdowns due to overflowing the "log":

BEGIN;
a million statements
COMMIT;

Notes:

  • Each SQL has some small overhead (network transmission; parsing; etc)
  • Each transaction as a noticeable overhead. In particular there is disk I/O to make it crash-safe. Hence #3 is faster than two copies of #1 or #2.
  • See innodb_flush_log_at_trx_commit for a tradeoff between integrity and speed.

Advice:

  • First priority: Do what the application needs -- this is, put the appropriate number of statements in each transaction.
  • Lower priority: Throw together multiple statements when suitable, thereby amortizing the transaction overhead.
  • In the case of INSERT, it is even better to have a single INSERT statement with multiple rows. Davit's Comment show a 9x speedup by putting multiple INSERTs in a single transaction. That is similar to the effect of the single batch insert.
  • Use ROLLBACK only when appropriate. It is costly. This is because InnoDB optimizes for COMMIT and has to do extra effort to undo.
  • For performance improvements, this whole discussion is pretty far down the checklist. More important things involve indexing and query formulation.
  • Multi-thousand-row DELETE and UPDATE have a lot of overhead and stress on the log. It is better to break them up.
0

An incomplete answer but nonetheless, here it is.

Following suggestions from the comments, I made a few tests and obtained the following performance. The question's query modified 350 000 rows out of 4 million.

Using the explicit transaction mentionned above took about 24 seconds, whereas the query alone took a little under 20 seconds.

It turns out using an explicit transaction looks more expensive than a single query. I cannot understand why though, so I'll have to search on that.

Some details about my system variables:
innodb_buffer_pool_size and innodb_log_file_size were increased from the default by a factor 3. For this particular test's purpose I set query_cache_type to 0 (from 1). Performance of our application dropped quite dramatically afterwards, but since it's a test machine, it's fine.
Also, the MySQL server is managed through systemd, where we modified the LimitNOFILE param to 8192. This file's other parameters are I think irrelevant.

About the query, EXPLAIN EXTENDED showed that only req and eq_ref join types were used.
In the end, I will go for the single query on my target database as it is faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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