July 29, 2014

Test Drive of Solid

Not so long ago Solid released solidDB for MySQL Beta 3 so I decided now is time to take a bit closer look on new transactional engine for MySQL. While my far goal is the performance and scalability testing before I wanted to look at basic transactional properties such as deadlock detection, select for update handling and phantom reads in the repeatable read isolation level.

Solid has OPTMISTIC (default) and PESSIMISTIC concurrency control, so it was interesting to test both.

We used default isolation mode which is REPEATABLE-READ for this test.

Test 1: Solid, deadlock detection, default (OPTIMISTIC) concurrency control.

So instead of waiting on row with id=2 to be unlocked by transaction in session 1 with OPTIMISTIC concurrency Solid simply reports deadlock due to conflicting update. For applications with rare update conflicts this actually may be good because it is simplier – you do not have to deal with complicated lock graphs or rely on timeout for deadlock resolution. For workloads with frequent row waits this however would result in increased number of deadlocks.

Test 2: SELECT FOR UPDATE:

As we can see deadlock happens in this case even for transaction which runs plain select. Behavior shown in this case does not match the one you would expect from multi versining engine – all the meaning of multi versioning is to allow you to perform consistent read even if rows are locked or even modified unless you require locking read, in which case SELECT FOR UPDATE or SELECT … LOCK IN SHARE MODE can be used. Quite possibly it is a bug which will be fixed in the future, if not it will be serious handycap.

Too bas Solid does not yet have lock investigation tool as SHOW INNODB STATUS (at least) – so it is hard to tell what is really happening.

Test 3: Phantom rows:

So there are no phantom rows which is what we would expect from repeatable-read isolation mode, at least in its interpretation by Innodb.

Let’s look how PESIMISTIC works (mysqld started with –soliddb_pessimistic).

Test 1: Deadlock detection:

So as we can see Solid does not have instant deadlock detection as Innodb and have to rely on timeouts instead. This may be serious problem for some applications, especially interactive ones (ie web) – instead of getting quick deadlock and retrying transaction or giving error to the user you’re forced to wait. Setting lock wait timeout to some small value is not optimal solution ether as it would make some transactions to be terminated even though they just had normal lock wait not a deadlock.

Test 2: SELECT FOR UPDATE:

Result well matches one which we got with OPTIMISTIC locking – just instead of simply balling out on first row lock conflict, query waits for rows to be unlocked. But wait why is it waiting if it suppose to be multi versioning system ?

Test 3: Phantom rows:

In this case we get phantom row, which looks like double bug to us. First we should not get phantom row in repeatable-read isolation mode, second query results should not be different in OPTIMISTIC vs PESSIMISTIC concurrency mode. We can get deadlocks differently but query results should be the same.

Test 4: Update Handling:

Strange error message. This is probably one more bug which hopefully be resolved in further versions.

Test 5: UPDATE / SELECT FOR UPDATE in OPTIMISTIC mode

Error message is obviously wrong in this case. It is not deadlock but update conflict – other transaction has already modified row and commited so update could not proceed.

Also in this case we can see SELECT FOR UPDATE does not play nicely with OPTIMISTIC concurrency. Generally you would use this statement to ensure rows are locked at once so transaction could proceed modifying them without causing deadlocks.

For comparion here are InnoDB sessions:

Test 1: Deadlock detection (deadlock is correctly detected):

Test 2: SELECT FOR UPDATE (second session is not blocked):

Test 3: Phantom rows (no phantom rows in repeatable-reads):

Conclusion: Current Beta version seems to have number of bugs when it comes to concurrency control, so it is hard to say what we’ll see in final version. Current behavior is however very different from Innodb in many cases which may make porting applications to use SolidDB instead of Innodb complicated.

P.S.

After previous experiments I tried SolidDB under sysbench and got next (1 client):

So we can’t run SysBench benchmark with current version. Hopefully next beta will be solid enough to run benchmark.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. Vadim,

    Thanks for taking the solidDB for MySQL Beta 3 for a test run. We have come a long way, and of course there is still a ways to go. We know that we have additional work to do on quality and functionality of our integration with the MySQL Server. I will address below the rest of the issues that you raised.

    About the pessimistic locking, our release notes mention that we do not yet support pessimistic locking. However, we could have been clearer. We will improve the documentation before next release.

    Regarding Test 2 for optimistic locking, thanks for catching the bug. It’s unfortunate that this bug slipped through our QA process. We have already fixed this in our latest code base, and the fix will show up in next release.

    The suggestion to have “SHOW SOLIDDB STATUS” is a great suggestion. I will investigate our support for showing status information or try to get this functionality added in a future release.

    I believe that you meant to place Test 5, which you have listed under the pessimistic cases, with the other optimistic locking cases. You are correct in noting that our error message is wrong. This will be fixed in the next release.

    You are also correct in noting that our SELECT FOR UPDATE behaves differently from InnoDB. One thing to remember is that our Storage Engine for MySQL comes from our main code base, which has been commercially available since 1994 or so. That is the behavior of our core product (BoostEngine). However, you bring up an important point. Migration from other storage engines, and compatibility with them, is an important area that we are currently working on. This is an area where we especially value community feedback.

    The problem with SysBench may be related to the fact that we do not yet support pessimistic locking. We will investigate this further to see if that is the case. We will of course support SysBench and other benchmarks.

    As we ramp up our processes, we expect to encounter some hiccups, but we are dedicated to producing a quality product, with the participation of our community. We appreciate your feedback, and thanks again for all your help.

    Jonathan Cheyer
    Open Source Community Manager
    Solid Information Technology

  2. Vadim says:

    Jonathan,

    Thank you for response.

    One additional from me – In the latest bug with Sysbench I used optimistic (default) locking.

  3. Vadim: thanks, I’ll pass that info along to our engineering folks.

  4. Ilya Zvyagin says:

    Vadim, you wrote:

    PESSIMISTIC mode, Test 3 Phantom rows:

    In this case we get phantom row, which looks like double bug to us.
    First we should not get phantom row in repeatable-read isolation mode,

    Actually ANSI/ISO Standard for SQL allows phantom rows phenomena at REPEATABLE READ isolation level.
    It is not allowed only at the highest isolation level, which is SERIALIZABLE.

    Was it a mistake in your text ? If so, I think it is better to correct the text.

    Other notions are more or less consistent.

  5. peter says:

    Ilya,

    This is right. I specially mention earlier in the article I use “REPEATABLE READ” in Innodb sense not in ANSI Sense. This Will look like double bug to most MySQL/Innodb users.

    Most applications written for MySQL/Innodb rely (often unintentially) on this behavior.

    On the side note I personally think in this case standard is flawed, probably done to accommodate vendors who could not implement repeatable read to be simply repeatable read without any exceptions. It is easy to explain easy to use.

    Just to note – even some MySQL tools rely on it. Ie mysqldump –single-transaction is used to dump transactional tables in consistent snapshot using repeatable-read isolation. This obviously would not work with phantoms.

  6. Binh Thanh Nguyen says:

    Thanks, very nice post. It helps me a lot.

Speak Your Mind

*