Archivo de la etiqueta: optimizaciones

Google, reinventando Internet III: el DNS

Después de dar alternativas a los protocolos SMTP (Wave) y HTTP (SPDY), le ha tocado el turno al otro caballo de batalla de Internet: el DNS. Mucha gente no le da a este protocolo la importancia que se merece, pero muchas páginas que tienen problemas de rendimiento son por culpa de una mala gestión del DNS, suya o del dominio de algún elemento que tengan en la página (publicidad sobre todo).

Google ha anunciado en su blog oficial la disponibilidad de su nuevo servidor de DNS público. Esta vez no han inventado un nuevo protocolo, simplemente han añadido a su servidor una serie de optimizaciones como gestión compartida de caché entre todo el cluster (que a saber lo grande que es, con lo que va a estar cacheado ¿todo?) y pre-fetching, amén de unas cuantas medidas de seguridad para prevenir ataques DoS y de envenenamiento de cachés.

Esta vez no hay código, parece ser que esto es sólo un servicio gestionado. Las IPs de los DNS de Google son:

  • 8.8.8.8
  • 8.8.4.4

Puede ser una buena alternativa al DNS de tu ISP (la mitad hacen aguas) o a OpenDNS, que se granjeó bastantes enemistades cuando empezaron a redirigir el tráfico de dominios que no existían (y curiosamente, el de Google también) a páginas suyas. Por supuesto, esto va a levantar otra oleada de opiniones anti-Gran-Google-Hermano: si usas el servidor de DNS de Google, van a saber todos los dominios que visites. No cada página individual, pero si cada dominio. Y es que como (¿casi?) todo lo que hace Google, parece que tiene la doble intención de conseguir más y más información sobre los hábitos de los usuarios en Internet para afinar aún más su maquinaria de AdWords/AdSense.

Optimizando el SQL de osCommerce

[spanish]Un apunte sobre la importancia de cómo la forma en que se construye una query en SQL afecta a su rendimiento, algo en lo que creo que mucha gente no se fija y que he sufrido en mis carnes laboralmente más de una vez:

Llevo unos días pegándome con la base de datos de una tienda on-line con osCommerce. Todo funcionaba de maravilla hasta que tras cuatro años de andadura tenemos una BD con más de 1500 categorías y 16000 productos, y la web cada día va un poco más lenta. Usamos osCommerce 2.2 con bastantes modificaciones por lo que ni me he planteado probar la 3, además de que aún es alpha y esto es producción, así que toca ponerese a optimizar. Y después de optimizar el sistema y no conseguir grandes mejoras, toca mirar el código y la BD.

Primero y principal: activar log-slow-queries en la configuración de mySQL. Esto genera un nuevo log con las queries que tardan más de la cuenta en ejecutar (p.def más de 10 segundos, pero es configurable). Con la información de éste log podemos ir viendo qué queries son las que más están penalizando el rendimiento global, e ir optimizando.

Para que os hagáis una idea, esta es la query que saca todos los productos de una categoría:

[code lang=”sql”]
SELECT pd.products_name, pd.products_description, m.manufacturers_name,
p.products_image, p.products_quantity,  p.products_date_added,
p.products_id, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status, s.specials_new_products_price,
p.products_price) AS final_price
FROM products_description pd, products p
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id,
products_to_categories p2c
WHERE
p.products_status = ‘1’ AND p.products_id = p2c.products_id AND
pd.products_id = p2c.products_id AND pd.language_id = ‘3’ AND
p2c.categories_id = ‘114’
ORDER BY p.products_date_added DESC;
[/code]

En primer lugar para quien no conozca osCommerce, hay un mazo de tablas. En este ejemplo seis: categorías, productos, productos por categoría, descripción de producto (para tenerlas en varios idiomas), fabricantes y productos en oferta (specials). Según el mysql-slow.log, esa query estaba tardando del orden de 12 segundos, y también indicaba “Rows_examined: 1903433”. No me extraña que tardara tanto. :-D

¿Por qué tantas “rows_examined”? Repasemos teoría de BD de las clases de la universidad. ;-D Optimizaciones de cada motor de BD aparte, al hacer un select de varias tablas se hace el producto cartesiano de sus tuplas, un “todos con todos”, del que luego se extraen aquellas que coincidan con las condiciones del where (digo lo de optimizaciones aparte porque no me salen los números: 16k productos, 16k descripciones, 1k5 categorías, ¿1903433 combinaciones?). En cambio con los JOIN, a las tuplas que ya se han seleccionado con where Y SÓLO A ESAS, se les añade la información relacionada de otra tabla.

Entonces, ¿por qué usar varias tablas en el where si no es estrictamente necesario? En el caso anterior, queremos extraer toda la información de los productos de una categoría, y hacemos from de la tabla productos. ¿No sería mejor basar la query en products_to_categories, restringiendo a la categoría que nos interesa, y con join añadir el resto de información? Es por lo mismo que a la hora de encadenar varios comandos en shell-script, primero se hace el grep y luego el cut: para restringir cuanto antes el nº de datos a tratar en los siguientes pasos, reduciendo así el tiempo de proceso cada vez más.

Con este cambio, la query queda así:

[code lang=”sql”]
SELECT pd.products_name, pd.products_description, m.manufacturers_name,
p.products_image, p.products_quantity,  p.products_date_added,
p.products_id, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
AS specials_new_products_price, IF(s.status, s.specials_new_products_price,
p.products_price) AS final_price
FROM products_to_categories p2c
LEFT JOIN products p ON p.products_id=p2c.products_id
LEFT JOIN products_description pd ON p.products_id=pd.products_id AND pd.language_id=’3′
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE p2c.categories_id = ‘114’ AND p.products_status = ‘1’
ORDER BY p.products_date_added DESC
[/code]

El resultado de ambas queries es el mismo, pero mientras que la primera estaba tardando unos 12s en ejecutar, la segunda lo hace en unos pocos milisegundos. :-) He encontrado ya varios casos de queries que se han podido optimizar así, con los mismos resultados de bajar el tiempo de ejecución de +10s a unos pocos ms.

Otro punto importante son los índices: ha habido casos de queries que no se podían optimizar, ya usaban una única tabla en el from, la más restrictiva, y luego algún join. En estos casos lo que he visto es que no había índice en la clave ajena de una de las dos tablas, y añadiendo un íncide a esa columna los tiempos se han reducido igual que con las optimizaciones anteriores, de segundos a milisegundos.
[/spanish]
[english]
An entry about how the way SQL queries are built affects their performance, something I think many people don’t pay attention to:

For the last couple of days I’ve been struggling with the database of an osCommerce-based on-line shop. Everything ran perfectly until after four years of existence we have a DB with over 1500 categories and 16000 products, and the web is getting increasingly slower over time. We use a heavily modified osCommerce 2.2 installation, so moving to osCommerce 3 is out of the question (besides, it’s still alpha and this is a production site). After some hw and os-level optimization that didn’t help to improve performance, I had to start digging into the code and the DB.

First of all: activate the log-slow-queries option on mySQL’s config. This creates a new log with all the queries that take longer than expected to run (def. more than 10 seconds). With this information, we can concentrate on those queries that are hitting the bigger performance penalties and start the optimization process there.

To get you on context, this is the query that extracts all the products from a given category:

[code lang=”sql”]
SELECT pd.products_name, pd.products_description, m.manufacturers_name,
p.products_image, p.products_quantity, p.products_date_added,
p.products_id, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status, s.specials_new_products_price,
p.products_price) AS final_price
FROM products_description pd, products p
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id,
products_to_categories p2c
WHERE
p.products_status = ‘1’ AND p.products_id = p2c.products_id AND
pd.products_id = p2c.products_id AND pd.language_id = ‘3’ AND
p2c.categories_id = ‘114’
ORDER BY p.products_date_added DESC;
[/code]

On the first place for those unfamiliar with osCommerce, there are a heap of tables involved. Six on this example: categories, products, products_to_categories, products_description, manufacturers and specials (products with discounted prices). According to mysql-slow.log, that query was taking around 12 seconds to run. The log also read “Rows_examined: 1903433”. No wonder it was taking that long. :-D

Why so many “rows_examined”? Going back to DB theory on the University, ;-D if memory serves and leaving each particular DB engine’s optmizations aside, when selecting from several tables the DB engine does the cartesian product (product set) of all their tuples, this is, every possible combination of each tuple on one table with every other tuple on the other tables. After that, those combinations matching the WHERE criteria are selected (and I say engine optimizations aside because I don’t get the math here: 16k products, 16k descriptions, 1k5 categories, and just 1903433 combinations?). On the other hand with JOIN the related data is appended to those tuples already selected by the WHERE clause and ONLY TO THOSE.

So, why using more than one table on the where clause if it isn’t strictly necessary? On the previous example, we want to extract all the info of the products on a given category, but we select from the products, products_description and products_to_categories tables. Wouldn’t it be better using only the products_to_categories table on the from clause, reducing the result set to only the category we are interested on, and then get all the products’ information with JOIN? It’s the same principle as when piping data through several commands on a shell-script, using first grep and then cut: first reduce the data set, so that on each following step there is less data to spend CPU cycles with, increasingly reducing the overall processing time.

With this modification, the resulting query looks like:
[code lang=”sql”]
SELECT pd.products_name, pd.products_description, m.manufacturers_name,
p.products_image, p.products_quantity, p.products_date_added,
p.products_id, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
AS specials_new_products_price, IF(s.status, s.specials_new_products_price,
p.products_price) AS final_price
FROM products_to_categories p2c
LEFT JOIN products p ON p.products_id=p2c.products_id
LEFT JOIN products_description pd ON p.products_id=pd.products_id AND pd.language_id=’3′
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id
WHERE p2c.categories_id = ‘114’ AND p.products_status = ‘1’
ORDER BY p.products_date_added DESC
[/code]

The result sets of both queries are identical, but while the first one took around 12 seconds to run, the second one only takes a couple of milliseconds. :-) I’ve found several similar cases already on my osCommerce installation, that have gone from more than 10 seconds to milliseconds after rearanging the query in a similar fashion.

Another important point are indexes: I’ve found some queries that couldn’t be further optimized, they already used only one table on the from clause, the more restricting one, and then some joins. On these cases what I’ve found is that some table lacked an index on the foreign key column, and after adding it the execution time has gone down again from seconds to milliseconds.
[/english]