Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Mohammed 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 @Mohammed Amine , 

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

Mohammed 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 showcase
Published in Data Center

Architect your Atlassian Data Center Application (Jira, Confluence, Bitbucket) in Azure

Hello folks! To the member of organizations who are still running their Atlassian applications on the server, we are on the side of the bridge, and if we need to sail the boat with confidence either...

310 views 0 8
Read article

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