Odoo trabaja, y seguirá trabajando por varios años más, con PostgreSQL. PostgreSQL es una excelente base de datos open-source la cual para la mayoría de las instalaciones en Latinoamérica requiere poco mantenimiento (lo digo en base a mi experiencia).
Pese a esto, a medida que pasa el tiempo y si la base de datos crece en tamaño de manera considerable, requiere mantenimiento. No mucho para serles honestos (no conozco instalación de Odoo con DBA), pero si mantenimiento enfocado en acelerar la ejecución de ciertas consultas en Odoo. No es necesario ser un DBA para realizar dichas tareas, pero uno debe manejar algunos aspectos básicos que vamos a cubrir a continuación (no cubrimos el backup debido a que hablamos de eso en otro post).
Que es el sequential scan?
El sequential scan es una acción de la base de datos que ocurre cuando esta tiene que leer todos los registros de una tabla con el fin responder a una consulta. Puede ser caro o no hacerlo (en términos de performance). Por ejemplo si la tabla tiene pocos registros, puede ser inofensivo (supongamos la tabla res_company). Ahora si la tabla que se esta leyendo tiene millones de registros, el sequential scan pasa a ser dañino para la performance de la base de datos. Para evitar estos sequential scans uno debe crear índices que le permiten al optimizador de la base de datos elegir el mejor plan de ejecución para que la consulta sea lo menos costosa en términos de recursos consumidos.
Vale la pena aclarar que Odoo al crear sus modelos automáticamente crea los índices. Tambien cuando uno crea un campo puede indicarle si el campo va a ser indexado o no (Odoo creará un índice btree para dicha columna)
my_new_field = fields.Char('My New Field',index=True)
Detectando los queries que tardan mucho tiempo
La indicación de Odo es que todas las transacciones de Odoo deberían durar menos de 100 milisegundos. En mi experiencia, si en la base de datos hay queries que duran más de 500 ms, uno debería saber de ellos y por lo menos chequearlos para asegurarnos que no hay ninguna situación que tenga que ser corregida (por ejemplo falta de índices en una tabla con miles de registros, por ejemplo account_move_line o stock_move_line). Una forma rápida de saber esto, es modificando el archivo de configuración de postgresql (por lo general llamado postgresql.conf) y agregar la siguiente opción
log_min_duration_statement = 500
Esto logueará en el archivo de log (de PostgreSQL, no en el de Odoo) aquellas sentencias de SQL que duren más de medio segundo. En el archivo de log verá un resultado como el siguiente:
Aca se puede ver como una sentencia SELECT duró un segundo y medio (lo cual indica que hay un problema de performance). Si bien esta información es util, es solo una fotografía de lo que pasó en un momento dado y no indica si el query es realmente problemático o no (uno busca problemas recurrentes; ya que un query puede durar veinte segundos, pero si se lo ejecuta una vez por mes no es problema para nadie).
Una vez detectado un query problemático, uno debe conocer su plan de ejecución.
Como entender el plan de ejecución de un query
Cada consulta ejecutada por la base de datos pasa por cuatro etapas: parseo del query, reescritura, optimización y ejecución. La tercer etapa (optimización) es realizada por el optimizador de queries, el cual para ello utiliza las estadísticas sobre la dispersión de los datos de la base de datos. Es en esta etapa cuando el motor de la base de datos decide si utilizar un índice, hacer sequential scan... etc. Lo que busca la base de datos es realizar el query de forma tal que sea lo menos costoso (a nivel recursos o tiempo).
Si tenemos una consulta podemos conocer su plan de ejecución de la siguiente manera
EXPLAIN <query>
Por ejemplo; si queremos ver el plan de ejecución del query descripto en el punto anterior:
explain select min(res_partner.id) as id,count(res_partner.id) as city_count,
res_partner.city as city from res_partner
where (res_partner.active = true)
and (((("res_partner"."partner_share" IS NULL
or "res_partner"."partner_share" = false )
OR ("res_partner"."company_id" in (1)))
OR "res_partner"."company_id" IS NULL )
AND (((("res_partner"."type" != 'private') OR "res_partner"."type" IS NULL)
OR "res_partner"."type" IS NULL )
OR ("res_partner"."type" = 'private'))) group by city order by city;
El cual dará el siguiente resultado
El resultado de este comando se lee desde la parte interna derecha hacia afuera a la izquierda. En este ejemplo podemos ver como primero se aplica un sequential scan, luego se agrupa por una columna para al final ordenar por la misma columna. En la primer línea podemos conocer dos datos interesantes, la cantidad de filas que se estiman que se van a devolver (en este caso 14) y el costo del query; 128.45. Dicho costo es un costo interno de la base de datos. No representa tiempo ni IO ni nada. Es un costo interno, cuanto más alto, mas lento será el query.
Detectando la ausencia de índices
Una consulta (que es una gran herramienta) que se puede utilizar para conocer a lo largo del tiempo cuales tablas están teniendo muchos sequential scans es la siguiente:
select schemaname,relname,seq_scan,seq_tup_read,idx_scan,
seq_tup_read / seq_scan from pg_stat_user_tables
where seq_scan > 0 order by seq_tup_read desc
que por ejemplo devuelve el siguiente resultado
Esta consulta nos muestra información como: cantidad de sequential scans que tuvo una tabla y la cantidad de registros leidos en cada uno, cantidad de veces que se leyó con un índice la tabla, cantidad de registros leidos en cada sequential scan.
En el ejemplo de arriba podemos observar la tercer línea para la tabla t_test; la cual no tuvo ningún uso del índice, tuvo 8 sequential scans y en cada uno leyó 1,250,000 registros (una situación muy común en Odoo). Esa tabla es candidata para la creación de un índice.
Los índices que crea Odoo
La mayoría de los índices de una tabla en Odoo son creados al momento de instalarse un módulo (o actualizarse el mismo). Por defecto Odoo crea un índice sobre la columna ID (para mantener la integridad referencial de la clave primaria). Y el resto de los índices son creados por el desarrollador del módulo a lo largo del desarrollo del módulo (el cual resulta ser el momento en el cual uno nunca piensa en la performance de las consultas). Es por eso que podemos ver situaciones como la tabla sale_order_line, donde vemos que existen los siguientes índices:
Como podemos ver tenemos índices para la clave primaria, otro para la compañía (en un entorno que no resulta ser multicompañía, que además es la norma en las instalaciones de Odoo), el pedido de ventas (lo que tiene mucho sentido), el proyecto y la tarea (estos últimos creado por el módulo project). El screenshot es de una instalación de e-commerce. Por que Odoo instaló los índices de proyectos en un sistema que va a manejar miles de órdenes y solo dos o tres proyectos? Es un misterio para mi (el otro misterio es porque se insiste en tener el ERP y el sistema de e-commerce en el mismo servidor, pero es para otra discusión).
Mi punto es, a medida que vaya usando el sistema, analice que índices se encuentran presente y si le son de utilidad. Por ejemplo, en este ejemplo que es de e-commerce yo borraría los índices relacionados con proyectos y crearía uno para productos, ya que muchas veces vamos a necesitar realizar consultas a los pedidos filtrando los productos (o los atributos de los mismos).
Como se crean índices
Los índices se crean con las herramientas de mantenimiento de base de datos (mi favorita es psql). Para ello se utiliza el comando CREATE INDEX. Por ejemplo
create index idx_partner_city on res_partner(city)
Crea un índice binario sobre la columna city en la tabla res_partner. En el siguiente screenshot podemos ver el efecto de realizar un query sin el índice y con el índice
Podemos observar como el primer query (sin el índice) tiene un costo de 115. Una vez creado el índice, el costo de la consulta se reduce a 0.28.
Extensión pg_stat_statements
Lo que explicamos hasta ahora tiene una gran limitación, son una foto del comportamiento de la base de datos y no te indican si una consulta se está realizando con frecuencia o no. Lo que es importante es; optimizar aquellas consultas de alto costo que se realicen con frecuencia. Como podemos saber cuando un query se ejecuta muchas veces? Para ello tenemos la extensión pg_stat_statements del paquete contrib (que ya se encuentra incluido con PostgreSQL).
La extensión se instala así:
create extension pg_stat_statements;
Y luego se tiene que habilitar la extensión en el archivo de configuración de PostgreSQL
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
El paso siguiente (que siempre uno realiza cuando instala extensiones) se debe reiniciar PostgreSQL. Al instalarse la extensión se crea una tabla muy util, pg_stat_statements. Esta tabla contiene por cada query la cantidad de veces que se ejecutó, el tiempo total, tiempo mínimo y máximo, el tiempo promedio y el desvío, la cantidad de registros y estadísticas de IO. Realmente muy util.
A modo de ejemplo, supongamos que tenemos una tabla llamada t_test de la cual queremos ver que consultas se estan ejecutando, solo tenemos que hacer:
select * from pg_stat_statements where query like '%t_test%';
pg_stat_statements es una extensión que debería ser instalada en cada base de datos que uno estima va a crecer en tamaño y usuarios. Es una extensión "liviana"; debido a que su presencia no tiene ningún impacto en la performance de la base de datos.
Reseteando las estadísticas
Con frecuencia necesitamos borrar las estadísticas de la base de datos para arrancar de nuevo (por ejemplo para conocer si un cambio aplicado a la base de datos surtió efecto). En ese caso debemos utilizar el comando pg_stat_reset de la siguiente manera:
select pg_stat_reset();
Como pueden ver en el ejemplo, despues de ejecutarlo las estadísticas de la cantidad de sequential scans por tabla están vacías. Si necesitamos resetear las estadísticas de la extensión pg_stat_statements debemos ejecutar pg_stat_statements_reset
select pg_stat_statements_reset();
Vacuum
Vacuum es el equivalente del comando PACK en Clipper. Vacuum es un comando que reclama el espacio no utilizado por una base de datos. Las bases de datos de PostgreSQL tenden a crecer a lo largo del tiempo, debido a que los registros actualizados o borrados no son eliminados del filesystem (son marcados logicamente como borrados, por ejemplo).
VACUUM elimina físicamente esos registros no utilizados por la base de datos. También realiza otras tareas importantes; como por ejemplo actualizar las estadísticas de la base de datos (para que el optimizador funcione de forma apropiada) y recrear los índices.
El siguiente query nos indica cuando podemos ejecutar el VACUUM;
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup /
(n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float8
+ current_setting('autovacuum_vacuum_threshold')::float8) DESC LIMIT 10;
xxx
Como podemos ver la tabla account_move_line tiene unos 28,096 registros muertos y unos 153,409 registros vivos. Un 15% de la tabla. Y si chequeamos el tamaño de dicha tabla con el comando pg_total_relation_size podemos
SELECT pg_size_pretty( pg_total_relation_size('account_move_line') );
Podemos ver que dicha tabla ocupa 216Mb. Ahora vamos a realizar el vacuum full de la base de datos (que reclama el espacio para todas las tablas, recrea los índices y actualiza las estadísticas)
vacuum full
La función VACUUM tiene muchas opciones (sobre todo si estamos trabajando con bases de datos de gran tamaño), como por ejemplo ejecutarse solo para una tabla o ejecutarla y actualizar las estadísticas de la base de datos. En un principio recomiendo en horarios en los que no trabajan los usuarios, realizar un VACUUM FULL. Este comando solo requiere espacio en disco suficiente y que no se esté ejecutando Odoo (ya que requiere un lock exclusivo de cada tabla de la base de datos). Pero es el comando que compacta las tablas, recrea los índices y actualiza las estadísticas de la base de datos. Es muy completo. Tenga en cuenta que puede llegar a tener una larga duración.
PostgreSQL cuenta con la opción de que se ejecute el VACUUM en forma automática, no lo cubriremos en este post. Lo importante es comprender que hace el VACUUM y cuando utilizarlo.