Hi!
Gonchik is in touch.
Today I would like to get a little bit of help from you related SQL indexes on your Jira installation (Data Center, Server edition does not matter).
I would like to share a story about PostgreSQL and Jira 8.13.3 (JSD 4.13.3) release.
In that post I will be happy if you answer and comment about your instance indexes, as those info can help to improve the existing situation of heavy indexes.
Typically, it starts from improvement of configuration (e.g. using https://postgresqltuner.pl/),
then query analyzing, adding indexes, after removing as heavy one (shortly https://wiki.postgresql.org/wiki/Index_Maintenance
) :)
I use like this query in PostgreSQL (don't forget to connect your jiradb via \c ):
SELECT s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_size_pretty(pg_relation_size(s.indexrelid::regclass)) AS index_size,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM pg_catalog.pg_stat_user_indexes s
JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE
s.idx_scan = 0 -- has never been scanned
AND 0 <>ALL (i.indkey) -- no index column is an expression
AND NOT i.indisunique -- is not a UNIQUE index
AND NOT EXISTS -- does not enforce a constraint
(SELECT 1 FROM pg_catalog.pg_constraint c
WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 30;
My result is
schemaname | tablename | indexname | index_size
------------+--------------------------------+--------------------------------+------------
public | AO_54307E_SLAAUDITLOG | index_ao_54307e_sla1843930481 | 951 MB
public | AO_733371_EVENT_PARAMETER | index_ao_733371_eve1423945899 | 625 MB
public | cwd_user_attributes | idx_user_attr_dir_name_lval | 212 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud1602451017 | 188 MB
public | clusteredjob | clusteredjob_nextrun_idx | 170 MB
public | AO_319474_QUEUE | index_ao_319474_queue_topic | 121 MB
public | AO_319474_MESSAGE | index_ao_319474_mes1697012995 | 74 MB
public | AO_8542F1_IFJ_OBJ | index_ao_8542f1_ifj1846712946 | 60 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud845075831 | 58 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud449906846 | 50 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud365482369 | 49 MB
public | AO_319474_MESSAGE | index_ao_319474_mes525710975 | 44 MB
public | AO_319474_MESSAGE | index_ao_319474_mes1815442463 | 43 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud1940082561 | 41 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud740477310 | 40 MB
public | AO_544E33_AUDIT_LOG_ENTRY | index_ao_544e33_aud365906193 | 37 MB
public | issue_version | iv_parent_id | 32 MB
public | AO_733371_EVENT | index_ao_733371_event_user_key | 30 MB
public | jiraissue | issue_resolutiondate | 26 MB
public | jiraissue | issue_watches | 24 MB
public | jiraissue | issue_duedate | 24 MB
public | AO_733371_EVENT | index_ao_733371_eve902883849 | 21 MB
public | AO_733371_EVENT | index_ao_733371_event_action | 21 MB
public | AO_C77861_AUDIT_ENTITY | index_ao_c77861_aud237541374 | 13 MB
public | AO_4E8AE6_NOTIF_BATCH_QUEUE | index_ao_4e8ae6_not1193702477 | 12 MB
public | AO_C77861_AUDIT_ENTITY | index_ao_c77861_aud265617021 | 11 MB
public | AO_C77861_AUDIT_ENTITY | index_ao_c77861_aud470300084 | 11 MB
public | AO_4E8AE6_NOTIF_BATCH_QUEUE | index_ao_4e8ae6_not850480572 | 5 MB
public | AO_D530BB_CANNEDRESPONSEUSAGE | index_ao_d530bb_can955130060 | 2 MB
public | cwd_user | idx_last_name | 1212 KB
public | cwd_user | idx_first_name | 1171 KB
public | label | label_fieldissuelabel | 974 KB
public | AO_AEFED0_USER_INDEX | index_ao_aefed0_use916119569 | 688 KB
public | label | label_fieldissue | 638 KB
public | label | label_label | 565 KB
(35 rows)
Conclusion:
Based on that info, you can request that info to the app vendor to improve the situation. You can use that article to easy identify the vendor ( https://confluence.atlassian.com/jirakb/list-of-jira-server-ao-table-names-and-vendors-973498988.html , https://github.com/gonchik/cleanup-scripts/blob/master/sql/jira/jira_plugins_mapping_with_AO_tables.sql )
References:
Hope it helps.
Next time I will share story of improving and non-improving frequent queries created by a famous app.
Cheers,
Gonchik Tsymzhitov
Gonchik Tsymzhitov
Solution architect | DevOps
:)
Cyprus, Limassol
175 accepted answers
3 comments