PostgreSQL: Optimización de Consultas con Python y PgBouncer

Para generar datos de prueba para la base de datos de tu proyecto puedes usar Faker, una biblioteca de Python con la que puedes generar datos a partir de métodos ya definidos o creados por la comunidad.

Si necesitas generar una cantidad considerable de datos que te permita evaluar el comportamiento de la base de datos, puedes crear un script que haga lo siguiente:

• Mediante la implementación de multiprocesamiento puedes generar tantos registros como necesites, optimizando el tiempo de ejecución de dicha tarea, ya que la cantidad de registros a generar se divide entre el número de núcleos de CPU disponibles menos uno (para evitar que se bloquee el equipo de cómputo)
• Cada conjunto de datos es almacenado en un DataFrame de Pandas, que después son concatenados en un solo DataFrame
• El DataFrame es insertado en la base de datos usando el método to_sql de Pandas para bases de datos SQL, y el método insert_many de PyMongo si se trata de una base de datos MongoDB

Python usa por defecto un único núcleo de CPU, por lo que el proceso de generación de datos puede ser muy lento. Entonces, ¿cómo puede optimizarse el script? En lugar de generar los datos, almacenarlos en un DataFrame, y luego insertarlos en la base de datos, puedes hacer que cada núcleo de CPU se encargue de insertar los datos al instante de generarlos, sin tener que almacenarlos en otro lugar antes de ejecutar las sentencias SQL correspondientes.

Para lograr lo descrito anteriormente se necesita configurar un grupo de conexiones para tu servidor PostgreSQL. A través de este artículo aprenderás a instalar y configurar PgBouncer para poder realizar tantas conexiones a la base de datos como núcleos de CPU estén disponibles.

PgBouncer

#

PgBouncer es un agrupador de conexiones para PostgreSQL. Cualquier aplicación puede conectarse a PgBouncer como si fuera un servidor PostgreSQL, y PgBouncer se encargará de crear la conexión al servidor de base de datos, o reutilizará una de las conexiones existentes.

El objetivo de PgBouncer es reducir el impacto en el rendimiento al abrir nuevas conexiones a PostgreSQL.

Instalación

#

Si usas Ubuntu. puedes instalar PgBouncer desde los repositorios:

$ sudo apt install pgbouncer -y

Si no está disponible en los repositorios, puedes seguir las siguientes instrucciones para Debian y Ubuntu como se menciona en la documentación de Scaleway

1. Crear el archivo de configuración de apt para el repositorio

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

2. Importar la clave de firma del repositorio

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

3. Actualizar los repositorios de apt

$ sudo apt update

4. Instalar PgBouncer usando apt

$ sudo apt install pgbouncer -y

Configuración

#

Después de instalar PgBouncer, edita el archivo de configuración, como se menciona en la documentación de Scaleway.

1. Configura los detalles del servidor PostgreSQL en /etc/pgbouncer/pgbouncer.ini, en la sección [databases]

database_name = host=localhost port=5432 dbname=database_name

Proablemente quieras establecer listen_addr a * si quieres escuchar las conexiones TCP en todas las direcciones o definir una lista de direcciones IP.

El valor por defecto de listen_port es 6432

De este artículo por Abdullah Alger, las opciones de configuración max_client_conn y default_pool_size, se refieren al número de aplicaciones que se conectarán y al número de conexiones al servidor por base de datos, respectivamente. Los valores por defecto son 100 y 20.

2. Edita el archivo /etc/pgbouncer/userlist.txt y agrega tus credenciales de PostgreSQL

“username” “password”

3. Agrega la dirección IP del servidor PgBouncer al archivo de configuración pg_hba.conf de PostgreSQL

host all all PGBOUNCER_IP/NETMASK trust

Por defecto, PgBouncer viene con el método de autenticación trust. El método trust puede usarse en un entorno de desarrollo, pero no es recomendado en produccción. Para producción, se recomienda la autenticación hba.

4. Después de configurar PgBouncer, reinicia ambos servicios, PostgreSQL y PgBouncer

sudo systemctl reload postgresql
sudo systemctl reload pgbouncer

Para más información sobre opciones de configuración adicional, revisa la documentación de PgBouncer.

Python

#

Requisitos

#

Dependencias

#

Asegurar que todas las dependencias estén instaladas antes de crear el script de Python que va a generar los datos para tu proyecto.

Puedes crear el archivo requirements.txt con el siguiente contenido:

tqdm
faker
psycopg2

O si estás usando Anaconda, crea un archivo environment.yml:

name: pgbouncer-test
dependencies:
  - python=3.10
  - tqdm
  - faker
  - psycopg2

Puedes cambiar las versión de Python si es necesario. Este script se ha probado que funciona con cualquiera de estas versiones de Python: 3.7, 3.8, 3.9, 3.10, and 3.11.

Ejecuta el siguiente comando si estás usando pip:

pip install -r requirements.txt

O ejecuta la siguiente sentencia para configurar el entorno del projecto si usas Anaconda:

conda env create -f environment.yml

Base de Datos

#

Ahora que tienes las dependencias instaladas, debes crear una base de datos llamada company.

Inicia sesión en PostgreSQL:

$ sudo su postgres
$ psql

Crea la base de datos company:

create database company;

Y crea la tabla employees:

create table employees(
  id         serial        primary key,
  fist_name  varchar(50)   not null,
  last_name  varchar(50)   not null,
  job        varchar(100)  not null,   
  address    varchar(200)  not null,
  city       varchar(100)  not null,
  email      varchar(50)   not null
);

Insertando Datos

#

Ahora es tiempo de crear el script de Python que generará los datos y los insertará en la base de datos.

from multiprocessing import Pool, cpu_count
import psycopg2
from tqdm import tqdm
from faker import Faker

fake = Faker()
num_cores = cpu_count() - 1

def insert_data(arg):
    x = int(60000/num_cores)
    print(x)
    with psycopg2.connect(database="database_name", user="user", password="password", host="localhost", port="6432") as conn:
        with conn.cursor() as cursor:
            for i in tqdm(range(x), desc="Inserting Data"):
                sql = "INSERT INTO employees (first_name, last_name, job, address, city, email) VALUES (%s, %s, %s, %s, %s, %s)"
                val = (fake.first_name(), fake.last_name(), fake.job(), fake.address(), fake.city(), fake.email())
                cursor.execute(sql, val)

if __name__=="__main__":
    with Pool() as pool:
        pool.map(insert_data, range(num_cores))

Primero se crea el grupo de multiprocesamiento, y se configura para usar todos los núcleos de CPU disponibles menos uno. Cada núcleo llamará a la función insert_data().

En cada llamado a la función, se creará una conexión a la base de datos a través del puerto por defecto (6432) de PgBouncer, lo que significa que la aplicación abrirá un número de conexiones igual a num_cores, una variable que contiene el número de núcleos de CPU que se están usando.

En seguida, los datos serán generados con Faker e insertados en la base de datos ejecutando las sentencias SQL correspondientes.

En un CPU con 16 núcleos, el número de registros insertados en la base de datos en cada llamada a la función será igual a 60 mil dividido por 15, que es 4 mil sentencias SQL ejecutadas.

De esta forma puedes modificar el script y optimizarlo mediante la configuración de un grupo de conexiones con PgBouncer.

Artículo original publicado en inglés en percona.community