Le sugiero que lea la transcripción del informe de Vladimir Sitnikov de principios de 2016 "PostgreSQL y JDBC están exprimiendo todo el jugo".
Buenas tardes Mi nombre es Vladimir Sitnikov. Llevo 10 años trabajando para NetCracker. Y lo que más me interesa es la productividad. Todo lo relacionado con Java, todo lo relacionado con SQL es lo que me encanta.
Y hoy hablaré de lo que nos encontramos en la empresa cuando empezamos a utilizar PostgreSQL como servidor de base de datos. Y trabajamos principalmente con Java. Pero lo que les voy a contar hoy no se trata sólo de Java. Como ha demostrado la práctica, esto también ocurre en otros idiomas.
Hablaremos:
- sobre el muestreo de datos.
- Sobre guardar datos.
- Y también sobre el rendimiento.
- Y sobre los rastrillos submarinos que allí están enterrados.
Comencemos con una pregunta sencilla. Seleccionamos una fila de la tabla según la clave principal.
La base de datos está ubicada en el mismo host. Y toda esta agricultura lleva 20 milisegundos.
Estos 20 milisegundos son muchos. Si tiene 100 solicitudes de este tipo, entonces dedica tiempo por segundo a revisarlas, es decir, estamos perdiendo el tiempo.
No nos gusta hacer esto y miramos lo que nos ofrece la base para ello. La base de datos nos ofrece dos opciones para ejecutar consultas.
La primera opción es una simple solicitud. ¿Qué tiene de bueno? El hecho de que lo tomamos y lo enviamos, y nada más.
La base de datos también tiene una consulta avanzada, que es más complicada, pero más funcional. Puede enviar por separado una solicitud de análisis, ejecución, vinculación de variables, etc.
La consulta súper extendida es algo que no cubriremos en el informe actual. Quizás queramos algo de la base de datos y hay una lista de deseos que se ha formado de alguna forma, es decir, esto es lo que queremos, pero es imposible ahora y el próximo año. Así que simplemente lo grabamos y andaremos sacudiendo a las personas principales.
Y lo que podemos hacer es una consulta simple y una consulta extendida.
¿Qué tiene de especial cada enfoque?
Una consulta simple es buena para una ejecución única. Una vez hecho y olvidado. Y el problema es que no soporta el formato de datos binarios, es decir, no es adecuado para algunos sistemas de alto rendimiento.
Consulta extendida: le permite ahorrar tiempo en el análisis. Esto es lo que hicimos y comenzamos a usar. Esto realmente nos ayudó mucho. No sólo se ahorra en el análisis. Hay ahorros en la transferencia de datos. Transferir datos en formato binario es mucho más eficiente.
Pasemos a la práctica. Así es como se ve una aplicación típica. Podría ser Java, etc.
Creamos declaración. Ejecutó el comando. Creado cerca. ¿Dónde está el error aquí? ¿Cuál es el problema? Ningún problema. Esto es lo que dice en todos los libros. Así debería escribirse. Si quieres el máximo rendimiento, escribe así.
Pero la práctica ha demostrado que esto no funciona. ¿Por qué? Porque tenemos un método "cerrado". Y cuando hacemos esto, desde el punto de vista de la base de datos resulta que es como un fumador trabajando con una base de datos. Dijimos "PARSE EJECUTAR DEASIGNAR".
¿Por qué toda esta creación y descarga adicional de declaraciones? Nadie los necesita. Pero lo que suele pasar en PreparedStatements es que cuando los cerramos, cierran todo lo que hay en la base de datos. Esto no es lo que queremos.
Queremos, como personas sanas, trabajar con la base. Tomamos y preparamos nuestra declaración una vez, luego la ejecutamos muchas veces. De hecho, muchas veces (esto es una vez en toda la vida de las aplicaciones) han sido analizadas. Y usamos la misma identificación de declaración en diferentes REST. Este es nuestro objetivo.
¿Cómo podemos lograr esto?
Es muy simple: no es necesario cerrar declaraciones. Lo escribimos así: “preparar” “ejecutar”.
Si lanzamos algo como esto, entonces está claro que algo se desbordará en alguna parte. Si no está claro, puedes probártelo. Escribamos un punto de referencia que utilice este método simple. Crea una declaración. Lo iniciamos en alguna versión del controlador y descubrimos que falla bastante rápido con la pérdida de toda la memoria que tenía.
Está claro que estos errores se corrigen fácilmente. No hablaré de ellos. Pero diré que la nueva versión funciona mucho más rápido. El método es estúpido, pero aún así.
¿Cómo trabajar correctamente? ¿Qué necesitamos hacer para esto?
En realidad, las aplicaciones siempre cierran declaraciones. En todos los libros dicen que lo cierres, de lo contrario se perderá la memoria.
Y PostgreSQL no sabe cómo almacenar en caché las consultas. Es necesario que cada sesión cree este caché por sí misma.
Y tampoco queremos perder el tiempo analizando.
Y como siempre tenemos dos opciones.
La primera opción es que la tomemos y digamos que empaquetemos todo en PgSQL. Hay un caché allí. Almacena todo en caché. Saldrá genial. Vimos esto. Tenemos 100500 solicitudes. No funciona. No aceptamos convertir solicitudes en procedimientos manualmente. No no.
Tenemos una segunda opción: tomarlo y cortarlo nosotros mismos. Abrimos las fuentes y comenzamos a cortar. Vimos y vimos. Resultó que esto no es tan difícil de hacer.
Esto apareció en agosto de 2015. Ahora hay una versión más moderna. Y todo es genial. Funciona tan bien que no cambiamos nada en la aplicación. E incluso dejamos de pensar en PgSQL, es decir, esto fue suficiente para reducir todos los costos generales a casi cero.
En consecuencia, las declaraciones preparadas por el servidor se activan en la quinta ejecución para evitar desperdiciar memoria en la base de datos en cada solicitud única.
Quizás te preguntes: ¿dónde están los números? ¿Que estas obteniendo? Y aquí no daré números, porque cada solicitud tiene la suya.
Nuestras consultas fueron tales que dedicamos unos 20 milisegundos a analizar las consultas OLTP. Hubo 0,5 milisegundos para la ejecución y 20 milisegundos para el análisis. Solicitud: 10 KiB de texto, 170 líneas de plano. Esta es una solicitud OLTP. Solicita 1, 5, 10 líneas, a veces más.
Pero no queríamos perder 20 milisegundos en absoluto. Lo reducimos a 0. Todo es estupendo.
¿Qué te puedes llevar de aquí? Si tiene Java, tome la versión moderna del controlador y regocíjese.
Si hablas otro idioma, piensa: ¿quizás tú también necesites esto? Porque desde el punto de vista del lenguaje final, por ejemplo, si PL 8 o tiene LibPQ, entonces no es obvio para usted que no está dedicando tiempo a la ejecución, sino al análisis, y vale la pena comprobarlo. ¿Cómo? Todo es gratis.
Excepto que hay errores y algunas peculiaridades. Y hablaremos de ellos ahora mismo. La mayor parte será sobre arqueología industrial, sobre lo que encontramos, lo que nos encontramos.
Si la solicitud se genera dinámicamente. Sucede. Alguien pega las cadenas, lo que da como resultado una consulta SQL.
¿Por qué es malo? Es malo porque cada vez terminamos con una cadena diferente.
Y es necesario volver a leer el código hash de esta cadena diferente. Esta es realmente una tarea de la CPU: encontrar un texto de solicitud largo incluso en un hash existente no es tan fácil. Por tanto, la conclusión es simple: no genere solicitudes. Guárdelos en una variable. Y regocíjate.
Siguiente problema. Los tipos de datos son importantes. Hay ORM que dicen que no importa qué tipo de NULL haya, que haya algún tipo. Si es Int, entonces decimos setInt. Y si es NULL, que siempre sea VARCHAR. ¿Y qué diferencia hay al final qué NULL hay? La propia base de datos lo entenderá todo. Y esta imagen no funciona.
En la práctica, a la base de datos no le importa en absoluto. Si dijo la primera vez que es un número y la segunda vez dijo que es un VARCHAR, entonces es imposible reutilizar las declaraciones preparadas por el servidor. Y en este caso, tenemos que recrear nuestra declaración.
Si está ejecutando la misma consulta, asegúrese de que los tipos de datos en su columna no se confundan. Debes tener cuidado con NULL. Este es un error común que tuvimos después de que comenzamos a usar PreparedStatements.
Bien, encendido. Quizás se llevaron al conductor. Y la productividad cayó. Las cosas se pusieron mal.
¿Como sucedió esto? ¿Es esto un error o una característica? Desafortunadamente, no fue posible entender si se trata de un error o una característica. Pero existe un escenario muy sencillo para reproducir este problema. Ella nos tendió una emboscada de manera completamente inesperada. Y consiste en muestrear literalmente de una mesa. Por supuesto, tuvimos más solicitudes de este tipo. Como regla general, incluían dos o tres tablas, pero existe un escenario de reproducción así. Tome cualquier versión de su base de datos y reprodúzcala.
El caso es que tenemos dos columnas, cada una de las cuales está indexada. Hay un millón de filas en una columna NULL. Y la segunda columna contiene sólo 20 líneas. Cuando ejecutamos sin variables vinculadas, todo funciona bien.
Si comenzamos a ejecutar con variables ligadas, es decir ejecutamos el "?" o “$1” por nuestra solicitud, ¿qué terminamos obteniendo?
La primera ejecución es la esperada. El segundo es un poco más rápido. Algo estaba almacenado en caché. Tercero, cuarto, quinto. Luego bang, y algo así. Y lo peor es que esto sucede en la sexta ejecución. ¿Quién sabía que era necesario realizar exactamente seis ejecuciones para comprender cuál era el plan de ejecución real?
¿Quién es culpable? ¿Qué pasó? La base de datos contiene optimización. Y parece estar optimizado para el caso genérico. Y, en consecuencia, a partir de algún momento cambia a un plan genérico que, lamentablemente, puede resultar diferente. Puede resultar igual o puede ser diferente. Y existe algún tipo de valor umbral que conduce a este comportamiento.
¿Qué puedes hacer al respecto? Aquí, por supuesto, es más difícil suponer algo. Hay una solución simple que utilizamos. Esto es +0, OFFSET 0. Seguramente conoces este tipo de soluciones. Simplemente lo tomamos y agregamos “+0” a la solicitud y todo está bien. Te muestro mas tarde.
Y hay otra opción: mirar los planos con más atención. El desarrollador no sólo debe escribir una solicitud, sino también decir "explicar analizar" 6 veces. Si son 5, no funcionará.
Y hay una tercera opción: escribir una carta a pgsql-hackers. Escribí, sin embargo, aún no está claro si se trata de un error o una característica.
Mientras pensamos si esto es un error o una característica, solucionémoslo. Tomemos nuestra solicitud y agreguemos "+0". Todo esta bien. Dos símbolos y ni siquiera tienes que pensar en cómo es o qué es. Muy simple. Simplemente prohibimos a la base de datos utilizar un índice en esta columna. No tenemos índice en la columna “+0” y listo, la base de datos no usa el índice, todo está bien.
Esta es la regla del 6, explica. Ahora en las versiones actuales tienes que hacerlo 6 veces si tienes variables vinculadas. Si no tiene variables vinculadas, esto es lo que hacemos. Y al final es precisamente esta petición la que fracasa. No es algo complicado.
Al parecer, ¿cuánto es posible? Un error aquí, un error allá. En realidad, el error está en todas partes.
Miremos más de cerca. Por ejemplo, tenemos dos esquemas. Esquema A con tabla S y diagrama B con tabla S. Consulta: seleccione datos de una tabla. ¿Qué tendremos en este caso? Tendremos un error. Tendremos todo lo anterior. La regla es: hay un error en todas partes, tendremos todo lo anterior.
¿Ahora la pregunta es porque?" Parecería que hay documentación de que si tenemos un esquema, entonces hay una variable "search_path" que nos dice dónde buscar la tabla. Parecería que hay una variable.
¿Cuál es el problema? El problema es que las declaraciones preparadas por el servidor no sospechan que alguien pueda cambiar search_path. Este valor permanece prácticamente constante para la base de datos. Y es posible que algunas partes no adquieran nuevos significados.
Por supuesto, esto depende de la versión que estés probando. Depende de cuán seriamente difieren sus tablas. Y la versión 9.1 simplemente ejecutará las solicitudes antiguas. Las nuevas versiones pueden detectar el error y decirle que tiene un error.
¿Cómo tratarlo? Hay una receta sencilla: no la hagas. No es necesario cambiar search_path mientras la aplicación se está ejecutando. Si cambia, es mejor crear una nueva conexión.
Puedes discutir, es decir, abrir, discutir, agregar. Tal vez podamos convencer a los desarrolladores de la base de datos de que cuando alguien cambia un valor, la base de datos debería informarle al cliente sobre esto: “Mira, tu valor se ha actualizado aquí. ¿Quizás necesites restablecer las declaraciones y recrearlas? Ahora la base de datos se comporta en secreto y no informa de ninguna manera que las declaraciones hayan cambiado en algún lugar del interior.
Y lo enfatizaré nuevamente: esto es algo que no es típico de Java. Veremos lo mismo en PL/pgSQL uno a uno. Pero allí se reproducirá.
Intentemos más selección de datos. Elegimos y elegimos. Tenemos una tabla con un millón de filas. Cada línea es un kilobyte. Aproximadamente un gigabyte de datos. Y tenemos una memoria de trabajo en la máquina Java de 128 megas.
Nosotros, como se recomienda en todos los libros, utilizamos procesamiento de flujo. Es decir, abrimos resultSet y leemos los datos de ahí poco a poco. ¿Funcionará? ¿Se caerá de la memoria? ¿Leerás un poquito? Confiemos en la base de datos, confiemos en Postgres. No lo creemos. ¿Nos caeremos OutOFMemory? ¿Quién experimentó OutOfMemory? ¿Quién logró arreglarlo después de eso? Alguien logró arreglarlo.
Si tienes un millón de filas, no puedes simplemente elegir. Se requiere COMPENSACIÓN/LÍMITE. ¿Quién está a favor de esta opción? ¿Y quién está a favor de jugar con autoCommit?
Aquí, como siempre, la opción más inesperada resulta ser la correcta. Y si de repente desactivas el autoCommit, será de ayuda. ¿Porqué es eso? La ciencia no sabe nada de esto.
Pero de forma predeterminada, todos los clientes que se conectan a una base de datos de Postgres obtienen todos los datos. PgJDBC no es una excepción a este respecto; selecciona todas las filas.
Hay una variación en el tema FetchSize, es decir, puede decir en el nivel de una declaración separada que aquí, seleccione datos por 10, 50. Pero esto no funciona hasta que desactive la confirmación automática. Desactivado autoCommit: comienza a funcionar.
Pero revisar el código y configurar setFetchSize en todas partes es un inconveniente. Por lo tanto, hicimos una configuración que indicará el valor predeterminado para toda la conexión.
Eso es lo que dijimos. El parámetro ha sido configurado. ¿Y qué conseguimos nosotros? Si seleccionamos cantidades pequeñas, si, por ejemplo, seleccionamos 10 filas a la vez, entonces tenemos costos generales muy grandes. Por lo tanto, este valor debe establecerse en aproximadamente cien.
Lo ideal, por supuesto, es que aún tengas que aprender a limitarlo en bytes, pero la receta es la siguiente: establece defaultRowFetchSize en más de cien y sé feliz.
Pasemos a insertar datos. La inserción es más sencilla, existen diferentes opciones. Por ejemplo, INSERTAR, VALORES. Esta es una buena opción. Puedes decir “INSERTAR SELECCION”. En la práctica es lo mismo. No hay diferencia en el rendimiento.
Los libros dicen que necesita ejecutar una declaración por lotes, los libros dicen que puede ejecutar comandos más complejos con varios paréntesis. Y Postgres tiene una característica maravillosa: puedes COPIAR, es decir, hacerlo más rápido.
Si lo mides, podrás volver a hacer algunos descubrimientos interesantes. ¿Cómo queremos que esto funcione? Queremos no analizar ni ejecutar comandos innecesarios.
En la práctica, TCP no nos permite hacer esto. Si el cliente está ocupado enviando una solicitud, entonces la base de datos no lee las solicitudes en sus intentos de enviarnos respuestas. El resultado final es que el cliente espera a que la base de datos lea la solicitud y la base de datos espera a que el cliente lea la respuesta.
Y por tanto el cliente se ve obligado a enviar periódicamente un paquete de sincronización. Interacciones de red adicionales, pérdida adicional de tiempo.
Y cuanto más los agregamos, peor se pone. El conductor es bastante pesimista y los añade con bastante frecuencia, aproximadamente una vez cada 200 líneas, dependiendo del tamaño de las líneas, etc.
Sucede que corriges solo una línea y todo se acelerará 10 veces. Sucede. ¿Por qué? Como es habitual, una constante como ésta ya se ha utilizado en alguna parte. Y el valor "128" significaba no utilizar procesamiento por lotes.
Es bueno que esto no esté incluido en la versión oficial. Descubierto antes de que comenzara el lanzamiento. Todos los significados que doy se basan en versiones modernas.
Probémoslo. Medimos InsertBatch simple. Medimos InsertBatch varias veces, es decir, lo mismo, pero hay muchos valores. Movimiento complicado. No todo el mundo puede hacer esto, pero es un movimiento muy sencillo, mucho más fácil que COPIAR.
Puedes hacer COPIA.
Y puedes hacer esto en estructuras. Declare el tipo predeterminado del usuario, pase la matriz e INSERTE directamente en la tabla.
Si abre el enlace: pgjdbc/ubenchmsrk/InsertBatch.java, entonces este código está en GitHub. Puede ver específicamente qué solicitudes se generan allí. No importa.
Lanzamos. Y lo primero que nos dimos cuenta fue que no utilizar lotes es simplemente imposible. Todas las opciones de procesamiento por lotes son cero, es decir, el tiempo de ejecución es prácticamente cero en comparación con una ejecución única.
Insertamos datos. Es una mesa muy sencilla. Tres columnas. ¿Y qué vemos aquí? Vemos que estas tres opciones son más o menos comparables. Y COPY es, por supuesto, mejor.
Aquí es cuando insertamos piezas. Cuando dijimos que un valor VALORES, dos valores VALORES, tres valores VALORES, o indicamos 10 de ellos separados por una coma. Esto es sólo horizontal ahora. 1, 2, 4, 128. Se puede ver que el inserto por lotes, que está dibujado en azul, lo hace sentir mucho mejor. Es decir, cuando insertas uno a la vez o incluso cuando insertas cuatro a la vez, se vuelve dos veces mejor, simplemente porque metimos un poco más en VALORES. Menos operaciones de EJECUCIÓN.
Usar COPY en volúmenes pequeños es extremadamente poco prometedor. Ni siquiera dibujé los dos primeros. Se van al cielo, o sea, estos números verdes para COPIAR.
COPY debe usarse cuando tenga al menos cien filas de datos. Los gastos generales de abrir esta conexión son grandes. Y, para ser honesto, no investigué en esta dirección. Optimicé Batch, pero no COPY.
¿Qué hacemos a continuación? Nos lo probamos. Entendemos que necesitamos utilizar estructuras o un bacth inteligente que combine varios significados.
¿Qué debería sacar del informe de hoy?
- PreparedStatement es nuestro todo. Esto aporta mucho a la productividad. Produce un gran fracaso en la pomada.
- Y necesitas EXPLICAR ANALIZAR 6 veces.
- Y necesitamos diluir OFFSET 0 y trucos como +0 para corregir el porcentaje restante de nuestras consultas problemáticas.
Fuente: habr.com