Home / server / Questions / 350364
Accepted
Mr.Boon
Asked: 2012-01-17 09:43:12 +0800 CST

High Lock Wait ratio in MySQL

  • 772

on my site I log every pageview (date,ip,referrer,page,etc) in a simple mysql table.

This table gets very little selects (3 per minute), but a lot of inserts. (about 100 per second)

Today I changed this table from an InnoDB table to a MEMORY table, this made sense to me to prevent unnecessary hard disk IO. I also prune this table once per minute, to make sure it never get's too big.

--

Performance wise, things are running fine. But I noticed that while running tuning-primer, that my Current Lock Wait ratio is quite high.

Current Lock Wait ratio = 1 : 561

My question: Should I worry about this Lock Wait Ratio? And is there something I can change in my my.cnf to improve things so that the lock wait ratio isn't so high?

performance mysql

1 Answers

  1. Best Answer

    I'd say that if you're happy with the performance you should not worry about it, especially because this is a MEMORY table.

    Guesses for the possible cause:

    1. You have lots of inserts. If those come from concurrent sources those may cause internal locking of the memory table during the inserts. This is not probably a problem because you're using MEMORY table which will be really fast. You cannot prevent all the locking for concurrent access. You could try INSERT DELAYED to see if it helps.
    2. Do you have any UNIQUE columns? The locking could be caused by a missing index that causes sequential table scan for each insert to verify the UNIQUE constraint or some similar cause (I'm not sure if MySQL allows UNIQUE columns without an index).
    • 1