I have a large and heavy loaded mysql database which performs quite fast at times, but some times get terribly slow. All tables are InnoDB, server has 32GB of RAM and database size is about 40GB.

Top 20 queries in my slow_query_log are update, insert and delete queries and I cannot understand why they are so slow (up to 120 seconds sometimes!)

Here is the most frequent query:

UPDATE comment_fallows set comment_cnt_new = 0 WHERE user_id = 1;

Profiling results:

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> update comment_fallows set comment_cnt_new = 0 where user_id = 1;
Query OK, 0 rows affected (2.77 sec)
Rows matched: 18  Changed: 0  Warnings: 0

mysql> show profile for query 1;
+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.000021 |
| checking permissions      | 0.000004 |
| Opening tables            | 0.000010 |
| System lock               | 0.000004 |
| init                      | 0.000041 |
| Searching rows for update | 0.000084 |
| Updating                  | 0.000055 |
| end                       | 0.000010 |
| query end                 | 2.766245 |
| closing tables            | 0.000007 |
| freeing items             | 0.000013 |
| logging slow query        | 0.000003 |
| cleaning up               | 0.000002 |
+---------------------------+----------+
13 rows in set (0.00 sec)

I am using master/server replication, so the binary log is enabled. I've fallowed one advice I've found on the internet and set flush_log_at_trx_commit to 0 but it did not make any difference:

mysql> show variables like '%trx%';
+-------------------------------------------+-------+
| Variable_name                    
Edit
Report