[SOLUCIONADO] mysql muy lento

[gamau6]

Buenas tengo una duda estoy trabajando sobre una base de datos mysql con cerca de 2 millones de registros, el servidor es un ubuntu petium 4 con 1.5gb de ram al momento de hacer alguna query tarda mas de la cuenta.

los campos son todos varchar, exepto el id que es int.

Ejemplo

Código MySQL:

mysql> select count(*) from textos;
+----------+
| count(*) |
+----------+
|  2291060 |
+----------+
1 row in set (19,23 sec)
 

suele tardar un poco mas, pero al realizar un query esto me da

Código MySQL:

mysql> select * from texto where texto = 'hola' limit 1;
 
1 row in set (40,66 sec)
 

Es normal que sea tan lento con un servidor de este tipo?
No estoy seguro si coloque toda la informacion correspondiente para esto.

Saludos!

[gnzsoloyo]

Si ese campo es un TEXT, o algo semejante, y no está indexado, estás haciendo lo que se denomina "full table scan", que es algo bastante espantoso...
para verificar cómo está realizando la consulta, habría que indicarle a MySQL que muestre el plan de la consulta:

Código MySQL:

EXPLAIN SELECT * FROM texto WHERE texto = 'hola' LIMIT 1;
 

Eso devolverá una tabla con ciertos datos. Postalo acá y veremos.

[BrujoNic]

Aparte de lo mencionado, un Pentium IV NO es un servidor ni está equipado para trabajar como tal y eso lógicamente también influye.

[gamau6]

Iniciado por gnzsoloyo

Si ese campo es un TEXT, o algo semejante, y no está indexado, estás haciendo lo que se denomina "full table scan", que es algo bastante espantoso...
para verificar cómo está realizando la consulta, habría que indicarle a MySQL que muestre el plan de la consulta:

Código MySQL:

EXPLAIN SELECT * FROM texto WHERE texto = 'hola' LIMIT 1;
 

Eso devolverá una tabla con ciertos datos. Postalo acá y veremos.

bien esto me aparecio

Código MySQL:

mysql> EXPLAIN SELECT * FROM texto WHERE texto = 'hola' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | texto   | ALL  | NULL        | NULL | NULL    | NULL | 2300356 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0,00 sec)
 

[gamau6]

Iniciado por BrujoNic

Aparte de lo mencionado, un Pentium IV NO es un servidor ni está equipado para trabajar como tal y eso lógicamente también influye.

Si lastimosamente con ese equipo tengo que trabajar, pero lei que mysql no necesita de tanto equipamiento solo necesita hasta 2gb de ram, no se si me estoy equivocando.

De todos modos vi una base de datos con mas de 300 millones de datos y no es tan lento como esta.

Gracias por responder!

[BrujoNic]

La RAM, no es lo único que influye. También hay que tomar en cuenta el procesador, velocidad del disco duro, tarjeta de red, cableado, etc.

Soy de las personas que reutilizan equipos viejos pero para cosas más específicas como servidores proxys, de correo o cosas más pequeñas y no una BD donde tiene que interactuar con personas donde los tiempos de respuesta son importantes.

Comienza a crear llaves secundarias o foráneas pero el desempeño sera bueno dependiendo del tipo de campo.

Edito por una confusión...
¿Ese pentium IV tiene instalado Ubuntu Server o Desktop? Eso también influye mucho ya que el server utiliza muchisimo menos recursos que un desktop porque es todo consola y el desempeño es mejor porque tiene más recursos.

[gnzsoloyo]

Código MySQL:

mysql> EXPLAIN SELECT * FROM texto WHERE texto = 'hola' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | texto   | ALL  | NULL        | NULL | NULL    | NULL | 2300356 | USING WHERE |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row IN SET (0,00 sec)
 

Esto me dice dos cosas: Por un lado, que estás haciendo un FULL TABLE SCAN, lo que resulta espantoso. Eso ocurre porque no existen índices definidos sobre el campo, por lo que debe leer la tabla entera para luego aplicar el WHERE.
Eso resulta muy ineficiente.
Como ya te remarcaron, conviene que generes un INDEX sobre ese campo, pero siendo un TEXT eso puede tener algunas complicaciones, ya que si los textos son demasiado extensos, deberás segmentar la clave del índice creado.
Por otro lado, si las búsquedas son por proximidad, conviene hacerlo con indices FULLTEXT, que son mejores para eso, pero con el inconveniente que sólo se admiten en tablas de tipo MyISAM, y si estás usando InnoDB, no se podrá. Eso a menos que separes el campo en otra tabla, relacionándolo por medio de una FK programada (ya que las MyISAM no tienen FK).

En definitiva, se puede mejorar la performance, usando diversas aproximaciones al problema, más allá de convenir usar un hardware mejor. Pero no te podemos dar consejos avanzados sin conocer mejor el contexto de tu sistema, el tipo de consultas que usas, los datos y la estructura de la base.
Nos falta demasiada info para poder dar un buen diagnóstico.
Respecto a las consultas en si con servidores limitados, es bastante variable. Yo he hecho consultas eficientes con tablas de decenas de millones de registros y más de 1 Gb de peso, usando una notebook con 6 Gb de RAM y sin mayores retrasos. Y también he tenido problemas con consultas de apenas algunos cientos de miles de registros...
Muchas veces se resuelve con mejores consultas e índices. Pero necesitamos saber más para decirlo con certeza.

[gamau6]

Iniciado por BrujoNic

La RAM, no es lo único que influye. También hay que tomar en cuenta el procesador, velocidad del disco duro, tarjeta de red, cableado, etc.

Soy de las personas que reutilizan equipos viejos pero para cosas más específicas como servidores proxys, de correo o cosas más pequeñas y no una BD donde tiene que interactuar con personas donde los tiempos de respuesta son importantes.

Comienza a crear llaves secundarias o foráneas pero el desempeño sera bueno dependiendo del tipo de campo.

Edito por una confusión...
¿Ese pentium IV tiene instalado Ubuntu Server o Desktop? Eso también influye mucho ya que el server utiliza muchisimo menos recursos que un desktop porque es todo consola y el desempeño es mejor porque tiene más recursos.

Gracias por responder.
Es un ubuntu desktop

[gamau6]

Iniciado por gnzsoloyo

Código MySQL:

mysql> EXPLAIN SELECT * FROM texto WHERE texto = 'hola' LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | texto   | ALL  | NULL        | NULL | NULL    | NULL | 2300356 | USING WHERE |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row IN SET (0,00 sec)
 

Esto me dice dos cosas: Por un lado, que estás haciendo un FULL TABLE SCAN, lo que resulta espantoso. Eso ocurre porque no existen índices definidos sobre el campo, por lo que debe leer la tabla entera para luego aplicar el WHERE.
Eso resulta muy ineficiente.
Como ya te remarcaron, conviene que generes un INDEX sobre ese campo, pero siendo un TEXT eso puede tener algunas complicaciones, ya que si los textos son demasiado extensos, deberás segmentar la clave del índice creado.
Por otro lado, si las búsquedas son por proximidad, conviene hacerlo con indices FULLTEXT, que son mejores para eso, pero con el inconveniente que sólo se admiten en tablas de tipo MyISAM, y si estás usando InnoDB, no se podrá. Eso a menos que separes el campo en otra tabla, relacionándolo por medio de una FK programada (ya que las MyISAM no tienen FK).

En definitiva, se puede mejorar la performance, usando diversas aproximaciones al problema, más allá de convenir usar un hardware mejor. Pero no te podemos dar consejos avanzados sin conocer mejor el contexto de tu sistema, el tipo de consultas que usas, los datos y la estructura de la base.
Nos falta demasiada info para poder dar un buen diagnóstico.
Respecto a las consultas en si con servidores limitados, es bastante variable. Yo he hecho consultas eficientes con tablas de decenas de millones de registros y más de 1 Gb de peso, usando una notebook con 6 Gb de RAM y sin mayores retrasos. Y también he tenido problemas con consultas de apenas algunos cientos de miles de registros...
Muchas veces se resuelve con mejores consultas e índices. Pero necesitamos saber más para decirlo con certeza.

Gracias por responder.
Solo 2 usuarios realizan consultas a la base de datos, no se puede utilizar indices ya que se tiene que hacer por condiciones al campo texto que es de tipo text, se podria cambiar a un varchar mas pequeño si influye en tiempo de respuesta.
No indique que el motor es innoDB.

[gnzsoloyo]

Solo 2 usuarios realizan consultas a la base de datos, no se puede utilizar indices ya que se tiene que hacer por condiciones al campo texto que es de tipo text

¿Qué tiene que ver que sean sólo dos usuarios?
Si la consulta es ineficiente lo será con un sólo usuario, o con mil. Con más lo único que sucede es que la performance empeora.
Por otro lado, si hay condiciones operando sobre ese campo, con más razón debe ser indexado.

¿Puedes explicarme en qué tipos de condiciones, y bajo qué contexto, que no sean inserciones masivas de datos, consideras que un índice es nocivo para una consulta?

Eso es algo que jamás he escuchado. Sería interesante ver un ejemplo de algo así de extraordinario.

[gamau6]

Iniciado por gnzsoloyo

¿Qué tiene que ver que sean sólo dos usuarios?
Si la consulta es ineficiente lo será con un sólo usuario, o con mil. Con más lo único que sucede es que la performance empeora.
Por otro lado, si hay condiciones operando sobre ese campo, con más razón debe ser indexado.

¿Puedes explicarme en qué tipos de condiciones, y bajo qué contexto, que no sean inserciones masivas de datos, consideras que un índice es nocivo para una consulta?

Eso es algo que jamás he escuchado. Sería interesante ver un ejemplo de algo así de extraordinario.

Creo que no me doy a entender bien.

Tiene que ser exacta la palabra del where en el campo

Código MySQL:

select * from texto where texto = 'hola'
 

Tampoco entiendo bien el indexado que menciona.
Mil disculpas si empeoro la duda.

[gnzsoloyo]

Pues te están faltando fundamentos clave:

- Indices (Bases de Datos)
- CREATE INDEX (MySQL)
- Cómo usa MySQL los índices.

Si no los sabes usar, ponte urgente a aprender, porque esa es la solución para mejorar la performance de tu caso.

[BrujoNic]

Estoy de acuerdo, el indexar un campo NO INFLUYE en lo más mínimo si van a buscar todo el contenido del campo indexado o solo una parte.

Resumiendo, un índice se utiliza para realizar búsquedas más rápidas y no secuencialmente hasta llegar al resultado que es mucho más lento.

Los índices son necesario y es bueno que leas lo que te recomendaron ya que es IMPORTANTE.

Yo empezaría cambiando el Ubuntu Desktop por Ubuntu Server y que sea LTS (en este momento sería la 12.04.1).

Lo que recomiendan mucho para el desempeño es desactivarle la búsqueda por DNS y eso lo podrías hacer de 2 formas:

1. Editando el archivo /etc/my.cnf y agregando al final de la etiqueta [mysqld] skip-name-resolve.
2. Hacerlo por línea de comando con mysqld -skip-name-resolve.

La otra recomendación, es modificar el tamaño de la cache:

Para chequear la query chache ejecuta:
mysql> SHOW STATUS LIKE ‘Qcache%’;

Para ver el estado de la misma:
mysql> SHOW VARIABLES LIKE ‘%query_cache%’;

Para cambiar el tamaño de la misma:
mysql> SET GLOBAL query_cache_size = 52428800;

Podemos tambien modificar el cache de la misma en el archivo /etc/my.cnf

[mysqld]
…
…
query_cache_size = 52428800
query_cache_type = 1
en GNU/Linux

Fuente: Mysql mejorar tiempo respuesta en GNU/Linux

[gnzsoloyo]

Excelente aporte.
Copiado para Herramientas y Métodos útiles

[gamau6]

Muchas gracias por tomarse el tiempo en responder voy a leer un poco sobre toda la informacion que me pasaron y luego voy a postear los resultados.

[gamau6]

Por si les hacia la duda nada mas les posteo

Código MySQL:

mysql> show status like 'Qcache%'
    -> ;
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0,10 sec)
 
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0,03 sec)
 

[BrujoNic]

La duda de qué????

¿Intentaste lo de los DNS? ¿Le cambiaste el tamaño de la cache?

Simplemente estas mostrando lo que tenes actualmente y de esa forma, no vamos a avanzar nada ya que al parecer, no vas o van a cambiar el Ubuntu Desktop.