Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,301,527
Community Members
 
Community Events
165
Community Groups

Atlassian Community, let's collaborate and provide stats to vendors about our SQL index usage

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;

PostgreSQL stats query 

 

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

3 comments

M Amine Community Leader Apr 12, 2021

Hi @Gonchik Tsymzhitov 

excellent idea. Will definitely get back you with some stats.

Like Gonchik Tsymzhitov likes this

Hi @M Amine , 

Thank you! Yeah, it will be good to compare and check the usage. 

M Amine Community Leader Apr 17, 2021

schemanametablenameindexnameindex_sizeidx_tup_readidx_tup_fetchidx_scan

publicAO_733371_EVENT_PARAMETERindex_ao_733371_eve142394589917 MB000
publiccwd_user_attributesidx_user_attr_dir_name_lval7376 kB000
publicAO_54307E_SLAAUDITLOGindex_ao_54307e_sla18439304811848 kB000
publicAO_8542F1_IFJ_PRG_OT_RESindex_ao_8542f1_ifj5614896721408 kB000
publicjiraissueissue_resolutiondate952 kB000
publicjiraissueissue_votes920 kB000
publicjiraissueissue_watches856 kB000
publicAO_733371_EVENTindex_ao_733371_event_user_key784 kB000
publicAO_733371_EVENTindex_ao_733371_event_action736 kB000
publicAO_8542F1_IFJ_IMPORT_MISSindex_ao_8542f1_ifj539071317712 kB000
publicAO_733371_EVENTindex_ao_733371_eve902883849560 kB000
publicaudit_logidx_audit_log_created528 kB000
publicAO_4E8AE6_NOTIF_BATCH_QUEUEindex_ao_4e8ae6_not850480572480 kB000
publicAO_4E8AE6_NOTIF_BATCH_QUEUEindex_ao_4e8ae6_not1193702477448 kB000
publicAO_4E8AE6_NOTIF_BATCH_QUEUEindex_ao_4e8ae6_not1081986701384 kB000
publicAO_8542F1_IFJ_OBJindex_ao_8542f1_ifj1752004591232 kB000
publicAO_8542F1_IFJ_OBJindex_ao_8542f1_ifj1846712946224 kB000
publiccwd_useridx_last_name168 kB000
publicissuelinkissuelink_type136 kB000
publicAO_319474_QUEUEindex_ao_319474_queue_topic136 kB000
publiccwd_useridx_first_name120 kB000
publicfieldscreenlayoutitemfieldscreen_field112 kB000
publicAO_319474_MESSAGEindex_ao_319474_message_msg_id72 kB000
publicAO_D530BB_CANNEDRESPONSEUSAGEindex_ao_d530bb_can95513006056 kB000
publictempattachmentsmonitoridx_tam_by_form_token56 kB000
publicconfigurationcontextconfcontext48 kB000
publicclusteredjobclusteredjob_jrk_idx40 kB000
publicavataravatar_filename_index40 kB000
publicfieldconfigurationfc_fieldid40 kB000
publicAO_319474_MESSAGEindex_ao_319474_mes169701299540 kB000

Comment

Log in or Sign up to comment
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you