rendimiento - optimizar consultas sql server 2014

Cómo acelerar el rendimiento de inserción en PostgreSQL

Question

Estoy probando el rendimiento de inserción de Postgres. Tengo una tabla con una columna con número como su tipo de datos. Hay un índice también. Llené la base de datos usando esta consulta:

insert into aNumber (id) values (564),(43536),(34560) ...

Inserté 4 millones de filas muy rápidamente, 10 000 a la vez con la consulta anterior. Después de que la base de datos alcanzó 6 millones de filas, el rendimiento disminuyó drásticamente a 1 millón de filas cada 15 minutos. ¿Hay algún truco para aumentar el rendimiento de inserción? Necesito un rendimiento de inserción óptimo en este proyecto.

Usando Windows 7 Pro en una máquina con 5 GB de RAM.

Answer 1

Además de la excelente publicación de Craig Ringer y la publicación de blog de depesz, si desea acelerar sus inserciones a través de la interfaz ODBC ( psqlodbc ) mediante el uso de inserciones de enunciados preparados dentro de una transacción, hay algunas cosas adicionales que debe hacer para hacerlo trabaja rapido:

1. Establezca el nivel de rollback-on-errors en "Transaction" especificando Protocol=-1 en la cadena de conexión. De forma predeterminada, psqlodbc usa el nivel de "Declaración", que crea un SAVEPOINT para cada declaración en lugar de una transacción completa, lo que hace que las inserciones sean más lentas.
2. Utilice declaraciones preparadas del lado del servidor especificando UseServerSidePrepare=1 en la cadena de conexión. Sin esta opción, el cliente envía toda la instrucción de inserción junto con cada fila que se inserta.
3. Deshabilitar la confirmación automática en cada instrucción mediante SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
4. Una vez que se hayan insertado todas las filas, SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); la transacción usando SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT); . No es necesario abrir explícitamente una transacción.

Desafortunadamente, psqlodbc "implementa" SQLBulkOperations al emitir una serie de instrucciones de inserción no preparadas, de modo que para lograr la inserción más rápida es necesario codificar manualmente los pasos anteriores.

Answer 2

Encontré este problema de rendimiento de inserción también. Mi solución genera algunas rutinas para terminar el trabajo de inserción. Mientras tanto, a SetMaxOpenConns se le debe dar un número adecuado, de lo contrario se SetMaxOpenConns demasiados errores de conexión abierta.

db, _ := sql.open() 
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
var wg sync.WaitGroup
for _, query := range queries {
    wg.Add(1)
    go func(msg string) {
        defer wg.Done()
        _, err := db.Exec(msg)
        if err != nil {
            fmt.Println(err)
        }
    }(query)
}
wg.Wait()

La velocidad de carga es mucho más rápida para mi proyecto. Este fragmento de código acaba de dar una idea de cómo funciona. Los lectores deberían poder modificarlo fácilmente.

Answer 3

Para un rendimiento de inserción óptimo, deshabilite el índice si esa es una opción para usted. Aparte de eso, un mejor hardware (disco, memoria) también es útil

Answer 4

Use la COPY table TO ... WITH BINARY que según la documentación es " algo más rápida que los formatos de texto y CSV ". Solo haga esto si tiene millones de filas para insertar y si se siente cómodo con los datos binarios.

Aquí hay una receta de ejemplo en Python, usando psycopg2 con entrada binaria .

Answer 5

Vea llenar una base de datos en el manual de PostgreSQL, el excelente artículo habitual de depesz sobre el tema, y esta pregunta SO .

(Tenga en cuenta que esta respuesta es sobre la carga masiva de datos en un DB existente o para crear uno nuevo. Si le interesa el rendimiento de restauración de DB con la ejecución pg_restore o psql de la salida pg_dump , gran parte de esto no se aplica desde pg_dump y pg_restore ya hace cosas como crear desencadenantes e índices una vez que finaliza un esquema + restauración de datos) .

Hay mucho por hacer. La solución ideal sería importar a una tabla UNLOGGED sin índices, luego cambiarla a registrar y agregar los índices. Desafortunadamente en PostgreSQL 9.4 no hay soporte para cambiar las tablas de UNLOGGED a registradas. 9.5 agrega ALTER TABLE ... SET LOGGED para permitirte hacer esto.

Si puede desconectar su base de datos para la importación masiva, use pg_bulkload .

De otra manera:

• Deshabilita cualquier activador en la mesa

• Coloque los índices antes de comenzar la importación, vuelva a crearlos después. (Se necesita mucho menos tiempo para generar un índice en una pasada que para agregarle los mismos datos progresivamente, y el índice resultante es mucho más compacto).

• Si realiza la importación en una sola transacción, es seguro eliminar las restricciones de clave externa, hacer la importación y volver a crear las restricciones antes de confirmar. No haga esto si la importación se divide en varias transacciones ya que podría introducir datos no válidos.

• Si es posible, use COPY lugar de INSERT s

• Si no puede usar COPY considere usar INSERT s de múltiples valores si es práctico. Parece que ya estás haciendo esto. No intente enumerar demasiados valores en un solo VALUES ; esos valores tienen que caber en la memoria un par de veces, así que mantenlo en unos pocos cientos por declaración.

• Inserte sus insertos en transacciones explícitas, haciendo cientos de miles o millones de insertos por transacción. No existe un límite práctico AFAIK, pero el procesamiento por lotes le permitirá recuperarse de un error al marcar el inicio de cada lote en sus datos de entrada. De nuevo, pareces estar haciendo esto ya.

• Use synchronous_commit=off y un commit_delay enorme para reducir los costos de fsync (). Sin embargo, esto no ayudará mucho si ha agrupado su trabajo en grandes transacciones.

• INSERT o COPY en paralelo desde varias conexiones. Cuántos dependen del subsistema de disco de su hardware; como regla general, desea una conexión por disco duro físico si usa almacenamiento conectado directamente.

• Establezca un alto valor de checkpoint_segments y habilite log_checkpoints . Mire los registros de PostgreSQL y asegúrese de que no se queje de que los puntos de control ocurran con demasiada frecuencia.

• Si, y solo si no le importa perder todo su clúster PostgreSQL (su base de datos y cualquier otro en el mismo clúster) a corrupción catastrófica si el sistema falla durante la importación, puede detener Pg, establecer fsync=off , iniciar Pg, hacer su importación, entonces (vitalmente) pare Pg y configure fsync=on nuevamente. Vea la configuración de WAL . No haga esto si ya hay datos que le interesan en cualquier base de datos en su instalación de PostgreSQL. Si configura fsync=off , también puede configurar full_page_writes=off ; nuevamente, solo recuerde volver a activarlo después de la importación para evitar daños en la base de datos y pérdida de datos. Consulte las configuraciones no duraderas en el manual de Pg.

También deberías ver cómo ajustar tu sistema:

• Utilice SSD de buena calidad para el almacenamiento tanto como sea posible. Las buenas unidades de estado sólido (SSD) con cachés de escritura contra escritura confiables y protegidos de energía hacen que las tasas de compromiso sean increíblemente más rápidas. Son menos beneficiosas si sigues los consejos anteriores, que reducen las descargas de discos / número de fsync() s, pero aún pueden ser una gran ayuda. No utilice SSD baratos sin la protección adecuada de falla de energía a menos que no se preocupe por conservar sus datos.

• Si está utilizando RAID 5 o RAID 6 para el almacenamiento directo, deténgase ahora. Retroceda sus datos, reestructure su matriz RAID a RAID 10 y vuelva a intentarlo. RAID 5/6 es inútil para el rendimiento de escritura masiva, aunque un buen controlador RAID con un gran caché puede ayudar.

• Si tiene la opción de utilizar una controladora RAID de hardware con una gran caché de recuperación de respaldo respaldada por batería, esto realmente puede mejorar el rendimiento de escritura para cargas de trabajo con muchas asignaciones. No ayuda tanto si está utilizando la confirmación asincrónica con commit_delay o si realiza menos transacciones grandes durante la carga masiva.

• Si es posible, almacene WAL ( pg_xlog ) en una matriz de disco / disco separada. No tiene sentido usar un sistema de archivos separado en el mismo disco. Las personas a menudo eligen usar un par RAID1 para WAL. Nuevamente, esto tiene más efecto en los sistemas con altas tasas de compromiso, y tiene poco efecto si está usando una tabla no registrada como objetivo de carga de datos.

También puede estar interesado en Optimizar PostgreSQL para pruebas rápidas .