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

Comments

Post a Comment

Popular posts from this blog

Overview and practical use cases with open source tracing tool for Java Apps. Glowroot. Installation

-
Image
In that small article, you can find how to install the agent of Glowroot. Installation of the application is quite simple on agents, and this article shows a scheme for working with a built-in collector, but for industrial installations we use it with a bundle with elasticsearch. The installation can be both for individual nodes and so for the central collector. In this case, consider for a separate node. Prerequisites: {jira_home} - is location of Jira home directory. {jira_installation_directory} is the location of Jira installation directory.   Steps: Downloading the installer wget -c https://github.com/glowroot/glowroot/releases/download/v0.13.6/glowroot-0.13.6-dist.zip To check, create a directory via command   mkdir -p /{jira_home}/glowroot/tmp Let the Jira process write and set ownership for simplicity, but it is recommended that you just give the write ability to the owner of the Jira process. chown -R jira: /{jira_home}/glowroot We additionally set the argument to setenv.sh in
Read more

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 O
Read more

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

-
Image
  Hello community, Gonchik Tsymzhitov is in touch. Today, I would like to share with you a short story about postgresqltuner.pl in usage.  So from previous articles I hope you configured the PostgreSQL server and postgresqltuner.pl.  After executing command, ./postgresqltuner.pl --ssd for ssd disks server. You can see like this output: postgresqltuner.pl version 1.0.1 [OK]      I can invoke executables Connecting to /var/run/postgresql:5432 database template1 as user 'postgres'... [OK]      The user account used by me for reporting has superuser rights on this PostgreSQL instance =====  OS information  ===== [INFO]    OS: linux Version: 5.4.17-2036.102.0.2.el8uek.x86_64 Arch: x86_64-linux-thread-multi [INFO]    OS total memory: 41.03 GB [OK]      vm.overcommit_memory is adequate: no memory overcommitment [INFO]    Running under a vmware hypervisor [INFO]    Currently used I/O scheduler(s): mq-deadline =====  General instance informations  ===== -----  PostgreSQL version  -----
Read more