Esta semana (como habitualmente de urgencia por necesidad y poca planificación del cliente) un compañero migró un Magento 1.9 a uno de nuestros servidores. En principio, aunque la tecnología para hacer funcionar un Magento 1 suele ser sencilla siempre hay que hacer algunas verificaciones, optimizaciones (muchos desarrolladores aún no conocen que se puede usar redis para la caché y las sesiones) y pruebas.
Tras la migración inicial, algunas pruebas de que las páginas cargan correctamente y la validación del cliente que todo OK, se cambian DNS y empieza a llegar todo el tráfico y con él la sorpresa. El sitio, al accederse a algunas categorías y de forma casi aleatoria empieza a dejar procesos del pool php-fpm bloqueados y sin respuesta al navegador cliente.
Mi compañero se pone en contacto conmigo para intentar buscar una solución. El cliente solo quiere que funcione y su argumentación y la de sus desarrolladores es clara e incontestable, en su antiguo servidor funcionaba bien…
¿ y ahora qué ?
La configuración del servidor está estudiada para el máximo rendimiento y con la última versión de Debian y con cientos de clientes disfrutando de un entorno estable, seguro y rápido. Pero a menudo hay peculiaridades.
Un rápido vistazo a los procesos mysql muestran una query en estado «Sending data» que por deducción corresponden a cada una de las páginas y procesos php bloqueados y permanecían en ese estado hasta el infinito. Obviamente algo pasaba con esa query que en la nueva versión de mariadb no funcionaba como debería (si, muchas veces mariadb y mysql hacen cosas raras en versiones recientes que funcionaba bien en anteriores).
La versión de mariadb del antiguo servidor era una 5.5 la actual una 10.5. Por realizar algunas pruebas, desactivo todas las posibles optimizaciones y dejo el servidor mariadb con la configuración por defecto (como el antiguo) pero nada. Poner la misma versión de mariadb en una versión reciente de Debian podría ser posible, bien con Docker, systemd-nspawnd, un chroot pero el vps en sí es un container y no se lleva bien con otros sistemas contenedores dentro de él. Percona y Mysql de Oracle no ofrecen versiones antiguas instalables para esta versión de Debian y compilar pues… es necesario que sea fácil de mantener en el futuro y que además tenga soporte de seguridad (la versión de mariadb 5.5 está obsoleta); esta migración empieza a no ser nada rentable.
Antes de seguir por el camino de instalar un mysql mas antiguo (que además no aseguraba tampoco nada que resolviera el problema) intento analizar la query a ver si saco algo en claro:
SELECT count(DISTINCT e.entity_id) FROM `sales_flat_order_item` AS `order_items`
INNER JOIN `sales_flat_order` AS `order` ON `order`.entity_id = order_items.order_id AND `order`.state <> 'canceled'
LEFT JOIN `catalog_product_entity` AS `e` ON (e.type_id NOT IN ('grouped', 'configurable', 'bundle')) AND e.entity_id = order_items.product_id AND e.entity_type_id = 4
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=6 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '343' WHERE (parent_item_id IS NULL);
Ya he tenido en el pasado peleas con queries complejas donde generalmente la falta de algún índice clave (incomprensible que no haya sido añadido por el desarrollador) generaba una merma de rendimiento considerable y el solo hecho de añadirlo variaba el tiempo de ejecución de la query de mas de 1 minuto a menos de un segundo, eso multiplicado con múltiples visitas concurrentes a un e-commerce en ciertas páginas es un mundo.
Volviendo a la query empiezo a analizar las tablas implicadas, los campos que intervienen en los JOIN y los índices relacionados y veo que falta un posible candidato: order_items.product_id. Así que añado un índice para dicha columna.
ALTER TABLE `sales_flat_order_item` ADD KEY `product_id` (`product_id`);
Y problema solucionado, no mas bloqueos con la query que resulta instantánea.
Así es como un simple index en una tabla puede salvarte el día.
¿ El motivo del problema y por qué con mariadb 5.5 funcionaba bien y con la 10.5 no ? Ni idea, puede que haya algún otro factor que no he contemplado o algún error en la importación de la base de datos. Seguro que hay una explicación pero requeriría de mucho tiempo de análisis y pruebas y el índice resolvió el problema.