MySQL Forums
Forum List  »  Performance

Re: lockfree approach to creating a single unique row without unique index
Posted by: Rick James
Date: January 05, 2015 12:17AM

1. ... exists ( from ...
is not valid syntax.

2. _Any_ INSERT/UPDATE you try will lock the row and/or the "gap" where the row goes, so you cannot avoid having an exclusive lock.

3. sha256 checksum of the url, 64 chars --
You may as well pack it into BINARY(32) to save space and speed things up. One approach is to use UNHEX(:hex_of_sha) in the INSERT statement

4. Make the BINARY(32) the PRIMARY KEY on an InnoDB table and do a simple
INSERT IGNORE ...
Abandon the complexity you are suggesting; it would be significantly slower.

5. To start with, you will be able to INSERT thousands of rows per second. Tweak innodb_flush_log_at_trx_commit = 2, sync_binlog=off, and maybe some more things.

6. But, once the table no longer fits in cache (of size innodb_buffer_pool_size), performance _will_ degrade due to the randomness of the PRIMARY KEY. Once the table is really big, INSERTs will slow to disk speed. This slowdown would happen with your scheme, too.

How much RAM do you have? How big do you plan for the table to become? If you need to avoid #6, let's talk further. However, it will probably involve abandoning Hibernate.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: lockfree approach to creating a single unique row without unique index
726
January 05, 2015 12:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.