optimizar - Actualice la tabla de base de datos PostgreSQL MUY GRANDE de manera eficiente

optimizar base de datos sql server

Question

Tengo una tabla de base de datos muy grande en PostgresQL y una columna como "copiado". Cada nueva fila comienza sin copia y luego será replicada a otra cosa por un programa de fondo. Hay un índice parcial en esa tabla "btree (ID) WHERE replicated = 0". El programa de fondo selecciona entre 2000 entradas como máximo (LÍMITE 2000), trabaja en ellas y luego confirma los cambios en una transacción utilizando 2000 comandos sql preparados.

Ahora el problema es que quiero darle al usuario la opción de restablecer este valor duplicado, hacer que vuelva a cero.

Un conjunto de tablas de actualización replicado = 0;

no es posible:

• Toma mucho tiempo
• Duplica el tamaño de la tabla debido a MVCC
• Se realiza en una transacción: o falla o se procesa.

En realidad, no necesito funciones de transacción para este caso: si el sistema deja de funcionar, procesará solo partes de él.

Varios otros problemas: hacer una

update set replicated=0 where id >10000 and id<20000

también es malo: hace un escaneo secuencial en toda la mesa, que es demasiado lento. Si no lo hiciera, aún sería lento porque sería demasiadas búsquedas.

Lo que realmente necesito es una forma de recorrer todas las filas, cambiarlas y no estar obligado a una transacción gigante.

Extrañamente, un

UPDATE table 
  SET replicated=0 
WHERE ID in (SELECT id from table WHERE replicated= LIMIT 10000)

también es lento, aunque debería ser algo bueno: pasar por la tabla en orden DISCO ...

(Tenga en cuenta que en ese caso también había un índice que cubría esto)

(Un LÍMITE de actualización como Mysql no está disponible para PostgresQL)

Por cierto: el problema real es más complicado y estamos hablando de un sistema integrado aquí que ya está implementado, por lo que los cambios de esquema remoto son difíciles, pero posibles. Desafortunadamente, PostgresQL 7.4.

La cantidad de filas de las que estoy hablando es, por ejemplo, 90000000. El tamaño de la base de datos puede ser de varios docenas y gigabytes.

La base de datos en sí misma solo contiene 5 tablas, una es muy grande. Pero ese no es un mal diseño, porque estos cuadros integrados solo operan con un tipo de entidad, ¡no es un sistema ERP o algo así!

¿Algunas ideas?

Answer 1

¿Qué le parece agregar una nueva tabla para almacenar este valor replicado (y una clave principal para vincular cada registro a la tabla principal). Luego, simplemente agrega un registro por cada elemento replicado y elimina los registros para eliminar el indicador replicado. (O tal vez al revés: un registro por cada registro no replicado, dependiendo de cuál sea el caso común).

Eso también simplificaría el caso cuando desee volver a ponerlos a 0, ya que puede truncar la tabla (lo que pone a cero el tamaño de la tabla en el disco, ni siquiera tiene que pasar la aspiradora para liberar el espacio)

Answer 2

Si está intentando restablecer toda la tabla, no solo unas pocas filas, generalmente es más rápido (en conjuntos de datos extremadamente grandes, no en tablas regulares) para simplemente CREATE TABLE bar AS SELECT everything, but, copied, 0 FROM foo , y luego intercambie las tablas y deje caer la anterior. Obviamente, deberá asegurarse de que no se inserta nada en la tabla original mientras lo hace. También deberá volver a crear ese índice.

Editar : una mejora simple para evitar bloquear la tabla mientras copia 14 gigabytes:

lock ;
create a new table, bar;
swap tables so that all writes go to bar;
unlock;
create table baz as select from foo;
drop foo;
create the index on baz;
lock;
insert into baz from bar;
swap tables;
unlock;
drop bar;

(Deja que las escrituras sucedan mientras haces la copia, e insértalas post-factum).

Answer 3

Esto es pseudocódigo. Necesitará un archivo temporal de 400MB (para ints) o 800MB (para bigints) (puede comprimirlo con zlib si es un problema). Necesitará aproximadamente 100 escaneos de una mesa para aspiradoras. Pero no inflará una mesa más de 1% (como máximo 1000000 filas muertas en cualquier momento). También puede intercambiar menos exploraciones para obtener más inflado de la tabla.

// write all ids to temporary file in disk order                
// no where clause will ensure disk order
$file = tmpfile();
for $id, $replicated in query("select id, replicated from table") {
        if ( $replicated<>0 ) {
                write($file,&$id,sizeof($id));
        }
}

// prepare an update query
query("prepare set_replicated_0(bigint) as
        update table set replicated=0 where id=?");

// reread this file, launch prepared query and every 1000000 updates commit
// and vacuum a table
rewind($file);
$counter = 0;
query("start transaction");
while read($file,&$id,sizeof($id)) {
        query("execute set_replicated_0($id)");
        $counter++;
        if ( $counter % 1000000 == 0 ) {
                query("commit");
                query("vacuum table");
                query("start transaction");
        }
}
query("commit");
query("vacuum table");
close($file);

Answer 4

Si bien no es probable que resuelva el problema del uso del espacio (es temporal, solo hasta que se produzca el vacío), probablemente puedas acelerar el proceso en términos de tiempo de reloj. El hecho de que PostgreSQL use MVCC significa que debe poder hacer esto sin ningún problema relacionado con las filas recién insertadas. La tabla de creación como selección sorteará algunos de los problemas de rendimiento, pero no permitirá el uso continuado de la tabla y ocupará el mismo espacio. Simplemente abandona el índice, y recíbelo, luego haz un vacío.

drop index replication_flag;
update big_table set replicated=0;
create index replication_flag on big_table btree(ID) WHERE replicated=0;
vacuum full analyze big_table;

Answer 5

Supongo que lo que debes hacer es a. copie el valor PK de 2000 registros en una tabla temporal con el mismo límite estándar, etc. b. seleccione los mismos 2000 registros y realice las operaciones necesarias en el cursor tal como está. do. Si tiene éxito, ejecute una sola consulta de actualización contra los registros en la tabla temporal. Borre la tabla temporal y ejecute el paso a nuevamente. re. Si no tiene éxito, borre la tabla temporal sin ejecutar la consulta de actualización. Simple, eficiente y confiable. Saludos, KT

Answer 6

Creo que es mejor cambiar tu postgres a la versión 8.X. probablemente la causa sea la versión baja de Postgres. Pruebe también esta consulta a continuación. Espero que esto pueda ayudar.

UPDATE table1 SET name = table2.value
FROM table2 
WHERE table1.id = table2.id;