OVH Community, your new community space.

eterno mysql vs postgresql


PacoSS
09/07/2011, 17:41
Hoy he visto otra cosilla interesante. El tema de partir las bases de datos de mysql.

Se usa cuando la cantidad de datos es muy grande y/o cuando la memoria disponible es pequeña.
La condición es que el campo clave para "partir" la base de datos debe ser un entero o resultado entero, y que el número máximo de trozos será de 1.000

Viene al caso, por ejemplo, del inicio de este hilo. Se pueden partir las bases de datos por año+mes, por id_cliente, ... o general por el índice maestro mas usado.

De interesante lectura:
http://www.slideshare.net/datacharme...ysql-51-and-55

No dejeis de leer la página 70, y a partir de la 77, donde explica las nuevas funcionalidades de mysql 5.5 en este aspecto.

dev2k
08/07/2011, 00:07
Cita Publicado inicialmente por luis_sanz
yo solo trabajo con innodb, myisam quiza sea mas rapido, pero prefiero seguridad ante todo, y bien programado tus consultas innodb es tan veloz como myisam, ADEMAS percona que fue lo que recomende probar esta especialmente optimizado para innoDB
blabla blaaa, bien!, a nivel de servidor MySQL InnoDB ofrece varias posibilidades que a MyISAM se les quedo largas.

La diferencia entre uno y otro no es la cuestión a discutir, la diferencia se radica en el tema de almacenamiento de indices!, en el cual myisam se relega al mercado de otros gestores en plan NOSQL, no de InnoDB.

(En 12 años aun no he visto una DB InnoDB romper)

dev2k
07/07/2011, 23:54
Cita Publicado inicialmente por Kilburn
Edit: Ah, y yo utilizaría "between" para las fechas en vez de >= y <=...
Desde mi punto de vista el "between" entre fechas (y el uso en general de fechas en myslq) siempre me ha parecido un puto desastre por el tema del formato.

Si el formato de la fecha se establece conforme a mysql le gusta: yyyy-MM-dd HH:mm:ss, no hace falta mandar ningun comando de procesamiento de fecha, un simple >= y <= da practicamente el mismo resultado en temas de eficiencia.

Mira que me duele usar un MONTH() o YEAR() para distinguir, muchas veces las soluciones mas "pellejeras" como un varchar(2) para el mes o un varchar(4) para el año ofrecen mejores rendimiento que un date mal "formateado".

PacoSS
07/07/2011, 23:30
Hoy me he acordado de este hilo leyendo una cosa:

http://gallir.wordpress.com/2011/02/...ltas-al-mysql/

Kilburn
07/07/2011, 07:56
Cita Publicado inicialmente por jotremar
Normal:
id __ select_type __ table __ type __ possible_keys ___ key ______ key_len __ ref ________ rows ___ Extra
1 __ SIMPLE ______ r ______ ref ___ key1,key2,key3 __ key1 ____ 8 _______ const ______ 720834 _ Using where; Using temporary; Using filesort
1 __ SIMPLE ______ o _____ eq_ref _ PRIMARY _______ PRIMARY _ 3 ________ db.r.int_op _ 1 ______


key1 => r.int_em + r.int_op + r.timestamp + r.varchar_otro
key2 => r.timestamp + r.int_em
El problema está bastante bien explicado aquí. Mysql se hace la picha un lio y solo utiliza el primer campo de la llave (ref = const). En este link explican el problema, y parece que lo solucionan utilizando force index con ese mismo índice, para forzar un range scan. Yo probaría eso antes de ponerme a cambiar de motor de bbdds.

Edit: Ah, y yo utilizaría "between" para las fechas en vez de >= y <=...

luis_sanz
04/07/2011, 20:52
Cita Publicado inicialmente por jotremar
¿Siempre hablando de MyIsam?
De Innodb a MyIsam la velocidad se nota...
yo solo trabajo con innodb, myisam quiza sea mas rapido, pero prefiero seguridad ante todo, y bien programado tus consultas innodb es tan veloz como myisam, ADEMAS percona que fue lo que recomende probar esta especialmente optimizado para innoDB

jotremar
04/07/2011, 20:42
¿Siempre hablando de MyIsam?
De Innodb a MyIsam la velocidad se nota...

luis_sanz
04/07/2011, 15:14
Cita Publicado inicialmente por jotremar
Lo de que es más rápido, eso depende a quien se lo preguntes. Yo como no lo he usado nunca no puedo opinar, pero se de aplicaciones comerciales que se apoyan en postgresql y cada vez hay más aplicaciones con compatibilidad para ambos sistemas, por algo será.
mysql es mas rapido y no poco, ademas empresas GIGANTES con bases de datos de infarto lo usan, aunq eso si, con un mysql modificado, tienes uno sencillo de instalar y modificiado ya, como PERCONA muy similar al que usa google

postgres no es malo y por eso se usa, incluso se dice que es un servidor muy estable y duro, osea se comenta sobre todo eso, que es una mole! de server pero como rapido, mysql lo es y por mucho mas que cualquier otro prueba con PERCONA si no estas muy convencido de usarlo tal como sale del horno

jotremar
04/07/2011, 15:05
Lo de que es más rápido, eso depende a quien se lo preguntes. Yo como no lo he usado nunca no puedo opinar, pero se de aplicaciones comerciales que se apoyan en postgresql y cada vez hay más aplicaciones con compatibilidad para ambos sistemas, por algo será.

Tal vez (lo digo por decir), mysql sea muy rápido en consultas simples, pero muy lento en consultas complejas, porque gestione peor los índices, su forma de organizar los resultados...

Lo de que es más fácil de usar, eso es cierto, postgresql me recuerda a oracle en los formatos sql, el tener soporte para disparadores...

Ya digo que en cuanto encuentre una forma decente de traspasar el backup de uno al otro lo pruebo, pero por ahora solo me da crujidos lo que pruebo.

luis_sanz
04/07/2011, 13:56
Cita Publicado inicialmente por jotremar
¿pasamos ya al tema de comparar con postgresql? XD
¿pero realmente que comparacion quieres?
mysql es de lejos, mas rápido que postgresql en sus consultas, aunq esto puedes comprobarlo tu mismo.

Aparte de la rapidez, mysql es mas facil de usar, y tiene una comunidad mucho mas amplia para resolver dudas, quiza esto pueda ser interesante tambien.

mi voto entre que servidor SQL usar entre todos, es para mysql5.5

jotremar
02/07/2011, 11:35
¿pasamos ya al tema de comparar con postgresql? XD

jotremar
30/06/2011, 15:42
Normal:
id __ select_type __ table __ type __ possible_keys ___ key ______ key_len __ ref ________ rows ___ Extra
1 __ SIMPLE ______ r ______ ref ___ key1,key2,key3 __ key1 ____ 8 _______ const ______ 720834 _ Using where; Using temporary; Using filesort
1 __ SIMPLE ______ o _____ eq_ref _ PRIMARY _______ PRIMARY _ 3 ________ db.r.int_op _ 1 ______


key1 => r.int_em + r.int_op + r.timestamp + r.varchar_otro
key2 => r.timestamp + r.int_em

fuerzo el uso de key2, la consulta tarda medio segundo más.

MarcosBL
30/06/2011, 13:58
Pega también si puedes el EXPLAIN de la consulta, a ver qué está haciendo exactamente

jotremar
30/06/2011, 12:33
Puede que tabla_o no tenga coincidencia para tabla_r, pero debe salir siempre la fila de tabla_r, la prioridad la marcar con el right, o importa el lado en el que esté puesto (al menos en teoría y lo probé en su momento cambiandolo al otro sentido y tardaba lo mismo, demostrando que no importa el lado, cuando indicas prioridad para un lado).

a mi tampoco me gusta lo de varchar, pero en este caso es inevitable, porque suele filtrarse por ese campo y lo de tenerlo externo y referenciado no es viable en este caso, quizás en un futuro.

El orden de los campos es el correcto, en este caso, el hasta es una fecha futuro y no tiene resultados. El desde da menos filas que int_em. varchar_fp solo tiene a dia de hoy 2 valores posible, pero da más filas que int_em.
He cambiado el orden de int_em y varchar_fp, con resultado del mismo tiempo.

##########
Hablando de configuración:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
log-bin=mysql-bin
server-id=1
sync_binlog=1
log_slow_queries=/var/log/mysql-slow.log
long_query_time=3
# lo he visto a 64 en 4gb
thread_cache_size=32
query_cache_limit=20M
query_cache_size=32M
query_cache_type=2
join_buffer_size=32M
key_buffer_size=256M
max_heap_table_size=64M
max_tmp_tables=64
myisam_sort_buffer_size=64M
# el doble de table_cache
open_files_limit=4096
read_buffer_size=4M
read_rnd_buffer_size=10M
skip-bdb
sort_buffer_size=16M
# lo he visto a 8192 en 4gb
table_cache=2048
tmp_table_size=64M
wait_timeout=150
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=170M
# lo he visto a 128M en 4gb
max_allowed_packet=64M

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Hablamos de 16Gb de ram, pero con becerros como JBoss en el mismo sistema.

PacoSS
30/06/2011, 11:23
Supongo que la tabla "tabla_r" tiene un índice simple o único por el campo "id".

¿Y por qué RIGHT OUTER JOIN? ¿No se supone que la tabla primera es la principal, y se busca algún dato adicional en la secundaria? Si tienes garantía de que va a existir siempre un registro en la secundaria, es mejor INNER JOIN, y si no va a existir, LEFT OUTER JOIN.

El segundo índice, solo usará int_em para esa consulta, y lo veo un poco grande, aparte de que siempre es mala costumbra indexar por varchars

Y ya en cuanto a optimización, tienes que analizar que conjunto de resultados es mas pequeño en el predicado de JOIN:
- El conjunto desde/hasta fecha
- El conjunto int_em = valor
- El conjunto varchar_fp = valor.

El motor de la base de datos los va a procesar linealmente, asi que es conveniente poner el conjunto mas pequeño/restrictivo en primer lugar, y así sucesivamente.
Incluso la sentencia AND de fecha se procesará así. Si estimas que habitualmente el "hasta" fecha devuelve un conjunto menor que el "desde", ponlo en primer lugar.

A ver que sale de este post

jotremar
30/06/2011, 10:53
La consulta:

select r.int_iv as int1, r.varchar_fp as varchar0, r.decimal_co as decimal1, o.varchar_1 as varchar1, r.int_op as int2, count(r.id) as numero, r.decimal_de as decimal2, r.decimal_ca as decimal3, o.varchar_no as varchar2, r.varchar_es as varchar3, r.int_3 as int3

from (tabla_o as o RIGHT OUTER JOIN tabla_r as r ON o.id = r.int_2) where r.timestamp >= '2011-06-01 00:00:00' and r.timestamp <= '2011-06-30 23:59:59' and r.int_em = 12345 and r.varchar_fp = 'TEXTO'

group by r.decimal_ca, r.decimal_de, r.varchar_es, r.decimal_co, r.int_iv, r.int_op

order by r.varchar_es desc, o.varchar_no, r.decimal_ca, r.decimal_de;

###############
Tiempo si group by y sin order by: La consulta tardó 3.9320 seg
Tiempo consulta completa: Query_time: 5 Lock_time: 0 Rows_sent: 532 Rows_examined: 625024

####################
Índices:
- r.timestamp + r.int_em
- r.int_em + r.int_op + r.timestamp + r.varchar_otro

hay otros índices, pero no sirven para esta consulta.

################
Si cambio int_em, por otro que tiene bastante menos filas en resultado: La consulta tardó 0.0149 seg

Si con el mismo int_em, reduzco la fecha en 1/3, la consulta tarda 1/3 del total

jotremar
29/06/2011, 21:33
Lo de configuración de mysql lo había pensado ya, pero ya en su día le dí un buen repaso y además le pasé el famoso pl script que te informaba sobre la bbdd y decía que estaba ok.
Probaré si acaso a darle un retoque más a los temporales, pero ya van por encima de lo recomendable. Creo recordar que el tiempo que tarda en ejecutarse es proporcional a las filas coincidentes con la consulta, que no hay un escalón a partir del cual se dispara el tiempo. No lo comenté, pero si el resultado de la consulta es pequeño, tarda como 0.01 en responder, depende directamente del resultado, no de la consulta en si.

Mañana si quereis os pongo un ejemplo de la consulta en detalle, con sus indices.

PacoSS
29/06/2011, 17:57
Cita Publicado inicialmente por Power
.... tiene toda la pinta de problemas en la configuración de MySQL.
Mira de optimizar MySQL.
100 a 1 a que es la consulta en sí y la estructura de índices.

Hace años y años y años que oigo esta música.

Power
29/06/2011, 17:50
Hola,

A mí también me parece muy extraño que una consulta con dos tablas relacionadas, con sólo 3 millones de filas una y 500 filas la otra, te pueda tardar 4 segundos.

A mí, consultas similares me tardan milisegundos.

Si tienes las tablas perfectamente bien indexadas y la consulta está bien relacionada, tiene toda la pinta de problemas en la configuración de MySQL.
Mira de optimizar MySQL.

Saludos

jotremar
29/06/2011, 17:39
los índices están más que machacados, incluso forzando el uso de otros índices, por si se veia mejoría, pero no hay diferencia de tiempo.
Tenía un tercer join, que se lo quité para hacerlo por código (solo tenía 6 filas) y el tiempo es el mismo.
De cada consulta, el group puede procesar 350.000 filas de los 3 millones, dando como resultado unas 50.
Una de las condiciones es un filtrado por timestamp, lo cual retrasa el asunto. Filtrado por unas 5 condiciones, por supuesto para los dos tablas del join.

soporta unos 5 updates por segundo de media. 1 insert cada 2 segundos de media.

PacoSS
29/06/2011, 15:33
Y si es imposible indexar, porque se busca mucho por inclusión o por casi todos los campos de la base de datos, si tus datos son texto sphinx es la solución.

Si no añades registros con frecuencia, y tienes buena cpu, entonces sphinx es la solución total para todo.

Además, que te indexa ficheros de texto como el agua, no hace falta ni que sean bases de datos, es flipante.

MarcosBL
29/06/2011, 14:26
Por los millones de filas no debería haber ningún problema. Yo tengo una tabla MyISAM de 8 millones de filas sobre la que hago consultas con 2-3 joins, y va a la velocidad del rayo, mi standard es del estilo del de PacoSS, sólo que llevado al 0.001

Si crees que has agotado las posibilidades de índices y demás (que lo dudo, sinceramente), el siguiente paso natural es o bien denormalizar, o bien pasar parte de la lógica a una NoSQL, estilo redis si necesitas máximo rendimiento con una curva un poco más complicada, o MongoDB si quieres una curva cuasi nula con un rendimiento más que aceptable.

PacoSS
29/06/2011, 12:08
:goto -2

jotremar
29/06/2011, 11:51
hablamos de millones de filas y consultas complejas...

PacoSS
29/06/2011, 10:53
Mi experiencia es que una consulta normal (un count de 200 registros, un select de 200 filas, ...) que tarde mas de 0.01 segundo, es un fallo de programación como una catedral, o bién una base de datos mal indexada por donde no toca.

jotremar
29/06/2011, 09:27
Supongamos una consula con order by, group , left join, count y alguna cosilla más, sobre una tabla con 3 millones de filas y otra de 500 filas.

En mysql me pasa de los 4 segundos con mysam (aunque ya debería cambiar a innodb para mejorar la concurrencia), con postgresql estoy en proceso para hacer alguna comparación.

¿Cuales son vuestras experiencias en consultas de este tipo en estas base de datos?