innodb_flush_log_at_trx_commit 1 -- safer, but slower. (2 _may_ be a better option)
MQ> OK i will set it at two, i can risk to lose a second transaction.
innodb_buffer_pool_size 671088640, 2GB RAM, (all db): 411 MB -- reasonable combination. Probably all tables are eventually fully cached.
MQ> I can drop some backup tables i think in order to lower DB size.
(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`.
MQ> it could be, it's a web game. I use memcached to avoid hitting the database but unfortunately the framework i use doesn't allow to cache some items. I don't see however selects on the top queries...
Do you always use BEGIN...COMMIT? Or sometimes depend on autocommit=1? (I hope you don't use autocommit=0.)
MQ> I use begin-commit only in some critical transactions. The rest is working with autocommit = 1.
Can you tell if the system is I/O bound? (If so, that change to trx_commit should help significantly.)
MQ> Hmm how can i find out?
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?
MQ> I use the PHP framework Kohana; The show columns can depend on their ORM class that resolves dependencies between objects (hasmany etc)
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).
MQ> I need some time to look into this, i am using it in few places though.
Is there anything magical about user.id=18570 ? Or is it just the one UPDATE that pt-query-digest chose?
MQ> I think is the update that pt-query-digest chose. That query just updated the number of logins the user made and it's run when the user log in.
I _think_ that this says that it is not stumbling over itself:
> 0.03 QPS, 0.01x concurrency
MQ> I don't understand this sentence, what do you mean?