python - must - ¿Cómo puedo acelerar las operaciones de actualización/reemplazo en PostgreSQL?

psycopg2 postgres 10

Question

Tenemos una aplicación bastante específica que usa PostgreSQL 8.3 como backend de almacenamiento (usando Python y psycopg2). Las operaciones que realizamos en las tablas importantes son, en la mayoría de los casos, inserciones o actualizaciones (rara vez se eliminan o se seleccionan).

Por razones de cordura, hemos creado nuestra propia capa similar a Data Mapper que funciona razonablemente bien, pero tiene un gran cuello de botella, el rendimiento de la actualización. Por supuesto, no espero que el escenario de actualización / reemplazo sea tan rápido como el de ''insertar en una tabla vacía'', pero sería bueno acercarse un poco más.

Tenga en cuenta que este sistema está libre de actualizaciones concurrentes

Siempre establecemos todos los campos de cada fila en una actualización, que se puede ver en la terminología donde uso la palabra "reemplazar" en mis pruebas. Hasta ahora he intentado dos enfoques para nuestro problema de actualización:

1. Cree un procedimiento replace() que tome una matriz de filas para actualizar:

   CREATE OR REPLACE FUNCTION replace_item(data item[]) RETURNS VOID AS $$
   BEGIN
   FOR i IN COALESCE(array_lower(data,1),0) .. COALESCE(array_upper(data,1),-1) LOOP
   UPDATE item SET a0=data[i].a0,a1=data[i].a1,a2=data[i].a2 WHERE key=data[i].key;
   END LOOP;
   END;
   $$ LANGUAGE plpgsql

2. Cree una regla insert_or_replace para que todo menos la eliminación ocasional se convierta en inserciones de varias filas

   CREATE RULE "insert_or_replace" AS
   ON INSERT TO "item"
   WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)
   DO INSTEAD
   (UPDATE item SET a0=NEW.a0,a1=NEW.a1,a2=NEW.a2 WHERE key=NEW.key);

Estos dos aceleran las actualizaciones un poco, aunque este último se ralentiza inserta un poco:

Multi-row insert : 50000 items inserted in 1.32 seconds averaging 37807.84 items/s
executemany() update : 50000 items updated in 26.67 seconds averaging 1874.57 items/s
update_andres : 50000 items updated in 3.84 seconds averaging 13028.51 items/s
update_merlin83 (i/d/i) : 50000 items updated in 1.29 seconds averaging 38780.46 items/s
update_merlin83 (i/u) : 50000 items updated in 1.24 seconds averaging 40313.28 items/s
replace_item() procedure : 50000 items replaced in 3.10 seconds averaging 16151.42 items/s
Multi-row insert_or_replace: 50000 items inserted in 2.73 seconds averaging 18296.30 items/s
Multi-row insert_or_replace: 50000 items replaced in 2.02 seconds averaging 24729.94 items/s

Notas al azar sobre la ejecución de la prueba:

• Todas las pruebas se ejecutan en el mismo equipo en el que reside la base de datos; conectando a localhost.
• Las inserciones y actualizaciones se aplican a la base de datos en lotes de 500 artículos, cada uno enviado en su propia transacción ( ACTUALIZADO ).
• Todas las pruebas de actualización / reemplazo utilizaron los mismos valores que ya estaban en la base de datos.
• Todos los datos se escaparon usando la función psycopg2 adapt ().
• Todas las tablas se truncan y se aspiran antes de usarlas ( AÑADIDAS , en ejecuciones anteriores solo ocurrió el truncamiento)

• La mesa se ve así:

  CREATE TABLE item (
  key MACADDR PRIMARY KEY,
  a0 VARCHAR,
  a1 VARCHAR,
  a2 VARCHAR
  )

Entonces, la verdadera pregunta es: ¿Cómo puedo acelerar un poco más las operaciones de actualización / reemplazo? (Creo que estos hallazgos podrían ser ''lo suficientemente buenos'', pero no quiero rendirme sin tocar a la multitud de SO :)

Además, cualquier insinuación a favor de un reemplazo más elegante de reemplazo (), o la evidencia de que mis exámenes están completamente rotos, sería bienvenido.

El script de prueba está disponible here si desea intentar reproducirlo. Sin embargo, recuerde verificarlo primero ... funciona ForMe, pero ...

Deberá editar la línea db.connect () para adaptarse a su configuración.

EDITAR

Gracias a andres en #postgresql @ freenode tengo otra prueba con una actualización de una sola consulta; al igual que una inserción de varias filas (enumeradas como update_andres arriba).

UPDATE item
SET a0=i.a0, a1=i.a1, a2=i.a2
FROM (VALUES (''00:00:00:00:00:01'', ''v0'', ''v1'', ''v2''),
(''00:00:00:00:00:02'', ''v3'', ''v4'', ''v5''),
...
) AS i(key, a0, a1, a2)
WHERE item.key=i.key::macaddr

EDITAR

Gracias a merlin83 en #postgresql @ freenode y jug / jwp a continuación, tengo otra prueba con un enfoque Insert-to-temp / delete / insert (listado como "update_merlin83 (i / d / i)" arriba).

INSERT INTO temp_item (key, a0, a1, a2)
VALUES (
(''00:00:00:00:00:01'', ''v0'', ''v1'', ''v2''),
(''00:00:00:00:00:02'', ''v3'', ''v4'', ''v5''),
...);
DELETE FROM item
USING temp_item
WHERE item.key=temp_item.key;
INSERT INTO item (key, a0, a1, a2)
SELECT key, a0, a1, a2
FROM temp_item;

Mi intuición es que estas pruebas no son muy representativas del rendimiento en el escenario del mundo real, pero creo que las diferencias son lo suficientemente grandes como para dar una idea de los enfoques más prometedores para una investigación más a fondo. La secuencia de comandos perftest.py contiene todas las actualizaciones también para aquellos de ustedes que quieran comprobarlo. Aunque es bastante feo, así que no olvides tus gafas :)

EDITAR

andres en #postgresql @ freenode señaló que debería probar con una variante insert-to-temp / update (listada como "update_merlin83 (i / u)" arriba).

INSERT INTO temp_item (key, a0, a1, a2)
VALUES (
(''00:00:00:00:00:01'', ''v0'', ''v1'', ''v2''),
(''00:00:00:00:00:02'', ''v3'', ''v4'', ''v5''),
...);
UPDATE item
SET a0=temp_item.a0, a1=temp_item.a1, a2=temp_item.a2
FROM temp_item
WHERE item.key=temp_item.key

EDITAR

Probablemente, la edición final: cambié mi secuencia de comandos para que se ajustara mejor a nuestro escenario de carga, y parece que los números se mantienen, incluso al escalar un poco las cosas y agregar algo de aleatoriedad. Si alguien obtuviera números muy diferentes de algún otro escenario, me interesaría saberlo.

Answer 1

En Oracle, bloquear la mesa definitivamente ayudaría. Es posible que desee probar eso con PostgreSQL, también.

Answer 2

En su insert_or_replace . prueba esto:

WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key LIMIT 1)

en lugar de

WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)

Como se señaló en los comentarios, eso probablemente no hará nada. Lo único que tengo que agregar es que siempre puede acelerar el rendimiento de INSERTAR / ACTUALIZAR eliminando índices. Es probable que esto no sea algo que quieras hacer a menos que encuentres que tu tabla está sobreindexada, pero al menos eso debería comprobarse.

Answer 3

Hace unos meses tuve una situación similar y terminé obteniendo el mayor impulso de velocidad de un tamaño de transacción / trozo sintonizado. Es posible que también desee revisar el registro para ver si hay una advertencia de punto de control durante la prueba y sintonizar adecuadamente.

Answer 4

La forma habitual en que hago estas cosas en pg es: cargar la tabla de objetivos de coincidencia de datos en bruto en la tabla temporal (sin restricciones) usando copiar, fusionar (la parte divertida), obtener ganancias.

Escribí una función merge_by_key específicamente para estas situaciones:

http://mbk.projects.postgresql.org/

Los documentos no son muy amigables, pero sugeriría que le eches un buen vistazo.

Answer 5

Para las actualizaciones, puede reducir su factor de relleno para las tablas y los índices y eso podría ayudar

http://www.postgresql.org/docs/current/static/sql-createtable.html

http://www.postgresql.org/docs/current/static/sql-createindex.html

Answer 6

Parece que vería los beneficios de usar WAL (Registro de escritura anticipada) con un UPS para almacenar en caché sus actualizaciones entre las escrituras de disco.

wal_buffers Esta configuración determina la cantidad de búferes que puede tener WAL (Registro de escritura anticipada). Si su base de datos tiene muchas transacciones de escritura, establecer este valor un poco más alto que el predeterminado podría resultar en un mejor uso del espacio en disco. Experimenta y decide. Un buen comienzo sería alrededor de 32-64 correspondiente a 256-512K de memoria.

http://www.varlena.com/GeneralBits/Tidbits/perf.html