Good day.
We are using Percona in our non-coomercial IoT Project narodmon (public monitoring project).
After the last update (via yum on CentOS 6.9) to 5.7.28-31 we have many sql system lock errors 1205 for simple UPDATE and INSERT (eg UPDATE only 1 field in row based on PK) commands which were not previously observed.
First, of course, I check mysql slow log (2sec+) - and see nothing.
Well, in the next step, I register all active mysql processes for all users on mysql after each error 1205 and see nothing again.
Only 1-2 active processes on other tables that are not included in UPDATE / INSERT.
Then I implement the 2nd attempt to start INSERT / UPDATE 2-3 seconds after error 1205 and write all processes in mysql before and after it and I see nothing that could block it.
Another problem after latest update is indexes of the most usable tables are broken… process list shows what in a simple select from the table without joins in 1000 rows analyzed 1000000 rows wtf ?
I can fix it only after doing ALTER TABLE … ENGINE = TokuDB any other commands like CHECK, OPTIMIZE, ANALYZE helps nothing.
innodb_file_per_table = 1
default_storage_engine = TokuDB
innodb lock wait timeout = 60
max_heap_table_size & tmp_table_size = 64M
Qcache = ON
If you need more system or status variables - ask me.
Thanks for your time.