medellin - ¿Por qué las funciones agregadas de SQL son mucho más lentas que Python y Java(u OLAP de los pobres)?

diplomado python (10)

Necesito una opinión real de DBA. Postgres 8.3 tarda 200 ms para ejecutar esta consulta en mi Macbook Pro, mientras que Java y Python realizan el mismo cálculo en menos de 20 ms (350,000 filas):

SELECT count(id), avg(a), avg(b), avg(c), avg(d) FROM tuples;

¿Es este comportamiento normal cuando se utiliza una base de datos SQL?

El esquema (la tabla contiene respuestas a una encuesta):

CREATE TABLE tuples (id integer primary key, a integer, b integer, c integer, d integer); /copy tuples from ''350,000 responses.csv'' delimiter as '',''

Escribí algunas pruebas en Java y Python para el contexto y aplastan SQL (a excepción de python puro):

java 1.5 threads ~ 7 ms java 1.5 ~ 10 ms python 2.5 numpy ~ 18 ms python 2.5 ~ 370 ms

Incluso sqlite3 es competitivo con Postgres a pesar de que supone que todas las columnas son cadenas (por contraste: incluso usando simplemente cambiar a columnas numéricas en lugar de números enteros en resultados de Postgres en 10 veces más lenta)

Los ajustes que he intentado sin éxito incluyen (siguiendo ciegamente algunos consejos web):

increased the shared memory available to Postgres to 256MB increased the working memory to 2MB disabled connection and statement logging used a stored procedure via CREATE FUNCTION ... LANGUAGE SQL

Entonces mi pregunta es, ¿mi experiencia aquí es normal, y esto es lo que puedo esperar cuando uso una base de datos SQL? Puedo entender que ACID debe venir con costos, pero esto es un poco loco en mi opinión. No estoy pidiendo velocidad de juego en tiempo real, pero como Java puede procesar millones de dobles en menos de 20 ms, me siento un poco celoso.

¿Hay una mejor manera de hacer OLAP simple a bajo precio (tanto en términos de dinero como de complejidad del servidor)? He investigado a Mondrian y Pig + Hadoop, pero no estoy muy entusiasmado con el mantenimiento de otra aplicación de servidor y no estoy seguro de si incluso podrían ayudar.

No, el código de Python y el código de Java hacen todo el trabajo en casa, por así decirlo. Solo genero 4 arreglos con 350,000 valores aleatorios cada uno, luego tomo el promedio. No incluyo la generación en los tiempos, solo el paso de promediado. El tiempo de los hilos java usa 4 hilos (uno por promedio de matriz), exagerado pero definitivamente es el más rápido.

El tiempo de sqlite3 es impulsado por el programa Python y se ejecuta desde el disco (no: memoria :)

Me doy cuenta de que Postgres está haciendo mucho más detrás de escena, pero la mayor parte de ese trabajo no me importa, ya que esto es solo de lectura.

La consulta de Postgres no cambia el tiempo en las siguientes ejecuciones.

He vuelto a ejecutar las pruebas de Python para incluirlas en el disco. El tiempo se reduce considerablemente a casi 4 segundos. Pero supongo que el código de manejo de archivos de Python está más o menos en C (aunque tal vez no en csv lib?) Así que esto me indica que Postgres tampoco está transmitiendo desde el disco (o que estás en lo cierto y debería inclinarme antes de quien escribió su capa de almacenamiento!)

Esas son respuestas muy detalladas, pero en su mayoría me preguntan cómo puedo obtener estos beneficios sin abandonar Postgres dado que los datos encajan fácilmente en la memoria, requieren lecturas concurrentes pero no escrituras y se consultan con la misma consulta una y otra vez.

¿Es posible precompilar el plan de consulta y optimización? Pensé que el procedimiento almacenado haría esto, pero realmente no ayuda.

Para evitar el acceso al disco, es necesario almacenar toda la tabla en la memoria, ¿puedo forzar a Postgres a hacer eso? Creo que ya está haciendo esto, ya que la consulta se ejecuta en solo 200 ms después de ejecuciones repetidas.

¿Puedo decirle a Postgres que la tabla es de solo lectura, por lo que puede optimizar cualquier código de bloqueo?

Creo que es posible estimar los costos de construcción de la consulta con una tabla vacía (los intervalos varían de 20 a 60 ms)

Todavía no puedo ver por qué las pruebas de Java / Python no son válidas. Postgres simplemente no está haciendo mucho más trabajo (aunque todavía no he abordado el aspecto de concurrencia, solo el caché y la construcción de consultas)

ACTUALIZACIÓN: no creo que sea justo comparar los SELECTS, como se sugiere, tirando 350,000 a través de los pasos de serialización y controlador en Python para ejecutar la agregación, ni siquiera omitir la agregación ya que la sobrecarga en el formateo y la visualización es difícil de separar de la sincronización. Si ambos motores están funcionando en los datos de la memoria, debería ser una comparación de manzanas a manzanas, aunque no estoy seguro de cómo garantizar que eso ya esté sucediendo.

No puedo entender cómo agregar comentarios, ¿tal vez no tengo suficiente reputación?

Gracias por los horarios de Oracle, ese es el tipo de cosas que estoy buscando (aunque decepcionante :-)

Las vistas materializadas probablemente valgan la pena considerar, ya que creo que puedo precalcular las formas más interesantes de esta consulta para la mayoría de los usuarios.

No creo que el tiempo de ida y vuelta de la consulta sea muy alto ya que estoy ejecutando las consultas en la misma máquina que ejecuta Postgres, por lo que no puede agregar mucha latencia.

También revisé el tamaño de la memoria caché, y parece que Postgres confía en el sistema operativo para manejar el almacenamiento en caché, mencionan específicamente a BSD como el sistema operativo ideal para esto, así que creo que Mac OS debería ser bastante inteligente al llevar la tabla a memoria. A menos que alguien tenga params más específicos en mente, creo que el caché más específico está fuera de mi control.

Al final, probablemente pueda soportar tiempos de respuesta de 200 ms, pero saber que 7 ms es un posible objetivo me hace sentir insatisfecho, ya que incluso 20-50 veces ms permitiría a más usuarios tener consultas más actualizadas y deshacerse de ellos. un montón de caché y hacks precalculados.

Acabo de verificar los tiempos usando MySQL 5 y son ligeramente peores que Postgres. Por lo tanto, salvo algunos grandes avances en el almacenamiento en caché, creo que esto es lo que puedo esperar de la ruta db relacional.

Desearía poder votar algunas de tus respuestas, pero todavía no tengo suficientes puntos.

Necesita aumentar las cachés de Postgres hasta el punto en que todo el conjunto de trabajo se ajuste a la memoria antes de que pueda ver el rendimiento comparable a hacerlo en memoria con un programa.

No creo que tus resultados sean tan sorprendentes, si algo es que Postgres es tan rápido.

¿La consulta de Postgres se ejecuta más rápido por segunda vez una vez que ha tenido la oportunidad de almacenar en caché los datos? Para ser un poco más justo, su prueba para Java y Python debe cubrir el costo de adquisición de los datos en primer lugar (lo ideal es cargarlo fuera del disco).

Si este nivel de rendimiento es un problema para su aplicación en la práctica, pero necesita un RDBMS por otros motivos, entonces puede mirar memcached . A continuación, tendrá un acceso en caché más rápido a los datos sin procesar y podría hacer los cálculos en código.

Otra cosa que un RDBMS generalmente hace por usted es proporcionar concurrencia protegiéndolo del acceso simultáneo por otro proceso. Esto se hace colocando bloqueos, y hay algo de sobrecarga de eso.

Si está tratando con datos completamente estáticos que nunca cambian, y especialmente si se encuentra en un escenario básicamente de "usuario único", entonces el uso de una base de datos relacional no necesariamente le beneficiará mucho.

¿Estás usando TCP para acceder al Postgres? En ese caso, Nagle está jugando con tu tiempo.

Postgres está haciendo mucho más de lo que parece (¡manteniendo la consistencia de los datos para empezar!)

Si los valores no tienen que ser 100% perfectos, o si la tabla se actualiza con poca frecuencia, pero está ejecutando este cálculo a menudo, es posible que desee examinar Vistas materializadas para acelerarlo.

(Tenga en cuenta que no he utilizado vistas materializadas en Postgres, miran al pequeño hacky, pero podrían adaptarse a su situación).

Vistas Materializadas

También considere la sobrecarga de la conexión real al servidor y el viaje de ida y vuelta requerido para enviar la solicitud al servidor y volver.

Considero que 200ms para algo como esto es bastante bueno. Una prueba rápida en mi servidor Oracle, la misma estructura de tabla con aproximadamente 500k filas y sin índices, toma aproximadamente 1 - 1.5 segundos, lo cual es casi todo solo oráculo chupando los datos fuera del disco

La verdadera pregunta es, ¿200ms es lo suficientemente rápido?

-------------- Más --------------------

Estaba interesado en resolver esto utilizando vistas materializadas, ya que nunca había jugado realmente con ellos. Esto está en el oráculo.

Primero creé un MV que se actualiza cada minuto.

create materialized view mv_so_x build immediate refresh complete START WITH SYSDATE NEXT SYSDATE + 1/24/60 as select count(*),avg(a),avg(b),avg(c),avg(d) from so_x;

Si bien es refrescante, no hay filas devueltas

SQL> select * from mv_so_x; no rows selected Elapsed: 00:00:00.00

Una vez que se actualiza, es MUCHO más rápido que hacer la consulta sin formato

SQL> select count(*),avg(a),avg(b),avg(c),avg(d) from so_x; COUNT(*) AVG(A) AVG(B) AVG(C) AVG(D) ---------- ---------- ---------- ---------- ---------- 1899459 7495.38839 22.2905454 5.00276131 2.13432836 Elapsed: 00:00:05.74 SQL> select * from mv_so_x; COUNT(*) AVG(A) AVG(B) AVG(C) AVG(D) ---------- ---------- ---------- ---------- ---------- 1899459 7495.38839 22.2905454 5.00276131 2.13432836 Elapsed: 00:00:00.00 SQL>

Si lo insertamos en la tabla base, el resultado no se puede ver inmediatamente en el MV.

SQL> insert into so_x values (1,2,3,4,5); 1 row created. Elapsed: 00:00:00.00 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> select * from mv_so_x; COUNT(*) AVG(A) AVG(B) AVG(C) AVG(D) ---------- ---------- ---------- ---------- ---------- 1899459 7495.38839 22.2905454 5.00276131 2.13432836 Elapsed: 00:00:00.00 SQL>

Pero espere un minuto más o menos, y el MV se actualizará detrás de escena, y el resultado se devuelve rápidamente como podría desear.

SQL> / COUNT(*) AVG(A) AVG(B) AVG(C) AVG(D) ---------- ---------- ---------- ---------- ---------- 1899460 7495.35823 22.2905352 5.00276078 2.17647059 Elapsed: 00:00:00.00 SQL>

Esto no es ideal para empezar, no es en tiempo real, las inserciones / actualizaciones no serán visibles inmediatamente. Además, tiene una consulta ejecutándose para actualizar el MV, ya sea que lo necesite o no (esto se puede sintonizar en cualquier marco de tiempo, o bajo demanda). Sin embargo, esto muestra cuánto más rápido puede hacer que un VM parezca al usuario final, si puede vivir con valores que no son del todo exactos.

Soy un tipo de MS-SQL y utilizamos DBCC PINTABLE para mantener una tabla en caché, y SET STATISTICS IO para ver que está leyendo desde el caché y no desde el disco.

No puedo encontrar nada en Postgres para imitar a PINTABLE, pero pg_buffercache parece dar detalles sobre lo que hay en la memoria caché; es posible que desee verificarlo y ver si su tabla está realmente en la memoria caché.

Una rápida vuelta del cálculo del sobre me hace sospechar que está paginando desde el disco. Suponiendo que Postgres utiliza enteros de 4 bytes, tiene (6 * 4) bytes por fila, por lo que su tabla tiene un mínimo de (24 * 350,000) bytes ~ 8.4MB. Asumiendo un rendimiento sostenido de 40 MB / s en su HDD, está buscando aproximadamente 200 ms para leer los datos (que, como se señaló , deberían estar donde se está gastando casi todo el tiempo).

A menos que haya metido la pata en alguna parte, no veo cómo es posible que pueda leer 8MB en su aplicación Java y procesarla en el tiempo que esté mostrando, a menos que ese archivo ya esté en la memoria caché del disco o de su unidad. OS.

Volví a probar con MySQL especificando ENGINE = MEMORY y no cambia nada (aún 200 ms). Sqlite3 usando un db en memoria da tiempos similares también (250 ms).

La matemática aquí parece correcta (al menos el tamaño, ya que así de grande es la sqlite db :-)

Simplemente no estoy comprando el argumento disco-causas-lentitud ya que hay toda indicación de que las tablas están en la memoria (todos los chicos de Postgres advierten contra intentar demasiado difícil anotar tablas en la memoria, ya que juran que el sistema operativo lo hará mejor que el programador )

Para aclarar los tiempos, el código de Java no lee del disco, por lo que es una comparación totalmente injusta si Postgres está leyendo desde el disco y calculando una consulta complicada, pero eso es realmente más que el punto, el DB debe ser lo suficientemente inteligente como para traer un pequeño tabla en la memoria y precompilar un procedimiento almacenado en mi humilde opinión.

ACTUALIZAR (en respuesta al primer comentario a continuación):

No estoy seguro de cómo probaría la consulta sin utilizar una función de agregación de una manera que sería justa, ya que si selecciono todas las filas, pasará mucho tiempo serializando y formateando todo. No digo que la lentitud se deba a la función de agregación, aún podría estar solo por encima de la simultaneidad, la integridad y los amigos. Simplemente no sé cómo aislar la agregación como la única variable independiente.

Yo diría que su esquema de prueba no es realmente útil. Para cumplir con la consulta db, el servidor db pasa por varios pasos:

analizar el SQL
elaborar un plan de consulta, es decir, decidir qué índices usar (si hay alguno), optimizar, etc.
si se utiliza un índice, búscalo en los punteros a los datos reales, luego ve a la ubicación adecuada en los datos o
si no se utiliza ningún índice, escanee toda la tabla para determinar qué filas son necesarias
cargar los datos del disco en una ubicación temporal (con suerte, pero no necesariamente, en la memoria)
realizar los cálculos de count () y avg ()

Entonces, al crear una matriz en Python y obtener el promedio, básicamente se saltan todos estos pasos y se guarda el último. Como la E / S de disco es una de las operaciones más costosas que debe realizar un programa, esta es una falla importante en la prueba (ver también las respuestas a esta pregunta que hice aquí antes). Incluso si lee los datos del disco en su otra prueba, el proceso es completamente diferente y es difícil decir qué tan relevantes son los resultados.

Para obtener más información acerca de dónde pasa su tiempo Postgres, sugeriría las siguientes pruebas:

Compare el tiempo de ejecución de su consulta con un SELECCIONAR sin las funciones de agregación (es decir, corte el paso 5)
Si encuentra que la agregación conduce a una desaceleración significativa, intente si Python lo hace más rápido, obteniendo los datos brutos a través del SELECT simple de la comparación.

Para acelerar su consulta, reduzca el acceso al disco primero. Dudo mucho que sea la agregación lo que tome el tiempo.

Hay varias formas de hacerlo:

Datos de caché (¡en la memoria!) Para acceder posteriormente, ya sea a través de las capacidades propias del motor de db o con herramientas como memcached
Reduzca el tamaño de sus datos almacenados
Optimizar el uso de índices. A veces, esto puede significar omitir el uso del índice (después de todo, también es acceso al disco). Para MySQL, parece recordar que se recomienda omitir índices si supone que la consulta obtiene más del 10% de todos los datos en la tabla.
Si su consulta hace un buen uso de los índices, sé que para las bases de datos MySQL ayuda poner los índices y los datos en discos físicos separados. Sin embargo, no sé si eso es aplicable para Postgres.
También podría haber problemas más sofisticados, como el intercambio de filas en el disco si, por alguna razón, el conjunto de resultados no se puede procesar por completo en la memoria. Pero dejaría ese tipo de investigación hasta que me tope con serios problemas de rendimiento que no puedo encontrar otra manera de solucionar, ya que requiere conocimiento sobre muchos detalles pequeños en el proceso.

Actualizar:

Me acabo de dar cuenta de que parece que no tiene ningún uso para los índices de la consulta anterior y probablemente tampoco los use, por lo que mi consejo sobre los índices probablemente no fue útil. Lo siento. Aún así, diría que la agregación no es el problema sino el acceso al disco. Dejo las cosas del índice, de todos modos, todavía podría tener algún uso.