¿Cómo hacer que Python / PostgreSQL sea más rápido?

Ahora mismo tengo un analizador de registros que lee 515 mb de archivos de texto sin formato (un archivo para cada día durante los últimos 4 años). Mi código actualmente es el siguiente: http://gist.github.com/12978 . He usado psyco (como se ve en el código) y también lo estoy comstackndo y usando la versión comstackda. Está haciendo unas 100 líneas cada 0,3 segundos. La máquina es una MacBook Pro estándar de 15 “(2.4 GHz C2D, 2 GB de RAM)

¿Es posible que esto vaya más rápido o es una limitación en el idioma / base de datos?

No pierdas tiempo perfilando. El tiempo está siempre en las operaciones de la base de datos. Haz lo menos posible. Sólo el número mínimo de inserciones.

Tres cosas.

Uno. No SELECCIONE una y otra vez para cumplir con las dimensiones Fecha, Nombre de host y Persona. Obtenga todos los datos UNA VEZ en un diccionario de Python y utilícelos en la memoria. No haga repetidas selecciones singleton. Utilice Python.

Dos. No actualizar

Específicamente, no hagas esto. Es un código malo por dos razones.

cursor.execute("UPDATE people SET chats_count = chats_count + 1 WHERE id = '%s'" % person_id) 

Se reemplazará con un simple SELECCIONAR CUENTA (*) DESDE …. Nunca actualizar para incrementar un conteo. Solo cuenta las filas que están allí con una instrucción SELECT. [Si no puede hacer esto con un simple SELECT COUNT o SELECT COUNT (DISTINCT), le faltan algunos datos: su modelo de datos siempre debe proporcionar recuentos completos correctos. Nunca actualizar.]

Y. Nunca compile SQL utilizando la sustitución de cadenas. Completamente tonto.

Si, por alguna razón, SELECT COUNT(*) no es lo suficientemente rápido (primero haga un benchmark, antes de hacer nada cojo) puede guardar el resultado del conteo en otra tabla. DESPUÉS de todas las cargas. Haga un SELECT COUNT(*) FROM whatever GROUP BY whatever e insértelo en una tabla de conteos. No actualizar Siempre.

Tres. Utilice las variables de enlace. Siempre.

 cursor.execute( "INSERT INTO ... VALUES( %(x)s, %(y)s, %(z)s )", {'x':person_id, 'y':time_to_string(time), 'z':channel,} ) 

El SQL nunca cambia. Los valores están vinculados en el cambio, pero el SQL nunca cambia. Esto es mucho más rápido. Nunca construya sentencias SQL dinámicamente. Nunca.

Use variables de enlace en lugar de valores literales en las sentencias de SQL y cree un cursor para cada sentencia de SQL única para que no sea necesario volver a analizar la sentencia la próxima vez que se use. Desde el documento de python db api:

Preparar y ejecutar una operación de base de datos (consulta o comando). Los parámetros pueden proporcionarse como secuencia o asignación y estarán vinculados a las variables en la operación. Las variables se especifican en una notación específica de la base de datos (consulte el atributo paramstyle del módulo para obtener más detalles). [5]

Una referencia a la operación será retenida por el cursor. Si se vuelve a pasar el mismo objeto de operación, entonces el cursor puede optimizar su comportamiento. Esto es más efectivo para algoritmos donde se usa la misma operación, pero diferentes parámetros están vinculados a ella (muchas veces).

SIEMPRE SIEMPRE SIEMPRE use variables de enlace.

En el bucle for, se está insertando en la tabla de ‘chats’ repetidamente, por lo que solo necesita una única instrucción SQL con variables de enlace, que se ejecutará con diferentes valores. Así que podrías poner esto antes del bucle for:

 insert_statement=""" INSERT INTO chats(person_id, message_type, created_at, channel) VALUES(:person_id,:message_type,:created_at,:channel) """ 

Luego, en lugar de cada sentencia SQL que ejecute, coloque esto en su lugar:

 cursor.execute(insert_statement, person_id='person',message_type='msg',created_at=some_date, channel=3) 

Esto hará que las cosas corran más rápido porque:

  1. El objeto del cursor no tendrá que repasar la instrucción cada vez.
  2. El servidor db no tendrá que generar un nuevo plan de ejecución, ya que puede usar el que creó anteriormente.
  3. No tendrá que llamar a santitize () ya que los caracteres especiales en las variables de vinculación no formarán parte de la instrucción sql que se ejecuta.

Nota: La syntax de la variable de enlace que utilicé es específica de Oracle. Tendrá que consultar la documentación de la biblioteca psycopg2 para conocer la syntax exacta.

Otras optimizaciones:

  1. Estás incrementando con el “ACTUALIZAR personas SET chatscount” después de cada iteración de bucle. Mantenga un usuario de mapeo de diccionario para chat_count y luego ejecute la statement del número total que ha visto. Esto será más rápido que golpear la base de datos después de cada registro.
  2. Use variables de enlace en TODAS sus consultas. No solo la statement de inserción, elijo eso como un ejemplo.
  3. Cambie todas las funciones de encontrar _ * () que realizan búsquedas de bases de datos para almacenar en caché sus resultados para que no tengan que golpear la base de datos cada vez.
  4. psycho optimiza los progtwigs de Python que realizan una gran cantidad de operaciones numéricas. La secuencia de comandos es IO costosa y no la CPU costosa, por lo que no esperaría darle mucha optimización, si la hubiera.

Como sugirió Mark, usa variables vinculantes. La base de datos solo tiene que preparar cada statement una vez, luego “completar los espacios en blanco” para cada ejecución. Como un efecto secundario agradable, se encargará automáticamente de los problemas de comillas (que su progtwig no está manejando).

Active las transacciones (si aún no lo han hecho) y realice una única confirmación al final del progtwig. La base de datos no tendrá que escribir nada en el disco hasta que no sea necesario confirmar todos los datos. Y si su progtwig encuentra un error, ninguna de las filas se confirmará, lo que le permitirá simplemente volver a ejecutar el progtwig una vez que se haya corregido el problema.

Sus funciones log_hostname, log_person y log_date están realizando SELECTs innecesarios en las tablas. Haz los atributos de tabla apropiados. TECLA PRIMARIA o ÚNICA. Luego, en lugar de verificar la presencia de la clave antes de INSERTAR, simplemente haga clic en INSERTAR. Si la persona / fecha / nombre de host ya existe, INSERT fallará debido a la violación de la restricción. (Esto no funcionará si utiliza una transacción con un solo compromiso, como se sugirió anteriormente).

Alternativamente, si sabe que es el único INSERTAR en las tablas mientras su progtwig se está ejecutando, cree estructuras de datos paralelas en la memoria y manténgalas en la memoria mientras hace sus INSERTOS. Por ejemplo, lea todos los nombres de host de la tabla en una matriz asociativa al inicio del progtwig. Cuando quiera saber si hacer un INSERT, simplemente haga una búsqueda de matriz. Si no se encuentra ninguna entrada, haga INSERTAR y actualice la matriz de manera apropiada. (Esta sugerencia es compatible con transacciones y un solo compromiso, pero requiere más progtwigción. Sin embargo, será increíblemente más rápida).

Además de las muchas sugerencias que ha dado @Mark Roddy, haga lo siguiente:

  • no utilice readlines , puede iterar sobre objetos de archivo
  • intente utilizar la executemany lugar de execute : intente hacer inserciones por lotes en lugar de inserciones individuales, esto tiende a ser más rápido porque hay menos gastos generales. También reduce el número de confirmaciones.
  • str.rstrip funcionará bien en lugar de eliminar la nueva línea con una expresión regular

El procesamiento por lotes de las inserciones usará más memoria temporalmente, pero eso debería estar bien cuando no lea todo el archivo en la memoria.