MySQL cycling between periods of high I/O and high lock contention

innodblockingMySQL

Starting about 2 days ago, my CloudSQL DB started having this periodic cycle of high latency. The time between these latency bursts vary, from 30 minutes to 50 minutes.

I finally set up a PMM instance today to get a better look at things. I see that the periods of high latency coincides with high active thread count and a lot of lock activity. CloudSql metrics show that these high latency periods also correlate with a reduction in the buffer pool dirty page count

Our workfloads are pretty consistent, certainly we have nothing running that's cyclic with a varying period. Changing some MySQL configs (io threads and lru scan depth) seem to have some impact on this period.

I uploaded some PMM screenshots here, of I/O, locks, and flushing over the last couple of hours. The pattern is pretty apparent.

Data I/O

Locks

Flushing

What is going on? I assume there is something going on under the hood of MySQL operating normally while using up some resource, then going through this period of high latency while trying to free up more of that resource to be able to operate normally again, but I can't piece together exactly what this is.

For more context: this started happening when we added a couple columns (unindexed) to a table. Since then we've reversed that ALTER TABLE and restarted the DB a couple of times to apply config changes. I'm interested both in finding a root cause and in applying a short term band aid to decrease the effect of these spikes.

Thanks!

Best Answer

Point 1 : Locking Management

You must check which processes are staying in locking state & what you can do it for locking i.e creating index for faster retrieval ... You can check deadlock in show engine innodb status

Point 2: Redo Log Buffer & Files

Make sure the redo log buffer & files are big enough to capture many transaction.

Point 3: Flusing Method

Set innodb_flush_method=O_DIRECT for speedup with write operation.

Still this is not enough, you need to check Innodb buffer pool,OS level parameters(iostat,vmstat...)

Related Solutions

Tracing, debugging and fixing Row Lock Contentions

Would it be possible to find out which line of a stored procedure is causing these row lock contentions?

Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

What would be the general guideline to reduce/avoid/eliminate such problems with coding?

The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT or ROLLBACK before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.

COMMIT more frequently. Every COMMIT releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
Make sure you aren't updating any rows without changing their values. For example, UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1); should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
Make sure you are using sequences rather than locking a table to add one to the highest current value.
Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with BULK COLLECT ... FORALL.
Reduce the work that gets done between the first UPDATE and the COMMIT. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
Design the application to handle waiting by doing a SELECT ... FOR UPDATE NOWAIT or WAIT 2. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.

Mysql – Frequently slow queries while in the ‘Update’ state

You need to make some adjustments in your InnoDB setup

ADJUSTMENT #1 : Bigger InnoDB Redo Logs

Since I I do not see innodb_log_file_size, I assume you have the default of 5M. Since your innodb_buffer_pool_size = is 4G, you need 1G redo logs.

ADJUSTMENT #2 : Have InnoDB uses all CPUs

Out of the box, InnoDB does not use all CPUs. I wrote a post long ago about how InnoDB LEFT UNCONFIGURED may work faster in older versions. I also wrote posts about multicore engagement for InnoDB:

With these things said, here are the adjustments to make

cp /etc/my.cnf /etc/my.cnf_old

Add this setting to /etc/my.cnf

[mysqld]
innodb_log_file_size = 1G
innodb_io_capacity = 20000
innodb_read_io_threads = 5000
innodb_write_io_threads = 5000

Next, run these steps

service mysql stop
mv /var/log/mysql/ib_logfile0 /var/log/mysql/ib_logfile0_old 
mv /var/log/mysql/ib_logfile1 /var/log/mysql/ib_logfile1_old
service mysql start

Now, all cores with assist InnoDB and there is much more room for transaction isolation

Give it a Try !!!