CODEWITHSUNDEEP
mysqldatabasetransactions
David Parks
David Parks

Reputation: 32111

Running 3 DB queries 1k times is twice as fast with a transaction than without. Correct?

I'm doing a small test. The following code runs 3 random queries 1000 times (against an innoDB table):

delimiter //
create procedure dtest ()
begin
declare v_max int;
declare v_counter int default 0;
declare holder int;
set v_max = 1000;
  truncate table user;

  start transaction;

  while v_counter < v_max do
    # random query
    insert into user (username) values (CONCAT("user", floor(0 + (rand() * 65535))));
    select count(*) from user into holder;
    select count(*) from user where username = 'user' into holder;
    set v_counter = v_counter + 1;
  end while;

  commit;

end //

I ran the above code with the start transaction; and commit; in, and then again with these two statements removed.

The idea being that I wanted to see if it runs slower with a transaction defined than without.

What I found was that the first test with the start transaction/commit the test runs in ~7 seconds. When I removed start transaction/commit the query then runs in ~15 seconds!

That was a much bigger difference than I expected. I wonder if there is something I'm not understanding?

Upvotes: 3

Views: 132

Answers (2)

Eugen Rieck
Eugen Rieck

Reputation: 65324

The easy answer in this case is, that write combining allowed the combined INSERT, that is result of the query cycle, to hit the disk faster, than single queries would have had.

The not so easy answer is, that this depends heavily upon your hardware and OS - e.g. an Intel Atom with a state-of-the-art SSD (presuambly CPU-bound) will react differently, than an i7 with a 5400rpm disk (presumably disk-bound).

Additionally this highly depends on your concurrency: In your experiment, there was no concurrency involved, which is best case for the transaction. Retrying from 100 threads in parallell might give you different results.

Upvotes: 1

Rok Kralj
Rok Kralj

Reputation: 48775

Not in genral. It can be any factor. There is no rule that inserting w/o transaction is exactly 2 times slower.

The factor (in this example 2) comes form the fact that if you use transactions, all the inserts are done together at once. And of course many other factors, just to complex to deal with (like indexes).

Upvotes: 0

Related Questions