innodb_flush_log_at_trx_commit 1 -- safer, but slower. (2 _may_ be a better option)
innodb_buffer_pool_size 671088640, 2GB RAM, (all db): 411 MB -- reasonable combination. Probably all tables are eventually fully cached.
(The other settings look defaulted and/or reasonable.)
It should almost never take more than 10ms for
UPDATE ... WHERE <primary key> = <constant>;
Yet, it usually does for this case. Puzzle.
> 3.25M total, 1.11k unique
Does this say that some queries are run _thousands_ of times? I mean _identically_ the same query? This feels like sloppiness on the part of the application. Lots of unnecessary SELECTs against `users` could impact UPDATE `users`.
Do you always use BEGIN...COMMIT? Or sometimes depend on autocommit=1? (I hope you don't use autocommit=0.)
Can you tell if the system is I/O bound? (If so, that change to trx_commit should help significantly.)
Why is SHOW COLUMNS happening so often? I essentially _never_ see that query used, except by certain 3rd-party MySQL layers. What are you using?
Look at the 'transactions' -- that is sequences of code starting with BEGIN (or START) and ending with COMMIT. Decide if there is any time-consuming code inside them that could safely be pulled outside. To better understand the extent of this potential problem, please provide the details for the 'worst' statement (the COMMIT).
Is there anything magical about user.id=18570 ? Or is it just the one UPDATE that pt-query-digest chose?
I _think_ that this says that it is not stumbling over itself:
> 0.03 QPS, 0.01x concurrency