Suxiro que lea a transcrición do informe de principios de 2016 de Vladimir Sitnikov "PostgreSQL e JDBC están exprimindo todo o zume"
Boas tardes Chámome Vladimir Sitnikov. Levo 10 anos traballando para NetCracker. E estou principalmente pola produtividade. Todo o relacionado con Java, todo o relacionado con SQL é o que me encanta.
E hoxe falarei do que nos atopamos na empresa cando comezamos a usar PostgreSQL como servidor de bases de datos. E traballamos principalmente con Java. Pero o que vos vou contar hoxe non é só sobre Java. Como demostrou a práctica, isto tamén ocorre noutras linguas.
Falaremos:
- sobre a mostraxe de datos.
- Sobre o gardar datos.
- E tamén sobre o rendemento.
- E sobre os anciños submarinos que alí están enterrados.
Comecemos cunha pregunta sinxela. Seleccionamos unha fila da táboa en función da clave primaria.
A base de datos está situada no mesmo host. E toda esta agricultura leva 20 milisegundos.
Estes 20 milisegundos son moitos. Se tes 100 solicitudes deste tipo, pasas tempo por segundo percorrendo estas solicitudes, é dicir, estamos perdendo o tempo.
Non nos gusta facelo e mira o que nos ofrece a base para iso. A base de datos ofrécenos dúas opcións para executar consultas.
A primeira opción é unha simple solicitude. Que ten de bo? O feito de que o collemos e o mandemos, e nada máis.
A base de datos tamén ten unha consulta avanzada, que é máis complicada, pero máis funcional. Pode enviar por separado unha solicitude de análise, execución, vinculación de variables, etc.
A consulta súper estendida é algo que non trataremos no informe actual. Nós, quizais, queremos algo da base de datos e hai unha lista de desexos que se formou dalgún xeito, é dicir, isto é o que queremos, pero é imposible agora e no próximo ano. Así que acabamos de gravalo e iremos sacudindo as persoas principais.
E o que podemos facer é unha consulta sinxela e unha consulta ampliada.
Que ten de especial cada enfoque?
Unha consulta sinxela é boa para a execución dunha soa vez. Unha vez feito e esquecido. E o problema é que non admite o formato de datos binarios, é dicir, non é adecuado para algúns sistemas de alto rendemento.
Consulta ampliada: permíteche aforrar tempo na análise. Isto é o que fixemos e comezamos a usar. Isto realmente axudounos. Non só hai aforros na análise. Hai aforros na transferencia de datos. A transferencia de datos en formato binario é moito máis eficiente.
Pasemos á práctica. Así se ve unha aplicación típica. Pode ser Java, etc.
Creamos declaración. Executou o comando. Creado preto. Onde está o erro aquí? Cal é o problema? Sen problema. Isto é o que di en todos os libros. Así debe escribirse. Se queres o máximo rendemento, escribe así.
Pero a práctica demostrou que isto non funciona. Por que? Porque temos un método "pechado". E cando facemos isto, desde o punto de vista da base de datos resulta que é como un fumador traballando cunha base de datos. Dixemos "PARSE EXECUTE DEALLOCATE".
Por que toda esta creación e descarga extra de declaracións? Ninguén os necesita. Pero o que adoita ocorrer en PreparedStatements é que cando as pechamos, pechan todo o que hai na base de datos. Isto non é o que queremos.
Queremos, como xente sa, traballar coa base. Tomamos e preparamos a nosa declaración unha vez, despois executámola moitas veces. De feito, moitas veces - isto é unha vez en toda a vida das aplicacións - foron analizados. E usamos o mesmo id de declaración en REST diferentes. Este é o noso obxectivo.
Como podemos conseguir isto?
É moi sinxelo: non hai que pechar declaracións. Escribimos así: "preparar" "executar".
Se lanzamos algo así, está claro que algo se desbordará nalgún lugar. Se non está claro, podes probalo. Escribamos un punto de referencia que use este método sinxelo. Crea unha declaración. Lanzámolo nalgunha versión do controlador e descubrimos que falla bastante rápido coa perda de toda a memoria que tiña.
Está claro que tales erros son facilmente corrixidos. Non vou falar deles. Pero direi que a nova versión funciona moito máis rápido. O método é estúpido, pero aínda así.
Como traballar correctamente? Que temos que facer para iso?
En realidade, as aplicacións sempre pechan declaracións. En todos os libros din que se pecha, se non, a memoria se escapará.
E PostgreSQL non sabe como almacenar as consultas na caché. É necesario que cada sesión cree esta caché por si mesma.
E tampouco queremos perder o tempo analizando.
E como é habitual temos dúas opcións.
A primeira opción é que o tomemos e digamos que imos envolver todo en PgSQL. Hai un caché alí. Almacena todo en caché. Sairá xenial. Vimos isto. Temos 100500 solicitudes. Non funciona. Non aceptamos converter as solicitudes en procedementos manualmente. Non non.
Temos unha segunda opción: tomala e cortámola nós. Abrimos as fontes e comezamos a cortar. Vimos e vimos. Resultou que non é tan difícil de facer.
Isto apareceu en agosto de 2015. Agora hai unha versión máis moderna. E todo é xenial. Funciona tan ben que non cambiamos nada na aplicación. E ata deixamos de pensar na dirección de PgSQL, é dicir, isto foi suficiente para reducir todos os custos xerais a case cero.
En consecuencia, as instrucións preparadas polo servidor actívanse na quinta execución para evitar perder memoria na base de datos en cada solicitude única.
Podes preguntar: onde están os números? Que estás recibindo? E aquí non vou dar números, porque cada petición ten a súa.
As nosas consultas foron tales que gastamos uns 20 milisegundos en analizar consultas OLTP. Houbo 0,5 milisegundos para a execución, 20 milisegundos para a análise. Solicitude: 10 KiB de texto, 170 liñas de plano. Esta é unha solicitude OLTP. Solicita 1, 5, 10 liñas, ás veces máis.
Pero non queriamos perder 20 milisegundos en absoluto. Reducimos a 0. Todo é xenial.
Que podes levar de aquí? Se tes Java, tomas a versión moderna do controlador e alégrate.
Se falas un idioma diferente, pensa: quizais tamén o necesites? Porque desde o punto de vista da linguaxe final, por exemplo, se PL 8 ou tes LibPQ, entón non che resulta obvio que esteas a dedicar tempo non á execución, á análise, e isto paga a pena comprobar. Como? Todo é gratuíto.
Excepto que hai erros e algunhas peculiaridades. E falaremos deles agora mesmo. A maior parte versará sobre arqueoloxía industrial, sobre o que atopamos, o que atopamos.
Se a solicitude se xera de forma dinámica. Ocorre. Alguén pega as cadeas, o que resulta nunha consulta SQL.
Por que é malo? É malo porque cada vez acabamos cunha corda diferente.
E o hashCode desta cadea diferente cómpre ler de novo. Esta é realmente unha tarefa da CPU: atopar un texto de solicitude longo mesmo nun hash existente non é tan sinxelo. Polo tanto, a conclusión é sinxela: non xerar solicitudes. Gárdaos nunha única variable. E alegrate.
O seguinte problema. Os tipos de datos son importantes. Hai ORM que din que non importa o tipo de NULL que haxa, que haxa algún. Se Int, dicimos setInt. E se NULL, que sempre sexa VARCHAR. E que diferenza fai ao final o que NULL hai? A propia base de datos entenderao todo. E esta imaxe non funciona.
Na práctica, a base de datos non lle importa nada. Se dixo a primeira vez que este é un número e a segunda vez que dixo que é un VARCHAR, entón é imposible reutilizar as instrucións preparadas polo servidor. E neste caso, temos que volver a crear a nosa declaración.
Se está a executar a mesma consulta, asegúrese de que os tipos de datos da súa columna non estean confusos. Debes estar atento a NULL. Este é un erro común que tivemos despois de comezar a usar PreparedStatements
Está ben, acendido. Quizais levaron o condutor. E a produtividade baixou. As cousas puxéronse mal.
Como ocorre isto? É un erro ou unha característica? Desafortunadamente, non foi posible entender se se trata dun erro ou dunha característica. Pero hai un escenario moi sinxelo para reproducir este problema. Ela emboscounos de forma completamente inesperada. E consiste na toma de mostras literalmente dunha mesa. Nós, por suposto, tiñamos máis solicitudes deste tipo. Como regra xeral, incluíron dúas ou tres táboas, pero hai tal escenario de reprodución. Colle calquera versión da túa base de datos e xoga.
A cuestión é que temos dúas columnas, cada unha das cales está indexada. Hai un millón de filas nunha columna NULL. E a segunda columna contén só 20 liñas. Cando executamos sen variables vinculadas, todo funciona ben.
Se comezamos a executar con variables vinculadas, é dicir, executamos o "?" ou "$1" para a nosa solicitude, que acabamos recibindo?
A primeira execución é a esperada. O segundo é un pouco máis rápido. Algo gardouse na caché. Terceiro, cuarto, quinto. Entón golpe - e algo así. E o peor é que isto ocorre na sexta execución. Quen sabía que era necesario facer exactamente seis execucións para entender cal era o plan de execución real?
Quen é o culpable? Que pasou? A base de datos contén optimización. E parece estar optimizado para o caso xenérico. E, en consecuencia, a partir nalgún momento, ela cambia a un plan xenérico, que, por desgraza, pode resultar diferente. Pode resultar ser o mesmo, ou pode ser diferente. E hai algún tipo de valor límite que leva a este comportamento.
Que podes facer respecto diso? Aquí, por suposto, é máis difícil asumir nada. Hai unha solución sinxela que usamos. Isto é +0, OFFSET 0. Seguro que coñeces tales solucións. Simplemente collemos e engadimos "+0" á solicitude e todo está ben. Ensinareino máis tarde.
E hai outra opción: mira os plans con máis atención. O desenvolvedor non só debe escribir unha solicitude, senón tamén dicir "explicar analizar" 6 veces. Se é 5, non funcionará.
E hai unha terceira opción: escribir unha carta para pgsql-hackers. Escribín, con todo, aínda non está claro se se trata dun erro ou dunha función.
Mentres estamos pensando se se trata dun erro ou dunha función, imos solucionalo. Imos tomar a nosa solicitude e engadir "+0". Todo está ben. Dous símbolos e nin sequera tes que pensar como é ou que é. Moi sinxelo. Simplemente prohibimos que a base de datos utilizase un índice nesta columna. Non temos un índice na columna "+0" e xa está, a base de datos non usa o índice, todo está ben.
Esta é a regra de 6 explicacións. Agora nas versións actuais tes que facelo 6 veces se tes variables vinculadas. Se non tes variables vinculadas, isto é o que facemos. E ao final é precisamente esta petición a que falla. Non é unha cousa complicada.
Parece que canto é posible? Un erro aquí, un erro alí. En realidade, o erro está en todas partes.
Vexamos máis de cerca. Por exemplo, temos dous esquemas. Esquema A coa táboa S e diagrama B coa táboa S. Consulta: selecciona datos dunha táboa. Que teremos neste caso? Teremos un erro. Teremos todo o anterior. A regra é: un erro está en todas partes, teremos todo o anterior.
Agora a pregunta é: "Por que?" Parece que hai documentación de que se temos un esquema, entón hai unha variable "search_path" que nos indica onde buscar a táboa. Parece que hai unha variable.
Cal é o problema? O problema é que as declaracións preparadas polo servidor non sospeitan que alguén pode cambiar o camiño_busca. Este valor permanece, por así decirlo, constante para a base de datos. E algunhas partes poden non coller novos significados.
Por suposto, isto depende da versión na que esteas probando. Depende do en serio que sexan as túas táboas diferentes. E a versión 9.1 simplemente executará as solicitudes antigas. As novas versións poden detectar o erro e dicirche que tes un erro.
Como tratalo? Hai unha receita sinxela: non o fagas. Non é necesario cambiar search_path mentres a aplicación está en execución. Se cambia, é mellor crear unha nova conexión.
Podes discutir, é dicir, abrir, discutir, engadir. Quizais poidamos convencer aos desenvolvedores de bases de datos de que cando alguén cambia un valor, a base de datos debería informarlle ao cliente sobre isto: "Mira, o teu valor actualizouse aquí. Quizais necesites restablecer as declaracións e recrealas?" Agora a base de datos compórtase en segredo e non informa de ningún xeito que as declaracións cambiaron nalgún lugar dentro.
E volverei enfatizar: isto é algo que non é típico de Java. Veremos o mesmo en PL/pgSQL un a un. Pero alí reproducirase.
Imos tentar seleccionar máis datos. Escollemos e escollemos. Temos unha táboa cun millón de filas. Cada liña é un kilobyte. Aproximadamente un gigabyte de datos. E temos unha memoria de traballo na máquina Java de 128 megabytes.
Como recomendamos en todos os libros, utilizamos o procesamento de fluxos. É dicir, abrimos resultSet e lemos os datos dende alí pouco a pouco. Funcionará? Caerá da memoria? Vai ler un pouco? Confiemos na base de datos, confiemos en Postgres. Non o cremos. Caeremos fóra da memoria? Quen experimentou OutOfMemory? Quen conseguiu arranxalo despois diso? Alguén conseguiu arranxalo.
Se tes un millón de filas, non podes simplemente escoller e escoller. Requírese OFFSET/LIMIT. Quen é para esta opción? E quen é partidario de xogar con autoCommit?
Aquí, como é habitual, a opción máis inesperada resulta ser correcta. E se de súpeto desactivas autoCommit, axudarache. Por que é iso? A ciencia non sabe disto.
Pero de forma predeterminada, todos os clientes que se conectan a unha base de datos de Postgres obteñen todos os datos. PgJDBC non é unha excepción a este respecto; selecciona todas as filas.
Hai unha variación no tema FetchSize, é dicir, pode dicir a nivel dunha declaración separada que aquí, seleccione os datos por 10, 50. Pero isto non funciona ata que desactive a confirmación automática. AutoCommit desactivado: comeza a funcionar.
Pero pasar polo código e configurar setFetchSize en todas partes é un inconveniente. Polo tanto, fixemos unha configuración que dirá o valor predeterminado para toda a conexión.
Iso dixemos. O parámetro foi configurado. E que conseguimos? Se seleccionamos cantidades pequenas, se, por exemplo, seleccionamos 10 filas á vez, entón temos custos xerais moi grandes. Polo tanto, este valor debe establecerse nuns cen.
O ideal, por suposto, aínda tes que aprender a limitalo en bytes, pero a receita é esta: establece defaultRowFetchSize en máis de cen e sé feliz.
Pasemos á inserción de datos. A inserción é máis sinxela, hai diferentes opcións. Por exemplo, INSERT, VALUES. Esta é unha boa opción. Podes dicir "INSERT SELECT". Na práctica é o mesmo. Non hai diferenza no rendemento.
Os libros din que cómpre executar unha instrución Batch, os libros din que podes executar comandos máis complexos con varios parénteses. E Postgres ten unha característica marabillosa: podes facer COPIA, é dicir, facelo máis rápido.
Se o mides, podes facer de novo algúns descubrimentos interesantes. Como queremos que isto funcione? Queremos non analizar e non executar comandos innecesarios.
Na práctica, TCP non nos permite facelo. Se o cliente está ocupado enviando unha solicitude, entón a base de datos non le as solicitudes nos intentos de enviarnos respostas. O resultado final é que o cliente espera a que a base de datos lea a solicitude e a base de datos agarda a que o cliente lea a resposta.
E polo tanto, o cliente está obrigado a enviar periodicamente un paquete de sincronización. Interaccións de rede extra, perda de tempo extra.
E canto máis os engadimos, peor vai. O condutor é bastante pesimista e engádeos con bastante frecuencia, aproximadamente unha vez cada 200 liñas, dependendo do tamaño das liñas, etc.
Acontece que corrixe só unha liña e todo se acelerará 10 veces. Ocorre. Por que? Como é habitual, unha constante como esta xa se utilizou nalgún lugar. E o valor "128" significaba non usar lotes.
É bo que isto non fose incluído na versión oficial. Descuberto antes do lanzamento. Todos os significados que dou están baseados en versións modernas.
Probámolo. Medimos InsertBatch simple. Medimos InsertBatch varias veces, é dicir, o mesmo, pero hai moitos valores. Movemento complicado. Non todos poden facelo, pero é un movemento tan sinxelo, moito máis doado que COPIAR.
Podes facer COPIA.
E podes facelo en estruturas. Declarar o tipo predeterminado de usuario, pasar matriz e INSERTAR directamente á táboa.
Se abres a ligazón: pgjdbc/ubenchmsrk/InsertBatch.java, este código está en GitHub. Podes ver especificamente que solicitudes se xeran alí. Non importa.
Lanzamos. E o primeiro que nos demos conta foi que non usar batch é simplemente imposible. Todas as opcións de lotes son cero, é dicir, o tempo de execución é practicamente cero en comparación cunha execución única.
Introducimos datos. É unha táboa moi sinxela. Tres columnas. E que vemos aquí? Vemos que estas tres opcións son aproximadamente comparables. E COPIAR é, por suposto, mellor.
Isto é cando introducimos pezas. Cando dixemos que un valor VALUES, dous valores VALORES, tres valores VALORES, ou indicamos 10 deles separados por coma. Isto é só horizontal agora. 1, 2, 4, 128. Pódese ver que o Inserto de lote, que está debuxado en azul, fai que se sinta moito mellor. É dicir, cando inseris un a un ou mesmo cando inseris catro á vez, faise o dobre de bo, simplemente porque ateigamos un pouco máis en VALORES. Menos operacións EXECUTE.
Usar COPY en pequenos volumes é moi pouco prometedor. Nin sequera debuxei nos dous primeiros. Van ao ceo, é dicir, estes números verdes para COPIAR.
COPY debe usarse cando teña polo menos cen filas de datos. A sobrecarga de abrir esta conexión é grande. E, para ser sincero, non cavei nesta dirección. Optimicei Batch, pero non COPY.
Que facemos despois? Probámolo. Entendemos que necesitamos utilizar estruturas ou un baño intelixente que combine varios significados.
Que debes quitar do informe de hoxe?
- PreparedStatement é o noso todo. Isto dá moito para a produtividade. Produce un gran fracaso na pomada.
- E cómpre facer EXPLICAR ANALIZAR 6 veces.
- E necesitamos diluír OFFSET 0 e trucos como +0 para corrixir a porcentaxe restante das nosas consultas problemáticas.
Fonte: www.habr.com