Mysql – AWS RDS MySQL some time get slow performance when update table1 set field=’any word’ where primarykey=1234

This very much depends on the data present in the table at the time of the UPDATE.

If, for instance, the table has 1,000,000 rows where value1 = 'foo' and only 1 row where value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; will only actually update a single row. Whereas UPDATE table1 SET value1='foo' WHERE value2='bar'; would update 1,000,001 rows.

If you are talking about a table with only two rows, the difference is entirely negligible.

If you were using SQL Server and had a filtered index like WHERE value2 = 'bar' AND value1 <> 'foo' then UPDATE table1 SET value1='foo' WHERE value2='bar' AND value1 <> 'foo'; would most likely use the index instead of scanning the entire table.

Memory is not instrumented in MySQL until version 5.7 (currently in development), so this does make your question a bit of a guessing game.

I can see from inside InnoDB status, that it doesn't appear to be InnoDB consuming the memory (assuming you collected this from when the problem was occurring):

Total memory allocated 26217103360; in additional pool allocated 0
Dictionary memory allocated 1212141
Buffer pool size   1563519

Total memory: 24.41 GiB (InnoDB needs about 5-10% more than your buffer pool for internal structures) Buffer Pool memory: ~23.85 GiB

I'm going to have to guess that the memory is consumed at the MySQL layer (not InnoDB), and two common causes are intrinsic temporary tables, and very large sort/join buffers.