PHP + MySQL con millones de registros

[alberto...@gmail.com]

Aquí abro un nuevo tema relacionado con bases de datos MySQL con grandes volúmenes de datos (millones de registros)

Me acaban de pasar un proyecto ya desarrollado que tiene problemas de rendimiento y el soporte de su servidor les dice “El cuello de botella parece venir del acceso a disco. Creemos que en este caso puede deberse a la saturación de MySQL, ya que vemos que tienen tablas con más de 1 millón de registros, lo que puede ocasionar problemas.”

He navegado por la red, y veo que efectivamente esto puede suponer un problema para MySQL, pero también tengo entendido que sistemas cómo Twitter o Facebook que pueden manejar millones de registros en sus inicios al menos usaban MySQL. Entonces mi duda es, ¿cuál sería el planteamiento? He leído a cerca de NoSQL al respecto..

Me estoy mirando la ayuda de MySQL y diversas opiniones y comentarios que hay en la red, pero quiero consultarlo con vosotros, que hasta ahora habéis sido un aporte valiosísimo. También lo quiero dejar aquí, por si el día de mañana le vale a otro.

Bueno, vayamos al grano:

1.- Tengo claro la importancia de revisar el esquema de la BD, las consultas, así como índices y claves, etc. (sql query log, y explain me vienen de maravilla)

2.- La cache de página, de base de datos, etc.. para no sobrecargar la base de datos si hay muchas visitas concurrentes.

Hasta ahí bien (avisadme si no me dejo nada importante).
¿Ahora por dónde seguir? ¿Me dejo algo?

Y por ponernos en un ejemplo real, en este caso veo una tabla “productos” con casi 1/2 millón de filas, y una relacionada que es “imagenes”, que tiene 5 millones de imágenes. Un producto tiene de 1 a N imágenes, siendo la media por lo veo aproximadamente 10. Por cada imagen solo se guarda un id, y una ruta al archivo físico.

¿Sería más eficiente para la base de datos pasar las imágenes de cada producto a una columna de la tabla productos? ¿Cuál es vuestra opinión? (estoy haciendo pruebas con este ejemplo)

Y por otro lado veo estos libros/recursos veo:
http://shop.oreilly.com/product/0636920022343.do
http://shop.oreilly.com/product/0636920026907.do
http://shop.oreilly.com/product/0636920000136.do

¿Alguien me recomienda más?

Y sobre todo, gracias de antemano!

[Julian Coccia]

5 millones de imagenes en la base de datos? No sera mejor usar el sistema de archivos para eso y dejar solo en la base de datos las referencias a los archivos?

Con respecto al millon de registros, no creo que sea preocupante. He visto hasta 300 millones de registros por tabla en MySQL con respuestas practicamente inmediatas por query.

Yo creo que apuntaria a revisar bien la estructura de tu base de datos, reducir el tamaño de los campos a lo que realmente haga falta, asegurarse que todos los indices estan como corresponde y sobre todo, que los queries esten bien hechos. Podes tener una estructura impecable y un query cavernicola que se lleva todos tus recursos.

Fijate de setear slow queries para que te cante los queries que demoren mas de lo que consideras como aceptable.

Suerte!
Julian

--
Has recibido este mensaje porque estás suscrito al grupo "Grupo PHP Argentina" de Grupos de Google.
Para anular la suscripción a este grupo y dejar de recibir sus correos electrónicos, envía un correo electrónico a php-arg+u...@googlegroups.com.
Para publicar una entrada en este grupo, envía un correo electrónico a php...@googlegroups.com.
Visita este grupo en http://groups.google.com/group/php-arg.
Para obtener más opciones, visita https://groups.google.com/groups/opt_out.
 
 

[Rodrigo Reyes]

Julian, las imagenes estan en disco, en la BD solo tiene la ruta de la imagen, fijate que lo dice en el email.

Alberto, como bien dijiste tendrias que analizar cada consulta y si se puede optimizarla, y chequear que esten los indices necesarios creados.
El primer paso luego de esto, seria separar los servidores, el mysql deberia estar separado del servidor web. Luego buscate articulos relacionados al tunning de mysql.
Si tenes el cuello de botella en el acceso a disco, pues entonces deberias  minimo incrementar la memoria RAM.
Si todo eso no funciona tendrias que ver de usar memcached.
El tema es detectar donde esta exactamente el cuello de botella tambien puede estar en el servidor web.

Hasta ahi te puedo ayudar.

Saludos

---

From: Julian Coccia <jul...@coccia.com>
To: php...@googlegroups.com
Sent: Monday, June 17, 2013 1:29 PM
Subject: Re: [php-arg] PHP + MySQL con millones de registros

[Mariano Rodríguez]

Casi seguro que el problema está o en los índices o en la query. Puedes utilizar varias herramientas para monitorizar esto, over la opción EXPLAIN de tu query.
Un millón de registros es poco, no debería causarte problemas.

[Adrian Ramiro]

En mi humilde experiencia con tablas de millones de registros, el acceso a disco no suele ser el problema. De hecho muchas veces opté por un cache en disco, que hace uso mucho más intensivo del mismo.

Como bien dicen hay que revisar la estructura y los indices, y no olvidarse que indices de más son tan malos (a la hora de escribir) como los indices mal hechos o de menos (a la hora de leer)

2013/6/17 Rodrigo Reyes <rodri...@yahoo.com>

--

/**

* @author: Adrian R. Gay Cattaneo

*/

[Ricardo Luis Mender]

Aparte de los indices, fijate que cuando hagas consultas no sean de la manera select * from [table] me toco trabajar con un proyecto que ya estaba hecho y no sabiamos por que eran tan pesadas las consultas, y resultaba ser que se hacia el select * de una tabla que uno de sus campos era una imagen.

 

Saludos

Ricardo Luis Mender

[Tordek]

On 17/06/13 12:43, alberto...@gmail.com wrote:

> 1.- Tengo claro la importancia de revisar el esquema de la BD, las

> consultas, as� como �ndices y claves, etc. (sql query log, y explain
> me vienen de maravilla)
>
> 2.- La cache de p�gina, de base de datos, etc.. para no sobrecargar

> la base de datos si hay muchas visitas concurrentes.

�ndices.
�ndices sobre m�s de una columna.
Todos los �ndices que necesites y ninguno m�s.
No usar funciones en las queries (si la query puede usar s�lo
�ndices, te pod�s ahorrar el acceder a la tabla).
Evitar JOINs super complejos.
Eviter round-trips que puedas evitar con un JOIN.
Slow-query log.
No hacer SELECT(*) cuando necesit�s una sola columna. Usar el engine
apropiado (MyISAM hace COUNT(*) r�pido, pero no tiene transacciones
ni integridad como InnoDB; hay un engine estrictamente in-memory, si
ten�s valores que cambian muy seguido y no importa perderlos).
No usar LIKE "%...".
Mucho ojo con las queries sobre fechas.
Us� un engine de b�squeda como Solr si necesit�s b�squedas complejas.
Denormalizar si es necesario.
Memcache.
Optimizaciones varias y abundantes: asegurarte que la DB tiene
suficiente RAM disponible para aprovecharla.
Caches en todos los niveles relevantes: cachear los resultados de
las queries; cachear el HTML generado; cachear las requests HTTP con
un proxy; mandar headers de caching.
Posiblemente: reestructurar la generaci�n para cachear parcialmente
las p�ginas (Symfony (o casi cualquier framework), por ejemplo, te
deja declarar que un fragmento del HTML se cachee para evitar
rehacer queries a partes que cambian infrecuentemente.).

> �Ser�a m�s eficiente para la base de datos pasar las im�genes de
> cada producto a una columna de la tabla productos? �Cu�l es vuestra
> opini�n? (estoy haciendo pruebas con este ejemplo)

Poco probable.

http://use-the-index-luke.com/3-minute-test/mysql (y el resto del
sitio).

--
Guillermo O. �Tordek� Freschi. Programador, Escritor, Genio Maligno.
http://tordek.com.ar :: http://twitter.com/tordek

[DEH]

Hola,

En una empresa en la que trabajaba la solución a corto plazo era invertir en mejor hardware (hardware muy caro por cierto, pero menos costoso que una caída de ventas).
Lo de pasar a NoSQL lo tenés que plantear a muy largo plazo, no es algo trivial.

Firma: yo

[Gonzalo Sainz-Trápaga]

Antes de mirar nada, fijate que el campo por el que se hace el JOIN entre las tablas esté indexado.

Segundo tema, lo que dijeron todos, está mal la query o los índices. Sacá del slow query log las consultas que se están comiendo todo el servidor, y posteá eso junto con un describe de las tablas involucradas. No es volumen de datos como para preocuparse.

Gonzalo

2013/6/17 Mariano Rodríguez <lmari...@gmail.com>

--

Has recibido este mensaje porque estás suscrito al grupo "Grupo PHP Argentina" de Grupos de Google.
Para anular la suscripción a este grupo y dejar de recibir sus correos electrónicos, envía un correo electrónico a php-arg+u...@googlegroups.com.
Para publicar una entrada en este grupo, envía un correo electrónico a php...@googlegroups.com.
Visita este grupo en http://groups.google.com/group/php-arg.
Para obtener más opciones, visita https://groups.google.com/groups/opt_out.
 
 

--
Gonzalo Sainz-Trápaga

InvGate

gonzalo.sa...@invgate.com

www.invgate.com

[diego]

nunca entendi el EXPLAIN de SQL :P

alguien podria tirar un ejemplito de indices correctamente hechos?

2013/7/2 Gonzalo Sainz-Trápaga <gon...@invgate.com>

[Tordek]

Creemos un par de tablas:

mysql> create table test_groups (id int(10), nombre varchar(10));
Query OK, 0 rows affected (0.06 sec)

mysql> create table test_users (id int(10), nombre varchar(10), grupo int(10));
Query OK, 0 rows affected (0.03 sec)

Estas tablas no tienen ningún índice. Si pedimos que nos explique qué hace un "select *", nos dice "SIMPLE", porque tiene que escanear toda la tabla para darnos los resultados

Otros valores relevantes son "possible_keys", que te dice cuales keys considera el optimizador, "key", que es la que eligió, "rows", que es la longitud del resultado, y "Extra" que tiene más info útil.

mysql> explain select * from test_users;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test_users | ALL  | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

En este caso, no hay nada que se pueda hacer: El select * no se lo puede optimizar más, porque sí o sí hay que recorrer la tabla para encontrar resultados.

Ahora, ¿qué pasa si yo quiero saber cuantos usuarios hay en cada grupo? (No me importa el nombre; solo el ID.)


mysql> explain select count(id) from test_users group by grupo;
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | test_users | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+---------------------------------+

Sigue siendo un select SIMPLE. Sigo sin keys posibles, ni keys usadas, ni nada. Pero hay 2 nuevos valores en el Extra: "temporary" y "filesort". Me indica que me está seleccionando los resultados en una tabla temporal, y que tiene que ordenar la tabla cada vez que pida la query. Esto claramente no puede ser bueno.

Entonces, ¿qué hago? Obviamente necesito una clave... ¿pero sobre qué valores?

Esto se empieza a volver una magia negra, pero hay muchos lugares donde puede servir una key: en un WHERE, en un GROUP BY, en un SORT... etc.

Entonces agregamos una key en "grupo" y preguntamos de nuevo:

mysql> alter table test_users add index (grupo);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(id) from test_users group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 |       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------+
1 row in set (0.00 sec)

El "Extra" desapareció, pero ahora sí uso una key. Esto significa que el optimizador está aprovechando el índice... pero no del todo. Sigue escaneando toda la tabla porque le pedimos que nos devuelva `count(id)`. Si cambiamos esto por `count(*)`, cambia la cosa:

mysql> explain select count(*) from test_users group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 | Using index |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+

Otro extra: "Using index". Esto significa que Mysql solo tiene que mirar el índice de la tabla (que es un árbol bastante optimizado) y nada más. No se puede ser más rápido que esto. (Y esto te muestra más magia negra, y por qué tenés que escribir con cuidado tus consultas.)

==

Bien, ahora digamos que quiero contar lo mismo, pero solo los usuarios que no se llamen "Admin_*"

Entonces mi query es:

mysql> explain select count(*) from test_users where nombre not like "admin_%" group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 | Using where |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+


Desapareció "using index"... o sea, que nuevamente estamos escaneando la tabla de a 1 por 1... ¡Ya sé! Agreguemos un índice sobre "nombre"!

mysql> alter table test_users add index (nombre);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from test_users where nombre not like "admin_%" group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 | Using where |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)


Hmm... ¿no cambió nada? Es porque solo se usa un índice a la vez... entonces el índice sobre el nombre acá no sirve, porque el group by viene primero. Entonces, ¿ahí quedamos? ¡No! Índices complejos. Agreguemos un índice sobre ambos campos.

mysql> alter table test_users add index (nombre, grupo);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from test_users where nombre not like "admin_%" group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 | Using where |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)


¿Lo mismo? Sí. Porque el órden de los campos en el índice importa. Ojo al piojo.


mysql> alter table test_users add index (grupo, nombre);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from test_users where nombre not like "admin_%" group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 | Using where |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

...opa. ¿Qué pasó? ¿Seguimos en la misma?

Acá me sorprendí yo, porque claramente hay un índice apropiado... ¿Entonces qué pasa?

Saquemos el índice sobre "grupo":

mysql> alter table test_users drop key grupo;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from test_users where nombre not like "admin_%" group by grupo;
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo_2 | 17      | NULL |    1 | Using where; Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

Ahora sí. Y la otra query sigue igual:

mysql> explain select count(*) from test_users group by grupo;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo_2 | 17      | NULL |    1 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)


¡Ah, ya sé! Será que Mysql considera que de los dos índices, el primero era mejor... Entonces si lo vuelvo a agregar, va a usar ese...

mysql> alter table test_users add index (grupo);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from test_users where nombre not like "admin_%" group by grupo;
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo_2 | 17      | NULL |    1 | Using where; Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

No.

No sé si será un bug, feature, simplicidad, error mio, o qué, pero parece que usa los índices en el órden que los agregué... pero en la query más simple sí usa el que solo involucra esa columna...

mysql> explain select count(*) from test_users group by grupo;
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | test_users | index | NULL          | grupo | 5       | NULL |    1 | Using index |
+----+-------------+------------+-------+---------------+-------+---------+------+------+-------------+
1 row in set (0.01 sec)

2013/7/2 diego <dieg...@gmail.com>

[Tordek]

Ahora bien... este ejemplo involucra 2 tablas excesivamente simples y 2 queries hechas específicamente para mostrar explain. En un sitio de verdad tenés que considerar muchos más factores... por ejemplo:

* MySQL no puede indizar NULLs. Si querés contar cuantos users están sin grupo, la única es recorrer la tabla.

* MySQL no puede indizar funciones. Supongamos que guardamos el timestamp de registro del usuario. Si queremos saber "Cuantos usuarios se registraron en abril de 2012", podés escribir

   SELECT Count(*) from test_users where month(registro) = 4 and year(registro) = 2012

  pero eso va a hacer que PARA CADA USUARIO de tu DB llamás a la función month(). Esto va a ser LEEEEEEEEEENTO.

  En Oracle (entre otros) podés agregar un índice sobre la función, olvidándote de este problema. En mysql, lo mejor que podés hacer es algo como

    SELECT Count (*) from test_users where registro between '2012-04-01' and '2012-04-30', que puede usar el índice... pero tenés que poner la fecha más explícita.

* Algunas bases de datos, a pesar de no poder indizar NULLs, permiten un truco muy tonto: Permiten indizar concatenaciones... así que pedís que indice "<columna>+a", entonces agrega "NULLa" al índice.

* Cada índice que agregás hace el insert más lento: Implica calcular la nueva tupla e ingresarla a un árbol. No es solución simplemente agregar un índice sobre cada combinación de columnas y dejar al optimizador que haga magia (incluso, como viste en mi otro mail, a veces omite un índice erróneamente).

* Cada índice que agregás lo tiene que considerar el optimizador a la hora de consultar. Otro motivo más para no agregarlos porque sí.

* Cada índice implica escribir más cosas a disco, y más información repetida. Si agregás un índice sobre una columna de texto, estás repitiendo datos que ya existen en la tabla.

* Los índices se pueden usar parcialmente: El índice sobre (grupo, nombre) se puede usar en la query de count * group by grupo, pero _SOLO_ de izquierda a derecha. No puedo usar ese índice sobre una consulta que haga, por ejemplo, "select ... where nombre = 'juan'".

* Los strings _SOLO_ se pueden indizar de izquierda a derecha. Puedo hacer "Where nombre like 'admin_%'", y usar el índice, pero si escribo "where nombre like '%_admin'", no. (Y, de nuevo: otras DBs pueden indizar funciones, y lo único que tengo que hacer es un índice sobre reverse(nombre)).

2013/7/3 Tordek <ked...@gmail.com>

[César Miguel Ingaruca Cruzado]

Hola  a todos,

yo creo que el volúmen de la información si es relevante y más aún cuando existe concurrencia de usuarios.

Mysql me presenta problemas de lentitud con consultas recurrentes a una tabla de dos millones de registros. Siendo un simple select campo from where PK=?

El acceso a la data en disco es un punto que hay que tener en cuenta en sistemas que son altamente transaccionales.

Alguien sabe si Mysql tiene un límite?  Porque estoy pensando seriamente en cambiar de motor.

Saludos