Re: MySQL gets long semaphore locks on dict0dict.cc - but on DML operations
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.
Subject
Views
Written By
Posted
5307
August 02, 2016 09:05AM
2123
August 14, 2016 06:48PM
2093
August 16, 2016 08:31AM
2052
August 18, 2016 04:31PM
Re: MySQL gets long semaphore locks on dict0dict.cc - but on DML operations
1895
August 22, 2016 11:32AM
1755
August 23, 2016 05:23PM
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.