Por defecto, PostgreSQL no está configurado para la carga de trabajo. Los valores predeterminados se establecen para garantizar que PostgreSQL sea funcional en todas partes con la menor cantidad de recursos. Hay configuraciones predeterminadas para todas las configuraciones de la base de datos. La responsabilidad principal de un administrador o desarrollador de bases de datos es configurar PostgreSQL para que se ajuste a la carga de su sistema. En este blog, describiremos algunas pautas básicas para ajustar la configuración de la base de datos PostgreSQL para mejorar el rendimiento de la base de datos de acuerdo con su carga de trabajo.
Tenga en cuenta que si bien la optimización de la configuración de un servidor PostgreSQL mejora el rendimiento, el diseñador de la base de datos también debe tener cuidado al escribir consultas. Si las consultas realizan un escaneo completo de la tabla donde se puede usar un índice, o realizan uniones pesadas o costosas operaciones de agregación, entonces el sistema aún puede funcionar mal, incluso si la configuración de la base de datos está configurada correctamente. Al escribir consultas en la base de datos, es importante prestar atención al rendimiento.
Sin embargo, los parámetros de la base de datos también son muy importantes, así que echemos un vistazo a los ocho que tienen el mayor potencial para mejorar el rendimiento.
Opciones personalizadas de PostgreSQL
PostgreSQL usa su propio búfer, y también usa un núcleo de E / S con búfer. Esto significa que los datos se almacenan en la memoria dos veces, primero en el búfer de PostgreSQL y luego en el búfer del núcleo. A diferencia de otras bases de datos, PostgreSQL no proporciona E / S directa. Esto se llama doble búfer. El búfer PostgreSQL se llama
shared_buffer , que es el parámetro personalizado más eficiente para la mayoría de los sistemas operativos. Este parámetro establece la cantidad de memoria asignada que PostgreSQL usará para el almacenamiento en caché.
El valor predeterminado para shared_buffer se establece muy bajo y no obtendrá muchos beneficios. Esto se debe a que algunas máquinas y sistemas operativos no admiten valores más altos. Pero en la mayoría de las máquinas modernas necesita aumentar este valor para obtener un rendimiento óptimo.
El valor recomendado es el 25% de la RAM total de la computadora. Debe probar algunos valores cada vez más bajos, porque en algunos casos puede obtener un buen rendimiento con una configuración de más del 25%. Pero la configuración real depende de su máquina y el conjunto de datos de trabajo. Si su conjunto de datos de trabajo puede caber fácilmente en su RAM, puede aumentar el valor de shared_buffer para que contenga toda su base de datos y todo el conjunto de datos de trabajo pueda estar en la memoria caché. Sin embargo, obviamente no desea reservar toda la RAM para PostgreSQL.
Se observa que en entornos de producción, un buen rendimiento realmente le da gran importancia a shared_buffer, aunque las pruebas siempre deben realizarse para lograr el equilibrio correcto.
Comprobando el valor de shared_buffertestdb=
Nota : Tenga cuidado, ya que algunos núcleos no admiten un valor mayor , especialmente en Windows.wal_buffers
PostgreSQL primero escribe las entradas en el WAL (registro previo) en los buffers, y luego estos buffers se vuelcan al disco. El tamaño predeterminado del búfer definido por
wal_buffers es de 16 MB. Pero si tiene muchas conexiones concurrentes, un valor más alto puede mejorar el rendimiento.
eficaz_caché_tamaño
eficaz_caché_tamaño proporciona una estimación de la memoria disponible para el almacenamiento en caché del disco. Esto es solo una guía, no la cantidad exacta de memoria asignada o caché. No asigna memoria real, pero le dice al optimizador la cantidad de caché disponible en el núcleo. Si este parámetro se establece demasiado bajo, el planificador de consultas puede decidir no usar algunos índices, incluso si son útiles. Por lo tanto, establecer un gran valor siempre tiene sentido.
work_mem
Esta configuración se utiliza para la ordenación compleja. Si necesita hacer una clasificación compleja, aumente el valor de
work_mem para obtener buenos resultados. Ordenar en la memoria es mucho más rápido que ordenar los datos en el disco. Establecerlo en un valor muy alto puede causar un cuello de botella de memoria para su entorno, ya que esta opción se relaciona con la operación de clasificación del usuario. Por lo tanto, si tiene muchos usuarios intentando realizar operaciones de clasificación, el sistema resaltará:
work_mem * total sort operations
para todos los usuarios Establecer este parámetro globalmente puede resultar en un uso de memoria muy alto. Por lo tanto, es muy recomendable que lo cambie a nivel de sesión.
work_mem = 2MB testdb=
El nodo de clasificación de solicitud inicial se evalúa en 514431.86. El costo es una unidad calculada arbitraria. Para la solicitud anterior, tenemos work_mem solo 2 MB. Para fines de prueba, aumentemos este valor a 256 MB y veamos si afecta el costo.
work_mem = 256MB testdb=
El costo de la solicitud se redujo de 514431.86 a 360617.36, es decir, disminuyó en un 30%.
mantenimiento_trabajo_mem
maintenance_work_mem es un parámetro de memoria utilizado para tareas de mantenimiento. El valor predeterminado es 64 MB. Establecer un valor grande ayuda en tareas como VACÍO, RESTAURAR, CREAR ÍNDICE, AGREGAR CLAVE EXTRANJERA y ALTERAR TABLA.
maintenance_work_mem = 10MB postgres=
maintenance_work_mem = 256MB postgres=
El tiempo de creación del índice es 170091.371 ms si el parámetro maintenance_work_mem se establece en solo 10 MB, pero disminuye a 111274.903 ms cuando aumentamos el parámetro maintenance_work_mem a 256 MB.
síncrono de compromiso
Se utiliza para garantizar que una confirmación de transacción espere a que un WAL escriba en el disco antes de devolver un estado de finalización exitoso al cliente. Esta es una compensación entre rendimiento y confiabilidad. Si su aplicación está diseñada de tal manera que el rendimiento es más importante que la confiabilidad, deshabilite
synchronous_commit . En este caso, la transacción se confirma muy rápidamente porque no esperará a que se restablezca el archivo WAL, pero la confiabilidad se verá comprometida. En el caso de una falla del servidor, los datos pueden perderse incluso si el cliente recibió un mensaje que indica que la confirmación de la transacción se completó con éxito.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL escribe cambios en el WAL. El proceso de punto de control vacía los datos a los archivos. Esta acción se realiza cuando se produce un punto de interrupción (CHECKPOINT). Esta es una operación costosa y puede causar una gran cantidad de operaciones de E / S. Todo este proceso implica costosas operaciones de lectura / escritura en el disco. Los usuarios siempre pueden iniciar la tarea de punto de control (CHECKPOINT) cuando sea necesario, o automatizar el inicio utilizando los parámetros
checkpoint_timeout y
checkpoint_completion_target .
El parámetro checkpoint_timeout se usa para establecer el tiempo entre los puntos de interrupción de WAL. Establecerlo demasiado bajo reduce el tiempo de recuperación después de una falla porque se escriben más datos en el disco, pero también reduce el rendimiento porque cada punto de control finalmente consume recursos valiosos del sistema.
checkpoint_completion_target es la fracción del tiempo entre puntos de control para completar un punto de control. Los puntos de control de alta frecuencia pueden afectar el rendimiento. Para completar sin problemas el trabajo de
punto de control ,
checkpoint_timeout debe ser bajo. De lo contrario, el sistema operativo acumulará todas las páginas sucias hasta que se observe la relación, y luego producirá un reinicio grande.
Conclusión
Hay más opciones que puede ajustar para obtener un mejor rendimiento, pero tienen menos impacto que las resaltadas aquí. Al final, siempre debemos recordar que no todos los parámetros son relevantes para todo tipo de aplicaciones. Algunas aplicaciones funcionan mejor al configurar las opciones, y otras no. La configuración de la base de datos PostgreSQL debe adaptarse a las necesidades específicas de la aplicación y el sistema operativo en el que se ejecuta.