microsoft
Allow data directory to be mounted on tmpfs
This may possibly be solved by #10, #12, or #34, but I'm unable to tell at this stage. It is currently not possible to use tmpfs for the data directory.
Whilst this may seem a pretty weird thing to do, it's particularly helpful when using the docker image for continuous integration testing of a project where the database is wiped away at the start or end of every run anyway. In such a scenario one of the key desirables is the speed of the run and use of a tmpfs makes a huge difference to the speed of the runs.
I've had a look at extending the standard image to facilitate this, and while I can change the default data directory to another overlaid directory, it does not seem to support being moved to tmpfs:
2010 nicols@boysenberry:~> docker run -it -e ACCEPT_EULA=Y -e SA_PASSWORD=Passw0rd! --tmpfs /var/lib/mssql:rw,exec,suid,dev --entrypoint bash microsoft/mssql-server-linux
root@b4f910c32ee2:/# mount | grep mssql
tmpfs on /var/lib/mssql type tmpfs (rw,relatime)
root@b4f910c32ee2:/# mkdir /var/lib/mssql/data
root@b4f910c32ee2:/# ls -ld /var/lib/mssql/
drwxrwxrwt 3 root root 60 Jun 16 01:35 /var/lib/mssql/
root@b4f910c32ee2:/# ls -l /var/lib/mssql/
total 0
drwxr-xr-x 2 root root 40 Jun 16 01:35 data
root@b4f910c32ee2:/# /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/lib/mssql/data
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
root@b4f910c32ee2:/# /opt/mssql/bin/sqlservr
This is an evaluation version. There are [147] days left in the evaluation period.
Opening a second terminal session and trying to create a new DB:
root@b4f910c32ee2:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Passw0rd!' -Q "CREATE DATABASE example"
Msg 5123, Level 16, State 1, Server b4f910c32ee2, Line 1
CREATE FILE encountered operating system error 31(A device attached to the system is not functioning.) while attempting to open or create the physical file '/var/lib/mssql/data/example.mdf'.
Msg 1802, Level 16, State 4, Server b4f910c32ee2, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Confirmed on:
- MacOS running docker 17.06.0-ce-rc2; and
- Linux (Ubuntu) hosts running docker 17.03.0-ce.
I found a workaround for this that you might find useful - if you create a loopback filesystem on top of a file on top of a tmpfs filesystem, the database will work using it.
On your docker host:
# Create a 10GB tmpfs file system.
mount -t tmpfs -o size=10G tmpfs /ramcache
# Create a disk image inside of the tmpfs.
cd /ramcache
dd if=/dev/zero of=disk.img bs=1M count=10240
# Create a new loopback device from the disk image.
losetup /dev/loop0 disk.img
# Format as ext4.
mkfs.ext4 /dev/loop0
# Mount the loopback image as /data
mount /dev/loop0 /data
Then to use it with docker, pass it as a volume:
docker run --volume /data:/var/opt/mssql/data ...
I found this drops a snapshot restore of our test database from ~10 seconds to ~2 - makes a big difference when running a lot of tests!
I use the following Dockerfile for running SQL Server:
FROM debian as mssql-docker-tmpfs
ADD nodirect_open.c /
RUN apt update && apt install -y gcc && \
gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl && \
apt purge -y gcc
FROM mcr.microsoft.com/mssql/server:2017-latest
COPY --from=mssql-docker-tmpfs /nodirect_open.so /nodirect_open.so
RUN echo "/nodirect_open.so" >> /etc/ld.so.preload
ENV LD_PRELOAD=/nodirect_open.so
It's still a hack, but it works. For me, using the loop device workaround is difficult because our CI server may run multiple SQL Server containers at once, and also the CI runner doesn't have root privileges.
The original source which I got it from is here: https://github.com/t-oster/mssql-docker-zfs/blob/master/Dockerfile (note that the licence is GNU GPLv3, if you distribute images made from modified versions of the Dockerfile you must also offer to distribute the Dockerfile itself).
For me this version of SQL server starts up in 2.49 seconds, as opposed to 4.22 seconds for the official image.
(Edit: I should probably mention that this hack only works if you are not using snapshots)
Any update on this? It can greatly improve the performance of integration tests. See: https://www.stefanproell.at/2019/02/08/lightning-fast-integration-tests-with-docker-mysql-and-tmpfs/ https://vladmihalcea.com/how-to-run-integration-tests-at-warp-speed-with-docker-and-tmpfs/
The hacks above work, but it would be better to pass this responsibility to Docker without the need to solve used/unused loopback devices, creating directories, etc.
Encountered this issue, and the use case was on a CI/CD server as well. There should be an environmental variable that can be set to disable direct access to the filesystem so that this works cleanly.
I tried creating the database in a different directory but stil I get the error:
Msg 5123, Level 16, State 1, Line 1 CREATE FILE encountered operating system error 87(The parameter is incorrect.) while attempting to open or create the physical file
Copying the file manually to this folder does work
As of today, the only supported filesystems XFS and ext4. We have tmfs in our backlog but I dont have an immediate timeline to share.
@asztal shared a Dockerfile for SQL Server 2017 which didn't work for me for 2019 on K8S on AWS EKS and it took me a while to have it working so will share my setup:
Dockerfile:
FROM mcr.microsoft.com/mssql/server:2019-latest
ADD nodirect_open.c /
USER root
RUN apt-get update \
&& apt-get install -y gcc \
&& gcc -shared -fpic -o /nodirect_open.so nodirect_open.c -ldl
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*
RUN echo "/nodirect_open.so" >> /etc/ld.so.preload
Pod helm template:
apiVersion: v1
kind: Pod
metadata:
name: {{ .Values.services.db_sql.name }}
namespace: {{ .Values.namespace }}
labels:
app: {{ .Values.services.db_sql.name }}
annotations:
"cluster-autoscaler.kubernetes.io/safe-to-evict": "false"
spec:
containers:
- name: {{ .Values.services.db_sql.name }}
image: {{ .Values.docker_container_registry }}/{{ .Values.services.db_sql.image }}
imagePullPolicy: {{ .Values.image_pull_policy }}
env:
- name: ACCEPT_EULA
value: "y"
- name: MSSQL_PID
value: "Express"
- name: MSSQL_SA_PASSWORD
value: {{ .Values.mssql.password }}
- name: LD_PRELOAD
value: /nodirect_open.so
ports:
- containerPort: {{ .Values.services.db_sql.port }}
resources:
requests:
memory: {{ .Values.services.db_sql.resources.requests.memory }}
cpu: {{ .Values.services.db_sql.resources.requests.cpu }}
limits:
memory: {{ .Values.services.db_sql.resources.limits.memory }}
volumeMounts:
- mountPath: /var/opt/mssql/data
name: dbdata
volumes:
- name: dbdata
emptyDir:
{{- if .Values.services.db_sql.memory_storage }}
medium: Memory
{{ end }}
I'm sure it works because I can see it in the memory usage of the pod, I see that it is occupied. Though interesting thing is that despite it finally started to work, it didn't speed up at all in my case, I took only 10 attempts which is not enough to have strong prove, but for those 10 attempts tests evaluation took even slightly more time on average. So at the end I'm postponing tmpfs and using normal disk, despite having it working.
