sakila postgres database postgresql

postgresql sakila database



¿Aumentar la velocidad de escritura de PostgreSQL a costa de la probable pérdida de datos? (7)

1M confirmaciones en 22 minutos parece razonable, incluso con synchronous_commit = off , pero si puede evitar la necesidad de confirmar cada inserción, puede obtener mucho más rápido que eso. Acabo de intentar insertar filas 1M (idénticas) en su tabla de ejemplo de 10 escritores simultáneos, usando el comando COPY inserción masiva:

$ head -n3 users.txt | cat -A # the rest of the file is just this another 99997 times Random J. User^[email protected]^Ihttp://example.org^I100$ Random J. User^[email protected]^Ihttp://example.org^I100$ Random J. User^[email protected]^Ihttp://example.org^I100$ $ wc -l users.txt 100000 users.txt $ time (seq 10 | xargs --max-procs=10 -n 1 bash -c "cat users.txt | psql insertspeed -c ''COPY /"user/" (username, email, website, created) FROM STDIN WITH (FORMAT text);''") real 0m10.589s user 0m0.281s sys 0m0.285s $ psql insertspeed -Antc ''SELECT count(*) FROM "user"'' 1000000

Claramente, solo hay 10 confirmaciones, lo que no es exactamente lo que estás buscando, pero eso te da algún tipo de indicación de la velocidad que podría ser posible al agrupar tus inserciones. Esto está en una VM VirtualBox que ejecuta Linux en un host de escritorio Windows bastante estándar, por lo que no es exactamente el hardware de mayor rendimiento posible.

Para dar algunas cifras de juguetes menos, tenemos un servicio en producción que tiene un solo hilo que transmite datos a Postgres a través de un comando COPY similar al anterior. Finaliza un lote y se confirma después de un cierto número de filas o si la transacción alcanza una cierta edad (lo que ocurra primero). Puede sostener 11,000 inserciones por segundo con una latencia máxima de ~ 300 ms haciendo ~ 4 confirmaciones por segundo. Si ajustáramos la edad máxima permitida de las transacciones, obtendríamos más confirmaciones por segundo, lo que reduciría la latencia pero también el rendimiento. Una vez más, esto no es en hardware terriblemente impresionante.

Sobre la base de esa experiencia, recomiendo encarecidamente intentar usar COPY lugar de INSERT , y tratar de reducir el número de confirmaciones en la medida de lo posible, mientras se sigue logrando su objetivo de latencia.

Me encanta que PostgreSQL sea resistente a los choques, ya que no quiero perder tiempo arreglando una base de datos . Sin embargo, estoy seguro de que debe haber algunas cosas que puedo deshabilitar / modificar para que las inserciones / actualizaciones funcionen más rápido, incluso si pierdo un par de registros antes de un corte de energía. No me preocupa un par de registros, solo la base de datos en su conjunto.

Estoy tratando de optimizar PostgreSQL para grandes cantidades de escrituras. Actualmente se necesitan 22 minutos para insertar 1 millón de filas, lo que parece un poco lento.

¿Cómo puedo acelerar las escrituras de PostgreSQL?

Algunas de las opciones que he examinado (como full_page_writes) parecen correr el riesgo de corromper los datos, lo que no es algo que quiero. No me importa la pérdida de datos, simplemente no quiero corrupción.

Actualización 1

Aquí está la tabla que estoy usando - esto ya que la mayoría de las tablas contendrán caracteres de tamaño y cadenas pequeñas, esta tabla de "muestra" parece ser el mejor ejemplo de lo que debería esperar.

CREATE TABLE "user" ( id serial NOT NULL, username character varying(40), email character varying(70), website character varying(100), created integer, CONSTRAINT user_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); CREATE INDEX id ON "user" USING btree (id);

Tengo alrededor de 10 scripts que emiten 100,000 solicitudes a la vez utilizando declaraciones preparadas. Esto es para simular una carga de la vida real que mi aplicación dará a la base de datos. En mi aplicación cada página tiene 1+ inserciones.

Actualización 2

Ya estoy usando confirmaciones asíncronas, porque tengo

synchronous_commit = off

en el archivo de configuración principal.

22 minutos para 1 millón de filas no parece ser tan lento, especialmente si tiene muchos índices.

¿Cómo estás haciendo las inserciones? Supongo que está utilizando inserciones por lotes, no una fila por transacción.

¿PG admite algún tipo de carga masiva, como leer un archivo de texto o proporcionarle un flujo de datos CSV? Si es así, probablemente sería mejor que lo usaras.

Publique el código que está utilizando para cargar los registros de 1M, y la gente lo recomendará.

Por favor publique:

  • Sentencia CREATE TABLE para la tabla en la que está cargando
  • Código que está utilizando para cargar
  • pequeño ejemplo de los datos (si es posible)

EDITAR: Parece que el OP no está interesado en inserciones masivas, pero está haciendo una prueba de rendimiento para muchas inserciones de una sola fila. Asumiré que cada inserto está en su propia transacción.

  • Considere la posibilidad de agrupar las inserciones en el lado del cliente, por nodo, escribirlas en un archivo temporal (con suerte duradera / robusta) y tener un demonio o algún proceso periódico que realice de forma asincrónica una inserción de lotes de registros pendientes, en lotes de tamaño razonable.
  • Este mecanismo de procesamiento por lotes por dispositivo realmente proporciona el mejor rendimiento, según mi experiencia, en datos de auditoría como las aplicaciones de almacenamiento de datos donde los datos no necesitan ingresar a la base de datos en este momento . También le da a la aplicación resistencia contra la base de datos que no está disponible.
  • Por supuesto, normalmente tendrá varios dispositivos de punto final que crean registros de auditoría (por ejemplo, conmutadores telefónicos, retransmisiones de correo, servidores de aplicaciones web), cada uno debe tener su propia instancia de este mecanismo, que es totalmente independiente.
  • Esta es una optimización realmente "inteligente" que introduce mucha complejidad en el diseño de la aplicación y tiene una gran cantidad de lugares donde podrían ocurrir errores. No lo implemente a menos que esté realmente seguro de que lo necesita.

Bueno, no nos das mucho para seguir. Pero suena como si estuvieras buscando confirmaciones asíncronas .

No pase por alto una actualización de hardware: un hardware más rápido generalmente significa una base de datos más rápida.

Bueno, una cosa que podría hacer para acelerar el proceso es eliminar el índice que está creando manualmente: la restricción de la primary key ya crea automáticamente un índice único en esa columna, como puede ver a continuación (estoy probando en 8.3):

postgres=> CREATE TABLE "user" postgres-> ( postgres(> id serial NOT NULL, postgres(> username character varying(40), postgres(> email character varying(70), postgres(> website character varying(100), postgres(> created integer, postgres(> CONSTRAINT user_pkey PRIMARY KEY (id) postgres(> ) postgres-> WITH ( OIDS=FALSE ); NOTICE: CREATE TABLE will create implicit sequence "user_id_seq" for serial column "user.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "user_pkey" for table "user" CREATE TABLE postgres=> CREATE INDEX id ON "user" USING btree (id); CREATE INDEX postgres=> /d user Table "stack.user" Column | Type | Modifiers ----------+------------------------+--------------------------------------------------- id | integer | not null default nextval(''user_id_seq''::regclass) username | character varying(40) | email | character varying(70) | website | character varying(100) | created | integer | Indexes: "user_pkey" PRIMARY KEY, btree (id) "id" btree (id)

También, considere cambiar wal_sync_method a una opción que use O_DIRECT - este no es el predeterminado en Linux

Creo que el problema no se puede resolver tratando solo con el servidor.

Encontré que PostgreSQL puede comprometer más de 3000 filas por segundo, y tanto el servidor como el cliente no estaban ocupados, pero el tiempo pasó. En contraste, SQL Server puede alcanzar más de 5000 filas por segundo, y Oracle es aún más rápido, puede llegar a 12,000+ por segundo, aproximadamente 20 campos seguidos.

Supongo que el viaje de ida y vuelta es el problema: envíe una fila al servidor y reciba la respuesta del servidor. Tanto SQL Server como Oracle admiten operaciones por lotes: envíe más de una fila en una llamada de función y espere la respuesta.

Hace muchos años trabajé con Oracle: al intentar mejorar el rendimiento de escritura utilizando OCI, leo documentos y descubrí que demasiados viajes redondean el rendimiento. Finalmente, lo resolví mediante operaciones por lotes: envíe 128 o más filas al servidor en un lote y espere la respuesta. Alcanzó más de 12000 filas por segundo. Si no usa lotes y envía todas las filas individualmente (incluida la espera), solo alcanzó unas 2000 filas por segundo.

Los registros de 1M insertados en 22 minutos resultan en 758 registros / segundo. Cada INSERT aquí es un compromiso individual para el disco, con el registro de escritura anticipada y los componentes de la base de datos eventualmente. Normalmente espero que incluso un buen hardware con un caché respaldado por batería y todo lo que tendrá la suerte de alcanzar 3000 cometer / segundo. Entonces, en realidad no lo está haciendo tan mal si este es un hardware normal sin tal aceleración de escritura. El límite normal aquí está en el rango de 500 a 1000 confirmaciones / segundo en la situación en la que se encuentra, sin ajustes especiales para esta situación.

En cuanto a cómo se vería, si no puede hacer que los compromisos incluyan más registros cada uno, sus opciones para acelerar esto incluyen:

  • Desactivar synchronous_commit (ya hecho)

  • Incrementa wal_writer_delay. Cuando synchronous_commit está desactivado, los spools de la base de datos se comprometen a escribirse cada 200 ms. Puede hacer ese número de segundos en lugar de eso, si lo desea al ajustar esto hacia arriba, solo aumenta el tamaño de la pérdida de datos después de una caída.

  • Incremente wal_buffers a 16MB, solo para hacer que toda la operación sea más eficiente.

  • Aumente checkpoint_segments, para reducir la frecuencia con la que se escriben los datos normales en el disco. Probablemente quieras al menos 64 aquí. Las desventajas son un mayor uso de espacio en disco y un tiempo de recuperación más largo después de una caída.

  • Aumentar los cookies compartidos. El valor predeterminado aquí es pequeño, normalmente de 32 MB. Debe aumentar la cantidad de memoria compartida UNIX que el sistema debe asignar. Una vez hecho esto, los valores útiles suelen ser> 1/4 del RAM total, hasta 8 GB. La tasa de ganancia aquí cae por encima de 256 MB, aunque el aumento desde el valor predeterminado hasta allí puede ser realmente útil.

Eso es practicamente todo. Cualquier otra cosa que haya tocado que pueda ayudar podría causar la corrupción de datos en un accidente; estos son todos completamente seguros.

También debe aumentar checkpoint_segments (por ejemplo, a 32 o incluso más) y, probablemente, también wal_buffers

Editar:
Si se trata de una carga masiva, debe usar COPY para insertar las filas. Es mucho más rápido que los INSERTOS simples.

Si necesita usar INSERT, ¿consideró usar lotes (para JDBC) o inserciones de varias filas?