Yann Neuhaus
PostgreSQL 12: Explain will display custom settings, if instructed
How many times did you try to solve a performance issue but have not been able to reproduce the explain plan? Whatever you tried you always got a different result. Lets say you managed to get a dump of the database in question, got all the PostreSQL parameters the same, gathered statistics but still you do not manage to get the same plan as the one who reported the issue. What could be a potential issue here? Lets do a short demo:
Imagine someone is sending you this plan for a simple count(*) against pg_class:
postgres=# explain (analyze) select count(*) from pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=23.10..23.11 rows=1 width=8) (actual time=0.293..0.293 rows=1 loops=1)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.27..22.12 rows=390 width=0) (actual time=0.103..0.214 rows=390 loops=1)
Heap Fetches: 0
Planning Time: 0.155 ms
Execution Time: 0.384 ms
(5 rows)
When you try the same on your environment the plan always looks like this (sequential scan, but not an index only scan):
postgres=# explain (analyze) select count(*) from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.88..17.89 rows=1 width=8) (actual time=0.322..0.323 rows=1 loops=1)
-> Seq Scan on pg_class (cost=0.00..16.90 rows=390 width=0) (actual time=0.017..0.220 rows=390 loops=1)
Planning Time: 1.623 ms
Execution Time: 0.688 ms
(4 rows)
In this case the index only scan is even faster, but usually you get a sequential scan because costs are lower. Whatever you try, you can not reproduce it. What you can’t know: The person reporting the issue didn’t tell you about that:
postgres=# set enable_seqscan = off;
SET
postgres=# explain (analyze) select count(*) from pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=23.10..23.11 rows=1 width=8) (actual time=0.230..0.230 rows=1 loops=1)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.27..22.12 rows=390 width=0) (actual time=0.032..0.147 rows=390 loops=1)
Heap Fetches: 0
Planning Time: 0.130 ms
Execution Time: 0.281 ms
Just before executing the statement a parameter has been changed which influences PostgreSQL’s choise about the best plan. And this is where the new feature of PostgreSQL 12 becomes handy:
postgres=# explain (analyze,settings) select count(*) from pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=23.10..23.11 rows=1 width=8) (actual time=0.309..0.310 rows=1 loops=1)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.27..22.12 rows=390 width=0) (actual time=0.045..0.202 rows=390 loops=1)
Heap Fetches: 0
Settings: enable_seqscan = 'off'
Planning Time: 0.198 ms
Execution Time: 0.395 ms
(6 rows)
postgres=#
From PostgreSQL 12 on you can ask explain to display any setting that has been changed and influenced the decision on which plan to choose. This might be optimizer parameters as here, but this might also be others when they differ from the global setting:
postgres=# explain (analyze,settings) select count(*) from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.88..17.89 rows=1 width=8) (actual time=0.197..0.198 rows=1 loops=1)
-> Seq Scan on pg_class (cost=0.00..16.90 rows=390 width=0) (actual time=0.016..0.121 rows=390 loops=1)
Settings: work_mem = '64MB'
Planning Time: 0.162 ms
Execution Time: 0.418 ms
(5 rows)
… or:
postgres=# set from_collapse_limit = 13;
SET
postgres=# explain (analyze,settings) select count(*) from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.88..17.89 rows=1 width=8) (actual time=0.190..0.190 rows=1 loops=1)
-> Seq Scan on pg_class (cost=0.00..16.90 rows=390 width=0) (actual time=0.012..0.115 rows=390 loops=1)
Settings: from_collapse_limit = '13', work_mem = '64MB'
Planning Time: 0.185 ms
Execution Time: 0.263 ms
(5 rows)
Nice addition. Asking people to use the “settings” switch with analyze, you can be sure on what was changed from the global settings so it is much easier to reproduce the issue and to see what’s going on.
Parameters that do not influence the plan do not pop up:
postgres=# set log_statement='all';
SET
postgres=# explain (analyze,settings) select count(*) from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=17.88..17.89 rows=1 width=8) (actual time=0.199..0.200 rows=1 loops=1)
-> Seq Scan on pg_class (cost=0.00..16.90 rows=390 width=0) (actual time=0.018..0.124 rows=390 loops=1)
Settings: from_collapse_limit = '13', work_mem = '64MB'
Planning Time: 0.161 ms
Execution Time: 0.391 ms
(5 rows)
Cet article PostgreSQL 12: Explain will display custom settings, if instructed est apparu en premier sur Blog dbi services.
PostgreSQL 12: Copying replication slots
The concept of replication slots was introduced in PostgreSQL 9.4 and was created to prevent a primary instance to delete WAL that a replica still needs to apply. That could happen when you have a network interruption or the replica was down for another reason. With replication slots you can prevent that at the downside that your master could fill up your disk if the interruption is too long. This concept of a “physical replication slot” was then advanced so you can also create “logical replication slots” which are used in logical replication which made in into PostgreSQL 10. Now with PostgreSQL 12 being in active development another great feature made it into PostgreSQL core: Copying replication slots.
What might that be good for? Lets assume the following scenario:
- You want to attach two replicas to your master instance
- You want both replicas to use a physical replication slot
- You want to build both replicas from the same basebackup and to start at the same position
What you can do in PostgreSQL is to create base backup that will create a physical replication slot:
postgres@pgbox:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn -----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------- (0 rows) postgres@pgbox:/home/postgres/ [PGDEV] mkdir -p /var/tmp/basebackup postgres@pgbox:/home/postgres/ [PGDEV] pg_basebackup --create-slot --slot myslot --write-recovery-conf -D /var/tmp/basebackup/ postgres@pgbox:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn -----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------- myslot | | physical | | | f | f | | | | 0/2000000 | (1 row)
(Please note that there is no more recovery.conf in PostgreSQL 12 so the recovery parameters have been added to postgresql.auto.conf)
The replication slot will not be dropped after pg_basebackup finished and you can use it to attach a new replica. But before doing that: As of PostgreSQL 12 you can copy the slot and then attach a second replica to the copied slot, so both replicas will start at the same position:
postgres@pgbox:/home/postgres/ [PGDEV] psql -X -c "select pg_copy_physical_replication_slot('myslot','myslot2')" postgres
pg_copy_physical_replication_slot
-----------------------------------
(myslot2,)
(1 row)
postgres@pgbox:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
myslot | | physical | | | f | f | | | | 0/8000000 |
myslot2 | | physical | | | f | f | | | | 0/8000000 |
(2 rows)
As you can see both replication slots have the same value for “restart_lsn”. This will make it very easy to use the basebackup for the two replicas and start them from the same position:
postgres@pgbox:/home/postgres/ [PGDEV] mkdir -p /var/tmp/replica1 postgres@pgbox:/home/postgres/ [PGDEV] mkdir -p /var/tmp/replica2 postgres@pgbox:/home/postgres/ [PGDEV] cp -pr /var/tmp/basebackup/* /var/tmp/replica1/ postgres@pgbox:/home/postgres/ [PGDEV] cp -pr /var/tmp/basebackup/* /var/tmp/replica2/ postgres@pgbox:/home/postgres/ [PGDEV] sed -i 's/myslot/myslot2/g' /var/tmp/replica2/postgresql.auto.conf postgres@pgbox:/home/postgres/ [PGDEV] echo "port=8888" >> /var/tmp/replica1/postgresql.auto.conf postgres@pgbox:/home/postgres/ [PGDEV] echo "port=8889" >> /var/tmp/replica2/postgresql.auto.conf postgres@pgbox:/home/postgres/ [PGDEV] chmod o-rwx /var/tmp/replica1 postgres@pgbox:/home/postgres/ [PGDEV] chmod o-rwx /var/tmp/replica2
What happened here:
- Restore the same basebackup to the new replica locations
- Change the slot to use for the second replica to our copied slot name
- Change the ports of both replicas because we are running on the same host
- Fix the permissions so pg_ctl will not complain
That’s it. We can startup both replicas:
postgres@pgbox:/home/postgres/ [PGDEV] pg_ctl -D /var/tmp/replica1/ start postgres@pgbox:/home/postgres/ [PGDEV] pg_ctl -D /var/tmp/replica2/ start postgres@pgbox:/home/postgres/ [PGDEV] psql -X -p 8888 -c "select pg_is_in_recovery()" postgres pg_is_in_recovery ------------------- t (1 row) postgres@pgbox:/home/postgres/ [PGDEV] psql -X -p 8889 -c "select pg_is_in_recovery()" postgres pg_is_in_recovery ------------------- t (1 row)
Quite easy and we can confirm that both replicas are at the same location as previously:
postgres@pgbox:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn -----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------- myslot | | physical | | | f | t | 15622 | | | 0/9000148 | myslot2 | | physical | | | f | t | 15632 | | | 0/9000148 | (2 rows)
You can also copy logical replication slots, of course. Nice, thanks all involved.
Cet article PostgreSQL 12: Copying replication slots est apparu en premier sur Blog dbi services.
PostgreSQL 12: generated columns
PostgreSQL 12 will finally bring a feature other database systems already have for quite some time: Generated columns. What exactly is that and how does is look like in PostgreSQL? As usual, lets start with a simple test setup.
We begin with a simple table containing two columns:
postgres=# create table t1 ( a int, b text ); CREATE TABLE postgres=# insert into t1 values (1,'aaa'); INSERT 0 1 postgres=# select * from t1; a | b ---+----- 1 | aaa (1 row) postgres=#
A generated column is not a “real” column because it’s value is computed:
postgres=# alter table t1 add column c int generated always as (a*2) stored;
ALTER TABLE
postgres=# select * from t1;
a | b | c
---+-----+---
1 | aaa | 2
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
b | text | | |
c | integer | | | generated always as (a * 2) stored
The keyword “stored” means that the column is stored on disk. In a future version there will probably also be a “virtual” keyword which instructs PostgreSQL not to store the data on disk but always compute it when it is read rather then written.
What you can see here as well is, that you can refer other columns of the same table for the computation of the generated column. But this is not a requirement:
postgres=# alter table t1 add column d int generated always as (3*2) stored;
ALTER TABLE
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+------------------------------------
a | integer | | |
b | text | | |
c | integer | | | generated always as (a * 2) stored
d | integer | | | generated always as (3 * 2) stored
Referencing columns of other tables is not possible and it is not possible to reference another generated columns:
postgres=# alter table t1 add column d int generated always as (c*2) stored; ERROR: cannot use generated column "c" in column generation expression DETAIL: A generated column cannot reference another generated column.
Directly updating such a column does of course not work as well:
postgres=# update t1 set d=5; psql: ERROR: column "d" can only be updated to DEFAULT DETAIL: Column "d" is a generated column. postgres=# update t1 set d=default; UPDATE 1
What will happen when we create a generated column that uses a volatile function?
postgres=# alter table t1 add column e int generated always as (random()) stored; psql: ERROR: generation expression is not immutable postgres=#
Does not work as well. Only immutable expressions can be used here. That would work:
postgres=# alter table t1 add column e text generated always as (md5(b)) stored;
ALTER TABLE
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-------------------------------------
a | integer | | |
b | text | | |
c | integer | | | generated always as (a * 2) stored
d | integer | | | generated always as (3 * 2) stored
e | text | | | generated always as (md5(b)) stored
Nice feature. Documentation is here
Cet article PostgreSQL 12: generated columns est apparu en premier sur Blog dbi services.
RCSI with foreign keys, NULL values and paramater sniffing behavior
In this blog post let’s go back to the roots (DBA concern) with a discussion with one of my friends about a weird transaction locking issue. In fact, this discussion was specifically around two questions. The first one was why SQL Server continues to use shared locks in RCSI mode leading to blocking scenarios and the second one was about compiled objects with weird NULL value parameter sniffing behavior. This discussion was very funny for me because it included very interesting topics that we had to go through to figure out what happened in his case and I think this was enough funny to share it with you.
Let’s set the context: 2 tables (dbo.t1 and dbo.t2) in a parent-child relationship with a foreign key that allows NULL values. Transactions performed against these tables are performed in RCSI mode.
USE master; GO CREATE DATABASE test; GO -- Change default transaction isolation level to RCSI ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON; GO USE test; GO CREATE TABLE dbo.t1 ( id INT NOT NULL PRIMARY KEY, col1 CHAR(2) NOT NULL ); -- Create table t2 with FK (id_parent) that references primary key on t1 (id) CREATE TABLE dbo.t2 ( id INT NOT NULL PRIMARY KEY, id_parent INT NULL FOREIGN KEY REFERENCES dbo.t1 (id), col1 CHAR(2) NOT NULL ); GO -- Insert values in parent table t1 INSERT INTO dbo.t1 VALUES (1, 'TT');
Let’s insert 2 rows in the child table dbo.t2 in different scenarios.
The first one concerns insertion to the dbo.t2 table with a non-empty value in the FK column. The second one concerns insertion to the same table and same FK column with an empty / NULL value:
-- Insert values in child table t2 (non NULL value in FK column) INSERT INTO dbo.t2 VALUES (1, 1, 'TT'); -- Insert values in child table t2 (non NULL value in FK column) INSERT INTO dbo.t2 VALUES (2, NULL, 'TT');
And here their respective execution plans:
- Insert into dbo.t2 with a non-empty value in the FK column
In this first scenario, insert is performed by checking first any existing reference in the parent table (dbo.t1). This action is materialized by the clustered index seek operator and Nested Loop in the execution plan.
- Insert into dbo.t2 with a NULL value in the FK column
In the second scenario, there is no need to check values in the dbo.t1 parent table due to the empty value in the FK column.
In both cases, this is an expected behavior. But let’s now consider locks that are supposed to be taken in this first scenario. 2 different structures must be accessed (and locked) in different modes with an X mode lock to access and update the clustered index of the dbo.t2 table. But what about the dbo.t1 table here? The cluster index structure must be accessed as part of the FK validation. As we are running in RCSI we may suppose in a first place no shared lock (S lock) should be held by the lock manager.
Let’s configure an extended event to track locks acquired in this specific scenario:
USE test;
GO
SELECT DB_ID('test')
SELECT OBJECT_ID('dbo.t1') -- 1205579333
SELECT OBJECT_ID('dbo.t2') -- 1237579447
DROP EVENT SESSION [locks_check] ON SERVER
CREATE EVENT SESSION [locks_check]
ON SERVER
ADD EVENT sqlserver.lock_acquired
(
SET collect_resource_description=(1)
WHERE
(
[package0].[equal_uint64]([database_id],(10))
AND [package0].[not_equal_boolean]([sqlserver].[is_system],(1))
AND ([package0].[greater_than_equal_uint64]([associated_object_id],(1205579333))
OR [package0].[greater_than_equal_uint64]([associated_object_id],(1237579447))
)
AND [sqlserver].[session_id]=(54)
)
)
ADD TARGET package0.ring_buffer(SET max_memory=(65536))
WITH
(
MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF
)
GO
ALTER EVENT SESSION [locks_check]
ON SERVER STATE = START;
GO
Here the XE output generated for the first scenario:
;WITH target_data_xml
AS
(
SELECT
CAST(t.target_data AS XML) AS target_data
FROM sys.dm_xe_session_targets AS t
JOIN sys.dm_xe_sessions AS s ON t.event_session_address = s.address
WHERE s.name = 'locks_check'
),
target_data_output
AS
(
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), T.X.value('(./@timestamp)', 'DATETIME')) AS [timestamp],
T.X.value('(./data[@name="resource_type"]/text)[1]', 'sysname') AS lock_resource_type,
T.X.value('(./data[@name="mode"]/text)[1]', 'sysname') AS lock_mode,
T.X.value('(./data[@name="resource_0"]/value)[1]', 'INT') AS resource_0,
T.X.value('(./data[@name="object_id"]/value)[1]', 'BIGINT') AS [object_id],
T.X.value('(./data[@name="associated_object_id"]/value)[1]', 'BIGINT') AS [associated_object_id],
T.X.value('(./data[@name="resource_description"]/value)[1]', 'sysname') AS [resource_description]
FROM target_data_xml AS x
CROSS APPLY target_data.nodes('//event') AS T(X)
)
SELECT
t.timestamp,
t.lock_resource_type,
t.lock_mode,
CASE t.lock_resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(t.associated_object_id)
ELSE (SELECT OBJECT_NAME(p.object_ID) FROM sys.partitions AS p WHERE hobt_id = t.associated_object_id)
END AS [object_name],
t.resource_description
FROM target_data_output AS t
GO
Well, not really what we might expect in this case because a shared lock (S) was taken on the parent table (dbo.t1) despite being in RCSI mode. In fact, this behavior is expected because for 2 tables in an FK relationship SQL Server switches automatically to locking read committed (shared lock) to ensure constraints are not violated by relying on eventual stale versioned reads. In other words, you may expect to face some potential blocking issues if there exist other sessions that try to access concurrently to the parent table for modification while running in RCSI mode.
For the second scenario there is no ambiguity because only the clustered index of the dbo.t2 table is accessed to insert data accordingly to what we saw in related execution plan above:
So now let’s go through to the second weird issue (NULL value is not “sniffed” correctly by the stored procedure) and let’s wrap the ad-hoc query into a stored procedure as follows:
CREATE PROCEDURE dbo.pr_test ( @id_parent INT = NULL ) AS INSERT INTO dbo.t2 VALUES (ABS(CAST(CAST(CHECKSUM(NEWID()) AS bigint) / 1000 % 2100000000 AS int)), @id_parent, 'TT'); GO
Let’s execute the procedure without specifying a parameter. In this case a NULL value will be inserted I the FK column of dbo.t2 table.
EXEC dbo.pr_test;
The corresponding execution plan:
First of all, the plan differs from what we’ve seen in the previous example with the ad-hoc query. The plan was compiled with a NULL value and we still continue to see operators related to the FK constraint check. At the first glance this plan shape was more related to the first scenario where we inserted a non-empty value in the FK column. This is not obvious but we may notice some differences compared to the first scenario. With SQL Sentry plan explorer (v3 build 18.4.0.0) the relevant information is not displayed when you highlight the nested loop operator compared to SSMS execution plan but you may rely on the plan tree section by the adding Pass Thru column to retrieve the same information.
So, the question here is why SQL Server behaves different in this case? Well, when using a variable or parameter SQL Server needs to build a plan shape that will work correctly if reused for different values. However, the semi join operator has a pass-through predicate that skips the lookup if the runtime value is NULL (we may notice easily with SQL Sentry plan explorer that the lookup part of the plan is not used at the runtime in this case). With a constant NULL value (ad-hoc query scenario) the game changes because the optimizer is able to simplifiy the query and removes the join accordingly. In a nutshell, this an expected behavior by design and really related to a parameter sniffing issue. Thanks to @SQL_Kiwi to help clarifying this last point and thanks to my friend for this funny troubleshooting game.
See you!
Cet article RCSI with foreign keys, NULL values and paramater sniffing behavior est apparu en premier sur Blog dbi services.
PostgreSQL 12: More progress reporting
PostgreSQL 9.6 introduced a new view called pg_stat_progress_vacuum which gives information about currently running vacuum processes. That was a great addition because since then you could easily estimate on how long a specific vacuum process will need to complete. PostgreSQL 12 will use the same infrastructure to extend that to more operations.
The first operation that can now be tracked is cluster. For that a new view is available which gives the following information:
postgres=# \d pg_stat_progress_cluster
View "pg_catalog.pg_stat_progress_cluster"
Column | Type | Collation | Nullable | Default
---------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
phase | text | | |
cluster_index_relid | bigint | | |
heap_tuples_scanned | bigint | | |
heap_tuples_written | bigint | | |
heap_blks_total | bigint | | |
heap_blks_scanned | bigint | | |
index_rebuild_count | bigint | | |
As always, lets generate a sample table and some indexes so we have something cluster can work on:
postgres=# create table t1 as select a,md5(a::text) as txt, now() as date from generate_series(1,3000000) a;
SELECT 3000000
postgres=# create index i1 on t1(a);
CREATE INDEX
postgres=# create index i2 on t1(txt);
CREATE INDEX
postgres=# create index i3 on t1(date);
CREATE INDEX
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
a | integer | | |
txt | text | | |
date | timestamp with time zone | | |
Indexes:
"i1" btree (a)
"i2" btree (txt)
"i3" btree (date)
Once we cluster that table we should see the progress in pg_stat_progress_cluster, so in the first session:
postgres=# cluster verbose t1 using i1; psql: INFO: clustering "public.t1" using index scan on "i1" psql: INFO: "t1": found 0 removable, 3000000 nonremovable row versions in 28038 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.82 s, system: 0.55 s, elapsed: 1.87 s. CLUSTER postgres=#
… and in a second session:
postgres=# select * from pg_stat_progress_cluster; pid | datid | datname | relid | command | phase | cluster_index_relid | heap_tuples_scanned | heap_tuples_written | heap_blks_total | heap_blks_scanned | index_rebuild_count ------+-------+----------+-------+---------+------------------+---------------------+---------------------+---------------------+-----------------+-------------------+--------------------- 1669 | 13586 | postgres | 16384 | CLUSTER | rebuilding index | 16390 | 3000000 | 3000000 | 0 | 0 | 2 (1 row)
Nice. And the same is now available when indexes get created:
postgres=# \d pg_stat_progress_create_index
View "pg_catalog.pg_stat_progress_create_index"
Column | Type | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
phase | text | | |
lockers_total | bigint | | |
lockers_done | bigint | | |
current_locker_pid | bigint | | |
blocks_total | bigint | | |
blocks_done | bigint | | |
tuples_total | bigint | | |
tuples_done | bigint | | |
partitions_total | bigint | | |
partitions_done | bigint | | |
Cet article PostgreSQL 12: More progress reporting est apparu en premier sur Blog dbi services.
Second Meetup SOUG / DOAG /AOUG Region Lake of Constance
Tuesday last week I attended the second meeting of SOUG region lake of constance which took place at Robotron Switzerland in Wil SG. Eleven people attended this event, Georg Russ hold a quite interesting presentation about “Hacked Biometric Data”.
He told about how to fake finger prints, iris images, face ids and patterns of hand venes.
After this presentation a general discussion about security and other Oracle related themes took place.
It was a good event and I am looking forward to attend the next one.
Further information can be found at https://www.meetup.com/OracleBeerRegioBodensee/events/258060976/
Cet article Second Meetup SOUG / DOAG /AOUG Region Lake of Constance est apparu en premier sur Blog dbi services.
Cause for looping sssd
In RedHat Enterprise Linux 7, the sssd daemons can connect to active directory servers. Default behaviour is to update DNS entries dynamically.
If a statical DNS entry already exists this can lead to a CPU consuming sssd_nss daemon.
To prevent this behaviour, the dynamic DNS updates should be switched off with this setting in every doman section of config file /etc/sssd/sssd.conf:
dyndns_update = False
After that sssd should be restarted to take this change effect.
Cet article Cause for looping sssd est apparu en premier sur Blog dbi services.
The EDB filter log extension
This is another post dedicated to EnterpriseDB Postgres. Sometimes you may want to get specific messages not getting logged to the server’s logfile or audit records. That might be specific error codes or, even more important, passwords you specify when you create users. EDB comes with a solution for that by providing an extension which is called EDB Filter Log. Lets see how you can install, and even more important, how to use that extension.
The first thing I usually do when I want to check what extensions are available is looking at . I was quite surprised that this extension is not listed there:
edb=# select * from pg_available_extensions where name like '%filter%'; name | default_version | installed_version | comment ------+-----------------+-------------------+--------- (0 rows)
Anyway you can load it by adjusting the shared_preload_libraries parameter:
edb=# show shared_preload_libraries;
shared_preload_libraries
---------------------------------------------------
$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq
(1 row)
edb=# alter system set shared_preload_libraries='$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/edb_filter_log';
ALTER SYSTEM
edb=# \q
enterprisedb@edb1:/var/lib/edb/ [pg1] pg_ctl -D $PGDATA restart -m fast
enterprisedb@edb1:/var/lib/edb/ [pg1] psql edb
psql.bin (11.2.9)
Type "help" for help.
edb=# show shared_preload_libraries ;
shared_preload_libraries
--------------------------------------------------------------------------
$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/edb_filter_log
(1 row)
But even then the extension does not show up in pg_available_extensions:
edb=# select * from pg_available_extensions where name like '%filter%'; name | default_version | installed_version | comment ------+-----------------+-------------------+--------- (0 rows)
Lets assume you do not want violations on unique constraints to get logged in the server’s logfile. Usually you get this in the log file once a constraint is violated:
edb=# create table t1 ( a int );
CREATE TABLE
edb=# create unique index i1 on t1(a);
CREATE INDEX
edb=# insert into t1 values(1);
INSERT 0 1
edb=# insert into t1 values(1);
ERROR: duplicate key value violates unique constraint "i1"
DETAIL: Key (a)=(1) already exists.
edb=# select pg_current_logfile();
pg_current_logfile
-------------------------------
log/edb-2019-03-24_162021.log
(1 row)
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log
...
2019-03-24 16:35:32 CET ERROR: duplicate key value violates unique constraint "i1"
2019-03-24 16:35:32 CET DETAIL: Key (a)=(1) already exists.
...
Using the extension you can do it like this (23505 is the SQLSTATE for unique constraint violations):
edb=# show edb_filter_log.errcode; edb_filter_log.errcode ------------------------ (1 row) edb=# alter system set edb_filter_log.errcode='23505'; ALTER SYSTEM edb=# select context from pg_settings where name = 'edb_filter_log.errcode'; context --------- user (1 row) edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) edb=# show edb_filter_log.errcode; edb_filter_log.errcode ------------------------ 23505 (1 row) edb=# insert into t1 values(1); ERROR: duplicate key value violates unique constraint "i1" DETAIL: Key (a)=(1) already exists. edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log ... 2019-03-24 16:39:05 CET LOG: received SIGHUP, reloading configuration files 2019-03-24 16:39:05 CET LOG: parameter "edb_filter_log.errcode" changed to "23505" edb=#
This specific error is not any more reported in the logfile. Of course can use multiple codes for edb_filter_log.errcode by separating them with a comma. The complete list of codes is documented here.
This is for suppressing messages in the log file. What about passwords? Imagine you are logging all statements:
edb=# alter system set log_statement='all'; ALTER SYSTEM edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) edb=# show log_statement; log_statement --------------- all (1 row)
In this configuration this will be captured as well and you will find the password in the logfile:
edb=# create user u1 with login password 'password';
CREATE ROLE
edb=# select pg_current_logfile();
pg_current_logfile
-------------------------------
log/edb-2019-03-24_162021.log
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log | grep password
2019-03-24 16:46:59 CET LOG: statement: create user u1 with login password 'password';
This is what you usually do not want to see there and exactly this is what “edb_filter_log.redact_password_commands” is for:
edb=# alter system set edb_filter_log.redact_password_commands = true; ALTER SYSTEM edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) edb=# show edb_filter_log.redact_password_commands; edb_filter_log.redact_password_commands ----------------------------------------- on (1 row)
When this is set to on the plain text password will not be anymore written to the log file when you create or alter users:
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_162021.log | grep secret 2019-03-24 16:51:19 CET STATEMENT: create user u2 login with password 'secret'; 2019-03-24 16:51:28 CET LOG: statement: create user u2 with login password 'secret';
… and it is still there. A restart is required for that becoming active?:
enterprisedb@edb1:/var/lib/edb/as11/data/ [pg1] pg_ctl -D $PGDATA restart -m fast
enterprisedb@edb1:/var/lib/edb/as11/data/ [pg1] psql -X edb
psql.bin (11.2.9)
Type "help" for help.
edb=# create user u3 with login password 'topsecret';
CREATE ROLE
edb=# select pg_current_logfile();
pg_current_logfile
-------------------------------
log/edb-2019-03-24_165229.log
(1 row)
edb=# \! tail -20 $PGDATA/log/edb-2019-03-24_165229.log | grep topsecret
2019-03-24 16:54:22 CET LOG: statement: create user u3 with login password 'topsecret';
And we do still see it in the log file, why that? The issue is with the syntax. Consider this:
edb=# create user u4 with login password 'text'; CREATE ROLE edb=# create user u5 login password 'text2'; CREATE ROLE edb=# create user u6 password 'text3'; CREATE ROLE edb=#
Only the last command will replace the password in the log file:
2019-03-24 17:03:31 CET LOG: statement: create user u4 with login password 'text'; 2019-03-24 17:03:45 CET LOG: statement: create user u5 login password 'text2'; 2019-03-24 17:04:12 CET LOG: statement: create user u6 password 'x';
You have to follow exactly this syntax:
{CREATE|ALTER} {USER|ROLE|GROUP} identifier { [WITH] [ENCRYPTED]
PASSWORD 'nonempty_string_literal' | IDENTIFIED BY {
'nonempty_string_literal' | bareword } } [ REPLACE {
'nonempty_string_literal' | bareword } ]
…otherwise it will not work.
Cet article The EDB filter log extension est apparu en premier sur Blog dbi services.
Auditing with EDB Postgres Enterprise
It might be that there is a requirement to audit operations in the database. Maybe because of legal requirements, maybe because of security requirements or whatever. I’ve already written a post in the past describing what you can do in community PostgreSQL, this post is specific to EDB Postgres. The auditting features come be default in EDB Postgres and you do not need to install any extension such as pgaudit.
I am using EDB Postgres Enterprise version 11.2 for this post but it should work the same in previous versions:
enterprisedb@edb1:/var/lib/edb/ [pg1] psql -X postgres
psql.bin (11.2.9)
Type "help" for help.
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 (EnterpriseDB Advanced Server 11.2.9) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
The parameter which controls if auditing is enabled or not is “edb_audit”:
postgres=# show edb_audit; edb_audit ----------- (1 row) postgres=#
When it is not set (the default) auditing is enabled. You have two options to enable it:
- csv: Enabled autiting and will write the audit records to a csv file
- xml: Enabled autiting and will write the audit records to a csv file
Before enabling auditing you should think about where you want to store the audit files. It should be a location only the operating system user which runs EDB Postgres should have access to. You might think of $PGDATA but do you really want to have all the audit files included in every base backup you will be doing in the future? A better location is outside $PGDATA so you can keep the audit files separated. Lets go with “/var/lib/edb/audit” for the scope of this post:
postgres=# \! mkdir /var/lib/edb/audit
postgres=# \! chmod 700 /var/lib/edb/audit
postgres=# alter system set edb_audit_directory = '/var/lib/edb/audit';
ALTER SYSTEM
postgres=# alter system set edb_audit='csv';
ALTER SYSTEM
postgres=# select name,context from pg_settings where name in ('edb_audit_directory','edb_audit');
name | context
---------------------+---------
edb_audit | sighup
edb_audit_directory | sighup
(2 rows)
Both parameter changes can be made active by reloading the server, a restart is not required:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
The default file name that will be used for the audit logs is:
postgres=# show edb_audit_filename; edb_audit_filename --------------------- audit-%Y%m%d_%H%M%S (1 row)
Lets keep that as it is which is sufficient for the scope of this post. Now you need to think about what you want to audit. There are several options available:
- edb_audit_connect: Logs all connections to the instance, either successful, failed or all
- edb_audit_disconnect: The opposite of edb_audit_connect, logs all disconnections/li>
- edb_audit_statement: Here you have several options to log SQL statements such as insert,truncate, whatever, more on that later
- edb_audit_tag: When set, adds a string value to all audit log files
We start with logging connections and disconnections. When we set edb_audit_connect to all, we should see all connections to a database, no matter if successful or failed:
postgres=# alter system set edb_audit_connect = 'all'; ALTER SYSTEM postgres=# select context from pg_settings where name = 'edb_audit_connect'; context --------- sighup (1 row) postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
From now on we should have the audit information in the log file for every successful connection and every connection attempt that failed. Is it true?
postgres=# \! psql edb psql.bin (11.2.9) Type "help" for help. [local]:5444 enterprisedb@edb=# \q postgres=# \! psql -U dummy edb psql.bin: FATAL: role "dummy" does not exist postgres=#
That should have produced two lines in the latest audit file:
enterprisedb@edb1:/var/lib/edb/audit/ [pg1] pwd /var/lib/edb/audit enterprisedb@edb1:/var/lib/edb/audit/ [pg1] ls -latr total 8 drwx------. 5 enterprisedb enterprisedb 183 Mar 24 14:24 .. -rw-------. 1 enterprisedb enterprisedb 611 Mar 24 14:38 audit-20190324_143640.csv drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 . -rw-------. 1 enterprisedb enterprisedb 412 Mar 24 14:41 audit-20190324_143805.csv enterprisedb@edb1:/var/lib/edb/audit/ [pg1] cat audit-20190324_143805.csv 2019-03-24 14:40:54.683 CET,"enterprisedb","edb",1534,"[local]",5c9788e6.5fe,1,"authentication",2019-03-24 14:40:54 CET,5/133,0,AUDIT,00000,"connection authorized: user=enterprisedb database=edb",,,,,,,,,"","","" 2019-03-24 14:41:16.617 CET,"dummy","edb",1563,"[local]",5c9788fc.61b,1,"authentication",2019-03-24 14:41:16 CET,5/136,0,AUDIT,00000,"connection authorized: user=dummy database=edb",,,,,,,,,"","",""
a
As expected, we can see the successful connection request and in addition the one that failed. When we want to log disconnections as well, we can do so:
postgres=# alter system set edb_audit_disconnect = 'all'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# \! psql edb psql.bin (11.2.9) Type "help" for help. [local]:5444 enterprisedb@edb=# \q postgres=#
In the same audit file as before:
2019-03-24 14:47:42.447 CET,"enterprisedb","edb",1929,"[local]",5c978a7a.789,2,"idle",2019-03-24 14:47:38 CET,,0,AUDIT,00000,"disconnection: session time: 0:00:03.708 user=enterprisedb database=edb host=[local]",,,,,,,,,"psql.bin","",""
The duration of the session is logged as well. So far for the basic auditing features. Logging connections and disconnections is a good start but probably not enough. You might soon come to a point where you want to have more information, such as what the user was doing exactly in the database. This is where “edb_audit_statement” comes into the game. You can set it to something simple like “all inserts” or “all updates”:
postgres=# alter system set edb_audit_statement = 'insert'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# create table t1 ( a int ); CREATE TABLE postgres=# insert into t1 values(1); INSERT 0 1
Looking at the audit file:
2019-03-24 14:55:36.744 CET,,,9004,,5c977540.232c,3,,2019-03-24 13:17:04 CET,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,"","","" 2019-03-24 14:55:53.460 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,6,"idle",2019-03-24 14:13:30 CET,4/477,0,AUDIT,00000,"statement: insert into t1 values(1);",,,,,,,,,"psql.bin","INSERT",""
The insert is logged. You may also spot a potential issue here: Depending on how the statement is written the actual values (1 in this case) is written to the log. This might open another security hole if the audit files are not handled with care. You can not prevent that using prepared statements and in fact the “prepare” part is logged as well:
postgres=# prepare stmt as insert into t1 values($1); PREPARE postgres=# execute stmt(2); INSERT 0 1 postgres=# select * from t1; a --- 1 2 (2 rows)
The entries in the audit log:
2019-03-24 14:58:50.395 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,7,"idle",2019-03-24 14:13:30 CET,4/478,0,AUDIT,00000,"statement: prepare stmt as insert into t1 values($1);",,,,,,,,,"psql.bin","PREPARE","" 2019-03-24 14:59:02.952 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,8,"idle",2019-03-24 14:13:30 CET,4/479,0,AUDIT,00000,"statement: execute stmt(2);","prepare: prepare stmt as insert into t1 values($1);",,,,,,,,"psql.bin","EXECUTE",""
Although we only asked to log “inserts”, the prepare and execute statements are logged as well. If we prepare an update it is not logged (what is correct):
postgres=# prepare stmt2 as update t1 set a = $1; PREPARE postgres=# execute stmt2(2); UPDATE 5
The last line in the audit file is still this one:
2019-03-24 15:02:33.502 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,9,"idle",2019-03-24 14:13:30 CET,4/487,0,AUDIT,00000,"statement: execute stmt(5);","prepare: prepare stmt as insert into t1 values($1);",,,,,,,,"psql.bin","EXECUTE",""
The power of edb_audit_statement comes when you want to audit multiple kinds of statements but do not want to set it to “all” (this would log all the statements):
postgres=# alter system set edb_audit_statement='insert,update,delete,create table,drop view'; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
This should log all inserts, updates and deletes and in addition every create table or drop view:
postgres=# create table t2 ( a int ); CREATE TABLE postgres=# insert into t2 values(1); INSERT 0 1 postgres=# update t2 set a = 2; UPDATE 1 postgres=# delete from t2 where a = 2; DELETE 1 postgres=# truncate t2; TRUNCATE TABLE postgres=# create view v1 as select * from t2; CREATE VIEW postgres=# drop view v1; DROP VIEW
We should see entries for the insert, the update and the delete, but not for the truncate. The drop view should be logged as well:
2019-03-24 15:08:46.245 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,10,"idle",2019-03-24 14:13:30 CET,4/496,0,AUDIT,00000,"statement: create table t2 ( a int );",,,,,,,,,"psql.bin","CREATE TABLE","" 2019-03-24 15:08:59.713 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,12,"idle",2019-03-24 14:13:30 CET,4/498,0,AUDIT,00000,"statement: insert into t2 values(1);",,,,,,,,,"psql.bin","INSERT","" 2019-03-24 15:09:21.299 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,13,"idle",2019-03-24 14:13:30 CET,4/499,0,AUDIT,00000,"statement: update t2 set a = 2;",,,,,,,,,"psql.bin","UPDATE","" 2019-03-24 15:09:29.614 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,14,"idle",2019-03-24 14:13:30 CET,4/500,0,AUDIT,00000,"statement: delete from t2 where a = 2;",,,,,,,,,"psql.bin","DELETE","" 2019-03-24 15:12:51.652 CET,"enterprisedb","postgres",31899,"[local]",5c97827a.7c9b,15,"idle",2019-03-24 14:13:30 CET,4/503,0,AUDIT,00000,"statement: drop view v1;",,,,,,,,,"psql.bin","DROP VIEW",""
Fine. Using edb_audit_statement we have control of what exactly we want to log. What we did now was valid for the whole instance, can we modify auditing to a specific role? Yes, this is possible:
edb=# alter user enterprisedb set edb_audit_statement = 'truncate'; ALTER ROLE edb=# create role test; CREATE ROLE edb=# alter role test set edb_audit_statement = 'truncate'; ALTER ROLE
The same is true on the database level:
edb=# alter database edb set edb_audit_statement = 'truncate'; ALTER DATABASE
Lets do a small test and create user and then set edb_audit_statement on the user level, and reset it on the instance level:
edb=# create user u1 with login password 'u1'; CREATE ROLE edb=# alter user u1 set edb_audit_statement = 'create table'; ALTER ROLE edb=# alter system set edb_audit_statement = 'none'; ALTER SYSTEM edb=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
Create table statements from that user should now be logged:
edb=# \c edb u1 You are now connected to database "edb" as user "u1". edb=> create table t1 ( a int ); CREATE TABLE
The statement is indeed logged:
2019-03-24 15:44:19.793 CET,"u1","edb",6243,"[local]",5c9797b7.1863,1,"idle",2019-03-24 15:44:07 CET,5/30177,0,AUDIT,00000,"statement: create table t1 ( a int );",,,,,,,,,"psql.bin","CREATE TABLE",""
Does the same work for a role?
edb=> \c edb enterprisedb You are now connected to database "edb" as user "enterprisedb". edb=# create role r1; CREATE ROLE edb=# alter role r1 set edb_audit_statement = 'drop table'; ALTER ROLE edb=# grant r1 to u1; GRANT ROLE edb=# \c edb u1 You are now connected to database "edb" as user "u1". edb=> drop table t1; DROP TABLE edb=>
No, in this case the drop statement is not logged. You can set the parameter for a role, but is does not have any effect.
The last test for today: What happens when the directory we configured for the audit files is removed?
enterprisedb@edb1:/var/lib/edb/ [pg1] pwd /var/lib/edb enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local enterprisedb@edb1:/var/lib/edb/ [pg1] mv audit/ audit_org enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit_org drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local
These two inserts should generate audit records:
edb=> create table t2 ( a int ); CREATE TABLE edb=> create table t3 ( a int ); CREATE TABLE edb=>
Nothing happens, not even a log entry in the server log file. I would have at least expected to get a warning that the directory does not exist. Lets restart the instance:
enterprisedb@edb1:/var/lib/edb/as11/data/log/ [pg1] pg_ctl -D /var/lib/edb/as11/data/ restart -m fast waiting for server to shut down.... done server stopped waiting for server to start....2019-03-24 15:51:59 CET LOG: listening on IPv4 address "0.0.0.0", port 5444 2019-03-24 15:51:59 CET LOG: listening on IPv6 address "::", port 5444 2019-03-24 15:51:59 CET LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" 2019-03-24 15:51:59 CET LOG: redirecting log output to logging collector process 2019-03-24 15:51:59 CET HINT: Future log output will appear in directory "log". done server started
And again: Nothing. But the audit directory is being recreated once the server starts:
enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 enterprisedb enterprisedb 39 Mar 24 15:51 audit drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit_org drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local
Changing the permissions so that the enterprisedb user can not write anymore to that directory will prevent the server from restarting:
enterprisedb@edb1:/var/lib/edb/ [pg1] sudo chown root:root audit enterprisedb@edb1:/var/lib/edb/ [pg1] ls -l total 0 drwx------. 4 enterprisedb enterprisedb 51 Mar 24 13:16 as11 drwx------. 2 root root 39 Mar 24 15:51 audit drwx------. 2 enterprisedb enterprisedb 72 Mar 24 14:38 audit_org drwxrwxr-x. 3 enterprisedb enterprisedb 17 Mar 24 13:09 local enterprisedb@edb1:/var/lib/edb/ [pg1] pg_ctl -D /var/lib/edb/as11/data/ restart -m fast waiting for server to shut down.... done server stopped waiting for server to start....2019-03-24 15:55:44 CET LOG: listening on IPv4 address "0.0.0.0", port 5444 2019-03-24 15:55:44 CET LOG: listening on IPv6 address "::", port 5444 2019-03-24 15:55:44 CET LOG: listening on Unix socket "/tmp/.s.PGSQL.5444" 2019-03-24 15:55:44 CET FATAL: could not open log file "/var/lib/edb/audit/audit-20190324_155544.csv": Permission denied 2019-03-24 15:55:44 CET LOG: database system is shut down stopped waiting pg_ctl: could not start server Examine the log output.
Hope that helps…
Cet article Auditing with EDB Postgres Enterprise est apparu en premier sur Blog dbi services.
Windocks, SQL Server and Azure in our internal CI pipeline
During the last DevOps Day in Geneva, I presented a sample of our CI implementation related to our MSSQL DMK maintenance product. It was definitely a very good experience for me and good opportunity to get feedback from DevOps community as well.
During the session I explained our CI pipeline includes SQL Server containers both on AKS (K8s as managed service in Azure) and Windocks. I got questions from some of attendees who asked me why we are using Windocks as container solution for SQL Server on the Windows side in our specific context? As promised here some explanations in this blog post but let’s provide now the quick answer: we are using Windocks to address challenges that exist with SQL Server containers on Windows side. The long answer will follow but let’s set first the context with a high-level overview of our continuous integration pipeline architecture:
We are using a hybrid scenario where tools for development (SSDT and GitLab) are located in an on-premises dbi internal infrastructure whereas the CI pipeline runs entirely on Azure. The pipeline breaks down into two main areas including CI testing performed on different SQL Server containers that run on Windows through Windocks as well as Microsoft SQL Server containers that run on Linux and AKS. The AKS (K8s managed service in Azure) hosts SQL Server availability groups (in a beta release) and Windocks (surrounded in green in the above picture) is also part of this Azure architecture in IaaS mode within an Azure virtual machine Standard D4s v3 (4 vCPUs, 16 GB memory and 512GB of disk space). As an aside, we choose this machine size because nested virtualization is required by Windocks and cloned database feature that uses Hyper-V differencing disk capabilities in the background.
- Maintaining docker SQL Server images on Windows may be cumbersome
The DMK maintenance tool performs database maintenance tasks, basically the tasks you may find usually on SQL Server environments including database backups, database check integrity and maintenance of indexes and statistics as well. We obviously brought our added value and best practices in the tool and we provided to our customers they want to use it. The main challenge here consists in supporting a wide range of versions from 2008R2 to 2017 versions at the moment of this write-up (both on Windows and Linux obviously) and most of issues encountered with Docker images came from SQL Server docker images on Windows. First, if you refer to the Docker Hub (and new Microsoft Container Registry), there are no real official images for SQL Server versions prior 2016. Thus, maintaining such images is at your own responsibility and risk and we were not confident to go this way. However, I kept motivated and I decided to perform further tests to check the feasibility with Docker images. However, I quickly figured out that the going through a Docker native based solution will lead to some boring challenges. Indeed, having no official images from Microsoft for older versions of SQL Server, I had to build mine but I was disappointed by the image size that was too large compared to those we may found officially for Linux – more than 10GB for a SQL Server docker image on Windows versus ~ 1.4GB on Linux.
SQL Server Docker image size on Windows after building the custom image
The total size includes the SQL Server binaries but event if we exclude it from the calculation the final size leads to the same conclusion.
SQL Server image size on Linux
In addition, building a basic image of SQL Server on Windows remains a boring stuff and may be time consuming to be honest because you need to write some pieces of code to install optional prerequisites, SQL Server itself meaning you have first to copy binaries (CUs or / and SPs according the version) and then run the command file to install it. A lot of work and no real added values (and no warranties) at the end. That is definitely at the opposite of what I may expect as part of a DevOps process when I want to be fast and to use simply a SQL Server docker based image. Indeed, in this case, I would like to just pick up the right docker image version and corresponding tag and then to focus on my work.
Windocks fills the gap that exists with older versions (and probably new ones) of SQL Server on Windows by providing a different way to create based images compared to the docker-native solution. The first step consists in installing SQL Server instances as we would in a traditional approach. Then the interesting point is that these instances will serve as based images when spinning up containers. This new approach provides several advantages we get through but here I would like to point out the ability to apply configuration settings directly at the SQL Server instance level that will be propagated automatically to new created containers. From my point of view, it is an interesting way to apply segregation of duties without compromising the architecture’s agility. DBAs (Ops) may still work on providing a well configured template from an architecture point of view whereas developers will focus on their work but both will interact with the same tool.
- Storage concern may exist even on DEV environment
Usually in DEV environment storage is not a big concern for DBAs. From my experience, they usually provide to developers a shared environment with different SQL Server instances and application databases as well. Most of time developers get often high privileges on those environments as – db_owner or sysadmin according to the context – because it is about a DEV environment after all and DBAs apply often a dirty fix to make these environments more “agile”. But this approach implies installing a static environment that is in fact not as flexible as we may think for developers. For instance, how to reinitialize an environment for a specific developer without impacting the work of other ones? The ideal context would be each developer is able to create quickly an isolated and ephemeral environment on-demand. But in turn this new approach comes with its new challenges: Indeed, how to deal with the total disk space consumption in this case? Let’s say each developer wants to spin up a new SQL Server container environment, then the total storage footprint would include the SQL Server docker image and the space consumed by the user databases as well, right? Let’s take a real customer example who wants to provide fresh data from production databases every week to the developers (after applying sanitized data scripts or not). This is a common scenario by the way and let’s say the final storage size of databases is roughly 500GB for this customer. Adding ~ 20 developers in the game, I ‘m pretty sure you already guessed the potential storage concern which may result here if all developers want to spin up their own environment in the same time. Let’s do a quick math: 20 [developers] x (10GB [Docker image size] + 500GB [user databases] ~= 10 TB.
Going back to my specific context (our DMK maintenance tool) the storage footprint is not so exacerbated because we could be up to 7 developers at the same time with a total storage footprint of 770GB (10GB for the Docker image + 100GB of user databases). It remains too much for us even if we have provisioned 512GB of premium SSD and we can increase it in an easy way … Storage has also a cost on Azure right? Furthermore, we know that for each developer the ratio between the payload disk space and real consumed disk space is low for the most part of developed features. We need to find a way to improve this ratio and Windocks provides a simple way to address it by providing Hyper-V differencing disk capabilities directly integrated with containerization.
- Security
How to secure our environment was a question that came at the end of our CI implementation. As many DevOps projects security is usually not at the heart of first concern but moving to the cloud helped to consider security as an important topic in our architecture.
First, we need to ensure images used by our team are secure. Insecure images are part of new issues that come with container environments and image checking process requires a more complex infrastructure with often EE capabilities and extra components on the container side (at least in the case you don’t want to put your images on a public repository. Using a private registry on Azure is another option but after some investigations we were in favor of Windocks capabilities in our context. Windocks goes through a different approach to create SQL Server images by using SQL Server native instance installation as based template rather than relying on a Docker native images and on potential docker registry. The built-in approach to prevent compromising the container infrastructure with potential malicious code without further complexifying the architecture was a good argument for us because it can help DBAs to keep security concerns under control here.
Then Windocks provides other features that help us securing the container environment in an easy way with basic authentication to prevent an unauthorized user to spin up a Windocks container for instance. The native support of Windows authentication was another good argument because it simplified the security management of admin users. We are using a mix of Windows sysadmin accounts and SQL Server logins for applications.
In the bottom line, as a small DEV team we are really satisfied with Windocks that was able to address challenges we faced on the operational side. But it is worth noting that our needs and challenges are closed to what we may see with some of our customers, but in a different order of magnitude, when SQL Server is introducing in their CI/CD pipeline. In our context, we are running standard edition of Windocks but EE capabilities are also available that are more suitable with enterprise-class environments.
See you
Cet article Windocks, SQL Server and Azure in our internal CI pipeline est apparu en premier sur Blog dbi services.
Using operating system users to connect to PostgreSQL
PostgreSQL supports many authentication methods by default and one of them is Ident authentication. Using that method you can use the users defined in the operating system and map them to users in PostgreSQL. So how does that work?
To start, lets create a new operating system user we want to use for connecting to the database:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo groupadd user1 postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo useradd -g user1 -m user1
The next step is to create a so called user name map. A user map contains the name of the map, the operating system user and the user in PostgreSQL:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] echo "my-map user1 user1" >> $PGDATA/pg_ident.conf postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] tail -5 $PGDATA/pg_ident.conf # Put your actual configuration here # ---------------------------------- # MAPNAME SYSTEM-USERNAME PG-USERNAME my-map user1 user1
In our case the name of the PostgreSQL user and the name of the operating system user is the same. You might well map the operating system to another user in PostgreSQL, e.g. user2.
Obviously our user needs to exist in PostgreSQL, so:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create user user1 with login" postgres CREATE ROLE
Finally we need to add an entry to pg_hba.conf that matches our map and authentication method:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] echo "host all all 192.168.22.0/24 ident map=my-map" >> $PGDATA/pg_hba.conf postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] pg_ctl -D $PGDATA reload server signaled
Lets try to connect to the database with our new user:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo su - user1 [user1@pgbox ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -h 192.168.22.99 -p 5433 -U user1 postgres psql: FATAL: Ident authentication failed for user "user1"
… and that fails. When we check the PostgreSQL log file this is reported:
2019-03-19 18:33:26.724 CET - 1 - 8174 - 192.168.22.99 - user1@postgres LOG: could not connect to Ident server at address "192.168.22.99", port 113: Connection refused 2019-03-19 18:33:26.724 CET - 2 - 8174 - 192.168.22.99 - user1@postgres FATAL: Ident authentication failed for user "user1" 2019-03-19 18:33:26.724 CET - 3 - 8174 - 192.168.22.99 - user1@postgres DETAIL: Connection matched pg_hba.conf line 94: "host all all 192.168.22.0/24 ident map=my-map"
Our entry in pg_hba.conf matches, at least that is fine. But PostgreSQL is not able to connect to the Ident server and this confirms that nothing is listening on that port:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo netstat -tulpen | grep 113
I am running CentOS 7 so the procedure for installing and starting an ident server is this:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo yum search oident Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: pkg.adfinis-sygroup.ch * epel: pkg.adfinis-sygroup.ch * extras: mirror1.hs-esslingen.de * updates: mirror.softaculous.com =============================================================================================== N/S matched: oident =============================================================================================== oidentd.x86_64 : Implementation of the RFC1413 identification server Name and summary matches only, use "search all" for everything. postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo yum install oidentd postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] systemctl list-unit-files | grep -i ident oidentd.service disabled postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo systemctl enable oidentd.service Created symlink from /etc/systemd/system/multi-user.target.wants/oidentd.service to /usr/lib/systemd/system/oidentd.service. postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo systemctl start oidentd.service postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo netstat -tulpen | grep 113 tcp 0 0 0.0.0.0:113 0.0.0.0:* LISTEN 0 48553 8978/oidentd
Lets try again:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo su - user1 Last login: Tue Mar 19 18:33:25 CET 2019 on pts/1 [user1@pgbox ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -h 192.168.22.99 -p 5433 -U user1 postgres psql (12devel) Type "help" for help. postgres=>
… and now it works. We can connect using the operating system without specifying a password. To complete this post lets create another operating system user and map it to a different account in PostgreSQL:
postgres@pgbox:/home/postgres/ [PGDEV] sudo groupadd user2 postgres@pgbox:/home/postgres/ [PGDEV] sudo useradd -g user2 -m user2 postgres@pgbox:/home/postgres/ [PGDEV] echo "my-map user2 user1" >> $PGDATA/pg_ident.conf postgres@pgbox:/home/postgres/ [PGDEV] tail $PGDATA/pg_ident.conf # a SIGHUP signal. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect. You can # use "pg_ctl reload" to do that. # Put your actual configuration here # ---------------------------------- # MAPNAME SYSTEM-USERNAME PG-USERNAME my-map user1 user1 my-map user2 user1 postgres@pgbox:/home/postgres/ [PGDEV] pg_ctl -D $PGDATA reload server signaled
user2 should now be able to connect to user1 in PostgreSQL as well:
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] sudo su - user2 Last login: Tue Mar 19 18:55:06 CET 2019 on pts/1 [user2@pgbox ~]$ /u01/app/postgres/product/DEV/db_1/bin/psql -h 192.168.22.99 -p 5433 -U user1 postgres psql (12devel) Type "help" for help. postgres=>
Finally, be careful with this authentication method. The documentation is very clear about that: “The drawback of this procedure is that it depends on the integrity of the client: if the client machine is untrusted or compromised, an attacker could run just about any program on port 113 and return any user name they choose. This authentication method is therefore only appropriate for closed networks where each client machine is under tight control and where the database and system administrators operate in close contact. In other words, you must trust the machine running the ident server. Heed the warning: The Identification Protocol is not intended as an authorization or access control protocol.”
Cet article Using operating system users to connect to PostgreSQL est apparu en premier sur Blog dbi services.
How To Push An Image Into Amazon ECR With Docker
Please bear in mind that Amazon elastic container registry (ECR) is a managed AWS Docker registry service. In this topic, we will use the Docker CLI to push an CentOS image into Amazon ECR.
1. Install Docker desktop for Windows and AWS CLIVerify and confirm that each version has been installed properly (see below):
- docker (dash dash)version
- aws (dash dash)version
Open Power Shell interface with administration privileges and enter the following commands:
- aws configure
- Access key: ****
- Secret key: ****
The region name and output format information are not mandatory.
The data above can be found from the IAM service on AWS console management.
Use the get-login command to log in to AWS elastic container registry and save it to a text file (see below):
- aws ecr get-login (dash dash)region eu-west-3 > text.txt
Replace the aws account id provided into the text file saved previously and specify the password:
- docker login -u AWS https://aws_account_id.dkr.ecr.eu-west-3.amazonaws.com
- Password: *****
Use the pull command to download the CentOs image:
- docker pull centos:6.6
- aws ecr create-repository (dash dash)repository-name centos
The repository has been created successfully into Amazon Elastic Container Registry (see below):
Before proceeding to the next step, make sure that the following requirements are met:
- Docker version must be greater or equal to 1.7
- The repository is created and that the user has sufficient privileges to access it
- The Docker authentication is successful
- docker images
- docker tag centos:6.6 aws_account_id.dkr.ecr.eu-west-3.amazonaws.com/centos:6.6 (replace the aws_account_id by your account id)
Verify that the image has been tagged:
- docker images
Use the push command to move the centos image into Amazon elastic container registry:
- docker push aws_account_id.dkr.ecr.eu-west-3.amazonaws.com/centos:6.6 (replace the aws_account_id by your account id)
From the Amazon management console, verify that the image has been pushed properly into Amazon elastic container registry (see below):
If you are in a test environment, to avoid extra costs, make sure to delete the image and the repository from Amazon elastic container registry.
Use the following command to delete the image:
- aws ecr batch-delete-image (dash dash)repository-name centos (dash dash)image-ids imageTag=6.6
Use the following command to delete the repository:
- aws ecr delete-repository (dash dash)repository-name centos
Need further details about Docker basics for Amazon ECR, click here.
Cet article How To Push An Image Into Amazon ECR With Docker est apparu en premier sur Blog dbi services.
When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified
Below, I will try to explain, a particular case for the general error : ORA-01555 snapshot too old error..
Normally, when we have this error, we are trying to adapt the retention parameters or to tune our queries.
SQL> show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 3600 --extended from 900, undo_tablespace string UNDOTBS1
But, are some scenarios where the above rule is not working .
We got from the alert log file of the DB the sql id which caused the issue : pmrbk5fdfd665
But, when you want to search for it, in V$SQL/V$SQLAREA is not there
SQL> select sql_fulltext from v$sql where sql_id like '%pmrbk5fdfd665%'; no rows selected
Why?
Seems that, sql_id is present in V$OPEN_CURSOR, with an input into the sqltext column.
The issue is coming from the fact that the statement is accessing a LOB column, which causes to Oracle to generate a new sql id.
The execution part related to the LOBs will not appear into V$SQL/V$SQLAREA and is not captured into the AWR reports.
SQL> select distinct * from v$open_cursor 2 where rownum < 25 3 and sql_id like '%pmrbk5fdfd665%'; SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_ID SQL_TEXT LAST_SQL SQL_EXEC_ID CURSOR_TYPE ---------------- ---------- ------------------------------ ---------------- ---------- ------------- ------------------------------------------------------------ -------- ----------- --------------- 0000000670A19780 74 my_user 00000002EB91F1F0 3831220380 pmrbk5fdfd665 table_104_11_XYZT_0_0_0 00000006747F0478 131 my_user 00000002EB91F1F0 3831220380 pmrbk5fdfd665 table_104_11_XYZT_0_0_0
Apparently, the string into the sql_text column is a HEX representation of the object_id that is being accessed.
In our case is :XYZT
SQL> select owner, object_name, object_type
2 from dba_objects
3 where object_id = (select to_number('&hex_value','XXXXXX') from dual);
Enter value for hex_value: XYZT
old 3: where object_id = (select to_number('&hex_value','XXXXXX') from dual)
new 3: where object_id = (select to_number('XYZT','XXXXXX') from dual)
OWNER OBJECT_TYPE OBJECT_NAME
---------------------- --------------------------------------------------------------------------
my_user TABLE my_table
SQL> desc my_user.my_table;
Name Type
------------------- ----------------
EXPERIMENT_ID VARCHAR2(20)
DOCUMENT BLOB
............….
If we are looking for the retention on the ” DOCUMENT ” column, we will see :
SQL> select table_name, pctversion, retention,segment_name from dba_lobs where table_name in ('my_table');
TABLE_NAME
PCTVERSION RETENTION SEGMENT_NAME
---------------------------------------------------------------------------------------- ------------------------------------
my_table 900 SYS_LOB0000027039C00002$$
In order to fix it , try to run this column to adapt the retention of the blob column, related to the new value of the UNDO_RETENTION parameter,
ALTER TABLE my_table MODIFY LOB (DOCUMENT) (3600);
Cet article When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified est apparu en premier sur Blog dbi services.
Documentum : Dctm job locked after docbase installation
A correct configuration of Documentum jobs is paramount, that’s why it is the first thing we do after the docbase installation.
A few days ago, I configured the jobs on a new docbase using DQL, and I got an error because a job is locked by the user dmadmin.
The error message was:
DQL> UPDATE dm_job OBJECTS SET target_server=' ' WHERE target_server!=' ' ; ... [DM_QUERY_F_UP_SAVE]fatal: "UPDATE: An error has occurred during a save operation." [DM_SYSOBJECT_E_LOCKED]error: "The operation on dm_FTQBS_WEEKLY sysobject was unsuccessful because it is locked by user dmadmin."
I checked the status of this job:
API> ?,c,select r_object_id from dm_job where object_name ='dm_FTQBS_WEEKLY'; r_object_id ---------------- 0812D68780000ca6 (1 row affected) API> dump,c,0812D68780000ca6 ... USER ATTRIBUTES object_name : dm_FTQBS_WEEKLY title : subject : qbs weekly job ... start_date : 2/28/2019 05:21:15 expiration_date : 2/28/2027 23:00:00 ... is_inactive : T inactivate_after_failure : F ... run_now : T ... SYSTEM ATTRIBUTES r_object_type : dm_job r_creation_date : 2/28/2019 05:21:15 r_modify_date : 2/28/2019 05:24:48 r_modifier : dmadmin ... r_lock_owner : dmadmin r_lock_date : 2/28/2019 05:24:48 ... APPLICATION ATTRIBUTES ... a_status : a_is_hidden : F ... a_next_invocation : 3/7/2019 05:21:15 INTERNAL ATTRIBUTES i_is_deleted : F ...
The job was locked 3 minutes after the creation date… And still locked since (4 days).
Let’s check job logs:
[dmadmin@CONTENT_SERVER1 ~]$ ls -rtl $DOCUMENTUM/dba/log/repository1/agentexec/*0812D68780000ca6* -rw-r--r--. 1 dmadmin dmadmin 0 Feb 28 05:24 /app/dctm/server/dba/log/repository1/agentexec/job_0812D68780000ca6.lck -rw-rw-rw-. 1 dmadmin dmadmin 695 Feb 28 05:24 /app/dctm/server/dba/log/repository1/agentexec/job_0812D68780000ca6 [dmadmin@CONTENT_SERVER1 ~]$ [dmadmin@CONTENT_SERVER1 ~]$ cat /app/dctm/server/dba/log/repository1/agentexec/job_0812D68780000ca6 Thu Feb 28 05:24:50 2019 [ERROR] [LAUNCHER 20749] Detected while preparing job ? for execution: Command Failed: connect,repository1.repository1,dmadmin,'',,,try_native_first, status: 0, with error message [DM_DOCBROKER_E_NO_SERVERS_FOR_DOCBASE]error: "The DocBroker running on host (CONTENT_SERVER1:1489) does not know of a server for the specified docbase (repository1)" ...NO HEADER (RECURSION) No session id for current job. Thu Feb 28 05:24:50 2019 [FATAL ERROR] [LAUNCHER 20749] Detected while preparing job ? for execution: Command Failed: connect,repository1.repository1,dmadmin,'',,,try_native_first, status: 0, with error message . ..NO HEADER (RECURSION) No session id for current job.
I noted three important information here:
1. The DocBroker consider that the docbase is stopped when the AgentExec sent the request.
2. The timestamp corresponds to the installation date of the docbase.
3. LAUNCHER 20749.
I checked the install logs to confirm the first point:
[dmadmin@CONTENT_SERVER1 ~]$ egrep " The installer will s.*. repository1" $DOCUMENTUM/product/7.3/install/logs/install.log* /app/dctm/server/product/7.3/install/logs/install.log.2019.2.28.8.7.22:05:03:24,757 INFO [main] - The installer will start component process for repository1. /app/dctm/server/product/7.3/install/logs/install.log.2019.2.28.8.7.22:05:24:39,588 INFO [main] - The installer will stop component process for repository1. /app/dctm/server/product/7.3/install/logs/install.log.2019.2.28.8.7.22:05:26:49,110 INFO [main] - The installer will start component process for repository1.
The AgentExec logs:
[dmadmin@CONTENT_SERVER1 ~]$ ls -rtl $DOCUMENTUM/dba/log/repository1/agentexec/*agentexec.log* -rw-rw-rw-. 1 dmadmin dmadmin 640 Feb 28 05:24 agentexec.log.save.02.28.19.05.27.54 -rw-rw-rw-. 1 dmadmin dmadmin 384 Feb 28 05:36 agentexec.log.save.02.28.19.05.42.26 -rw-r-----. 1 dmadmin dmadmin 0 Feb 28 05:42 agentexec.log.save.02.28.19.09.51.24 ... -rw-r-----. 1 dmadmin dmadmin 569463 Mar 8 09:11 agentexec.log [dmadmin@CONTENT_SERVER1 ~]$ [dmadmin@CONTENT_SERVER1 ~]$ cat $DOCUMENTUM/dba/log/repository1/agentexec/agentexec.log.save.02.28.19.05.27.54 Thu Feb 28 05:17:48 2019 [INFORMATION] [LAUNCHER 19584] Detected during program initialization: Version: 7.3.0050.0039 Linux64 Thu Feb 28 05:22:19 2019 [INFORMATION] [LAUNCHER 20191] Detected during program initialization: Version: 7.3.0050.0039 Linux64 Thu Feb 28 05:22:49 2019 [INFORMATION] [LAUNCHER 20253] Detected during program initialization: Version: 7.3.0050.0039 Linux64 Thu Feb 28 05:24:19 2019 [INFORMATION] [LAUNCHER 20555] Detected during program initialization: Version: 7.3.0050.0039 Linux64 Thu Feb 28 05:24:49 2019 [INFORMATION] [LAUNCHER 20749] Detected during program initialization: Version: 7.3.0050.0039 Linux64
I found here the LAUNCHER 20749 noted above 
So, this job corresponds to the last job executed by the AgentExec before being stopped.
The AgentExec was up, the Docbase should be up also, but the DocBroker said that the docbase is down 
Now, the question is : when execatly the DocBroker was informed that the docbase is shut down?
[dmadmin@CONTENT_SERVER1 ~]$ cat $DOCUMENTUM/dba/log/repository1.log.save.02.28.2019.05.26.49 ... 2019-02-28T05:24:48.644873 20744[20744] 0112D68780000003 [DM_DOCBROKER_I_PROJECTING]info: "Sending information to Docbroker located on host (CONTENT_SERVER1) with port (1489). Information: (Config(repository1), Proximity(1), Status(Server shut down by user (dmadmin)), Dormancy Status(Active))."
To recapitulate:
– 05:24:48.644873 : Docbase shut down and DocBroker informed
– 05:24:49 : AgentExec sent request to DocBroker
So, we can say that the AgentExec was still alive after the docbase stop!
Now, to resolve the issue it is easy 
API> unlock,c,0812D68780000ca6 ... OK
I didn’t find in the logs when exactly the docbase stop the AgentExec, I guess the docbase request the stop (kill) but don’t check if it has been really stopped.
I confess that I encounter this error many times after docbase installation, that’s why it is useful to know why and how to resolve it quickly. I advise you to configure Dctm jobs after each installation, at least check if the r_lock_date is set and if it is justified.
Cet article Documentum : Dctm job locked after docbase installation est apparu en premier sur Blog dbi services.
OpenText Enterprise World Europe 2019 – Day 3
Last but not least, today was mainly dedicated to demos and customer cases. It started with the global stream presenting some OpenText applications like Core for Quality: An application developed with AppWorks and integrated to Magellan. It was meant to manage quality issues and connected to Documentum in order to link issues with SOP documents.
In the different demos we saw the integration of these SaaS applications in OT2 and their responsiveness (drag and drop from desktop, loading time, easy accessibility to other OT2 applications and so on).
OT2We went to an OT2 specific session to get more info in this new way of bringing services and business to customers (and developers).
OT2 is a platform of services, it can be really interesting for companies to avoid IT management on site and to deport the infrastructure management inside OT2 at OpenText charge: security, maintenance, updates and patches aso.
The main purpose is “A2A”, means Any to Any or Anywhere, anytime. OT2 hosted applications can be accessed from anywhere because it’s a public cloud. As it’s hosted by OpenText, you should expect almost no downtime, applications up to dates and most important: Security.
Core is another main feature of OpenText. It’s a secure way to share content with people outside of the company’s organization like external partners, customers (documentation sharing). The content can be edited by external people as it will be synced with your application (or backend) at all time. We saw how easy it was to share the content based on rules or just selection inside the application, and everything is taken care of by the product.
Federated Compliance will also come as a service, allowing you to track data and usage of your applications. An easy way to keep an eye on the status of your infra.
Some other products where mentioned as SAP Archive Server to be brought to the cloud with the help of OpenText but we won’t focus on that point. The developers are really guided and escorted through Smart View application development and directly integrated to OT2. With this, OpenText is counting on Devs to enhance the panel of available solutions in OT2.
Documentum StoriesDuring the day we had the opportunity to discover some success stories from some of the Documentum customers.
WiesbadenWiesbaden is a city in Germany where they came across an issue of organization in the administration sector.
In this sector it’s difficult to make changes due to a recusant vision of changing habits. Dr. Thomas Ortseifen, who was presenting, told us that the administration was not well organized and each part of it was “living alone” in its proper ecosystem.
Hence, the city decided to put their trust in OpenText to bring coherence in this organization. The solution proposed by OpenText was to setup a centralized DMS (Documentum) in the middle of a SOA architecture allowing flexibility and possibility to use APIs to increase the scalability of new applications.
Here are the benefits of this solution:
- Enhanced information flow
- Faster, continous availability
- Less transporting times
- Enhanced usage of existing database
- Enhanced processes
- Cross-functional complex search and analysis options
- Reduced costs for information creation
- Reduced costs for information management
- Reduced costs for space required
Alstom is a french company managing transport solutions like Tram, metro, digital mobility, maintenance, modernisation, rails infrastructure and so on.
ACOMIS stands for Alstom COntent Management Information System. At first it was setup on premise with several Web Tops and docbases.
Alstom decided to create an ACOMIS V1 in order to merge all docbases and centralize the business. To achieve this, with help of OpenText, they migrated millions of documents and merge everything to D2 and one docbase, all of this in a private cloud, letting the on premise behind.
Added business value:
- Replacing webtop with D2 for better user experience
- ACOMIS operated by OpenText specialists
- One single repo for cross project searches
There was some new requirements then, and some performance issues. The need of GDPR compliance and new 3D standard format. In order to gain these features Alstrom decided to move to a V2. So they moved to the public cloud, still managed by OpenText in order to solve the perf issue (network lags). They used Brava! in order to view 3D objects in HTML 5 interface.
Added business value:
- Public cloud for perfs and external access
- GDPR compliance
- Security managed by OpenText
- Version 16.4 with Brava! integration for 3D viewer
The OpenText World in vienna is now closed. We met a lot of people and experts. We clearly see the trend of service and centralization from OpenText. We are exited to see where it is going.
Cet article OpenText Enterprise World Europe 2019 – Day 3 est apparu en premier sur Blog dbi services.
OpenText Enterprise World Europe 2019 – Day 2
Day 2 of OTEW, we followed the global stream this morning which was taking most of the points from yesterday. But we had the pleasure to have a session from Dr. Michio Kaku, Theoretical Physicist, Futurist and popularizer of science. He wrote several books about physics and how he sees the future.
He sees us in the next 20 years ultra connected with internet lenses, the Moore’s law will collapse in 2025 where it will probably be replaced by Graphene technology (instead of basic transistors), which will, in an unknown perspective, be replaced by Quantum calculation machines (q-bits instead of bits). The main issue with quantum calculation is that q-bit are really disrupted by noises and electromagnetic waves (decoherence). According to him, internet will be replaced by brain net thanks to biological new technologies focusing on sensations instead of visualization.
What’s new and what’s next for OpenText DocumentumWe were totally waiting for this session as we, documentum experts, were exited to see the future of this well spread technology. Micah Byrd, Director Product Management at OpenText started to talk about the generic integration roadmap with “Content in Context”, “Cloud”, “LoB and industry” and “Intelligent automation” and how Documentum interprets these guidelines.
Documentum will be more and more integrated to Office 365 thanks to the new UI Smart View. A Coherent solution across all platforms which allows easy and seamless fusion into leading applications like Word and SAP. This is content in context.
OpenText is aggressively pushing Documentum to the cloud since several years with custom solutions like private, managed or public cloud. With Private you keep your data on your data center (2014-2016). With Managed your data goes to OpenText cloud (2017-2018). With Public your data goes where you want on different cloud providers like AWS, Azure, Google and so on (2019). OpenText invests on containerization as well with Docker and Kubernetes for “Documentum from Everywhere”.
Documentum future innovationsAs part of the main new features we have the continous integration of Documentum in Office 365 which already supports Word and SAP and soon (EP7 in October) Excel, Power Point and Outlook. It means that you’ll be able to access Documentum data from Office softwares. In addition OpenText wants to enable Bi-Directional synchronization between Documentum and Core, implying possibilities of interrecting with content outside of the corporate network. Hence, the content will be synced no matter where, no matter when, in a secure and controlled way.
Next to come is also improved content creation experience in D2 thanks to more integration of Brava! for annotation sharing as well as more collaborative capabilities with Share point (improvement of DC4SP).
A new vision of security:
D2 on mobile will come soon on IOS and Android, developed in AppWorks:
We are particularly exited about a prototype which was presented today: the Documentum Security Dashboard. It gives a quick and easy view of user activities and tracks the content usage like views and downloads and can demonstrate trends about content evolution. We hope it will be released one day.
Many more topics around Documentum components where presented but we will not provide details here about it, we were only focusing on main features.
Documentum D2 DemoWe had a chance to put our hands on the new D2 Smart View which brings reactivity and modernity. Our feeling about it is: SMOOTH.
Another amazing day at the OTEW where we met a lot of expert people and attended interesting sessions about the huge OpenText world.
Cet article OpenText Enterprise World Europe 2019 – Day 2 est apparu en premier sur Blog dbi services.
Upgrading SQL Server pods on K8s and helm charts
It has been while since my last blog. Today it is about continuing with helm charts and how to upgrade / downgrade SQL Server containers to a specific cumulative update. My first write-up in my to-do list.
Last year, I wrote an introduction of SQL Server containers on K8s. I remembered to face some issues when testing upgrade scenarios (probably a lack of knowledge). Since then, I have discovered helm charts and I use them intensively with my environments and they also provide upgrade / rollback capabilities.
So, the question is how to upgrade an existing SQL Server container to a new cumulative update with a helm chart?
First of all, during deployment you need to specify a strategy type. There are several strategy types and most of them address upgrade scenarios with stateless applications (ramped, blue/green, canary and a/b testing). Unfortunately, with stateful applications like SGBDRs the story is not the same because persistent storage cannot be accessed by several at time. In this case K8s must first stop and remove the current pod and then spin up a new pod with the new version. “recreate” strategy type is designed to carry out this task and to address SQL Server pod upgrade scenarios.
My deployment file is as follow:
apiVersion: apps/v1beta2
kind: Deployment
metadata:
name: {{ template "mssql.fullname" . }}
labels:
app: {{ template "mssql.name" . }}
chart: {{ .Chart.Name }}-{{ .Chart.Version | replace "+" "_" }}
release: {{ .Release.Name }}
heritage: {{ .Release.Service }}
{{- if .Values.deployment.annotations }}
annotations:
{{ toYaml .Values.deployment.annotations | indent 4 }}
{{- end }}
spec:
replicas: {{ .Values.replicaCount }}
strategy:
type: Recreate
selector:
matchLabels:
app: {{ template "mssql.name" . }}
release: {{ .Release.Name }}
template:
metadata:
labels:
app: {{ template "mssql.name" . }}
release: {{ .Release.Name }}
spec:
containers:
- name: {{ .Chart.Name }}
image: "{{ .Values.image.repository }}:{{ .Values.image.tag }}"
imagePullPolicy: {{ .Values.image.pullPolicy }}
env:
- name: ACCEPT_EULA
value: "{{ .Values.acceptEula.value | upper }}"
- name: MSSQL_PID
value: "{{ .Values.edition.value }}"
- name: MSSQL_SA_PASSWORD
valueFrom:
secretKeyRef:
name: {{ template "mssql.fullname" . }}-sa-secret
key: sapassword
- name: MSSQL_TCP_PORT
value: "{{ .Values.service.port.value }}"
- name: MSSQL_LCID
value: "{{ .Values.lcid.value }}"
- name: MSSQL_COLLATION
value: "{{ .Values.collation.value }}"
- name: MSSQL_ENABLE_HADR
value: "{{ .Values.hadr.value }}"
{{ if .Values.resources.limits.memory }}
- name: MSSQL_MEMORY_LIMIT_MB
valueFrom:
resourceFieldRef:
resource: limits.memory
divisor: 1Mi
{{ end }}
ports:
- name: mssql
containerPort: {{ .Values.service.port.value }}
volumeMounts:
- name: data
mountPath: /var/opt/mssql/data
livenessProbe:
tcpSocket:
port: mssql
initialDelaySeconds: {{ .Values.livenessprobe.initialDelaySeconds }}
periodSeconds: {{ .Values.livenessprobe.periodSeconds }}
readinessProbe:
tcpSocket:
port: mssql
initialDelaySeconds: {{ .Values.readinessprobe.initialDelaySeconds }}
periodSeconds: {{ .Values.readinessprobe.periodSeconds }}
resources:
{{ toYaml .Values.resources | indent 12 }}
{{- if .Values.nodeSelector }}
nodeSelector:
{{ toYaml .Values.nodeSelector | indent 8 }}
{{- end }}
volumes:
- name: data
{{- if .Values.persistence.enabled }}
persistentVolumeClaim:
{{- if .Values.persistence.existingDataClaim }}
claimName: {{ .Values.persistence.existingDataClaim }}
{{- else }}
claimName: {{ template "mssql.fullname" . }}-data
{{- end -}}
{{- else }}
emptyDir: {}
{{- end }}
My default values (in values.yaml) are the following:
# General parameters
acceptEula:
value: "Y"
edition:
value: "Developer"
collation:
value: SQL_Latin1_General_CP1_CI_AS
lcid:
value: 1033
hadr:
value: 0
# User parameters
sapassword:
value: Password1
# Image parameters
image:
repository: mcr.microsoft.com/mssql/server
tag: 2017-CU12-ubuntu
pullPolicy: IfNotPresent
# Service parameters
service:
type:
value: LoadBalancer
port:
value: 1460
annotations: {}
deployment:
annotations: {}
# Volumes & persistence parameters
persistence:
enabled: true
storageClass: ""
dataAccessMode: ReadWriteOnce
dataSize: 5Gi
# Probe parameters
livenessprobe:
initialDelaySeconds: 20
periodSeconds: 15
readinessprobe:
initialDelaySeconds: 20
periodSeconds: 15
# Resourcep parameters
resources:
limits:
# cpu: 100m
memory: 3Gi
# requests:
# cpu: 100m
# memory: 2Gi
nodeSelector: {}
You may notice I will pull a SQL Server image from the MCR with the 2017-CU12-ubuntu tag.
Let’s now install SQL2017container release:
$ helm install --name sql2017container .
This command will install a helm release which includes among others a deployment, a replicaset with one pod (my SQL Server pod), a secret that contains the sa password, a persistence volume claim to persistent my database files (mapped to the /var/opt/mssql/data path inside the pod) and the service to expose the pod on port 1460 TCP.
$ helm status sql2017container LAST DEPLOYED: Tue Mar 12 20:36:12 2019 NAMESPACE: ci STATUS: DEPLOYED RESOURCES: ==> v1/Secret NAME TYPE DATA AGE sql2017container-dbi-mssql-linux-sa-secret Opaque 1 7m7s ==> v1/PersistentVolumeClaim NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE sql2017container-dbi-mssql-linux-data Bound pvc-18304483-44fe-11e9-a668-ca78ebdc2a19 5Gi RWO default 7m7s ==> v1/Service NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE sql2017container-dbi-mssql-linux LoadBalancer 10.0.104.244 xx.xx.xx.xx 1460:31502/TCP 7m6s ==> v1beta2/Deployment NAME DESIRED CURRENT UP-TO-DATE AVAILABLE AGE sql2017container-dbi-mssql-linux 1 1 1 1 7m6s ==> v1/Pod(related) NAME READY STATUS RESTARTS AGE sql2017container-dbi-mssql-linux-76b4f7c8f5-mmhqt 1/1 Running 0 7m6s
My SQL Server pod is running with the expected version and CU:
master> select @@version AS [version];
+-----------+
| version |
|-----------|
| Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)
Oct 18 2018 23:11:05
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS) |
+-----------+
(1 row affected)
Time: 0.354s
It’s time now to upgrade my pod with the latest CU13 (at the moment of this write-up). With helm charts this task is pretty simple. I will just upgrade my release with the new desired tag as follows:
$ helm upgrade sql2017container . --set=image.tag=2017-CU13-ubuntu Release "sql2017container" has been upgraded. Happy Helming!
Let’s dig further into deployment stuff:
$ kubectl describe deployment sql2017container-dbi-mssql-linux
The interesting part is below:
Events: Type Reason Age From Message ---- ------ ---- ---- ------- Normal ScalingReplicaSet 18m deployment-controller Scaled up replica set sql2017container-dbi-mssql-linux-76b4f7c8f5 to 1 Normal ScalingReplicaSet 1m deployment-controller Scaled down replica set sql2017container-dbi-mssql-linux-76b4f7c8f5 to 0 Normal ScalingReplicaSet 1m deployment-controller Scaled up replica set sql2017container-dbi-mssql-linux-799ff7979b to 1
Referring to the deployment strategy, the deployment controller has recreated a new ReplicaSet (and a new SQL Server pod) accordingly. A quick check from client tool confirms the instance has been upgraded correctly:
master> select @@version AS [version];
+-----------+
| version |
|-----------|
| Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)
Nov 30 2018 12:57:58
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS) |
+-----------+
(1 row affected)
Time: 0.716s
Another interesting part is how SQL Server detects the new image and starts upgrading process. Let’s dump the SQL Server log pod. I just put a sample of messages from the pod log to get a picture of scripts used during the upgrade.
$ kubectl logs sql2017container-dbi-mssql-linux-799ff7979b-knqrm 2019-03-12 19:54:59.11 spid22s Service Broker manager has started. 2019-03-12 19:54:59.44 spid6s Database 'master' is upgrading script 'ProvisionAgentIdentity.sql' from level 234884069 to level 234884072. 2019-03-12 19:54:59.45 spid6s Database 'master' is upgrading script 'no_op.sql' from level 234884069 to level 234884072. 2019-03-12 19:54:59.70 spid6s Database 'master' is upgrading script 'no_op.sql' from level 234884069 to level 234884072. …. 2019-03-12 19:54:59.70 spid6s ----------------------------------------- 2019-03-12 19:54:59.70 spid6s Starting execution of dummy.sql 2019-03-12 19:54:59.70 spid6s ----------------------------------------- … 2019-03-12 19:55:00.24 spid6s Starting execution of PRE_MSDB.SQL 2019-03-12 19:55:00.24 spid6s ---------------------------------- 2019-03-12 19:55:00.70 spid6s Setting database option COMPATIBILITY_LEVEL to 100 for database 'msdb'. 2019-03-12 19:55:00.90 spid6s ----------------------------------------- 2019-03-12 19:55:00.90 spid6s Starting execution of PRE_SQLAGENT100.SQL 2019-03-12 19:55:00.90 spid6s ----------------------------------------- … 2019-03-12 19:55:12.09 spid6s ---------------------------------- 2019-03-12 19:55:12.09 spid6s Starting execution of MSDB.SQL 2019-03-12 19:55:12.09 spid6s ---------------------------------- … 2019-03-12 19:55:12.86 spid6s ----------------------------------------- 2019-03-12 19:55:12.86 spid6s Starting execution of MSDB_VERSIONING.SQL 2019-03-12 19:55:12.86 spid6s ----------------------------------------- … 2019-03-12 19:55:51.68 spid6s ----------------------------------------- 2019-03-12 19:55:51.68 spid6s Starting execution of EXTENSIBILITY.SQL 2019-03-12 19:55:51.68 spid6s ----------------------------------------- … 2019-03-12 19:56:01.51 spid6s -------------------------------- 2019-03-12 19:56:01.51 spid6s Starting execution of Alwayson.SQL 2019-03-12 19:56:01.51 spid6s -------------------------------- … 2019-03-12 19:56:29.17 spid6s ------------------------------------ 2019-03-12 19:56:29.17 spid6s Moving 2005 SSIS Data to 2008 tables 2019-03-12 19:56:29.17 spid6s ------------------------------------ … 2019-03-12 19:56:32.52 spid6s ------------------------------------------------------ 2019-03-12 19:56:32.52 spid6s Starting execution of UPGRADE_UCP_CMDW_DISCOVERY.SQL 2019-03-12 19:56:32.52 spid6s ------------------------------------------------------ … 2019-03-12 19:56:32.66 spid6s ------------------------------------------------------ 2019-03-12 19:56:32.66 spid6s Starting execution of SSIS_DISCOVERY.SQL 2019-03-12 19:56:32.66 spid6s ------------------------------------------------------ … 2019-03-12 19:56:32.83 spid6s ------------------------------------------------------ 2019-03-12 19:56:32.83 spid6s Start provisioning of CEIPService Login 2019-03-12 19:56:32.83 spid6s ------------------------------------------------------ …
A set of scripts developed by the SQL Server team runs during the SQL Server pod startup and updates different parts of the SQL Server instance.
Helm provides a command to view release history …
$ helm history sql2017container REVISION UPDATED STATUS CHART DESCRIPTION 1 Tue Mar 12 20:36:12 2019 SUPERSEDED dbi-mssql-linux-1.0.0 Install complete 2 Tue Mar 12 20:53:26 2019 DEPLOYED dbi-mssql-linux-1.0.0 Upgrade complete
… and to rollback to previous release revision if anything goes wrong:
$ helm rollback sql2017container 1
The same process applies here. The deployment controller will recreate a ReplicaSet and a downgraded SQL Server pod to the previous version.
$ kubectl describe deployment sql2017container-dbi-mssql-linux Events: Type Reason Age From Message ---- ------ ---- ---- ------- Normal ScalingReplicaSet 31m deployment-controller Scaled down replica set sql2017container-dbi-mssql-linux-76b4f7c8f5 to 0 Normal ScalingReplicaSet 31m deployment-controller Scaled up replica set sql2017container-dbi-mssql-linux-799ff7979b to 1 Normal ScalingReplicaSet 6m deployment-controller Scaled down replica set sql2017container-dbi-mssql-linux-799ff7979b to 0 Normal ScalingReplicaSet 6m (x2 over 49m) deployment-controller Scaled up replica set sql2017container-dbi-mssql-linux-76b4f7c8f5 to 1
Same set of TSQL scripts seem to be executed again during the SQL Server pod startup for downgrade purpose this time.
The release rollback is logged in the release history:
$ helm history sql2017container REVISION UPDATED STATUS CHART DESCRIPTION 1 Tue Mar 12 20:36:12 2019 SUPERSEDED dbi-mssql-linux-1.0.0 Install complete 2 Tue Mar 12 20:53:26 2019 SUPERSEDED dbi-mssql-linux-1.0.0 Upgrade complete 3 Tue Mar 12 21:18:57 2019 DEPLOYED dbi-mssql-linux-1.0.0 Rollback to 1
Rollback capabilities of helm charts (and implicitly of K8s) may be attractive but for database applications it will likely not fit with all upgrade scenarios. To be used sparingly … What’s next? Taking a look at the upgrade scenarios with availability groups on K8s for sure … see you on a next write-up!
Cet article Upgrading SQL Server pods on K8s and helm charts est apparu en premier sur Blog dbi services.
SQL Tuning – Mix NULL / NOT NULL Values
One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica.
Let’s me explain you by an example :
Users can entered different values for a user field from an OBI report:
– If no value entered then all rows must be returned.
– If 1 value entered then only row(s) related to the filter must be returned.
– If List Of Values entered then only row(s) related to the filter must be returned.
The SQL we want to write must take into account all the conditions possible (the 3 listed above).
Here is the first version of the SQL query written by the customer :
select * from my_table a WHERE a.pt_name LIKE decode(:PT_PARAM, NULL, '%', '') OR a.pt_name IN (:PT_PARAM);
:PT_PARAM is the user variable.
The problem with this query is that the both conditions :
– a.pt_name LIKE decode(:PT_PARAM, NULL, ‘%’, ”)
– a.pt_name IN (:PT_PARAM)
are always TRUE, so unnecessary work will be done by oracle optimizer.
We can prove that by checking the execution plan :
If :PT_PARAM is equal to ‘Value1′ :
EXPLAIN PLAN FOR
select *
from my_table a
WHERE a.pt_name LIKE decode('Value1', NULL, '%', '')
OR a.pt_name IN ('Value1');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1606647163
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1140 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE | 5 | 1140 | 3 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | BIX_DMED_TERM_01 | | | | |
| 5 | BITMAP MERGE | | | | | |
|* 6 | BITMAP INDEX RANGE SCAN | BIX_DMED_TERM_01 | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."PT_NAME"='Value1')
6 - access("A"."PT_NAME" LIKE NULL)
filter("A"."PT_NAME" LIKE NULL AND "A"."PT_NAME" LIKE NULL)
Oracle Optimizer does 2 access :
– 1 access for NULL value
– 1 access for ‘Value1′ value
The first access is not necessary since the user has selected a Not Null Value (‘Value1′). Indeed if the user select one Not Null value (‘Value1′), we don’t want oracle execute condition for NULL value.
To avoid this couple of access, it’s necessary to re-write the SQL statement like that :
select * from my_table a where (:PT_PARAM is null AND a.pt_name like '%') OR (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM));
We just add a SQL clause indicating that if the first condition is TRUE, the second condition is FALSE and vice versa:
if (:PT_PARAM is null AND a.pt_name like ‘%’) is TRUE then (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM)) is FALSE
if (:PT_PARAM IS NOT NULL AND a.pt_name in (:PT_PARAM)) is TRUE then (:PT_PARAM is null AND a.pt_name like ‘%’) is FALSE
Checking the execution plan related to the new SQL statement :
EXPLAIN PLAN FOR
select *
from my_table a
where ('Value1' is null AND a.pt_name like '%')
OR ( 'Value1' IS NOT NULL AND a.pt_name in ('Value1'));
Plan hash value: 2444798625
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 1140 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE | 5 | 1140 | 2 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | BIX_DMED_TERM_01 | | | | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."PT_NAME"='Value1')
Now only one access is done, the one related to the value ‘Value1′ selected by the user.
Conclusion:Tuning a SQL query can be made within different way : modify the physical design for a table (indexes, partitioning), influence the optimizer (Hints) to force an execution plan, modify oracle optimizer database parameters.
But very often, SQL tuning can be made “simply” by re-written the SQL query. Most of the time, performance problem is due to bad written SQL statement.
The first advice before to write a SQL query is:
– always understand the business needs in order to avoid bad interpretations.
– avoid unnecessary step for oracle optimizer by checking oracle execution plan in details to control the path oracle choose to access the data.
– avoid writing complex SQL – SQL is a very simple language, don’t forget it.
Cet article SQL Tuning – Mix NULL / NOT NULL Values est apparu en premier sur Blog dbi services.
OpenText Enterprise World Europe 2019 – Partner Day
First day of the #OTEW here at the Austria International Center in Vienna, Guillaume Fuchs and I where invited to assist to the Partner Global sessions.
Welcome to OTEW Vienna 2019
Mark J. Barrenechea, the OpenText’s CEO & CTO, started the day with some generic topics concerning the global trends and achievements like:
- More and More partners and sponsors
- Cloud integration direction
- Strong security brought to customers
- AI & machine learning new trend
- New customer wave made of Gen Z and millennials to consider
- OpenText #1 in Content Services in 2018
- Turned to the future with Exabytes goals (high level transfers and storage)
- Pushing to upgrade to version 16 with most complete Content Platform ever for security and integration
- Real trend of SaaS with the new OT2 solutions
Today the big concern is the sprawl of data, OpenText is addressing this point by centralizing data and flux and create an information advantage. Using Cloud and OT2 SaaS, PaaS will open the business to every thing.
OT2 is the EIM as a service, it’s an hybrid cloud platform that brings security and scalability to customers solutions which you can integrates to leading applications like O365 Microsoft Teams, Documentum and more, it provides SaaS as well. One place for your data and many connectors to it. More info on it to come, stay tuned.
Smart View is the defaultSmart View is the new OpenText UI default for every components such as D2 for documentum, SAP integration, Extended ECM, SuccessFactor and so on.
New features:
- Add documents to subfodlers without opening folder first
- Multi-items download -> Zip and download
- Download phases displayed in progress bar
- Pages editable inline with smart view
- Possibility to add widgets in smart view
- Workspace look improved in smart view
- Image/media display improved: Galery View with sorting, filters by name
- Threaded discussion in smart view look and feel
- New permission management visual representation
- Mobile capabilities
- Integrated in other lead applications (Teams, SAP, Sharepoint and so on…)
OpenText trends are the following:
- New UI for products: Smart View: All devices, well integrated to OT2
- Content In Context
- Embrace Office 365, with Documentum integration
- Integration of documentum in SAP
- Push to Cloud
- More cloud based product: Docker, Kubernetes
- Run applications anywhere with OpenText Cloud, Azure, AWS, Google
- SaaS Applications & Services on OT2
- Line Of Business
- SAP applications
- LoB solutions like SuccessFactors
- Platform for industry solutions like Life Science, Engineering and Government
- Intelligent Automation
- Information extraction with machine learning (Capture)
- Cloud capture apps for SAP, Salesforce, etc
- Drive automation with Document Generation
- Automatic sharing with OT Core
- Leverage Magellan and AI
- Personal Assistant / Bots
- Governance:
- Smart Compliance
- GDPR and DPA ready
- Archiving and Application decommissioning
After this first day at OTEW we can see that OpenText is really pushing on new UI with Smart View, as well as centralized services and storage with OT2 and OpenText Cloud solutions. Content Services will become the angular stone for all content storage with plugged interfaces and components provided by the OT2 platform.
Cet article OpenText Enterprise World Europe 2019 – Partner Day est apparu en premier sur Blog dbi services.
SQL Server Temporal Table – How to store a history table in another file?
Few days ago, a customer asks me if it is possible to move the history table to slower but cheaper storage.
The question behind this is whether it is possible to create a history table on a separate filegroup and file.
Few years ago, I write a serie of blogs about temporal table here.
I will take the same example to try to set up a filegroup specific to a history table.
In my sample, I create a ‘Zoo’ database with a table ‘Animals’ which inventory animals.
First, I create the filegroup HISTORY and add a file Zoo_history.ndf:
USE [master] GO ALTER DATABASE [Zoo] ADD FILEGROUP [HISTORY] GO ALTER DATABASE [Zoo] ADD FILE ( NAME = N'Zoo_History', FILENAME = N'D:\DATA\Zoo_History.ndf' , SIZE = 131072KB , FILEGROWTH = 131072KB ) TO FILEGROUP [HISTORY] GO
Before I create the table Animals, I create the history table [AnimalsHistory] on this filegroup [HISTORY] with also a separate schema [History] (it’s a good practice):
USE [Zoo] GO CREATE SCHEMA [History] AUTHORIZATION [dbo] GO CREATE TABLE [History].[AnimalsHistory] ( [AnimalId] [int] NOT NULL, [Name] [varchar](200) NOT NULL, [Genus Species] [varchar](200) NOT NULL, [Number] [int] NOT NULL, [StartDate] [datetime2] NOT NULL, [EndDate] [datetime2] NOT NULL, ) ON [HISTORY]
At this time, the table is not a history table. It will be after the creation of the principal table:
CREATE TABLE [dbo].[Animals] ( [AnimalId] [int] NOT NULL, [Name] [varchar](200) NOT NULL, [Genus Species] [varchar](200) NOT NULL, [Number] [int] NOT NULL, CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED ([AnimalId] ASC), /*Temporal: Define the Period*/ [StartDate] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL, [EndDate] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME([StartDate],[EndDate]) ) WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE = [History].[AnimalsHistory]))
Now the history table is link to my table System-Versioned with a separate file.
I run few queries to have data and as you can see, all previous version of each updated row is inserted into the history table in the HISTORY filegroup.
INSERT INTO [Zoo].[dbo].[Animals]([AnimalId],[Name],[Genus Species],[Number])
VALUES(1,'African wild cat','Felis silvestris lybica',10)
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 21 WHERE Name = 'African wild cat' AND [Genus Species]= 'Felis silvestris lybica';
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 5 WHERE Name = 'African wild cat' AND [Genus Species]= 'Felis silvestris lybica';
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 12 WHERE Name = 'African wild cat' AND [Genus Species]= 'Felis silvestris lybica';
GO
UPDATE [Zoo].[dbo].[Animals] SET Number = 20 WHERE Name = 'African wild cat' AND [Genus Species]= 'Felis silvestris lybica';
GO
...
As you can see below, all changes are in the filegroup HISTORY:
I recommend creating a separate filegroup for history table in case of temporal table usage.
It is easier to manage (table growth), will not be place in your “principal” data file and can be place on a different storage if needed.
I hope this will help you to design your database
Cet article SQL Server Temporal Table – How to store a history table in another file? est apparu en premier sur Blog dbi services.