Docker-images: database is extremely slow

[agntdv]

Hi,

My docker container with Oracle EE 12c is extremely slow, please advise where to look?

Steps to reproduce are as follows:

docker run --name my-ora12ce -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=oracle -v /opt/oracle/oradata:/opt/oracle/oradata oracle/database:12.2.0.1-ee

define m_dir='/opt/oracle/oradata/'

alter session set "_oracle_script"=true;

create tablespace test_8k
blocksize 8K
datafile '&m_dir.test_8k.dbf' size 193m reuse
extent management local 
uniform size 1M
segment space management manual
;
create user test_user identified by "1" container=all;
grant create session to test_user container=all;
grant connect to test_user container=all;
grant resource to test_user container=all;
grant dba to test_user container=all;
grant execute on dbms_stats to test_user container=all;
alter user test_user quota unlimited on test_8k;
alter user test_user default tablespace test_8k;

and then connect as test_user

CREATE TABLE t1
    NOLOGGING
    AS
        WITH generator AS (
            SELECT /*+ materialize */
                DISTINCT
                trunc(dbms_random.value(0,1001) ) AS c1,
                trunc(dbms_random.value(1001,2001) ) AS c2
            FROM
                all_objects
            WHERE
                ROWNUM <= 1E4
        ) SELECT
            g.c1,
            g.c2,
            trunc(dbms_random.value(0,11) ) AS c3,
            trunc(dbms_random.value(0,11) ) AS c4
        FROM
            generator g where rownum < 1e4;

the this creation takes ~800 seconds, with the top wait event 'PGA memory operation'
though in Oracle 11g2 which runs directly on the same host it is less then 30 seconds, the host machine is RHEL 7.

Thank you.

Azat.

[gvenzl]

Hi @agntdv, PGA memory operation is the wait event shown for the Unix mmap and munmap calls. Basically, it show the wait for a memory segment allocation or de-allocation request. With that in mind, the question is why Docker takes that long to perform those operations when they are much quicker outside of a container. First and foremost, can you please share your docker info with us so that I can have @Djelibeybi double check your docker configuration for any well-known issues.

[agntdv]

@gvenzl

]# docker info
Containers: 1
 Running: 1
 Paused: 0
 Stopped: 0
Images: 26
Server Version: 17.05.0-ce
Storage Driver: devicemapper
 Pool Name: docker-253:0-12545678-pool
 Pool Blocksize: 65.54kB
 Base Device Size: 107.4GB
 Backing Filesystem: xfs
 Data file: /dev/loop0
 Metadata file: /dev/loop1
 Data Space Used: 15.57GB
 Data Space Total: 107.4GB
 Data Space Available: 91.8GB
 Metadata Space Used: 9.396MB
 Metadata Space Total: 2.147GB
 Metadata Space Available: 2.138GB
 Thin Pool Minimum Free Space: 10.74GB
 Udev Sync Supported: true
 Deferred Removal Enabled: false
 Deferred Deletion Enabled: false
 Deferred Deleted Device Count: 0
 Data loop file: /var/lib/docker/devicemapper/devicemapper/data
 Metadata loop file: /var/lib/docker/devicemapper/devicemapper/metadata
 Library Version: 1.02.140-RHEL7 (2017-05-03)
Logging Driver: json-file
Cgroup Driver: cgroupfs
Plugins:
 Volume: local
 Network: bridge host macvlan null overlay
Swarm: inactive
Runtimes: runc
Default Runtime: runc
Init Binary: docker-init
containerd version: 9048e5e50717ea4497b757314bad98ea3763c145
runc version: 9c2d8d184e5da67c95d601382adf14862e4f2228
init version: 949e6fa
Security Options:
 seccomp
  Profile: default
Kernel Version: 3.10.0-693.2.2.el7.x86_64
Operating System: Red Hat Enterprise Linux
OSType: linux
Architecture: x86_64
CPUs: 2
Total Memory: 11.57GiB
Name: machine1.myco.com
ID: RMDZ:FABA:FHED:XGHH:6HX3:V427:AF3T:FZMI:JRKX:RCAW:KFEU:MACK
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Experimental: false
Insecure Registries:
 127.0.0.0/8
Live Restore Enabled: false

WARNING: devicemapper: usage of loopback devices is strongly discouraged for production use.
         Use `--storage-opt dm.thinpooldev` to specify a custom block storage device.

]# docker version
Client:
 Version:      17.05.0-ce
 API version:  1.29
 Go version:   go1.7.5
 Git commit:   89658be
 Built:        Thu May  4 22:10:29 2017
 OS/Arch:      linux/amd64

Server:
 Version:      17.05.0-ce
 API version:  1.29 (minimum version 1.12)
 Go version:   go1.7.5
 Git commit:   89658be
 Built:        Thu May  4 22:10:29 2017
 OS/Arch:      linux/amd64
 Experimental: false

]# cat /etc/*release*
Oracle Linux Server release 7.4
NAME="Oracle Linux Server"
VERSION="7.4"
ID="ol"
VERSION_ID="7.4"
PRETTY_NAME="Red Hat Enterprise Linux"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:4:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.4
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.4
NAME="Red Hat Enterprise Linux Server"
VERSION="7.2 (Maipo)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="7.2"
PRETTY_NAME="Red Hat Enterprise Linux"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:7.2:GA:server"
HOME_URL="https://www.redhat.com/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"
REDHAT_BUGZILLA_PRODUCT_VERSION=7.2
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="7.2"
Red Hat Enterprise Linux Server release 7.4 (Maipo)
Oracle Linux Server release 7.4
cpe:/o:oracle:linux:7:4:server

[gvenzl]

Hey @Djelibeybi, anything obvious that you spot that could or would cause lower performance for mmap operations? Any chance to figure out whether any kind of resource management is throttling the speed?

[gvenzl]

@Djelibeybi, ping :)

[Djelibeybi]

I have no input here. If you have Oracle Linux support, please open an SR so that our development team can look into it.

[jlbutler]

The above output indicates Docker 17.05.0-ce running on OL7.4, which is not a supported configuration. So we're not chasing phantoms, is it possible to update to latest supported Docker (version 17.06.2-ol)? I don't know of any specific issues in the engine which would explain this, but it'd be best to get to the latest supported configuration before getting into it further.

[gvenzl]

@agntdv, can you please upgrade your Docker to the version @jlbutler is requesting and retest this?

[gvenzl]

Closing due to lack of response.

[coldserenity]

People report that running this query solves the issue (for me it increased DB's performance by x15 on Fedora 27, stock docker):

ALTER SYSTEM SET disk_asynch_io = FALSE SCOPE = SPFILE;

[1] [Oracle XE 11g poor response time in queries](https://forums.docker.com/t/oracle-xe-11g-poor-response-time-in-queries/5209/2)
[2] [Oracle 11g docker SELECT very slow](https://stackoverflow.com/questions/37722685/oracle-11g-docker-select-very-slow)

[davidritter]

Hello!

We are also seeing this issue using the Oracle Database 12c container. It seems like it is slow specifically when making queries against system tables, i.e.

SELECT COUNT(*) FROM ALL_OBJECTS;

Using that call against the container hosted database takes minutes to execute where against our non containerized Oracle server it returns practically instantly.

I think this issue should be re-opened. Please let me know what information I can provide to help diagnose it. To start with here is our docker and host OS information:

[ritter@larch] > sudo docker version
Client:
 Version:         1.13.1
 API version:     1.26
 Package version: <unknown>
 Go version:      go1.8.3
 Git commit:      774336d/1.13.1
 Built:           Wed Mar  7 17:06:16 2018
 OS/Arch:         linux/amd64

Server:
 Version:         1.13.1
 API version:     1.26 (minimum version 1.12)
 Package version: <unknown>
 Go version:      go1.8.3
 Git commit:      774336d/1.13.1
 Built:           Wed Mar  7 17:06:16 2018
 OS/Arch:         linux/amd64
 Experimental:    false

[ritter@larch] > sudo docker info
Containers: 1
 Running: 1
 Paused: 0
 Stopped: 0
Images: 31
Server Version: 1.13.1
Storage Driver: overlay2
 Backing Filesystem: xfs
 Supports d_type: true
 Native Overlay Diff: true
Logging Driver: journald
Cgroup Driver: systemd
Plugins:
 Volume: local
 Network: bridge host macvlan null overlay
Swarm: inactive
Runtimes: docker-runc runc
Default Runtime: docker-runc
Init Binary: docker-init
containerd version:  (expected: aa8187dbd3b7ad67d8e5e3a15115d3eef43a7ed1)
runc version: N/A (expected: 9df8b306d01f59d3a8029be411de015b7304dd8f)
init version: N/A (expected: 949e6facb77383876aeff8a6944dde66b3089574)
Security Options:
 seccomp
  WARNING: You're not using the default seccomp profile
  Profile: /etc/docker/seccomp.json
Kernel Version: 3.10.0-693.21.1.el7.x86_64
Operating System: CentOS Linux 7 (Core)
OSType: linux
Architecture: x86_64
Number of Docker Hooks: 3
CPUs: 2
Total Memory: 3.702 GiB
Name: larch.cvo.roguewave.com
ID: UVTK:F7KU:JSRT:U42R:MPEI:MDPX:435N:L7DX:W3QO:AHIA:UWRI:7BZN
Docker Root Dir: /var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Registry: https://index.docker.io/v1/
Experimental: false
Insecure Registries:
 127.0.0.0/8
Live Restore Enabled: false
Registries: docker.io (secure)

[ritter@larch] > cat /etc/*release*
CentOS Linux release 7.4.1708 (Core)
Derived from Red Hat Enterprise Linux 7.4 (Source)
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

CentOS Linux release 7.4.1708 (Core)
CentOS Linux release 7.4.1708 (Core)
cpe:/o:centos:centos:7

Our container is being run with this command:

sudo docker run -d --restart always -e TZ=America/Los_Angeles -e ORACLE_PWD=<value> -p 1521:1521 -p 5500:5500 --name oracle-12.2.0.1  rw-oracle-12.2.0.1

Where the image is just adding some databases and users using the setup directory and extending the image oracle/database:12.2.0.1-se2

[davidritter]

I should also note I tried the
ALTER SYSTEM SET disk_asynch_io = FALSE SCOPE = SPFILE;
setting and did not observer that making any difference in execution time of the query.

[davidritter]

One other note, I see that this is ONLY happening with Oracle 12.2, 12.1 seems to work just fine (accessing ALL_OBJECTS seems to work just fine).

[tjlee]

Confirm. If to work with system catalogs Oracle in container is dramatically slow (in our tests 5-10 times).

[tjlee]

As a possible workaround one needs to check values for the following parameters in sqlplus:

SHOW PARAMETER FILESYSTEMIO_OPTIONS;
SHOW PARAMETER DISK_ASYNCH_IO;

Then disable async_io:

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=DIRECTIO SCOPE=SPFILE
/
ALTER SYSTEM SET DISK_ASYNCH_IO=FALSE SCOPE=SPFILE
/

and restart oracle (/etc/init.d/oracle-xe restart).

[tjlee]

@davidritter

I should also note I tried the
ALTER SYSTEM SET disk_asynch_io = FALSE SCOPE = SPFILE;
setting and did not observer that making any difference in execution time of the query.

In addition, you need to change FILESYSTEMIO_OPTIONS value to DIRECTIO.

[davidritter]

@tjlee I tried the addition configuration option you suggested (now using 19c) and I am still observing the same slow access to system tables. Is anyone else still observing this?

[tjlee]

@tjlee I tried the addition configuration option you suggested (now using 19c) and I am still observing the same slow access to system tables. Is anyone else still observing this?

For me it works for Oracle DB 12.1 & 12.2. I didn't check it out for 18 & 19.

[rhallier]

After applying the solution suggested by tjlee, I'm also observing the slowness problem with a 18c XE instance.

[KamilJedrzejuk]

Please see a repo https://github.com/KamilJedrzejuk/oracle18c-xe

[cristian-spiescu]

@davidritter, regarding:

@tjlee I tried the addition configuration option you suggested (now using 19c) and I am still observing the same slow access to system tables. Is anyone else still observing this?

Did you find a solution to run Oracle > 12 in a container, w/o perf issues?

[davidritter]

@tjlee. No I haven't. I would love to as we would much prefer to be using a newer version of the database, but the performance issue makes such a move impossible. I don't see any way to re-open this issue or draw more attention to it (assuming it exists and it's not just me).

[KamilJedrzejuk]

I think that open source like Postgresql etc have better support...

[cristian-spiescu]

Can someone (with "reopen powers") reopen this issue? Because the issue still exists.
Maybe this way it may be detected by someone that has some hints and/or by people from Oracle?

Meanwhile I'll try to see if the support services that my employer pays may do some good. I fear a long chain of discussions and escalations will happen. But who knows, maybe in the end some kind of solution will arise.

[Djelibeybi]

If you're having this or a similar problem, please open a new issue.

[KamilJedrzejuk]

We have 2020 whow want still use a heavy db like oracle...

[davidritter]

@KamilJedrzejuk I'm not sure either of those comments are very useful. Indeed we have many database technologies available to use, all having their advantages and disadvantages. In this case I would like to be using Oracle, which is why I posted a question in an Oracle project/forum.

@Djelibeybi I did refile this issue in August 2018 as https://github.com/oracle/docker-images/issues/959

[KamilJedrzejuk]

@davidritter So what Can I say good luck...