Is a update-only-once-row table worth sharding?

Question

I have a big MySQL 5.6 Inno DB table that contains user's request.

A table row updates only once, shortly-after the initial insert!

(The initial insert is to get the unique auto-incrementing request_id in order to do the processing of the request)

CREATE TABLE requests (

id bigint(20) NOT NULL AUTO_INCREMENT,

user_id BIGINT NOT NULL,

date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

request text

data text,

) ENGINE InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

psuedo script code goes like this:

execute("INSERT INTO requests (id) VALUES (default)")

my $id = query("SELECT LAST_INSERT_ID()")

my $data = do_some_fast_processing($id, $query)

execute(qq# UPDATE request SET query="$query", data=$data WHERE id=$id #)

There are roughly 10 million request per day. Meaning 10 million instance of "insert-and-then-update". Assume I shard this into 2(odd/even user id), And assume both shards will do half of each day's request, would I gain a significant/worthy write performances? because of the lack of locking? Also assume my machine IO isn't maxed.

Answer 1

Is there a problem? Or are you expecting to grow significantly? 10M/day = 120/second, which is high, but not necessarily the limit.

innodb_flush_log_at_trx_commit = 1 is the safest, but it is the slowest. A value of 2 will give you a boost in performance.

Batching INSERTs is also a performance boost; however you may not be able to do so because of how your app works. Also combining statements into a transaction would help -- if practical.

What you have described of your app sounds like all the activity is concentrated near the 'end' of the one table. This implies that there is very little I/O (other than transaction logging, and that was what I was addressing above).

Back to your question...

Sharding across N servers will cut the I/O, locking, CPU, etc by a factor of nearly N.

The process that decides which shard to go to should be another machine, and the shards should be alone on each of the machines.

A problem with sharding to handle growth is what to do when you need more than N machines.

Answer 2

We clearly don't have enough information to answer your question, but I'll try to give you some generic advice.

You say you have 10M requests per second. It's not an impressive number (though it could be too much for a small machine, so again, we don't have enough information). But what really matters here is, how many requests per second do you have during the peaks?

You have a concern about AUTOINC locks. You can monitor how often you have such locks causing contention, by regularly querying the performance_schema.data_locks table.

If you need to reduce contention, check the value of innodb_autoinc_lock_mode. Ideally it should be 2, but this requires binlog_format=ROW and not replaying the binary logs (if you don't understand this part, you're safe).

Assuming that you have a contention problem caused by AUTOINC locks, you can also do something completely different: use UUIDs instead of an AUTO_INCREMENT primary key. In this way the responsibility of generating unique values is delegated to the application, and MySQL will not have to acquire special locks for that purpose. Each insertion will be a bit slower, not much, but anyway I recommend to test this idea before implementing it in production.