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:

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

Comments

Popular posts from this blog

How only 2 parameters of PostgreSQL reduced anomaly of Jira Data Center nodes

Stories about detecting Atlassian Confluence bottlenecks with APM tool [part 1]