Re: Randomly slow Insert / Update on small table

[Rick James]

First, thanks for the detailed data.

16GB of RAM, 4GB of buffer_pool, data is much smaller --> no ram or swapping problem.

And many other things look fine.

Query cache? How much is allocated to it? Is it turned on?
SHOW VARIABLES LIKE "query%";
Don't know that it will solve your problem, but please try changing to
query_cache_type = OFF

Unrelated notes:
set('yes','no') -- perhaps you meant ENUM, not SET. Or can it really be both yes and no?

I see 6 indexes, each with a single field. Sometimes that indicates the the author does not know the power of "compound indexes".

When it "locks" up, please run
SHOW PROCESSLIST;
a few times to get a feel for what is going on. Then, let's study how they might be stepping on each other. (Based on what you say, I really cannot explain 5 minutes for UPDATE on primary key, nor 1.44s for the SELECT you show.)

How many queries per second are you running? (Questions/Uptime).

`domain` is 'tiny'; it should not be acting this way.

Is MySQL 64-bit? Some of the settings might cause trouble in 32-bit.

Oh!
| innodb_log_file_size | 67108864 |
| innodb_log_files_in_group | 48 |
I've never seen that second value be anything other than 2 or 3. A guess: 48 is not good. Plz try changing it thus:

1. shutdown mysql cleanly -- now log files do not have anything critical
2. edit my.cnf to change innodb_log_files_in_group to 3
3. remove (or rename) the 48 iblog files.
4. restart mysql -- it will complain about 3 missing log files, but then rebuild them.

Now I am eager to hear if that is the cause & solution!