Thanks Gordon,
That's a good shout. Current settings for table_open_cache is set to
default of 2000. I've run a test to see what the difference in the
opened_tables value is in the space of a minute and that returned 3426.
So should definitely look at increasing that value.
table_definition_cache is currently 103784
Is there a rubric for working out what these should be set to? I've
re-run the MySQLTuner script against the DB but it's not mentioned
anything about adjusting table_open_cache. It gave a suggestion of a
small increase to the table_definition_cache (106246)
Kind regards
Derick
On 21/03/2025 13:38, Gordan Bobic via discuss wrote:
It sounds like you need to up table_open_cache (and maybe
table_definition_cache) by a significant amount.
On Fri, 21 Mar 2025, 15:35 Derick Turner via discuss,
<[email protected]> wrote:
Hi all,
We have been having some transient issues with processes sticking
on the
Opening tables state, which means that the total number of processes
grows till max processes is hit/locking out the database.
OS Ubuntu sever 24.04.02
MariaDB 11.4.5
It appears to be InnoDB deadlocks. So the example today the
output from
SHOW ENGINE INNODB STATUS \G showed that there was a deadlock
between a
couple of transactions:
MariaDB [(none)]> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2025-03-21 12:49:57 0x7200aeae76c0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1541 srv_active, 247133 srv_idle
srv_master_thread log flush and writes: 248664
----------
SEMAPHORES
----------
------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-03-18 15:35:00 0x72067aea16c0
*** (1) TRANSACTION:
TRANSACTION 57050747951, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s), undo log
entries 1
MariaDB thread id 17, OS thread handle 125372157531840, query id
101395
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2549335 page no 82714 n bits 320 index
mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747951
lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format;
info bits 0
0: len 30; hex
393062613639666233633334373364663239333634323134316333303735; asc
90ba69fb3c3473df293642141c3075; (total 40 bytes);
1: len 8; hex 80000000003ac8e3; asc : ;;
*** CONFLICTING WITH:
RECORD LOCKS space id 2549335 page no 82714 n bits 320 index
mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747928
lock
mode S
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format;
info bits 0
0: len 30; hex
393062613639666233633334373364663239333634323134316333303735; asc
90ba69fb3c3473df293642141c3075; (total 40 bytes);
1: len 8; hex 80000000003ac8e3; asc : ;;
Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format;
info bits 32
0: len 30; hex
393062613636353139363935316562666532646631636535653238326366; asc
90ba665196951ebfe2df1ce5e282cf; (total 40 bytes);
1: len 8; hex 8000000000b78d51; asc Q;;
RECORD LOCKS space id 2549335 page no 82714 n bits 320 index
mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747951
lock
mode S
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format;
info bits 0
0: len 30; hex
393062613639666233633334373364663239333634323134316333303735; asc
90ba69fb3c3473df293642141c3075; (total 40 bytes);
1: len 8; hex 80000000003ac8e3; asc : ;;
Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format;
info bits 32
0: len 30; hex
393062613636353139363935316562666532646631636535653238326366; asc
90ba665196951ebfe2df1ce5e282cf; (total 40 bytes);
1: len 8; hex 8000000000b78d51; asc Q;;
*** (2) TRANSACTION:
TRANSACTION 57050747928, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s), undo log
entries 1
MariaDB thread id 20, OS thread handle 125372148479680, query id
101369
Write_rows_log_event::write_row(-1) on table `mdl_files`
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2549335 page no 82714 n bits 320 index
mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747928
lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format;
info bits 0
0: len 30; hex
393062613639666233633334373364663239333634323134316333303735; asc
90ba69fb3c3473df293642141c3075; (total 40 bytes);
1: len 8; hex 80000000003ac8e3; asc : ;;
*** CONFLICTING WITH:
RECORD LOCKS space id 2549335 page no 82714 n bits 320 index
mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747928
lock
mode S
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format;
info bits 0
0: len 30; hex
393062613639666233633334373364663239333634323134316333303735; asc
90ba69fb3c3473df293642141c3075; (total 40 bytes);
1: len 8; hex 80000000003ac8e3; asc : ;;
Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format;
info bits 32
0: len 30; hex
393062613636353139363935316562666532646631636535653238326366; asc
90ba665196951ebfe2df1ce5e282cf; (total 40 bytes);
1: len 8; hex 8000000000b78d51; asc Q;;
RECORD LOCKS space id 2549335 page no 82714 n bits 320 index
mdl_file_pat_uix of table `client1`.`mdl_files` trx id 57050747951
lock
mode S
Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format;
info bits 0
0: len 30; hex
393062613639666233633334373364663239333634323134316333303735; asc
90ba69fb3c3473df293642141c3075; (total 40 bytes);
1: len 8; hex 80000000003ac8e3; asc : ;;
Record lock, heap no 208 PHYSICAL RECORD: n_fields 2; compact format;
info bits 32
0: len 30; hex
393062613636353139363935316562666532646631636535653238326366; asc
90ba665196951ebfe2df1ce5e282cf; (total 40 bytes);
1: len 8; hex 8000000000b78d51; asc Q;;
*** WE ROLL BACK TRANSACTION (2)
I'm assuming that the rest of the processes were waiting on the
rollback
to happen before they did anything else?
So my questions are:
Am I right in that?
Is there anything which can be done to speed up rollback?
Is there anything which can be done to prevent that rollback from
blocking all of the other processes, the majority of which were
against
completely different databases?
When I was investigating this the suggestion was to dump and
reload all
of the databases. We have 221 databases in the cluster which takes up
880GB of filesystem space. A chunk of that is the ibdata1 file
which is
290GB on that server. (we now have autoshrink on but only after we'd
upgraded to a version which supports it. File per table is on, so
much
of this is unused space.)
We manage this by pointing the webservers to a different node and
waiting for the deadlock to clear but I would like to fix it properly.
Thanks in advance
Derick
--
Derick Turner - He/Him
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
_______________________________________________
discuss mailing list [email protected]
To unsubscribe send an email [email protected]
--
Derick Turner - He/Him
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
