Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 5 hours 38 min ago

Docker-CE on Oracle Enterprise Linux 7

Thu, 2018-03-29 15:32

Here is how I install the latest Docker version on Oracle Linux 7. You find several blog posts about it which all install ‘docker-engine’. But things move fast in this agile world and docker package name has changed. The Community Edition is now ‘docker-ce’ and you want this one to run the latest version.

I’m on OEL 7.4 but should also wotj on RHEL 7:
[root@VM188 yum]# cat /etc/oracle-release
Oracle Linux Server release 7.4

docker-engine

If you enable [ol7_addons] you can install ‘docker-engine:’

# yum-config-manager --enable ol7_addons
# yum info docker-engine
Loaded plugins: ulninfo
Available Packages
Name : docker-engine
Arch : x86_64
Version : 17.06.2.ol
Release : 1.0.1.el7
Size : 21 M
Repo : ol7_addons/x86_64
Summary : The open-source application container engine
URL : https://dockerproject.org
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.

But forget it. That’s 17.06 which is 6 months old. You should consider Docker as a puppy. 6 month in puppy’s years is like 3 human years. So many changes happened.

You can remove all those old things:

# yum remove docker docker-common docker-selinux docker-engine

docker-ce

I’ve not found ‘docker-ce’ on OL7 repositories, as only the Enterprise Edition is there. Then I added the CentOS repo (with yum-config-manager that you can get with yum-utils if you don’t have it already):

yum -y install yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo

Once done, I’ve just installed Docker Community Edition with:

yum -y install docker-ce

And, at that time I got the version 17.12 easily:

[root@VM188 ~]# yum info docker-ce
Loaded plugins: ulninfo
Installed Packages
Name : docker-ce
Arch : x86_64
Version : 17.12.0.ce
Release : 1.el7.centos
Size : 123 M
Repo : installed
From repo : docker-ce-stable
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.
...

But now there’s a new version available:

Available Packages
Name : docker-ce
Arch : x86_64
Version : 18.03.0.ce
Release : 1.el7.centos
Size : 35 M
Repo : docker-ce-stable/x86_64
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.

The problem is that if you want to install docker-ce in this latest version, you will now get:

Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be installed
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Finished Dependency Resolution
Error: Package: docker-ce-18.03.0.ce-1.el7.centos.x86_64 (docker-ce-stable)
Requires: pigz
You could try using --skip-broken to work around the problem
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
2:microcode_ctl-2.1-22.5.0.3.el7_4.x86_64 has missing requires of kernel

(Ok Google, this is what you need to index…)

pigz

Starting from version 18.02 there’s a new dependency on ‘pigz’ for parallel gzip.

To get this ‘pigz’ package from the OL7 repository you need to enable EPEL in /etc/yum.repos.d/public-yum-ol7.repo

[ol7_developer_EPEL] name=Oracle Linux $releasever Developement Packages ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

Now, I’m able to install the latest docker-ce:

[root@VM188 yum.repos.d]# yum install docker-ce
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:17.12.0.ce-1.el7.centos will be updated
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be an update
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Running transaction check
---> Package pigz.x86_64 0:2.3.4-1.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Updating:
docker-ce x86_64 18.03.0.ce-1.el7.centos docker-ce-stable 35 M
Installing for dependencies:
pigz x86_64 2.3.4-1.el7 ol7_developer_EPEL 80 k
 
Transaction Summary
================================================================================================================================
Install ( 1 Dependent package)
Upgrade 1 Package
 
Total download size: 35 M
Is this ok [y/d/N]: y

Oracle Database on Docker

You may wonder why I install Docker on Oracle Linux rather than CentOS. The MOS Doc ID 2216342.1 mentions that Oracle will support customers running Oracle Database (single instance) in Docker containers running on Oracle Linux 7 with UEK4 or Red Hat Enterprise Linux 7.

If you want to validate your Docker install for running Oracle Database, the easiest is to use the image build script provided by Oracle:

git clone https://github.com/oracle/docker-images.git
cd ./docker-images/OracleDatabase/SingleInstance/dockerfiles/
# download and move linuxx64_12201_database.zip is in 12.2.0.1 subdirectory
sh buildDockerImage.sh -v 12.2.0.1 -e

Those are maintained by Gerald Venzl, Oracle product manager for database development, so they are obviously the best way to run Oracle Database on Docker. You can read all related best practices from the same author. Once you have that running, you have validated your environment and you can customize further if you want.

 

Cet article Docker-CE on Oracle Enterprise Linux 7 est apparu en premier sur Blog dbi services.

Insert…on conflict with partitions finally works in PostgreSQL 11

Thu, 2018-03-29 09:11

Another partitioning improvement for PostgreSQL 11: Insert…on conflict is now supported (for most cases) in PostgreSQL 11 thanks to this commit. Lets see how it works.

We’ll again use the slightly modified little list partitioned table from the last post, here in PostgreSQL 10:

postgres=# select version();
                                                          version                                       Insert                    
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
 
postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# create unique index iu1 on part_1 (a);
CREATE INDEX
postgres=# create unique index iu2 on part_2 (a);
CREATE INDEX
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['beer'::character varying(5)])))
Indexes:
    "iu1" UNIQUE, btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['wine'::character varying(5)])))
Indexes:
    "iu2" UNIQUE, btree (a)

Insert some sample data:

postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'beer');
INSERT 0 1
postgres=# select * from part;
 a | b | list 
---+---+------
 1 | 1 | beer
 2 | 2 | beer
(2 rows)

When you try the following in PostgreSQL 10 it will fail:

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
ERROR:  ON CONFLICT clause is not supported with partitioned tables

Doing that directly on the partition is working:

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
ERROR:  ON CONFLICT clause is not supported with partitioned tables
postgres=# insert into part_1(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
INSERT 0 1
postgres=# select * from part;
 a | b | list 
---+---+------
 2 | 2 | beer
 1 | 2 | beer
(2 rows)

But who wants to do that directly on the partition? For sure this is a big limitation and not very useful. That got fixed now with PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# alter table part add constraint pk1 primary key (a,list);
ALTER TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "pk1" PRIMARY KEY, btree (a, list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')


postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'wine');
INSERT 0 1
postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = 2;
INSERT 0 1

Cool. But be aware that there still is a case which is not working:

postgres=# select * from part;
 a | b | list 
---+---+------
 1 | 2 | beer
 2 | 2 | wine
(2 rows)

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set list='wine';
ERROR:  invalid ON UPDATE specification
DETAIL:  The result tuple would appear in a different partition than the original tuple.

When you do an on conflict clause that would require to move the row into another partition (updating the partition key) it will fail.

 

Cet article Insert…on conflict with partitions finally works in PostgreSQL 11 est apparu en premier sur Blog dbi services.

How reimaging detects the ODA hardware?

Thu, 2018-03-29 08:48

A long time ago, ODA referred to the only model that existed (nearly). Actually, not so long time ago, as ODA X3, X4 and X5 were released between 2013 and 2015. And it was quite the same hardware.

Now there is 3 current models in the X7 range, and they were 4 in the previous generation (X6).

ODA is now divided in 2 main lines. High Availability models, understand 2 nodes and a shared storage like the first generations. And lite models, with only 1 node and local NVMe SSD, obviously more afordable than HA models.

Beginning with X6, lite models came with a brand new ISO for reimaging, including the new odacli, a higher level administration tool (compared to oakcli), and X6-2HA remained with the same ISO as previous gen models.

But now, X7 range saw the adoption of odacli for all models. And if you stick with bare metal, ISO is now the same for reimaging all the range. Quite convenient isn’t it?

I recently asked myself: how the reimaging process determines the correct model?

First of all, you can look at your ODA model with odaadmcli:

odaadmcli show env_hw
BM ODA X7-2 Medium

 

As you may know, reimaging process is fully automatic, and you have nothing to provide unless the correct ISO.

If ODA X7-S has only one Xeon CPU, nodes for ODA X7-M and X7-2HA are barelly the same, so what differs from them?

I first thought it was somewhere hardcoded, but it doesn’t seems to. ODA X7 is just the same hardware as the multi-purpose Oracle Server X7-2. Among the ISO files, I found a script that detects correct model by counting the number of local NVMe SSD (quite smart because HA model has only M2 SSD disks for the system and no NVMe SSDs), but it was not used during the reimaging…

Looking deeper on the system side, I found that model was simply part of the grub.conf boot options:

kernel /vmlinuz-4.1.12-94.4.1.el6uek.x86_64 ro root=/dev/mapper/VolGroupSys-LogVolRoot rd_NO_LUKS rd_MD_UUID=1ee3fdbc:3fdcdcf4:3a28f182:1a674b68 rd_LVM_LV=VolGroupSys/LogVolRoot rd_LVM_LV=VolGroupSys/LogVolSwap SYSFONT=latarcyrheb-sun16 pci=noaer crashkernel=256M@64M loglevel=3 panic=60 transparent_hugepage=never biosdevname=1 ipv6.disable=1 debug audit=1 intel_idle.max_cstate=1 nofloppy nomce numa=off console=ttyS0,115200n8 console=tty0 KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM LANG=en_US.UTF-8 PRODUCT=ORACLE_SERVER_X7-2 TYPE=X7_2_LITE_M

I discovered that odacli is based on this information. If you remove these settings, your ODA will be considered as a HA model, and odacli will crash:

DCS-10002: Invalid hardware platform

Okay, but it doesn’t tell me how the reimaging process decides which model to put in the grub.conf parameters…

Actually, grub.conf is configured at deployment with something from the ILOM, the “SP System identifier” located in the settings under the ILOM hostname:

blog_X7_SP_identifier

As you can see, this field can be edited, and you can put everything in it (didn’t tried EXADATA…). Unfortunatly, it’s just below the “SP Hostname” and some people would probably like to change this identifier in the same time they are feeding the hostname. But it’s a bad idea because your ODA would not be correctly deployed for the next time you’ll need to reimage!

Be aware of that and please do not touch this identifier. Keep it as it is.

Notes :
– reimaging was done with patch 23530609 (version 12.2.1.2.0)
– default hostname for the ODA ILOM is… the SP System Identifier

 

Cet article How reimaging detects the ODA hardware? est apparu en premier sur Blog dbi services.

The WAL segement size becomes changeable in PostgreSQL 11

Tue, 2018-03-27 09:37

Up to PostgreSQL 10 the wal segment size was fixed once PostgreSQL was compiled and installed. When you wanted to have something else than the default you needed to tell that to the configure command when building from source, e.g.

postgres@pgbox:/home/postgres/postgresql/ [PGDEV] ./configure --with-wal-segsize=64MB

For PostgreSQL 11 two commits landed (1 and 2) which change this. Lets have a look.

When you check the current development documentation for initdb you’ll notice a new parameter called “–wal-segsize”. This allows to change the WAL segment size from the default of 16MB when you initialize you new cluster:

postgres@pgbox:/home/postgres/ [PGDEV] initdb --wal-segsize=32 /var/tmp/aa
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.utf8
  CTYPE:    en_US.utf8
  MESSAGES: en_US.utf8
  MONETARY: de_CH.UTF-8
  NUMERIC:  de_CH.UTF-8
  TIME:     en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/tmp/aa ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/tmp/aa -l logfile start

Btw: You can also use pg_controldata to get the size of the WAL segments:

postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment"
Bytes per WAL segment:                33554432
postgres@pgbox:/home/postgres/ [PGDEV] echo "33554432/1024/1024" | bc
32

The other commit added the same option to pg_resetwal.

postgres@pgbox:/home/postgres/ [PGDEV] pg_resetwal -D /var/tmp/aa/ --wal-segsize=64
Write-ahead log reset
postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment"
Bytes per WAL segment:                67108864
postgres@pgbox:/home/postgres/ [PGDEV] echo "67108864/1024/1024" | bc
64

So, hopefully this September when PostgreSQL 11 should be released, you do not need to install additional binaries for changing the WAL segment size. This will also allow major upgrades with pg_upgrade to a new cluster with a different WAL segment size. Nice, makes things easier.

 

Cet article The WAL segement size becomes changeable in PostgreSQL 11 est apparu en premier sur Blog dbi services.

The same MAC address for two interfaces on the same host?

Mon, 2018-03-26 23:50

Yes, sounds strange, but exactly this is what we faced today. In the end it is clear and not strange anymore but for a moment we really thought we are hitting a bug in the operating system, which was RedHat 7.4. For the little demo for this post I’ll use CentOS 7.4 but this should not really matter. Lets go.

The situation we faced looked like this:

root@:/etc/sysconfig/network-scripts/ [] ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:d6:95:ab brd ff:ff:ff:ff:ff:ff
    inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
       valid_lft 86295sec preferred_lft 86295sec
    inet6 fe80::a00:27ff:fed6:95ab/64 scope link 
       valid_lft forever preferred_lft forever
3: enp0s8:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:5c:b0:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.99/24 brd 192.168.22.255 scope global enp0s8
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe5c:b0e5/64 scope link 
       valid_lft forever preferred_lft forever
4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe85:2d8/64 scope link tentative 
       valid_lft forever preferred_lft forever

As you can see the interfaces enp0s8 and enp0s10 have exactly the same MAC address. In addition to that the bonding interface bond0 has the same mac address as well:

4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe85:2d8/64 scope link tentative 
       valid_lft forever preferred_lft forever

This was not so scary as we knew that the bonding interface will get one of the mac addresses of the slave interfaces. But both slaves had the same mac address, how that? It all depends on the bonding options. In our case:

root@:/etc/sysconfig/network-scripts/ [] cat ifcfg-bond0 
DEVICE=bond0
TYPE=Bond
BONDING_MASTER=yes
BOOTPROTO=static
IPADDR=192.168.22.223
NETMASK=255.255.255.0
GATEWAY=192.168.22.1
ONBOOT=yes
BONDING_OPTS="mode=active-backup miimon=100"

What you need to know is that there is another parameter/option called fail_over_mac. The default value for this option (as we did not explicitly set it we are using the default) is 0/none which means all slave interfaces will use the same MAC address. The original MAC addresses can be seen here:

root@:/etc/sysconfig/network-scripts/ [] cat /proc/net/bonding/bond0 
Ethernet Channel Bonding Driver: v3.7.1 (April 27, 2011)

Bonding Mode: fault-tolerance (active-backup)
Primary Slave: None
Currently Active Slave: enp0s9
MII Status: up
MII Polling Interval (ms): 100
Up Delay (ms): 0
Down Delay (ms): 0

Slave Interface: enp0s9
MII Status: up
Speed: 1000 Mbps
Duplex: full
Link Failure Count: 0
Permanent HW addr: 08:00:27:85:02:d8
Slave queue ID: 0

Slave Interface: enp0s10
MII Status: up
Speed: 1000 Mbps
Duplex: full
Link Failure Count: 0
Permanent HW addr: 08:00:27:e4:1c:63
Slave queue ID: 0

When we change that to 1/active and restart the network:

root@:/etc/sysconfig/network-scripts/ [] sed -i 's/BONDING_OPTS="mode=active-backup miimon=100"/BONDING_OPTS="mode=active-backup miimon=100 fail_over_mac=active"/g' ifcfg-bond0 
root@:/etc/sysconfig/network-scripts/ [] systemctl restart network

… we will have different MAC addresses:

4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fee4:1c63/64 scope link tentative 
       valid_lft forever preferred_lft forever

The downside of this approach is that the MAC address will change when the active interface goes down. The currently active interface is enp0s9 with the following MAC address:

root@:/etc/sysconfig/network-scripts/ [] cat /proc/net/bonding/bond0 | grep -i active
Bonding Mode: fault-tolerance (active-backup) (fail_over_mac active)
Currently Active Slave: enp0s9
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 enp0s9
4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff

The bonding interface has the same MAC address:

root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 bond0
4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever

When we bring the currently active interface (enp0s9) down the MAC address of the bonding interface will switch to the MAC address of enp0s10 (which then becomes the active interface):

root@:/etc/sysconfig/network-scripts/ [] ifdown enp0s9
Device 'enp0s9' successfully disconnected.
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 bond0
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever

The third an last option for fail_over_mac is 3/follow which will change the MAC address of the slave to the bonding’s MAC address when it becomes active. Documentation here.

 

Cet article The same MAC address for two interfaces on the same host? est apparu en premier sur Blog dbi services.

When you execute the same statement thousands of times -> prepare it

Mon, 2018-03-26 04:13

Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing of statements and only planning and executing will happen. Lets see how that works.

To generate some sample data I used pgbench with a scale factor of 100 which gives me 10’000’000 rows in the pgbench_accounts table:

postgres@pgbox:/home/postgres/ [PGDEV] pgbench -i -s 100 bench
postgres@pgbox:/home/postgres/ [PGDEV] psql bench

psql (11devel)
Type "help" for help.

bench=# select count(*) from pgbench_accounts;
  count   
----------
 10000000
(1 row)

bench=# \d pgbench_accounts
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null | 
 bid      | integer       |           |          | 
 abalance | integer       |           |          | 
 filler   | character(84) |           |          | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Lets assume we have the following simple query:

bench=# select count(*) from pgbench_accounts where aid = 11111;
 count 
-------
     1
(1 row)

As said at the beginning of this post PostgreSQL will need to parse that statement. Using explain with the right options you are able to see how much time was spend on planning:

bench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.060..0.063 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.034..0.039 rows=1 loops=1)
         Index Cond: (aid = 11111)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.150 ms
 Execution Time: 0.133 ms
(8 rows)

Planning this statement takes more time then executing it. Now assume you want to execute that very same statement one thousand times:

bench=# \t
bench=# select 'select count(*) from pgbench_accounts where aid = 11111;' from generate_series(1,1000) i; \g test.sql
bench=# \! cat test.sql | head
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
...

When we execute that we force PostgreSQL to parse all of those 1000 statements:

bench=# \timing
Timing is on.
bench=# \! time psql -f test.sql bench

real	0m0.148s
user	0m0.031s
sys	0m0.015s

What you can do to avoid that is to prepare the statement:

bench=# prepare tt as select count(*) from pgbench_accounts where aid = 11111;
PREPARE

Once it is prepared you can execute it:

bench=# execute tt;
 count 
-------
     1
(1 row)

… and you can also explain it:

bench=# explain(analyze,buffers) execute tt;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.066..0.069 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.037..0.043 rows=1 loops=1)
         Index Cond: (aid = 11111)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.021 ms
 Execution Time: 0.269 ms
(8 rows)

You’ll notice that the planning time went down quite a lot compared to the not prepared statement:

bench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.167..0.172 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.030..0.037 rows=1 loops=1)
         Index Cond: (aid = 11111)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.167 ms
 Execution Time: 0.248 ms
(8 rows)

When you do that one thousand times now:

bench=# \t
bench=# select 'execute tt;' from generate_series(1,1000) i; \g test.sql
bench=# \! sed -i '1s/^/prepare tt as select count(*) from pgbench_accounts where aid = 11111;/' test.sql
bench=# \! time psql -f test.sql bench

real	0m0.095s
user	0m0.031s
sys	0m0.010s

… execution time will come down. It is not much in that simple case but this is due to the simple statement. Btw: As prepared statements only life for the time of the session the sed command adds the prepare statement to the top of file, and preparing takes time as well. Without that execution time would be even less.

When your values change in the where clause you can do it like this:

bench=# prepare tt as select count(*) from pgbench_accounts where aid = $1;
PREPARE
Time: 0.571 ms
bench=# execute tt (123);
     1

For completeness: When you want to get rid of a prepared statement without losing your session there is deallocate:

bench=# deallocate tt;
DEALLOCATE
Time: 0.623 ms
 

Cet article When you execute the same statement thousands of times -> prepare it est apparu en premier sur Blog dbi services.

PostgreSQL – logical replication with pglogical

Fri, 2018-03-23 11:31

Although PostgreSQL 10 integrate native logical replication (have a look here or here), it is always interesting to be aware of alternative solutions to the available in-core features.
One of those is called pglogical. It’s a Postgres extension developed by 2ndQuadrant, major contributor to PostgreSQL development.
pg_logical
The goal of this blog post is to discover how to install pglogical and configure it in order to generate simple logical replication cases.

Installation

To get the extension we should start with RPM repository installation :
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10] sudo yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm
Loaded plugins: fastestmirror
pglogical-rhel-1.0-3.noarch.rpm | 8.3 kB 00:00:00
Examining /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm: pglogical-rhel-1.0-3.noarch
Marking /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm to be installed
...
...
...
Installed:
pglogical-rhel.noarch 0:1.0-3


Complete!
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10]

Once installed, we can add the extension inside our Postgres instance :
postgres=# CREATE EXTENSION pglogical;
2017-12-18 16:24:39.079 CET [4327] ERROR: pglogical is not in shared_preload_libraries
2017-12-18 16:24:39.079 CET [4327] STATEMENT: CREATE EXTENSION pglogical;
ERROR: pglogical is not in shared_preload_libraries
postgres=#

Oops… the pglogical library must be loaded when the cluster starts :
postgres=# alter system set shared_preload_libraries = 'pglogical';
ALTER SYSTEM
postgres=#

Restart the cluster to take it in account :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ stop
waiting for server to shut down....2017-12-18 16:28:03.895 CET [4447] LOG: received fast shutdown request
2017-12-18 16:28:03.902 CET [4447] LOG: aborting any active transactions
2017-12-18 16:28:03.923 CET [4447] LOG: worker process: logical replication launcher (PID 4455) exited with exit code 1
2017-12-18 16:28:03.923 CET [4449] LOG: shutting down
2017-12-18 16:28:03.940 CET [4447] LOG: database system is shut down
done
server stopped
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ start
waiting for server to start....2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv4 address "0.0.0.0", port 5420
2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv6 address "::", port 5420
2017-12-18 16:28:10.407 CET [4654] LOG: listening on Unix socket "/tmp/.s.PGSQL.5420"
2017-12-18 16:28:10.416 CET [4655] LOG: database system was shut down at 2017-12-18 16:28:03 CET
2017-12-18 16:28:10.426 CET [4654] LOG: database system is ready to accept connections
2017-12-18 16:28:10.430 CET [4661] LOG: starting pglogical supervisor
2017-12-18 16:28:10.435 CET [4663] LOG: manager worker [4663] at slot 0 generation 1 detaching cleanly
2017-12-18 16:28:10.439 CET [4664] LOG: manager worker [4664] at slot 0 generation 2 detaching cleanly
2017-12-18 16:28:10.444 CET [4665] LOG: manager worker [4665] at slot 0 generation 3 detaching cleanly
done
server started
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10]

Once the cluster restarted with the library, we may noticed a new OS process :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] ps -ef | grep pglogical
postgres 5001 4994 0 16:33 ? 00:00:00 postgres: bgworker: pglogical supervisor

We should now be able to create the extension :
postgres=# CREATE EXTENSION pglogical;
CREATE EXTENSION
postgres=#
postgres=# \dx+ pglogical
Objects in extension "pglogical"
Object description
------------------------------------------------------------------------------------------
function pglogical.alter_node_add_interface(name,name,text)
function pglogical.alter_node_drop_interface(name,name)
function pglogical.alter_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.alter_subscription_add_replication_set(name,name)
function pglogical.alter_subscription_disable(name,boolean)
function pglogical.alter_subscription_enable(name,boolean)
function pglogical.alter_subscription_interface(name,name)
function pglogical.alter_subscription_remove_replication_set(name,name)
function pglogical.alter_subscription_resynchronize_table(name,regclass,boolean)
function pglogical.alter_subscription_synchronize(name,boolean)
function pglogical.create_node(name,text)
function pglogical.create_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.create_subscription(name,text,text[],boolean,boolean,text[],interval)
function pglogical.drop_node(name,boolean)
function pglogical.drop_replication_set(name,boolean)
function pglogical.drop_subscription(name,boolean)
function pglogical.pglogical_gen_slot_name(name,name,name)
function pglogical.pglogical_max_proto_version()
function pglogical.pglogical_min_proto_version()
function pglogical.pglogical_node_info()
function pglogical.pglogical_version()
function pglogical.pglogical_version_num()
function pglogical.queue_truncate()
function pglogical.replicate_ddl_command(text,text[])
function pglogical.replication_set_add_all_sequences(name,text[],boolean)
function pglogical.replication_set_add_all_tables(name,text[],boolean)
function pglogical.replication_set_add_sequence(name,regclass,boolean)
function pglogical.replication_set_add_table(name,regclass,boolean,text[],text)
function pglogical.replication_set_remove_sequence(name,regclass)
function pglogical.replication_set_remove_table(name,regclass)
function pglogical.show_repset_table_info(regclass,text[])
function pglogical.show_subscription_status(name)
function pglogical.show_subscription_table(name,regclass)
function pglogical.synchronize_sequence(regclass)
function pglogical.table_data_filtered(anyelement,regclass,text[])
function pglogical.wait_slot_confirm_lsn(name,pg_lsn)
table pglogical.depend
table pglogical.local_node
table pglogical.local_sync_status
table pglogical.node
table pglogical.node_interface
table pglogical.queue
table pglogical.replication_set
table pglogical.replication_set_seq
table pglogical.replication_set_table
table pglogical.sequence_state
table pglogical.subscription
view pglogical.tables
(48 rows)

The wal_level parameter must be set to ‘logical’ for logical replication :
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)

And the pg_hba file must contains an entry to allow connections for replication purpose.
Important : all the steps we have seen until now must be done on the standby side as well.

What’s next ?

Logical replication with Postgres is based on several components :
– Provider : the primary node/cluster
– Subscriber : the standby node/cluster, on which data will be replicated
– Replication set : collection of table you want to replicate
– Subscription : runs the replication, based on the replication set

We’ll start by creating the provider on the primary side :
postgres=# select pglogical.create_node (node_name := 'provider1', dsn := 'host=192.168.22.37 port=5420 dbname=postgres');
create_node
-------------
2976894835
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-----------+------------+-----------------------------------------------
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)

Notice again a new OS process :
postgres@ppas01:/home/postgres/ [PG10] ps -ef | grep pglogical
postgres 1796 1788 0 15:13 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4566 1788 0 16:05 ? 00:00:00 postgres: bgworker: pglogical manager 13211

On the standby side, we create the subscriber :
postgres=# select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=192.168.22.38 port=5420 dbname=postgres');
create_node
-------------
330520249
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-------------+-----------+----------------------------------------------
2049915666 | subscriber1 | 330520249 | host=192.168.22.38 port=5420 dbname=postgres
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)

What about replication set ? There is 3 existing by default with a different DML replication behavior for each :
postgres=# select * from pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | replicate_update | replicate_delete | replicate_truncate
------------+------------+---------------------+------------------+------------------+------------------+--------------------
290045701 | 2976894835 | default | t | t | t | t
3461245231 | 2976894835 | default_insert_only | t | f | f | t
2465411902 | 2976894835 | ddl_sql | t | f | f | f

We can easily add our own :
postgres=# select pglogical.create_replication_set('my_set', true, true, true, true);
create_replication_set
------------------------
1521346531

To start the replication we have to create a subscription (using the replication set we created) from the standby side :
postgres=# select pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=192.168.22.37 port=5420 dbname=postgres', replication_sets := '{my_set}');
create_subscription
---------------------
1763399739
(1 row)


postgres=# select subscription_name, status FROM pglogical.show_subscription_status();
subscription_name | status
-------------------+-------------
subscription1 | replicating

Have a look in the log file… replication started with the correct provider and subscription :
2018-02-02 15:16:14.234 CET [5488] LOG: starting apply for subscription subscription1
2018-02-02 15:17:54.547 CET [5502] LOG: starting pglogical database manager for database postgres

postgres@ppas02:/u02/pgdata/PG10/ [PG10] ps -ef | grep pglogical
postgres 3113 3105 0 10:01 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4587 3105 0 13:47 ? 00:00:00 postgres: bgworker: pglogical manager 13211
postgres 5060 3105 0 15:06 ? 00:00:00 postgres: bgworker: pglogical apply 13211:1763399739

The last step is to add the tables in the replication set we created :
postgres=# select pglogical.replication_set_add_all_tables('my_set', ARRAY['public']);
replication_set_add_all_tables
--------------------------------
t
(1 row)

The insert/update/delete operations run against the primary server are now replicated to the standby one.

 

Cet article PostgreSQL – logical replication with pglogical est apparu en premier sur Blog dbi services.

Updating the partition key now works in PostgreSQL 11

Fri, 2018-03-23 11:00

In the last post about partitioning improvements in PostgreSQL 11 we talked about unique indexes and primary keys on partitioned tables. Both did not work in PostgreSQL 10 but now do in PostgreSQL 11. Another operation that did not work in PostgreSQL 10 and does now in PostgreSQL 11 is: Updating the partition key, which means that a row will move from one partition to another. Lets have a look at that.

We’ll use the same little list partitioned table as in the last post and start with PostgreSQL 10:

postgres=# select version();
                                                          version                                       Insert                     
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Insert some data:

postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10

So we have ten beers and ten wines.

postgres=# select count(*) from only part_1;
 count 
-------
    10
(1 row)

postgres=# select count(*) from only part_2;
 count 
-------
    10
(1 row)

What happens if we update the partition key because we like more beer than wine in PostgreSQL 10?

postgres=# update part set list = 'beer' where a = 15;
ERROR:  new row for relation "part_2" violates partition constraint
DETAIL:  Failing row contains (15, beer).

Not so good. Lets try the same in PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10
postgres=# update part set list = 'beer' where a = 15;
UPDATE 1

Excellent, just works.

 

Cet article Updating the partition key now works in PostgreSQL 11 est apparu en premier sur Blog dbi services.

Hash Partitioning in PostgreSQL 11

Fri, 2018-03-23 07:45

PostgreSQL 10 comes with partition support. But Hash Partitioning was not supported. PostgreSQL 11 will support Hash Partition.
In the documentation we can read
The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
For this demonstration let’s create a table with a hash partition

drop table dept;
create table dept (id  int primary key) partition by hash(id) ; 

Now let’s create for example 10 partitions

create table dept_1 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 2);
create table dept_4 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 3);
create table dept_5 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 4);
create table dept_6 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 5);
create table dept_7 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 6);
create table dept_8 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 7);
create table dept_9 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 8);
create table dept_10 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 9);

We can verify that partitions are created using the \d+ command

(postgres@[local]:5432) [test] > \d+ dept
                                   Table "public.dept"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | De
scription
--------+---------+-----------+----------+---------+---------+--------------+---
----------
 id     | integer |           | not null |         | plain   |              |
Partition key: HASH (id)
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
Partitions: dept_1 FOR VALUES WITH (modulus 10, remainder 0),
            dept_10 FOR VALUES WITH (modulus 10, remainder 9),
            dept_2 FOR VALUES WITH (modulus 10, remainder 1),
            dept_3 FOR VALUES WITH (modulus 10, remainder 2),
            dept_4 FOR VALUES WITH (modulus 10, remainder 3),
            dept_5 FOR VALUES WITH (modulus 10, remainder 4),
            dept_6 FOR VALUES WITH (modulus 10, remainder 5),
            dept_7 FOR VALUES WITH (modulus 10, remainder 6),
            dept_8 FOR VALUES WITH (modulus 10, remainder 7),
            dept_9 FOR VALUES WITH (modulus 10, remainder 8)

(postgres@[local]:5432) [test] >

Now let’s insert some rows in the table dept

(postgres@[local]:5432) [test] > insert into dept (select generate_series(0,200000));
INSERT 0 200001
(postgres@[local]:5432) [test] >

We can verify that rows are not in the base table

(postgres@[local]:5432) [test] > select count(*) from  only dept ;
 count
-------
     0
(1 row)

(postgres@[local]:5432) [test] >

But that row are in the partitions

(postgres@[local]:5432) [test] > select count(*) from  dept ;
 count
--------
 200001
(1 row)

What we can also observe it that rows are uniformly distributed among partitions. This distribution is automatically done by the hash algorithm.

(postgres@[local]:5432) [test] > select count(*) from  only dept_1 ;
 count
-------
 19982
(1 row)
                                   
(postgres@[local]:5432) [test] > select count(*) from  only dept_2 ;
 count
-------
 20199
(1 row)

(postgres@[local]:5432) [test] > select count(*) from  only dept_3 ;
 count
-------
 19770
(1 row)

(postgres@[local]:5432) [test] > select count(*) from  only dept_5 ;
 count
-------
 20068
(1 row)

(postgres@[local]:5432) [test] >
 

Cet article Hash Partitioning in PostgreSQL 11 est apparu en premier sur Blog dbi services.

What is the maximum in list size in PostgreSQL?

Fri, 2018-03-23 04:53

Yesterday, while being at a customer, an interesting question popped up: What is the maximum of in list values in PostgreSQL? I couldn’t answer although I never read somewhere that there is a limit. The following is for fun only and I am not saying that creating huge in lists is a good idea. Lets go.

The version I tested is PostgreSQL 10:

postgres=# select version(), now();
                                                  version                                                   |              now              
------------------------------------------------------------------------------------------------------------+-------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit | 2018-03-21 18:29:50.269369+01
(1 row)

postgres=# create table t1 ( a varchar(10));
CREATE TABLE

We will use a very simple bash script to build the in list and execute the result in PostgreSQL:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

statement='select * from t1 where a in ('

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        statement="${statement} '${i}',"
    elif [ "${i}" == "${count}" ]; then
        statement="${statement} '${i}');"
    fi
done

psql -c "${statement}" postgres

Lets start with 100:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100
 a 
---
(0 rows)

Time: 0.983 ms

1000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000
 a 
---
(0 rows)

Time: 1.525 ms

10000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 10000
 a 
---
(0 rows)

Time: 11.549 ms

… takes a much longer (because of the bash script which fully occupies my virtual core):

./t.sh: line 15: /u01/app/postgres/product/10/db_0/bin/psql: Argument list too long

So there at least is a limit with psql. Lets try by creating a sql script and execute that instead:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

echo 'select * from t1 where a in (' > ttt.sql

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        echo  "'${i}'," >> ttt.sql
    elif [ "${i}" == "${count}" ]; then
        echo "'${i}');" >> ttt.sql
    fi
done

psql -f ttt.sql postgres

This way of stringing together the statement is much more efficient than building the list by concatenating everything into one variable. Does it still work?

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100000
 a 
---
(0 rows)

Time: 155.986 ms

Not a problem, one more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000000
 a 
---
(0 rows)

Time: 14211.766 ms (00:14.212)

Still works. So now we could say: lets stop, who in the world will pass one million values into an in list. On the other hand, lets have fun and double:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 2000000
 a 
---
(0 rows)

Time: 3993.091 ms (00:03.993)

One more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 3000000
psql:ttt.sql:3000001: ERROR:  out of memory
DETAIL:  Failed on request of size 524288.
Time: 3026.925 ms (00:03.027)

Ok, now I am hitting some limits, but probably not those of PostgreSQL. I’ll test further when I have more time for that :)

 

Cet article What is the maximum in list size in PostgreSQL? est apparu en premier sur Blog dbi services.

Local partitioned indexes in PostgreSQL 11

Thu, 2018-03-22 02:23

When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in PostgreSQL 10.

A very simple list partitioned table:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

In PostgreSQL 10 what happens when we create an index on the partitioned table?

postgres=# create index i_test on part (a);
ERROR:  cannot create index on partitioned table "part"

You just can not do it. But you can create indexes on the partitions directly:

postgres=# create index i_test_1 on part_1 (a);
CREATE INDEX
postgres=# create index i_test_2 on part_2 (a);
CREATE INDEX

Lets do the same test with PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Try to create the index on the partitioned table:

postgres=# create index i_test on part (a);
CREATE INDEX
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_a_idx" btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_a_idx" btree (a)

The index is cascaded down to all the partitions in PostgreSQL 11 which is really nice. As a side effect of this, when you try this in PostgreSQL 10:

postgres=# alter table part add constraint part_pk primary key(a,list);
ERROR:  primary key constraints are not supported on partitioned tables
LINE 1: alter table part add constraint part_pk primary key(a,list);
                             ^

… you will get an error message telling you that primary keys are not supported on partitioned tables. The same applies here, you can do that on the partitions directly:

postgres=# alter table part_1 add constraint part1_pk primary key(a,list);
ALTER TABLE
postgres=# alter table part_2 add constraint part2_pk primary key(a,list);
ALTER TABLE

Now in PostgreSQL 11 this works as well:

postgres=# alter table part add constraint part_pk primary key(a,list);
ALTER TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "part_pk" PRIMARY KEY, btree (a, list)
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_pkey" PRIMARY KEY, btree (a, list)
    "part_1_a_idx" btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_pkey" PRIMARY KEY, btree (a, list)
    "part_2_a_idx" btree (a)

Quite some improvements to show up in PostgreSQL 11.

 

Cet article Local partitioned indexes in PostgreSQL 11 est apparu en premier sur Blog dbi services.

pg_basebackup and redirecting progress messages to a file

Wed, 2018-03-21 11:32

Recently I came over that commit and wondered what that is about. The answer is quite simple but I didn’t know that this issue existed. Basically it is about how progress messages are written to screen and how they are written to a file. Lets have a look.

When your run pg_basebackup with progress messages and in verbose mode the output looks like this:

postgres@pgbox:/home/postgres/ [PG10] pg_basebackup --pgdata=/var/tmp/aa --verbose --progress 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/30000028 on timeline 1
pg_basebackup: starting background WAL receiver
593320/593320 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 4/30000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

You’ll notice that the highlighted line always is overwritten on screen until we reach one hundred percent. Looking at that line when pg_basebackup is running will give you an estimate on how long it will take and you will see which file it is currently working on. When you do the same thing but kick it in the background like this:

postgres@pgbox:/home/postgres/ [PG10] pg_basebackup --version
pg_basebackup (PostgreSQL) 10.0 
postgres@pgbox:/home/postgres/ [PG10] mkdir /var/tmp/aa
postgres@pgbox:/home/postgres/ [PG10] nohup pg_basebackup --pgdata=/var/tmp/aa --verbose --progress  > /tmp/a.log 2>&1  &

… you will have the same output in the log file:

postgres@pgbox:/home/postgres/ [PG10] cat -f /tmp/a.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/28000028 on timeline 1
pg_basebackup: starting background WAL receiver
593315/593315 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 4/28000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

Somehow that was not considered very useful so the commit mentioned above changed that:

postgres@pgbox:/home/postgres/ [PGDEV] pg_basebackup --version
pg_basebackup (PostgreSQL) 11devel
postgres@pgbox:/home/postgres/ [PGDEV] nohup pg_basebackup --pgdata=/var/tmp/aa --verbose --progress  > /tmp/a.log 2>&1  &
postgres@pgbox:/home/postgres/ [PGDEV] cat /tmp/a.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_29846"
     0/184659 kB (0%), 0/1 tablespace (/var/tmp/aa/backup_label           )
  1705/184659 kB (0%), 0/1 tablespace (/var/tmp/aa/base/1/1249            )
  4697/184659 kB (2%), 0/1 tablespace (/var/tmp/aa/base/1/2657            )
  8395/184659 kB (4%), 0/1 tablespace (/var/tmp/aa/base/13276/1255        )
 20601/184659 kB (11%), 0/1 tablespace (/var/tmp/aa/base/13277/2670        )
 30614/184659 kB (16%), 0/1 tablespace (/var/tmp/aa/base/16395/2607_vm     )
 45367/184659 kB (24%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
 54743/184659 kB (29%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
 74327/184659 kB (40%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
118807/184659 kB (64%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
146647/184659 kB (79%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
175197/184659 kB (94%), 0/1 tablespace (/var/tmp/aa/base/16395/16432       )
184668/184668 kB (100%), 0/1 tablespace (/var/tmp/aa/global/pg_control      )
184668/184668 kB (100%), 0/1 tablespace (/var/tmp/aa/global/pg_control      )
184668/184668 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/E000168
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

When you redirect the output into a file you can see much more steps in the log file compared to what we saw before (only a single line which is overwritten all the time). Seems to be a good change.

 

Cet article pg_basebackup and redirecting progress messages to a file est apparu en premier sur Blog dbi services.

PostgreSQL 11: Procedures and transaction control

Tue, 2018-03-20 11:58

Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top of that there was another commit which enables transaction control inside procedures which can be quite interesting. Lets see how that works.

Up to PostgreSQL 10 the only choice to have something like a procedure is to create a function returning void:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   RAISE NOTICE 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# set client_min_messages = 'NOTICE';
SET
postgres=# select dummy_func(1);
NOTICE:  id is 1
 dummy_func 
------------
 
(1 row)

When you tried to do something like this it was not possible:

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE dummy_proc (id int) AS $$

But now in the PostgreSQL development version you can do it (CREATE PROCEDURE):

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call dummy_proc(1);
NOTICE:  id is 1
CALL

Also notice that you need to use call to execute a procedure. Using select as you would do it for a function will not work:

postgres=# select dummy_proc(1);
ERROR:  dummy_proc(integer) is a procedure
LINE 1: select dummy_proc(1);
               ^
HINT:  To call a procedure, use CALL.

On top of the commit which introduced procedures there was another one (see the beginning of this post) which introduced transaction control for procedures. As the name implies this can be useful when you want to control transactions inside the procedure, e.g.:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;

When you execute this it will violate the primary key with the third insert:

postgres=# call dummy_proc2(1);
NOTICE:  id is 1
NOTICE:  id is 2
NOTICE:  id is 2
ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(2) already exists.
CONTEXT:  SQL statement "insert into t1 (a) values (l_id)"
PL/pgSQL function dummy_proc2(integer) line 13 at SQL statement

… but because we can now commit (or rollback) in between we did not lose all the records:

postgres=# select * from t1;
 a 
---
 1
 2
(2 rows)

Trying to do the same with a function in PostgreSQL 10 will not work:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE FUNCTION dummy_func2 (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select dummy_func2(1);
NOTICE:  id is 1
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dummy_func2(integer) line 7 at SQL statement
postgres=# select * from t1;
 a 
---
(0 rows)

Nice features …

 

Cet article PostgreSQL 11: Procedures and transaction control est apparu en premier sur Blog dbi services.

redhat event Ansible Automates Zurich

Tue, 2018-03-20 10:08

Here are some fresh personal impressions from the “Ansible Automates Zurich” half day event organized by redhat.
There was a overwhelming interest for this event. About 200 people filled the room and there were many more on the waiting list.
The event started with a networking lunch. My impression was, that the visitors were not only interested in the food, but also gaining some practical knowledge about Ansible. Most of them are already using Ansible.

Eduard Modalek from redhat opened the Event. He was very impressed with the interest in the event.
AnsibleAutomates ZurichSacha Dubois who is a great speaker with solid technical background gave an introduction about Ansible and the impact to your team and enterprise. “Ansible loves the repetitive work your people hate.”

Ansible loves the repetitive work your people hate

Ansible community downloads

The strength of Ansible is the community. Enjoy the  statistics. Ansible has

  • more than 27’000 stars on GitHub
  • over 1250 modules and
  • more than 500’000 downloads a month

 

Christian Sanabria and Marc Frauenfelder from ipt – Innovation Process Technology showed how they do Continuous Delivery by combining Jenkins Pipeline with the Ansible Tower API for provisioning. They published the code in GitHub:
https://github.com/mfrauenfelder/ansible/blob/master/Jenkinsfile

After the break Rob McMahon, redhat Director Cloud EMEA, who had already presented a day before at the Ansible Automates Netherlands event, showed his visions about empowering customers with Ansible.  The future are automated organizations and Ansible is the right tool to automate everything and help to overcome the silo character of cross functional teams.

Ansible Automates Organizations
Another customer case has been shown by Nicolas Christener and Lukas Grossar from Adfinis SyGroup AG. They fully automated the roll out of desktop clients.

Roland Wolters from redhat reveals what is coming in March 2018 with Ansible 2.5

  • Networking now first level connection “plugin”- credentials just like with any other connections
  • Role versioning
  • become on Windows features NT AUTHORITY\System and others
  • new loop mechanism ( no with_loops anymore)
  • fact namespaces

He also explained the differences between Ansible Tower and  the open source project AWX.

The highlight at the end was a live demo which deployed a WordPress environment in the Microsoft Azure cloud. Roland Wolters and Sacha Dubois demonstrated it successfully. Congratulations ! If you have a valid subscription at Microsoft Azure you can try it out https://github.com/sa-ch/ansible-azure-deployment

Ansible deploy WordPress on Microsoft AzureThere has been time for drinks and networking afterwards.
I spoke with Eduard Modalek. The organization was a big challenge, so many people wanted to attend this event and this was the biggest room they could get at such short notice in Zürich. He said that in future there will be more events like this, and perhaps some workshops with time for the audience to exchange ideas.

 

Cet article redhat event Ansible Automates Zurich est apparu en premier sur Blog dbi services.

SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended?

Tue, 2018-03-20 07:18

This article is an additional article to my precedent about “SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

The goal is to see after I suspend the rebuild, if I can run a traditional rebuild without the option RESUME.

 

I start an index rebuild with the option Resumable:

ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction]
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

 

In a new query window, I stopped the rebuild:

USE [WideWorldImportersDW]
GO
ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
PAUSE
GO

 

As you can see, the index rebuild is stopped:
riro2_00

 

I have a look on the index state with the DMV sys.index_resumable_operations:
riro2_01

 

To restart, I run the rebuild without the RESUME or ABORT options:
riro2_02

As you can see, the query does not work and you have this error message:
Msg 10637, Level 16, State 1, Line 14
Cannot perform this operation on ‘object’ with ID 430624577 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

The standard rebuild will not change the status of the resumable rebuild and begin from scratch in order to do the rebuild from beginning.
Finally, the only way to rebuild the index is really to use the RESUME option and continue where it’s stopped.

 

Cet article SQL Server 2017: Can I run a “standard” rebuild on the index after a Resumable Online Index Rebuild is suspended? est apparu en premier sur Blog dbi services.

SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild?

Tue, 2018-03-20 06:14

Last week, I presented this option in our internal event, the dbi xChange.

My colleague David Barbarin asked me this question, what’s append if an interruption occurs (reboot, SQL Server service stopped,…) during a Resumable Online Index Rebuild?

I was not able to answer because I forgot to do this test.

The new enhancement for an index rebuild provides the capability to suspend, resume or abort an online index operation (msdn reference). I always use the option PAUSE to see how it works but never with a break.

Now I do the test to have the answer. It’s never too late! ;-)

To do my demo, I downloaded the Microsoft example database WideWorldImportersDW from github.

I choose the index NCI_dbo_Transaction in the Transaction table.

riro_00

As you can see with this query, the index is big enough to have the time to interrupt the rebuild.

I run the rebuild with this command:

ALTER INDEX [NCI_dbo_Transaction] ON [dbo].[Transaction] 
REBUILD WITH (RESUMABLE = ON, ONLINE = ON)
GO

After killing the SQL Server task in the task manager, I have this message in SSMS:
riro_01
Of course, the server is no more available…

I start the SQL Server service and have a look on the index state with the DMV sys.index_resumable_operations:
riro_02
As you can see the index is on state PAUSE with 19% completed.

To restart, I run the rebuild with the RESUME option:
riro_03

If the index rebuild is interrupted, you don’t need to restart from scratch.
The rebuild is in a PAUSE state and then you can decide or not to continue the rebuild.
Very clever option, isn’t it?

Don’t hesitate to go on David’s blog to read how this option works with AlwaysOn:
https://blog.dbi-services.com/sql-server-2017-high-availability-and-resumable-online-indexes/

 

Cet article SQL Server 2017: What’s happens with an interruption during a Resumable Online Index Rebuild? est apparu en premier sur Blog dbi services.

Finally you will be able to use exit and quit in psql

Tue, 2018-03-20 05:39

When giving the PostgreSQL DBA Essentials workshop one of the main issues people have is how they can exit psql. Even on stackoverflow this is a popular topic. The good news for people who still don’t like to use “\q” here is the commit that will add additional options to quit/exit from psql.

Up to PostgreSQL 10 what you can usually see is something like this:

postgres@pgbox:/home/postgres/ [PG10] psql -X postgres
psql (10.0)
Type "help" for help.

postgres=# exit
postgres-# exit
postgres-# quit
postgres-# let me out, what do I need to to?
postgres-# 

Starting with PostgreSQL 11 you can either use “quit”:

postgres@pgbox:/home/postgres/ [PGDEV] psql -X postgres
psql (11devel)
Type "help" for help.

postgres=# quit
postgres@pgbox:/home/postgres/ [PGDEV] 

… or “exit”:

postgres@pgbox:/home/postgres/ [PGDEV] psql -X postgres
psql (11devel)
Type "help" for help.

postgres=# exit
postgres@pgbox:/home/postgres/ [PGDEV] 

I am pretty sure MacBook users will love that :)

 

Cet article Finally you will be able to use exit and quit in psql est apparu en premier sur Blog dbi services.

Result cache invalidation caused by DML locks

Sun, 2018-03-18 16:03

In a previous post about Result Cache, I was using ‘set autotrace on’ in my session to see when the result cache hit saves the logical reads. I got a comment, by e-mail and with full test case, mentioning that the invalidation I had when doing some DML does not occur when autotrace is set to on. I reproduced the problem, on 18c because I use my cloud services to do my tests, but it works the same way in 12c.

I’ll focus here on DML which does not modify any rows from the table the result depends on, because this is the case that depends on the session transaction context. When rows are modified, the result is always invalidated.

For each test here I’ll flush the result cache:

SQL> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if;
PL/SQL procedure successfully completed.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
no rows selected

And I begin each test with the result cache loaded by a query.

DML on the dependent table

The result or the query is loaded into the cache, with a dependency on the SCOTT.DEPT table:

SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

Now, I’m executing some DML on this DEPT table:

SQL> connect scott/tiger@//localhost/PDB1
Connected.
SQL> delete from DEPT where DEPTNO>40;
0 rows deleted.

This delete does not touch any row, but declares the intention to modify the table with a Row eXclusive lock (TM mode=3):

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 0 0 0 21 SCOTT oracle 7772 3 5 DEPT

Note that the transaction ID is all zeros. Logically, we are in a transaction, as we have a lock that will be released only at the end of the transaction (commit or rollback). But, as we didn’t modify any block yet, there is no entry in the transaction table:

SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
no rows selected

I commit this transaction:

SQL> commit;
Commit complete.

and check the result cache objects:

SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

This look good: nothing was modified so there is no reason to invalidate the result. However, in the previous post I referenced, a similar test invalidated the cache even when no rows were touched. And a reader remarked that this occured only when I previously run a query with ‘autotrace on’. Without autotrace, the behaviour is like what I show here above: no invalidation when no rows is modified.

Same in an already started transaction

The difference is that the ‘autotrace on’ runs an ‘explain plan’, filling the plan table, which means that a transaction was already started. Here I run autotrace on a completely different query:

SQL> set autotrace on explain
Autotrace Enabled
Displays the execution plan only.
SQL> select * from dual;
DUMMY
-----
X
 
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
Plan hash value: 272002086
 
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
 
SQL> set autotrace off;
Autotrace Disabled

Following this, I have no locked objects, but a transaction has been initiated:

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
no rows selected
 
SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
XIDUSN XIDSLOT XIDSQN START_TIME USED_UREC
------ ------- ------ ---------- ---------
9 18 709 03/18/18 20:30:09 2

Here is the big difference: I have a transaction ID here.
Now doing the same as before, a delete touching no rows:

SQL> delete from DEPT where DEPTNO>40;
0 rows deleted.

When I query the lock objects, they are now associated to a non-zero transaction ID:

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 9 18 709 21 SCOTT oracle 7772 3 5 DEPT

Here is the difference, now the result cache has been invalidated:

SQL> commit;
Commit complete.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 1
1 Result Invalid select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

DML on another table – RS though referential integrity

CaptureLocksThis gives the idea that the invalidation is not really triggered by actual modifications, but at commit time from the DML locks when associated with a transaction.
And some DML on tables may acquire a Row-S or Row-X lock on the tables linked by referential integrity. This has changed a lot though versions – look at the slide on the right.

I start in the same situation, with the result cache loaded, no locked objects, but a transaction that has started:

SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0
 
SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
no rows selected
 
SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
 
XIDUSN XIDSLOT XIDSQN START_TIME USED_UREC
------ ------- ------ ---------- ---------
8 31 766 03/18/18 20:30:10 2

I delete from the EMP table and if you are familiar with the SCOTT schema, you know that it has a foreign key to DEPT.

SQL> delete from EMP where DEPTNO>=40;
0 rows deleted.

The delete acquires a Row eXclusive lock on EMP even when there are no rows deleted (DML locks are about the intention to modify rows). And from the table above, it acquires a Row Share (mode=2) on the parent table:

SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 8 31 766 21 SCOTT oracle 7772 2 5 DEPT
73378 8 31 766 21 SCOTT oracle 7772 3 5 EMP

I commit and check the result cache:

SQL> commit;
Commit complete.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
 
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
-- ---- ------ ---- -------- -------------
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 0
1 Result Published select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

No invalidation here, which makes sense because Row Share is not an intention to write.

DML on another table – RS though referential integrity

I do the same here, but with an insert on EMP which acquires a Row eXclusive on the parent table.

SQL> insert into EMP(EMPNO,DEPTNO)values(9999,40);
1 row inserted.
 
SQL> select * from v$locked_object natural join (select object_id,object_name from user_objects)
 
OBJECT_ID XIDUSN XIDSLOT XIDSQN SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE CON_ID OBJECT_NAME
--------- ------ ------- ------ ---------- --------------- ------------ ------- ----------- ------ -----------
73376 10 32 560 21 SCOTT oracle 7772 3 5 DEPT
73378 10 32 560 21 SCOTT oracle 7772 3 5 EMP
 
SQL> select xidusn,xidslot,xidsqn,start_time,used_urec from v$transaction
 
XIDUSN XIDSLOT XIDSQN START_TIME USED_UREC
------ ------- ------ ---------- ---------
10 32 560 03/18/18 20:30:10 4
 
SQL> commit;
Commit complete.
 
SQL> select id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id
---- ------ ------------------------------------------ ---- ---------------------- ----- -- --
ID TYPE STATUS NAME CACHE_ID INVALIDATIONS
0 Dependency Published SCOTT.DEPT SCOTT.DEPT 1
1 Result Invalid select /*+ result_cache */ count(*) from DEPT 50vtzqa5u0xcy4bnh6z600nj1u 0

Here, DEPT has been invalidated after the insert on EMP. There were no modifications on DEPT, but the result cache is not directly tracking the modifications, but rather the intention of modification. And then, the implementation of the result cache invalidation tracks, at commit, the Row eXclusive locks when they are related to a know transaction. You can have the same result if, from a transaction that has already started, you run a simple:

SQL> lock table DEPT in row exclusive mode;
Lock succeeded.

This means that there are many reasons why the result cache may be invalid even when the objects in the ‘Dependency’ are not modified. Be careful, invalidations and cache misses are expensive and do not scale on high load. And because of locks through referential integrity, this can happen even on static tables. Let’s take an example. in an order entry system, you may think that the ‘products’ table is a good candidate for result cache – updated twice a year but read all the times. But now that you know that inserts on child tables, such as the order table, will invalidate this cache, you may think again about it.

 

Cet article Result cache invalidation caused by DML locks est apparu en premier sur Blog dbi services.

Automatic Block Media Recovery in a DataGuard

Sat, 2018-03-17 13:39

With Oracle 12.2, in a Data Guard environment corrupted data blocks can be automatically replaced with uncorrupted copies of those blocks.
There are just some requirements:
• The physical standby database must be operating in real-time query mode, which requires an Oracle Active Data Guard license.
• The physical standby database must be running real-time apply.
Automatic block media recovery works in two directions depending on whether the corrupted blocks are encountered on the primary or on the standby.
This recovery can happen if corrupted data are encountered in the primary database or in the secondary database.
In this blog we are going to see how this feature works.
Below the configuration we are using

DGMGRL> show configuration;

Configuration - ORCL_DR

  Protection Mode: MaxAvailability
  Members:
  ORCL_SITE  - Primary database
    ORCL_SITE1 - Physical standby database
    ORCL_SITE2 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 19 seconds ago)

DGMGRL>

We can verifiy the status of our databases

DGMGRL> show database  'ORCL_SITE' ;

Database - ORCL_SITE

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    ORCL

Database Status:
SUCCESS

DGMGRL> show database  'ORCL_SITE1' ;

Database - ORCL_SITE1

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 5.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCL

Database Status:
SUCCESS

DGMGRL> show database  'ORCL_SITE2' ;

Database - ORCL_SITE2

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    ORCL

Database Status:
SUCCESS

DGMGRL>

The feature works for any protection mode, but in our case the tests are done with a MaxAvailability mode

If corrupt data blocks are on the primary database, then the primary automatically searches for good copies of those blocks on a standby and, if they are found, has them shipped back to the primary.
This only requirement is that the primary requires a LOG_ARCHIVE_DEST_n to the standby. That already should be the case in a Data Guard environment.

If corrupted block is located on the standby, the standby will automatically request uncorrupted copies of those blocks to the primary. The condition for this mechanism to work is
• The LOG_ARCHIVE_CONFIG parameter is configured with a DG_CONFIG list and a LOG_ARCHIVE_DEST_n parameter is configured for the primary database.
or
• The FAL_SERVER parameter is configured and its value contains an Oracle Net service name for the primary database.

In this demonstration we will simulate data corruption in the primary database. But the scenario is the same for a corrupted blocks at standby side and will work same.
From the primary we can verify that LOG_ARCHIVE_DEST_n is set.

SQL> select dest_name,DESTINATION,status from v$archive_dest where destination is not null;

DEST_NAME            DESTINATION                    STATUS
-------------------- ------------------------------ ---------
LOG_ARCHIVE_DEST_1   USE_DB_RECOVERY_FILE_DEST      VALID
LOG_ARCHIVE_DEST_2   ORCL_SITE1                     VALID
LOG_ARCHIVE_DEST_3   ORCL_SITE2                     VALID

For the demonstration let’s consider a table of user SCOTT in a tablespace mytab

SQL> select table_name,tablespace_name from dba_tables where owner='SCOTT' and table_name='EMPBIS';

TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
EMPBIS          MYTAB

SQL>

Now let’s identify blocks for the table on the primary and let’s corrupt them.

SQL> select * from (select distinct dbms_rowid.rowid_block_number(rowid)  from scott.empbis);

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 131

SQL>

And then let’s run following command to corrupt corresponding blocks.

[oracle@primaserver ORCL]$ dd of=/u01/app/oracle/oradata/ORCL/mytab01.dbf bs=8192 seek=131 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000315116 s, 26.0 MB/s
[oracle@primaserver ORCL]$

In a normal environment, accessing to corrupted data by a SELECT will return errors.
But in our case on the primary if we flush the buffer_cache, and and we do a select on the table, rows are returned without errors.

13:41:18 SQL> alter system flush buffer_cache;

System altered.

13:41:22 SQL> select * from scott.empbis;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10


14 rows selected.

13:41:27 SQL>

Indeed Oracle automatically recovered corrupted blocks. And in the primary alert log at the same time we can see that a recovery was done.

ALTER SYSTEM: Flushing buffer cache inst=0 container=0 global
2018-01-26T13:41:26.540640+01:00
Hex dump of (file 2, block 131) in trace file /u01/app/oracle/diag/rdbms/orcl_site/ORCL/trace/ORCL_ora_3359.trc

Corrupt block relative dba: 0x00800083 (file 2, block 131)
Completely zero block found during multiblock buffer read

Reading datafile '/u01/app/oracle/oradata/ORCL/mytab01.dbf' for corruption at rdba: 0x00800083 (file 2, block 131)
Reread (file 2, block 131) found same corrupt data (no logical check)
Automatic block media recovery requested for (file# 2, block# 131)
2018-01-26T13:41:26.545798+01:00
Corrupt Block Found
         TIME STAMP (GMT) = 01/26/2018 13:41:25
         CONT = 0, TSN = 7, TSNAME = MYTAB
         RFN = 2, BLK = 131, RDBA = 8388739
         OBJN = 74352, OBJD = 74352, OBJECT = EMPBIS, SUBOBJECT =
         SEGMENT OWNER = SCOTT, SEGMENT TYPE = Table Segment
2018-01-26T13:41:27.002934+01:00
Automatic block media recovery successful for (file# 2, block# 131)
2018-01-26T13:41:27.005015+01:00
Automatic block media recovery successful for (file# 2, block# 131)

Conclusion:
We have seen, that an active DataGuard environment may help with corrupted data.

 

Cet article Automatic Block Media Recovery in a DataGuard est apparu en premier sur Blog dbi services.

Connect-times to the DB suddenly become very slow using sqlcl

Mon, 2018-03-12 06:26

I recently wrote a couple of sql-scripts which had to run on all of my customer’s DBs. The sql-scripts had to be started from a Linux-client, which does not have any Oracle client software installed. So I thought of using sqlcl (see http://www.oracle.com/technetwork/developer-tools/sqlcl/downloads/index.html), because there is no need to “install” something then. All I needed was an installed JRE on the Linux-machine. Fortunately that was available. So I downloaded the newest version of sqlcl and unzipped it. Initially I had an issue with the timezone when connecting:


ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

I could workaround this by adding “AddVMOption -Doracle.jdbc.timezoneAsRegion=false” in <sqlcl-home>/bin/sql:


#
# set up the main arguments for java.
#
function setupArgs {
...
AddVMOption -Doracle.jdbc.timezoneAsRegion=false
....

When looping over the databases the connect-times suddenly became very slow. I had to wait for up to 2 minutes to setup a connection to the database. After doing strace on the sqlcl-connects I saw the reason for the slow connects:

The Oracle JDBC driver requires a random number for encrypting the connect string. By default this random number is generated from /dev/random. If the number of entropy-entries (cat /proc/sys/kernel/random/entropy_avail) becomes too low, the access to /dev/random is blocked until enough entropy-entries are available again. The number of entropy-entries is generated through some random noise on the machine (drivers for keyboard, mouse, network, etc. generate the entries). If there is no driver-activity then the entropy-pool may become empty.

The workaround is to
– either artificially generate some driver load (examples on how to do it are in referenced links at the end of the blog)
– or use /dev/urandom instead of /dev/random

The first workaround was not feasible in my case. So I had to use the more unsecure workaround of using /dev/urandom. I.e. I updated <sqlcl-home>/bin/sql again and added “AddVMOption -Djava.security.egd=file:///dev/urandom”:


#
# set up the main arguments for java.
#
function setupArgs {
...
AddVMOption -Djava.security.egd=file:///dev/urandom
....

Afterwards my sqlcl-connects were fast again.

See the following links for more info:

http://www.usn-it.de/index.php/2009/02/20/oracle-11g-jdbc-driver-hangs-blocked-by-devrandom-entropy-pool-empty
https://stackoverflow.com/questions/2327220/oracle-jdbc-intermittent-connection-issue
https://oraganism.wordpress.com/2015/09/20/slow-jdbc-connections-strace-and-random-numbers
http://www.oracle.com/technetwork/database/database-appliance/documentation/oda-eval-comparing-performance-1895230.pdf –> see page 7: “Specify non-blocking random number generators”

Remark: I updated the Oracle Community entry https://community.oracle.com/thread/4073170 with that information as well.

 

Cet article Connect-times to the DB suddenly become very slow using sqlcl est apparu en premier sur Blog dbi services.

Pages