Query Builder Single CF Slow

RT Users
1

Hi,

The below query in Query Builder takes on average 65s each time:

Queue = 'Change Management' AND CF.{Client} = 'Foo'

I have no experience in debugging RT so I have poked around and seen slow logged queries that spend almost all their time in “Sending data” state. Below we see that 8M+ rows are scanned for said slow queries.

Some of the tables are huge IMHO and one has 40M+ rows. Could lack of indexes for CF tables be the problem here or are we simply in need of major DB maintenance?

I am waiting for the admin to increase RAM to 64GB as mysqltuner.pl recommends 38GB memory for the innoDB buffer pool. ATM we have 4 vcpus and 8GB RAM. Buffer pool is 80% of RAM now.

Screenshot 2023-10-01 at 20.28.02
Screenshot 2023-10-01 at 20.28.021920×1039 221 KB

MariaDB [rt4]> select count(*) as records from CustomFields;
+---------+
| records |
+---------+
|     217 |
+---------+
1 row in set (0.002 sec)

MariaDB [rt4]> select count(*) as records from ObjectCustomFields;
+---------+
| records |
+---------+
|    1577 |
+---------+
1 row in set (0.002 sec)

MariaDB [rt4]> select count(*) as records from ObjectCustomFieldValues;
+----------+
| records  |
+----------+
| 40045432 |
+----------+
1 row in set (10.566 sec)

Largest tables on filesystem (ext4) size:

du -m /media/data/var/lib/mysql/rt4/* | sort -bn
457     /media/data/var/lib/mysql/rt4/Principals.ibd
693     /media/data/var/lib/mysql/rt4/GroupMembers.ibd
929     /media/data/var/lib/mysql/rt4/Tickets.ibd
2265    /media/data/var/lib/mysql/rt4/Groups.ibd
2645    /media/data/var/lib/mysql/rt4/CachedGroupMembers.ibd
5093    /media/data/var/lib/mysql/rt4/Transactions.ibd
9297    /media/data/var/lib/mysql/rt4/ObjectCustomFieldValues.ibd
15365   /media/data/var/lib/mysql/rt4/Attachments.ibd

MariaDB config:

# Pre-existing configs
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
datadir=/media/data/var/lib/mysql
#innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb-read-ahead-threshold=8
skip-name-resolve=ON
tmp_table_size=32M
max_heap_table_size=32M
performance_schema=ON
# For MyISAM.
#key_buffer_size=4M
innodb_buffer_pool_size=7000M
innodb_log_file_size=768M
table_open_cache=2000
innodb_buffer_pool_instances=7

All tables are innnoDB.

RT upgraded several times over 10+ year period.

CentOS Linux release 7.9.2009 (Core).

RT: 4.4.3 on VMware ESXi.

Apache: 2.4.6.

MariaDB: 10.3.38 on separate VM in ESXi.

Thank you.

1 Like