Connections with Microsoft SQL Server are 5 times slower on Linux compared to Windows

[AJAYKE]

Hi people,
I have a big problem with MICROSOFT SQL FROM KUBERNETES
I have setup my entire cluster.
I thought everything is working fine, then observed that all the api calls are very slow..
SETUP:
My cluster with services is running in my office server room, and i have microsoft sql server running on windows machine just beside it.
Now my requests to that sql server are 5-6x slower than my earlier setup where, requests used to go from services running on IIS on windows machine to MS sql server running on windows.
i ran profiler and saw that
pyodbc.connect is taking 90% of the time
The query which is taking 500ms to be executed on windows machine is taking 5.2seconds out of which 4.46 is consumed by pyodbc.connect
PLEASE HELP

[gordthompson]

Are you connecting using SQL Server authentication (UID and PWD) or some other authentication scheme?

[AJAYKE]

@gordthompson @v-chojas Yes, i am connecting using UID and pwd.
and yes the hardware is similar.
you can see that just pyodbc.connect is taking time, not any other functions.
on ubuntu os it is taking 4.5s seconds
and the entire api is taking 5 seconds

on windows os, the same code, same database connection, the entire api call is taking 500ms

[gordthompson]

Are you using the same ODBC driver in each case, e.g., "ODBC Driver 18 for SQL Server"?

[AJAYKE]

yes

[AJAYKE]

I will attach the exact script i used to download the driver and how am using it.

sudo apt-get update && \
    apt-get install -y --no-install-recommends \
        curl \
        unixodbc unixodbc-dev \
        gnupg2 \
        dpkg-dev \
        gcc \
        gnupg \
        libbluetooth-dev \
        libbz2-dev \
        libc6-dev \
        libdb-dev \
        libexpat1-dev \
        libffi-dev \
        libgdbm-dev \
        liblzma-dev \
        libncursesw5-dev \
        libreadline-dev \
        libsqlite3-dev \
        libssl-dev \
        make \
        tk-dev \
        uuid-dev \
        wget \
        xz-utils \
        zlib1g-dev

if ! [[ "18.04 20.04 22.04 23.04 24.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev


sudo apt-get update -y && \
    apt-get upgrade -y && \
    apt install build-essential checkinstall -y && \
    apt install unixodbc -y && \
    apt install wget -y && \
    cd /opt/ && \
    wget https://www.python.org/ftp/python/3.9.10/Python-3.9.10.tgz && \
    tar xzf Python-3.9.10.tgz && \
    cd Python-3.9.10/ && \
    ./configure --enable-optimizations && \
    make altinstall && \
    cd ../../ && \
    apt install python3-pip -y

DEFAULT_DRIVER_UNIX = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1"
DEFAULT_DRIVER_ODBC_UNIX = "/usr/lib64/libmsodbcsql-18.so"
if platform.system() == "Linux":
    DEFAULT_DRIVER = DEFAULT_DRIVER_UNIX
    DEFAULT_DRIVER_ODBC = DEFAULT_DRIVER_UNIX


def create_db_string(*, server, database, username, password, driver=DEFAULT_DRIVER):
    return (
        f"Driver={driver};"
        f"Server={server};"
        f"Database={database};"
        f"uid={username};"
        f"pwd={password};"
        "Trusted_Connection=no;"
        "integratedSecurity=false;"
"TrustServerCertificate=yes;"
    )
    
   def read_from_db(db_string, query, sorting_index=None, descending=False):
    """given database connection string, and a query returns a sorted list
    of queried items from database"""
    conn = pyodbc.connect(db_string)
    cursor = conn.cursor()
    cursor.execute(query)
    return_list = list(cursor.fetchall())
    if sorting_index is not None:
        return_list.sort(key=lambda x: x[sorting_index], reverse=descending)
    return return_list

[gordthompson]

In your profile output, tottime=4.46 and percall=0.01472, indicating that .connect() is being called 303 times.

On Windows, Microsoft's ODBC drivers for SQL Server (e.g., msodbcsql18) use connection pooling by default. On Linux, unixODBC < 2.3.12 does not use connection pooling by default. So what's probably happening is that on Windows your test is creating one actual database connection and re-using it, while on Linux your test is creating (i.e., connecting, authenticating, using, and discarding) 303 separate connections.

[AJAYKE]

acha okay,
how to do that?
is there an option that i should activate? somewhere?

[AJAYKE]

@gordthompson do we have to change the code aswell?

[gordthompson]

What versions of unixODBC and pyodbc are you using?

[AJAYKE]

@gordthompson
unixODBC 2.3.9
pyodbc 5.1.0

[gordthompson]

See the wiki update I posted earlier today.

https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#unixodbc-connection-pooling

[AJAYKE]

so if i update my odbc version to >= 2.3.11 it will work?
i will try that and will get back to you

[AJAYKE]

@gordthompson it workedddd
thanks a tonnnnneee

[AJAYKE]

@gordthompson it worked on my ubuntu 22.04 system
why isnt it working in the docker/kubernetes?
This is my dockerfile.

# Use the official Python image from the Docker Hub
FROM python:3.9

# Set the working directory in the container
WORKDIR /Solenoid

# Set Environment variable
ENV PYTHONPATH "${PYTHONPATH}:/Solenoid"

# Set environment variables
ENV ACCEPT_EULA=Y
ENV DEBIAN_FRONTEND=noninteractive

RUN apt update && apt upgrade -y

# Copy only the requirements file first to leverage Docker cache
COPY analysis_service_v9/prod_requirements.txt /app/prod_requirements.txt

# Install Python dependencies
RUN pip install --no-cache-dir -r /app/prod_requirements.txt && \
    pip install pandas numpy scipy gunicorn gevent

# Install required packages
RUN apt install -y \
    python3 python3-pip \
    unixodbc-dev \
    libssl-dev \
    wget \
    curl \
    gnupg2 \
    dpkg-dev \
    gcc \
    gnupg \
    libbluetooth-dev \
    libbz2-dev \
    libc6-dev \
    libdb-dev \
    libexpat1-dev \
    libffi-dev \
    libgdbm-dev \
    liblzma-dev \
    libncursesw5-dev \
    libreadline-dev \
    libsqlite3-dev \
    libssl-dev \
    make \
    tk-dev \
    uuid-dev \
    wget \
    xz-utils \
    zlib1g-dev \
    apt-transport-https \
    ca-certificates \
    build-essential \
    gcc \
    g++ 

# Install unixODBC 2.3.12
RUN curl -L http://www.unixodbc.org/unixODBC-2.3.12.tar.gz | tar zx && \
    cd unixODBC-2.3.12 && \
    ./configure && \
    make && \
    make install && \
    cd .. && \
    rm -rf unixODBC-2.3.12

# Add the Microsoft repository and install the ODBC driver 18 for SQL Server
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql18 && \
    ACCEPT_EULA=Y apt-get install -y mssql-tools18 && \
    echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> /root/.bashrc && \
    . /root/.bashrc

# # Create a symbolic link for the Microsoft SQL Server client library
# RUN ln -s /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1 /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.0


# Copy the application code
COPY analysis_service /app/analysis_service

# Set the working directory
WORKDIR /app

# Expose port 4000
EXPOSE 4000

# Run the application with gunicorn
CMD ["gunicorn", "-w", "2", "-k", "gevent", "-b", "0.0.0.0:4000", "analysis_service.app:app"]

[AJAYKE]

@gordthompson i bashed into the pod, and saw that odbcinst.ini file doesnt have pooling=yes

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1
UsageCount=1

this was my configuration

when i changed the configuration to

[ODBC]
Trace = No
Trace File = /tmp/sql.log
Pooling = Yes

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1
UsageCount=1
CPTimeout=60

it worked

[AJAYKE]

@gordthompson but you mentioned that by default unixodbc 2.3.12 will set pooling=yes right?

[gordthompson]

Yes. With my dockerfile

FROM python:3.9
WORKDIR /app
RUN apt-get update && apt-get -y upgrade && apt-get -y install build-essential curl
RUN curl -L http://www.unixodbc.org/unixODBC-2.3.12.tar.gz | tar zx && \
    cd unixODBC-2.3.12 && \
    ./configure && \
    make && \
    make install && \
    cd .. && \
    rm -rf unixODBC-2.3.12
RUN curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc && \
    curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | tee /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql18
RUN rm /usr/local/etc/odbcinst.ini
RUN ln -s /etc/odbcinst.ini /usr/local/etc/
RUN pip install pyodbc
COPY my_app.py my_app.py
RUN python my_app.py

and my_app.py

from time import perf_counter

import pyodbc

#pyodbc.pooling = False
connection_string = (
    "Driver=ODBC Driver 18 for SQL Server;"
    "Server=192.168.0.199;"
    "UID=scott;"
    "PWD=tiger^5HHH;"
    "Database=test;"
    "TrustServerCertificate=yes;"
)
t0 = perf_counter()
for i in range(100):
    cnxn = pyodbc.connect(connection_string)
    cnxn.close()
print(perf_counter() - t0)

my_app prints 0.1683779159993719.

If I uncomment the #pyodbc.pooling = False line and run docker build again it prints 7.796553281000342.