Are you in the loop? Keep up with the latest by making sure you're subscribed to Community Announcements. Just click Watch and select Articles.

×
Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

So when we need to adjust configures for RDBMS? Or DB Performance analysis tools through lazy eyes

Hi, the community!

Today, I would like to speak about configuration for the RDBMS (like MySQL, PostgreSQL, MS SQL, Oracle) and I will be happy to read your feedback. That article will have 4 parts (overview, use cases, specific products, deadlock).

Many times as I see into different environments, installations with Atlassian Suite, as most of the configs are defaults. Therefore I want to share with small tips and tricks, which is used by me for a long time.

 

Based on that maybe someone will be interested in the short links, for first the configuration before starting to go deep into ANALYZE, EXPLAIN utils.

Let’s start it. 

 

  1. PostgreSQL 

My favorite tool is https://pgtune.leopard.in.ua/#/

Via that service, I can easy to set the first parameters. I do recommend you check the DB pool connections in dbconfig.xml (KB of dbconfig.xml) + add a few extra connections (typically I add +10 it depends on replication/bi/other connections), otherwise, you can’t log in. 

You can see one of my server setups.

image.png

References:

 

Second favorite tool is postgresqltuner.pl (https://github.com/jfcoz/postgresqltuner).

[root@jiramirror ~]# wget -O postgresqltuner.pl postgresqltuner.pl
[root@jiramirror ~]# yum install -y perl-DBD-Pg
[root@jiramirror ~]# perl postgresqltuner.pl
image.png

2.MySQL (and other forks Percona, Galera, MariaDB)

So about the MySQL side, I must write about legendary tools mysqltuner.pl (https://github.com/major/MySQLTuner-perl)

On the below screenshots you can see some of my metrics. As that tool in a short time provide you a full overview of that moment.

image.png

Also, I do recommend read recommendations carefully, cause them will help you :)

image.png

 

And another my favourite tool is tuning-primer.sh

https://github.com/BMDan/tuning-primer.sh

image.png

I use both tools before going to investigate deeply slow queries.

 

As for slow queries I use pt-query-digest

https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html

I will describe next time to find some problematic plugin queries in your system.

image.png

3. Oracle

For the Oracle, I did not often do the investigation of the bottlenecks as most of problems fixed by the Spotlight. (https://www.quest.com/solutions/database-performance-monitoring/)

4. MS SQL Server 

For the MS SQL server is the same mostly, I used the general performance counters and a few tips and tricks

So as a lazy person, I use sometimes the SolarWinds Database Performance Analyzer (https://www.solarwinds.com/database-performance-analyzer). 

image.png

Conclusion:

 First of all, I want to say thank you for the improving your ecosystem and Atlassian team for the improving DB schema each release for the performance perspectives.

I will be happy if you share your favourite tools/services/products for the RDBMS performance review, analysis, reporting etc.. Also, I hope you saw I like most open-source tools, as proprietary tools optimized by some specific products easy.

If you are interested of some tips and tricks around tuning RDBMS I can share my experience and use cases and do a small investigation on your environments if you want.

But please, mark the continuous monitoring. measuring will help you to have a tuned ecosystem. 

 

 

Cheers,

Gonchik Tsymzhitov

 

2 comments

Comment

Log in or Sign up to comment
Gonchik Tsymzhitov
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 31, 2020

I will be happy if someone will share his setup, (just interesting to understand what kind RDBMS is used more). 

like:

  • MySQL 5.6 for Jira 8.5. (used the standard configs using the next tutorial, have not met with problem).
  • PostgreSQL 11 for Confluence 7.4 (tuning only shared buffers)
  • Oracle for all systems as we have DBA for that dirty work.
  • MS SQL 2019 for Confluence, Jira (never met with problem, as I know best knowledge it works well)
  • Galera (we used patched version and we know is not supported but it works well :) )

Of course, we kept in our mind Atlassian did all things mostly on PostgreSQL and recommended that product.

  

Vikrant Yadav
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Nov 03, 2022

Hi @Gonchik Tsymzhitov  Thanks for sharing very informative post. 

Need your help in improving our JIRA DB performance. We are using MySQL DB, JIRA version 9.1.0, users are 8000+ , project :- 650+ 

DB Connection pool is so high, due to which JIRA performance is slow. What are the parameter i need to check in Database to find the root cause. Kindly suggest. 

 

Thanks

TAGS
AUG Leaders

Atlassian Community Events