Rendimiento MySQL y PHP

Question

Rendimiento MySQL y PHP

banderas20 02 sep 2016 17:16

LMFAO

13.861 mensajes
desde feb 2002
en Barcelona

Buenas.

Estoy desarrollando con Wordpress y uso unos ficheros PHP que consultan a una BBDD MySQL.

En una máquina con 8GB de RAM y config por defecto no hay problemas. Lo estoy migrando a otra con 4GB y el rendimiento es horrible. Tanto que me da errores de timeout incluso subiéndolo a 100 segundos.

Ambos son Win7 de 64 bits. La máquina que da problemas tiene instalado lo básico. No entiendo qué puede estar pasando.

¿Alguna pista?

Gracias!

28 respuestas

Answer 1 · 2016-09-02T16:43:07+00:00

Mira el administrador de tareas, a ver el uso de CPU y RAM, y asi saber que te está limitando.
Que CPU tenía la máquina de 8GB y que CPU tiene la de 4GB?
Que servidor/stack estas usando?

Answer 2 · 2016-09-02T21:07:54+00:00

WaterDark escribió:Mira el administrador de tareas, a ver el uso de CPU y RAM, y asi saber que te está limitando.
Que CPU tenía la máquina de 8GB y que CPU tiene la de 4GB?
Que servidor/stack estas usando?

La CPU de la de 8GB es un i7-4712MQ @2.3GHz.
La del de 4GB es un Pentium Dual Core T3400 a 2.16Ghz.

Utilizo Appserv para Windows (Apache+MySQL+PHP5).

Miraré el admin de tareas a ver.

Gracias por responder!

Answer 3 · 2016-09-02T21:56:37+00:00

banderas20 escribió:Buenas.

Estoy desarrollando con Wordpress y uso unos ficheros PHP que consultan a una BBDD MySQL.

En una máquina con 8GB de RAM y config por defecto no hay problemas. Lo estoy migrando a otra con 4GB y el rendimiento es horrible. Tanto que me da errores de timeout incluso subiéndolo a 100 segundos.

Ambos son Win7 de 64 bits. La máquina que da problemas tiene instalado lo básico. No entiendo qué puede estar pasando.

¿Alguna pista?

Gracias!

Si tienes timeouts con 100 segundos, algo bien gordo tienes montado. Ni te molestes con el administrador de tareas y mira las queries que tengas en ejecución, tienes alguna muy mal montada. O queries en bucles, o insert continuos individuales, o select consecutivos en tablas grandes sin índices.... O todas a la vez, con 100 segundos...

Answer 4 · 2016-09-02T21:59:26+00:00

Hay una diferencia importante en la CPU, al menos 4 veces menos potente.

La base de datos que consultas con los scripts PHP tuyos propios es la de wordpress o es una propia?

Answer 5 · 2016-09-02T22:58:26+00:00

Stylish escribió:Si tienes timeouts con 100 segundos, algo bien gordo tienes montado. Ni te molestes con el administrador de tareas y mira las queries que tengas en ejecución, tienes alguna muy mal montada. O queries en bucles, o insert continuos individuales, o select consecutivos en tablas grandes sin índices.... O todas a la vez, con 100 segundos...

Joder, qué palo.... había conseguido que me funcionaran las queries y ahora no fastidies que no están optimizadas.... Lo raro es que si ejecuto la misma query en PHPMyAdmin la procesa relativamente rápido.

WaterDark escribió:Hay una diferencia importante en la CPU, al menos 4 veces menos potente.
La base de datos que consultas con los scripts PHP tuyos propios es la de wordpress o es una propia?

Ya...pero en la CPU potente ni se despeina...

Tanto los scripts como la BBDD son propios.

Gracias a ambos!

Answer 6 · 2016-09-02T23:13:45+00:00

Es posible que compartas la estructura de la DB y las consultas que te van lentas?

Es posible que en PHP estes realizando un bucle de consultas? osea, en phpmyadmin te va rapido porque ejecutas una consulta, pero si en PHP estas ejecuntando una consulta muchas veces, pues normal que vaya mas lento.

Sabes si tienes activo la cache de consultas? quizas en el viejo entorno lo tenias y en este no, y puede ser parte de la diferencia que notas.

Answer 7 · 2016-09-03T07:07:18+00:00

WaterDark escribió:Es posible que compartas la estructura de la DB y las consultas que te van lentas?

No entiendo bien lo que me preguntas. ¿Cómo puedo comprobarlo?

WaterDark escribió:Es posible que en PHP estes realizando un bucle de consultas? osea, en phpmyadmin te va rapido porque ejecutas una consulta, pero si en PHP estas ejecuntando una consulta muchas veces, pues normal que vaya mas lento.

Hay algunos PHP que hacen bucles, pero donde me encuentro problemas es en algunos PHP que hacen una única consulta. Eso sí, son consultas complejas que contienen joins entre tres tablas. Para que te hagas una idea, la consulta es ésta:

select (from_unixtime(fechas.fecha)) as fecha_hr, 
  FORMAT(datosTE.valor,2) as TE, 
  FORMAT(datosT1.valor,2) as T1, 
  FORMAT(datosT2.valor,2) as T2
  from (select distinct (fecha) from datos) as fechas 
  left join (select * from datos where idvariable=5) as datosTE on fechas.fecha=datosTE.fecha 
  left join (select * from datos where idvariable=1) as datosT1 on fechas.fecha=datosT1.fecha 
  left join (select * from datos where idvariable=3) as datosT2 on fechas.fecha=datosT2.fecha
  order by fechas.fecha asc limit 100

Esta consulta en PHPMyAdmin me tarda 174.3808 segundos en un PC y 0.0630 segundos en otro. No me explico la brutal diferencia...

WaterDark escribió:Sabes si tienes activo la cache de consultas? quizas en el viejo entorno lo tenias y en este no, y puede ser parte de la diferencia que notas.

De nuevo desconozco lo que me preguntas

. ¿Cómo lo compruebo?

Muchísimas gracias por la ayuda! [beer]

Answer 8 · 2016-09-03T07:33:56+00:00

Has probado si en ese Windows 7, el servidor PHP es de 64 bits?
Yo tenia una instalación de moodle con Debian virtualizado 64 bits e iba como un tiro, lo pasé a Windows mi SO principal e iba super lento, me dí cuenta que con XAMPP instalaba el servidor PHP de 32bits, fue cambiar a Wampp64 que incluye PHP de 64 bits y volvía a volar.
Un saludo

EDIT: Por probar que no sea http://www.wampserver.com/en/#wampserve ... 6-15-php-7

Answer 9 · 2016-09-03T07:58:03+00:00

banderas20 escribió:
WaterDark escribió:Es posible que compartas la estructura de la DB y las consultas que te van lentas?

No entiendo bien lo que me preguntas. ¿Cómo puedo comprobarlo?

WaterDark escribió:Es posible que en PHP estes realizando un bucle de consultas? osea, en phpmyadmin te va rapido porque ejecutas una consulta, pero si en PHP estas ejecuntando una consulta muchas veces, pues normal que vaya mas lento.

Hay algunos PHP que hacen bucles, pero donde me encuentro problemas es en algunos PHP que hacen una única consulta. Eso sí, son consultas complejas que contienen joins entre tres tablas. Para que te hagas una idea, la consulta es ésta:

select (from_unixtime(fechas.fecha)) as fecha_hr, FORMAT(datosTE.valor,2) as TE, FORMAT(datosT1.valor,2) as T1, FORMAT(datosT2.valor,2) as T2 from (select distinct (fecha) from datos) as fechas left join (select * from datos where idvariable=5) as datosTE on fechas.fecha=datosTE.fecha left join (select * from datos where idvariable=1) as datosT1 on fechas.fecha=datosT1.fecha left join (select * from datos where idvariable=3) as datosT2 on fechas.fecha=datosT2.fecha order by fechas.fecha asc limit 100

Esta consulta en PHPMyAdmin me tarda 174.3808 segundos en un PC y 0.0630 segundos en otro. No me explico la brutal diferencia...

WaterDark escribió:Sabes si tienes activo la cache de consultas? quizas en el viejo entorno lo tenias y en este no, y puede ser parte de la diferencia que notas.

De nuevo desconozco lo que me preguntas . ¿Cómo lo compruebo?

Muchísimas gracias por la ayuda!

Comprueba que en las tablas están generados los mismos índices. Ejecuta la query con EXPLAIN por delante en ambos phpmyadmin y mira si la salida es exactamente igual. A mí me apesta a índices desde el principio

.

Answer 10 · 2016-09-03T09:06:18+00:00

Eso son índices de cráneo. Pero de todos modos, esto deberías ejecutarlo en un entorno Linux y no en un Windows con un pseudo emulador para ver si realmente se ejecuta bien.

Answer 11 · 2016-09-03T09:42:47+00:00

banderas20 escribió:
WaterDark escribió:Es posible que compartas la estructura de la DB y las consultas que te van lentas?

No entiendo bien lo que me preguntas. ¿Cómo puedo comprobarlo?

Me refería a que subieses unas capturas de la estructura de las tablas de la base de datos, y las consultas que haces, para ver si te faltan indices y tal.

banderas20 escribió:Hay algunos PHP que hacen bucles, pero donde me encuentro problemas es en algunos PHP que hacen una única consulta. Eso sí, son consultas complejas que contienen joins entre tres tablas. Para que te hagas una idea, la consulta es ésta:

select (from_unixtime(fechas.fecha)) as fecha_hr, FORMAT(datosTE.valor,2) as TE, FORMAT(datosT1.valor,2) as T1, FORMAT(datosT2.valor,2) as T2 from (select distinct (fecha) from datos) as fechas left join (select * from datos where idvariable=5) as datosTE on fechas.fecha=datosTE.fecha left join (select * from datos where idvariable=1) as datosT1 on fechas.fecha=datosT1.fecha left join (select * from datos where idvariable=3) as datosT2 on fechas.fecha=datosT2.fecha order by fechas.fecha asc limit 100

Esta consulta en PHPMyAdmin me tarda 174.3808 segundos en un PC y 0.0630 segundos en otro. No me explico la brutal diferencia...

En el viejo servidor usabas el mismo stack y versíon? para descartar que sea algo de la configuracíon por defecto lo que marque la diferencia.
Como has hecho la migración de la base de datos?, osea, has copiado directamente las carpetas del servidor, o lo has importado/exportado usando PHPMyAdmin o la has creado a mano de nuevo o como? lo pregunto por si en la vieja base de datos es posible que algunos campos que eran llaves primarias, foraneas o indexadas, en la nueva no lo sean.
En tu consulta creo que el único campo que importa es idvariable, es una llave primara¿no?.

banderas20 escribió:
WaterDark escribió:Sabes si tienes activo la cache de consultas? quizas en el viejo entorno lo tenias y en este no, y puede ser parte de la diferencia que notas.

De nuevo desconozco lo que me preguntas . ¿Cómo lo compruebo?

La cache de consultas es una opción de la MySQL, que sirve para que el resultado de las consultas las guarde en memoria, de forma que si una consulta se repite, no tiene que procesarla, solo devolver lo que tenía en memoria.
Para saber si está activo, en PHPMyAdmin ejecuta esta consulta: SHOW VARIABLES LIKE "query_cache_type"

Answer 12 · 2016-09-03T09:51:39+00:00

@luciferfran
Ambos Windows 7 son calcados, y el Appserv es el mismo. Igualmente, comprobaré a ver si el PHP es de 64 bits.

@Stylish
Las salidas son diferentes. ¿Qué significa? ¿Se puede hacer algo?:

En el PC rápido:

1   PRIMARY   <derived2>   ALL               34944   Using filesort   
1   PRIMARY   <derived3>   ref   <auto_key0>   <auto_key0>   4   fechas.fecha   10      
1   PRIMARY   <derived4>   ref   <auto_key0>   <auto_key0>   4   fechas.fecha   10      
1   PRIMARY   <derived5>   ref   <auto_key0>   <auto_key0>   4   fechas.fecha   10      
5   DERIVED   datos   ALL               34944   Using where   
4   DERIVED   datos   ALL               34944   Using where   
3   DERIVED   datos   ALL               34944   Using where   
2   DERIVED   datos   ALL               34944   Using temporary

En el PC lento

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra    
1    PRIMARY    <derived2>    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using temporary; Using filesort
1    PRIMARY    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using where; Using join buffer (Block Nested Loop)
1    PRIMARY    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using where; Using join buffer (Block Nested Loop)
1    PRIMARY    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using where; Using join buffer (Block Nested Loop)
2    DERIVED    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using temporary

@cipoteloth

Si son índices, ¿por qué esa diferencia tan brutal de rendimiento cuando ambas BBDDs son calcos una de la otra?
No tengo tiempo de probarlo en un entorno Linux. Estoy migrando voy un poco contrarreloj.

Mira las salidas de EXPLAIN que he pegado a ver si te dicen algo.

@WaterDark

La migración la he hecho exportando e importando desde y hacia PHPMyAdmin. Entiendo que esto respeta las claves principales. idvariable es primaria, sí. Aunque uso otros campos para hacer los JOIN.

query_cache_type está a OFF en ambos PCs.

Muchas gracias a todos!

Answer 13 · 2016-09-03T10:11:46+00:00

Estas seguro de que el appserver es la misma versión? la explicación parece tener más columnas en el nuevo servidor que en el viejo. Una versión diferente puede suponer opciones por defecto diferentes que sean lo que marque la diferencia.

Busca en ambos PCs, en la carpeta del servidor, el archivo my.ini o my.cnf, y comparalos a ver.

Comprueba en ambos PCs estas variables:
SHOW VARIABLES LIKE "join_buffer_size";
SHOW VARIABLES LIKE "sort_buffer_size";
SHOW VARIABLES LIKE "max_heap_table_size";
SHOW VARIABLES LIKE "tmp_table_size";

En ambos PCs se usan un disco duro tradicional? o es posible que en el viejo tenga un SSD y el nuevo un HDD viejillo?

Si quieres asegurarte de que ambas bases de datos tienen los mismos indices, ejecuta está consulta en ambos y comparalo:
SHOW INDEXES FROM datos;

Answer 14 · 2016-09-03T12:33:16+00:00

banderas20 escribió:@luciferfran
Ambos Windows 7 son calcados, y el Appserv es el mismo. Igualmente, comprobaré a ver si el PHP es de 64 bits.

@Stylish
Las salidas son diferentes. ¿Qué significa? ¿Se puede hacer algo?:

En el PC rápido:

1 PRIMARY <derived2> ALL 34944 Using filesort 1 PRIMARY <derived3> ref <auto_key0> <auto_key0> 4 fechas.fecha 10 1 PRIMARY <derived4> ref <auto_key0> <auto_key0> 4 fechas.fecha 10 1 PRIMARY <derived5> ref <auto_key0> <auto_key0> 4 fechas.fecha 10 5 DERIVED datos ALL 34944 Using where 4 DERIVED datos ALL 34944 Using where 3 DERIVED datos ALL 34944 Using where 2 DERIVED datos ALL 34944 Using temporary

En el PC lento

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 35030 100.00 Using temporary; Using filesort 1 PRIMARY datos NULL ALL NULL NULL NULL NULL 35030 100.00 Using where; Using join buffer (Block Nested Loop) 1 PRIMARY datos NULL ALL NULL NULL NULL NULL 35030 100.00 Using where; Using join buffer (Block Nested Loop) 1 PRIMARY datos NULL ALL NULL NULL NULL NULL 35030 100.00 Using where; Using join buffer (Block Nested Loop) 2 DERIVED datos NULL ALL NULL NULL NULL NULL 35030 100.00 Using temporary

@cipoteloth

Si son índices, ¿por qué esa diferencia tan brutal de rendimiento cuando ambas BBDDs son calcos una de la otra?
No tengo tiempo de probarlo en un entorno Linux. Estoy migrando voy un poco contrarreloj.

Mira las salidas de EXPLAIN que he pegado a ver si te dicen algo.

@WaterDark

La migración la he hecho exportando e importando desde y hacia PHPMyAdmin. Entiendo que esto respeta las claves principales. idvariable es primaria, sí. Aunque uso otros campos para hacer los JOIN.

query_cache_type está a OFF en ambos PCs.

Muchas gracias a todos!

Te faltan todos los índices, ponlos iguales y te irá igual. El problema es que al exportar la primera bbdd ya tenias la estructura hecha en el segundo servidor, y al importar los datos no se metieron los índices.

Answer 15 · 2016-09-03T14:02:07+00:00

@WaterDark

PC Viejo:

join_buffer_size    262144
sort_buffer_size    262144
max_heap_table_size    16777216
tmp_table_size    519045120

PC Potente

join_buffer_size    262144
sort_buffer_size    262144
max_heap_table_size    16777216
tmp_table_size    34603008

SHOW INDEXES FROM datos;

devuelve 0 resultados en ambos PCs.
Ambos PCs tienen HD normal. no SSD.

Y acabo de ver que sólo tengo "my.ini" en el PC viejo, porque creo que ahí instalé primero MySQL aparte del paquete WAMP.
De hecho, debajo de "Appserv" sólo existe la carpeta "MySQL" en el PC viejo.

Por cierto,
PC Viejo: Appserv8.1.0
PC Nuevo: Appserv8.1.3

Lo que no sé es qué MySQL está usando el PC nuevo, si no existe dicha carpeta.

¿por cuál de todas estas diferencias la puedo estar cagando? [+risas]

@Stylish
Lo que he hecho siempre es un DROP DATABASE, luego exporto de uno y luego vuelvo a importar en otro.
¿Cómo pongo los índices igual? ¿Qué es lo que he hecho mal con la estructura y cómo evito que me vuelva a pasar?

Os debo una birra a todos!

Answer 16 · 2016-09-03T14:20:11+00:00

banderas20 escribió:@WaterDark

PC Viejo:
join_buffer_size 262144 sort_buffer_size 262144 max_heap_table_size 16777216 tmp_table_size 519045120

PC Potente
join_buffer_size 262144 sort_buffer_size 262144 max_heap_table_size 16777216 tmp_table_size 34603008

SHOW INDEXES FROM datos;
devuelve 0 resultados en ambos PCs.
Ambos PCs tienen HD normal. no SSD.

Y acabo de ver que sólo tengo "my.ini" en el PC viejo, porque creo que ahí instalé primero MySQL aparte del paquete WAMP.
De hecho, debajo de "Appserv" sólo existe la carpeta "MySQL" en el PC viejo.

Por cierto,
PC Viejo: Appserv8.1.0
PC Nuevo: Appserv8.1.3

Lo que no sé es qué MySQL está usando el PC nuevo, si no existe dicha carpeta.

¿por cuál de todas estas diferencias la puedo estar cagando?

@Stylish
Lo que he hecho siempre es un DROP DATABASE, luego exporto de uno y luego vuelvo a importar en otro.
¿Cómo pongo los índices igual? ¿Qué es lo que he hecho mal con la estructura y cómo evito que me vuelva a pasar?

Os debo una birra a todos!

Es complicado saber exactamente lo que ha pasado, pero DROP es para borrar. Ahora lo que deberías hacer es ver en la pestaña "ESTRUCTURA" del phpmyadmin los índices de todas las tablas en el equipo que va rápido, una por una, y replicarlos sin más en el equipo que va lento. Si tienes dudas de cómo se hace y para qué sirven los índices, mira en internet. Será un aprendizaje realmente importante.

Answer 17 · 2016-09-03T14:44:47+00:00

@banderas20 Hay bastante diferencia en la variable tmp_table_size, de 500MB+ en el viejo a 33MB en el nuevo, y puede ser la diferencia, ya que a tu consulta le afecta esa variable (creación de tablas temporales).

Puedes cambiar el valor en la sesion actual para probar con esta consulta:
SET GLOBAL tmp_table_size=519045120;

SHOW INDEXES FROM datos; debe devolver una tabla con la lista de indices, a no ser que `datos` sea una vista, ¿es una vista?
Si es una tabla, al ver la estructura de la tabla en PHPMyAdmin ¿aparecen al lado de algunos campos unas llaves? en la llave primaria tiene que salir una llave dorada.

Si no sabes donde esta instalado el servidor mysql, prueba a buscar el ejecutable.
El archivo my.ini creo que no es obligatorio, ya que el propio servidor tiene unas opciones por defecto que usará si algo no esta definido en el archivo my.ini.

Answer 18 · 2016-09-03T23:11:53+00:00

@Stylish
Cuando hablas de índices, ¿te refieres a claves primarias o es otro concepto? En todo caso, miraré como replicarlo.

@WaterDark
Probaré a cambiar "tmp_table_size" a ver qué resultado me da. Aunque no sé por qué se ha cambiado esto.
datos no es una vista. Es una tabla. Pero es la única que no tiene definidas claves primarias, porque no hay un único campo que sea único, sino una combinación de ellos. Por eso no le he puesto nada.
¿Esto es malo?

Gracias!

Answer 19 · 2016-09-03T23:14:32+00:00

@banderas20 pero no habías dicho que el campo idvariable es una llave primaria??

Un índice es como guardar un resumen en la RAM sobre los distintos valores de un campo y donde encontrar las filas que corresponde con cada valor, de forma que mejora el rendimiento de las consultas que busquen resultados basándose en el campo indexado.
Cuando declaras un campo como llave primaria, ajena o único, se crea automáticamente un índice para ese campo. También puedes crear índices específicamente para un campo que no tiene por que ser una llave primaria, ajena o único.
Normalmente los campos que se usan para hacer los JOIN deben tener un índice, sino la unión es muy lenta.
También se suelen asignar índices a algunos campos que se usan mucho en la clausula WHERE y ORDER.

Answer 20 · 2016-09-03T23:44:43+00:00

structure.jpg (36.12 KB)

estructura

WaterDark escribió:@banderas20 pero no habías dicho que el campo idvariable es una llave primaria??

Un índice es como guardar un resumen en la RAM sobre los distintos valores de un campo y donde encontrar las filas que corresponde con cada valor, de forma que mejora el rendimiento de las consultas que busquen resultados basándose en el campo indexado.
Cuando declaras un campo como llave primaria, ajena o único, se crea automáticamente un índice para ese campo. También puedes crear índices específicamente para un campo que no tiene por que ser una llave primaria, ajena o único.
Normalmente los campos que se usan para hacer los JOIN deben tener un índice, sino la unión es muy lenta.
También se suelen asignar índices a algunos campos que se usan mucho en la clausula WHERE y ORDER.

Hola WaterDark.

Sí, idvariable es clave primaria de la tabla variables. Pero no de la tabla datos.

Lo que me explicas de los índices supongo que es consecuencia de la salida del EXPLAIN (en el PC lento es así):

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra    
1    PRIMARY    <derived2>    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using temporary; Using filesort
1    PRIMARY    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using where; Using join buffer (Block Nested Loop)
1    PRIMARY    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using where; Using join buffer (Block Nested Loop)
1    PRIMARY    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using where; Using join buffer (Block Nested Loop)
2    DERIVED    datos    NULL   ALL    NULL   NULL   NULL   NULL   35030    100.00    Using temporary

El tema es que no sé por qué al exportar la BBDD no se ha exportado también esa configuración de índices, y ahora no sé cómo recrearla (no entiendo nada del código que te he pegado). Si hubiera una opción en PHPMyadmin que pudiera marcar al exportar la BBDD para que me respetara los índices, lo probaría de nuevo a ver.

Adjunto captura de la estructura de la tabla datos.

Answer 21 · 2016-09-04T00:10:20+00:00

@banderas20
Los índices no se han perdido, según entiendo tienes los mismos... osea, que antes te faltaban y ahora te siguen faltando, lo que quizás está pasando es que has perdido el parche que tenía para funcionar bien, que es la variable tmp_table_size, cuyo valor de 500M creo que es una barbaridad. Falta que pruebes a cambiarla a ver si es por eso por lo que en el viejo servidor iba bien.

¿Cuantos registros tiene la tabla datos?

Luego si quieres optimizar la DB:

Si idvariable no es la llave primaria de datos, como estas usandola para localizar registros 3 veces en la misma consulta, debería tener un índice. Puedes añadir el índice con esta consulta:
ALTER TABLE datos ADD INDEX(idvariable);

Tambíen quizás crear un índice para el campo fecha mejore el rendimiento, puedes probar a añadir el índice y comprobar si mejora.
ALTER TABLE datos ADD INDEX(fecha);
Si no mejora, puedes quitar el índice(consume RAM):
DROP INDEX fecha ON datos;

Con el índice en la fecha quizás la consulta rinda mejor si el ON del JOIN los haces al revés, en vez de

 left join (select * from datos where idvariable=5) as datosTE on fechas.fecha=datosTE.fecha
  left join (select * from datos where idvariable=1) as datosT1 on fechas.fecha=datosT1.fecha
  left join (select * from datos where idvariable=3) as datosT2 on fechas.fecha=datosT2.fecha

que sea así

 left join (select * from datos where idvariable=5) as datosTE on datosTE.fecha=fechas.fecha
  left join (select * from datos where idvariable=1) as datosT1 on datosT1.fecha=fechas.fecha
  left join (select * from datos where idvariable=3) as datosT2 on datosT2.fecha=fechas.fecha

Answer 22 · 2016-09-04T01:11:39+00:00

@WaterDark.

La tabla datos tiene 34598 registros.
He cambiado tmp_table_size a 34603008.
He añadido los índices a la tabla datos con:

ALTER TABLE datos ADD INDEX(idvariable);
ALTER TABLE datos ADD INDEX(fecha);

Y la consulta se me ejecuta en 0.0690 segundos! [tadoramo]

¿por qué en mi PC potente iba tan rápido sin hacer estas modificaciones?

En fin. Muchísimas gracias!!!

Answer 23 · 2016-09-04T01:18:28+00:00

@banderas20 pero solo con cambiar el valor de tmp_table_size va rápido? en el pc viejo tenía el valor que acabas de cambiar y es lo que posiblemente hace que vaya rápido.

Answer 24 · 2016-09-04T02:44:47+00:00

WaterDark escribió:@banderas20 pero solo con cambiar el valor de tmp_table_size va rápido? en el pc viejo tenía el valor que acabas de cambiar y es lo que posiblemente hace que vaya rápido.

Ostras, pues no lo sé, porque he aplicado todos los cambios a la vez. Quizá los deshago y los aplico uno a uno a ver qué es lo que era.

¿Y por qué co*ones se iba a cambiar ese jodido parámetro él solito? [buuuaaaa]

Answer 25 · 2016-09-04T03:05:43+00:00

@banderas20 Los parámetros no son parte de la base de datos, por lo que al importar la base de datos, no se incluyen. Los parámetros son parte de la configuración del servidor de base de datos, y pueden cambiar sus valores por defecto según versión, además pueden venir unos valores específicos según que stack te descargues.

Yo creo que con los índices solo va a ir bien, pero me queda la curiosidad si con esa variable sola también iba bien.
Si solo con los índices va bien, olvídate de esa variable a no ser que te de problemas por otro lado, ya que 500M creo que es una barbaridad.

Answer 26 · 2016-09-04T08:45:07+00:00

@WaterDark.
Entendido al 100%. Muchísimas gracias! [oki]

Veo que ni tú ni yo dormimos mucho, por las horas de los post [jaja]

Answer 27 · 2016-09-04T11:19:20+00:00

@banderas20 [hallow]

Por cierto, ahora que la tabla tiene índices, hacer la consulta sin sub-consultas y usar en ON del JOIN para filtrar va a ir algo más rápido.

SELECT 
   FROM_UNIXTIME(fechas.fecha) AS fecha_hr,
   FORMAT(datosTE.valor,2) AS TE,
   FORMAT(datosT1.valor,2) AS T1,
   FORMAT(datosT2.valor,2) AS T2
   FROM (SELECT distinct (fecha) FROM datos) AS fechas
   LEFT JOIN datos AS datosTE ON datosTE.fecha=fechas.fecha AND datosTE.idvariable=5
   LEFT JOIN datos AS datosT1 ON datosT1.fecha=fechas.fecha AND datosT1.idvariable=1
   LEFT JOIN datos AS datosT2 ON datosT2.fecha=fechas.fecha AND datosT2.idvariable=3
   ORDER BY fechas.fecha ASC
   LIMIT 100

Answer 28 · 2016-09-04T15:45:25+00:00

banderas20 04 sep 2016 17:45

LMFAO

13.861 mensajes
desde feb 2002
en Barcelona

@WaterDark.

[qmparto]

Lo probaré. Gracias!!! [oki]