1 - Slowness was because the session was blocked
-
This Friday of 09/02/2022, a treatment taking a few seconds usually lasted minutes
-
The cause of the slowness was that the queries in the treatment were blocked by the queries of other treatments
Fig 2-2 : SQL-queries in blocked and blocking sessions at 11:21 a.m. of 09/02/2022
2 - Slowness was related to the fact that there were too many treatments in parallel
-
This Monday of 09/05/2022, a SQL-query taking a few seconds usually lasted minutes
-
The cause of the slowness was that there were too many SQL-queries at the same time
Fig 2-3 : There were 62 running queries at 9:01 p.m. of 09/05/2022
Fig 2-4 : Our query and other parallel queries at 8:46 p.m. of 09/05/2022
2. Case study in resolving performance issues occurred in the past
Fig 2-1 : There were 12 blocked sessions at 11:21 a.m of 09/02/2022
3. Case study in resolving real-time performance issues (troubleshooting)
1 - Slowness is your DB-session is blocked
-
Our session does not terminate (first query in the figure)
-
The cause of the slowness is that the query in the session is blocked (for 22511 seconds already)
Fig 3-1 : Some sessions are blocked in "Activity reporting" tab
2 - Slowness is related to the fact that there are too many treatments in parallel
-
Our query is very slow (first query in the figure)
-
The cause of the slowness is that there are too many SQL-queries at the same time
Fig 3-2 : Our query and other parallel queries in "Activity reporting" tab
3 - Slowness is due to a bad query execution plan
-
Our query is very slow
-
The cause of the slowness is that the statistic of a table is obsolete
Fig 3-3 : The query is running for 9489 seconds (because the statistic for t_prm_pm_comportement_ano table is obsolete)
Fig 3-4 : The statistic for t_prm_pm_comportement_ano table was computed at 5:40 pm. of 09/22/2022 (when the table was empty)
4. Case study for configuring exploitation-scripts (in a db-host)
1 - dpm.conf for an PostgreSQL instance listening on all IP (listen_addresses="*") :​
[INST1]
gv_dbms_home="/usr/pgsql-11"
gv_dbms_version="11"
gv_config_file="/INST1/data/postgresql.conf"
gv_port="5432"
gv_login="postgres"
gv_passwd="fEFTfXlLY7JO8lXiqZ5YnQ=="
2 - dpm.conf for an PostgreSQL instance listening on a VIP :
[INST1]
gv_dbms_home="/usr/pgsql-11"
gv_dbms_version="11"
gv_config_file="/INST1/data/postgresql.conf"
gv_port="5432"
gv_login="postgres"
gv_passwd="fEFTfXlLY7JO8lXiqZ5YnQ=="
gv_virtual_host="vip1"
gv_sql_client='psql -h $gv_virtual_host -p $gv_port -U $gv_login -d postgres'
gv_dump_cmd='pg_dump -h $gv_virtual_host -p $gv_port -U $gv_login'
gv_import_cmd='pg_restore -h $gv_virtual_host -p $gv_port -U $gv_login'
3 - dpm.conf for an MySQL (or MariaDB) instance listening on all IP (bind-address=0.0.0.0) :​
[INST1]
gv_dbms_home="/opt/mysql5.7/usr"
gv_dbms_version="5.7"
gv_config_file="/etc/my.cnf"
gv_port="3306"
gv_login="root"
gv_passwd="fEFTfXlLY7JO8lXiqZ5YnQ=="
4 - dpm.conf for an MySQL (or MariaDB) instance listening on a VIP :​
[INST1]
gv_dbms_home="/opt/mysql5.7/usr"
gv_dbms_version="5.7"
gv_config_file="/etc/my.cnf"
gv_port="3306"
gv_login="root"
gv_passwd="fEFTfXlLY7JO8lXiqZ5YnQ=="
gv_virtual_host="vip1"
gv_sql_client='mysql -N -c -h $gv_virtual_host -P $gv_port -u $gv_login --password="$gv_passwd"'
gv_dump_cmd='mysqldump -h $gv_virtual_host -P $gv_port -u $gv_login --password="$gv_passwd"'
gv_backup_cmd='mysqlbackup -h $gv_virtual_host -P $gv_port --user=$gv_login --password="$gv_passwd"'