02-04-2024, 07:14 PM
Joe,
I did seevral tests and while the locks are certainly a valid concern it's definitely NOT the culprit of the slow performance we face.
I was lucky to see once a lock on the transactions table (information_schema.innodb_trx) but in the following 10+ additional tests there was nothing.
I have no traffic or users on the crm. No processes returned by processlist ... in other words Db comoletely idle.
As sson as I try to save a specific record I see up to 3 (typically 2) processes in sleep status ... they stay in that status for 1,5 minute and then finally the sql to update the contact info is executed (see below)
MariaDB [(none)]> source list_db_process+transactions.sql;
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
| 20284 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
| 20291 | crm_usr | localhost | crm_db | Sleep | 199 | | NULL | 0.000 |
| 20292 | crm_usr | localhost | crm_db | Sleep | 173 | | NULL | 0.000 |
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
3 rows in set (0.000 sec)
Empty set (0.000 sec)
MariaDB [(none)]> source list_db_process+transactions.sql;
+-------+------+-----------+------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+------+-----------+------+---------+------+----------+------------------+----------+
| 20284 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
+-------+------+-----------+------+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)
Empty set (0.000 sec)
Tried to incrase Error logging but still I don't get anything.
Is there any specific setting to be applied at DB level?
I'm currently running
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12
+-----------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------+----------------------------+
| in_predicate_conversion_threshold | 1000 |
| protocol_version | 10 |
| slave_type_conversions | |
| system_versioning_alter_history | ERROR |
| system_versioning_asof | DEFAULT |
| system_versioning_insert_history | OFF |
| tls_version | TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 10.11.4-MariaDB-1~deb12u1 |
| version_comment | Debian 12 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| version_malloc_library | system |
| version_source_revision | |
| version_ssl_library | OpenSSL 3.0.11 19 Sep 2023 |
| wsrep_patch_version | wsrep_26.22 |
+-----------------------------------+----------------------------+
Please note that I have this slow behavior only while editing a "complex" Contact with Sales Orders, Tickets, Invoices, Surveys ...
Editing a new "clean" contact is fine .. the update takes only few seconds.
What else can I check?
Any other hint?
Let me know
thanks
I did seevral tests and while the locks are certainly a valid concern it's definitely NOT the culprit of the slow performance we face.
I was lucky to see once a lock on the transactions table (information_schema.innodb_trx) but in the following 10+ additional tests there was nothing.
I have no traffic or users on the crm. No processes returned by processlist ... in other words Db comoletely idle.
As sson as I try to save a specific record I see up to 3 (typically 2) processes in sleep status ... they stay in that status for 1,5 minute and then finally the sql to update the contact info is executed (see below)
MariaDB [(none)]> source list_db_process+transactions.sql;
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
| 20284 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
| 20291 | crm_usr | localhost | crm_db | Sleep | 199 | | NULL | 0.000 |
| 20292 | crm_usr | localhost | crm_db | Sleep | 173 | | NULL | 0.000 |
+-------+--------------+-----------+-------------+---------+------+----------+------------------+----------+
3 rows in set (0.000 sec)
Empty set (0.000 sec)
MariaDB [(none)]> source list_db_process+transactions.sql;
+-------+------+-----------+------+---------+------+----------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+------+-----------+------+---------+------+----------+------------------+----------+
| 20284 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
+-------+------+-----------+------+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)
Empty set (0.000 sec)
Tried to incrase Error logging but still I don't get anything.
Is there any specific setting to be applied at DB level?
I'm currently running
Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12
+-----------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------+----------------------------+
| in_predicate_conversion_threshold | 1000 |
| protocol_version | 10 |
| slave_type_conversions | |
| system_versioning_alter_history | ERROR |
| system_versioning_asof | DEFAULT |
| system_versioning_insert_history | OFF |
| tls_version | TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 10.11.4-MariaDB-1~deb12u1 |
| version_comment | Debian 12 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| version_malloc_library | system |
| version_source_revision | |
| version_ssl_library | OpenSSL 3.0.11 19 Sep 2023 |
| wsrep_patch_version | wsrep_26.22 |
+-----------------------------------+----------------------------+
Please note that I have this slow behavior only while editing a "complex" Contact with Sales Orders, Tickets, Invoices, Surveys ...
Editing a new "clean" contact is fine .. the update takes only few seconds.
What else can I check?
Any other hint?
Let me know
thanks