MySQL Forums
Forum List  »  InnoDB

Re: MySQL gets long semaphore locks on - but on DML operations
Posted by: Nikhil Nair
Date: August 22, 2016 11:32AM

Its queueing because the level of concurrency is too high for the number of processes. Plus I need a queue to maintain order and such.

I have not checked select count (*). I know for sure there are rows, but it might not be 17k. This is from the SQL pro window of approx rows - which I think comes from Information_schema.

It is with innodb_file_per_table=ON. I considered running optimize table - but in the event that it is not instantaneous, I'd hit massive backlog.

The 99GB I suspect is because rows weren't deleted. This table has been operational for around a year.

Compressing is certainly possible, I could give that a shot - but do you suspect that its the time for the application to retrieve the data thats causing the locks, or just its contribution to size?

I appreciate the discussion - i've run tests on non-production machines - with a watered down instances (lower buffer pool, smaller disk size, fewer CPU etc) and smaller load, while I see numerous locks there as well (chiefly when the table cannot fit in buffer_pool size), i cannot pin it down to this particular problem.

As a solution, I'm changing the application a little - to not rely on a single table, but distributing the payload. So the queries will not hit only 1 table, it'll hit different ones.

I still don't know what's causing this particular sem lock.

Options: ReplyQuote

Written By
Re: MySQL gets long semaphore locks on - but on DML operations
August 22, 2016 11:32AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.