MySQL 5 vs PostgreSQL 9 el verdadero test

[santa]

Estimados estoy por desarrollar un sistema el cual espero tenga un buen futuro y para eso necesito una buena base de datos.

Pero hace poco tuve que hacer unos trabajos con una base ya consolidada en PostgreSQL 8.1 y he tenido que ver muchos aspectos de PostgreSQL. Lo que puedo decir rápidamente es que es una excelentísima base de datos
la cual tiene muchísimas mas cosas que MySQL, posiblemente por que hace mas tiempo que esta siendo programada. Algunas de las cosas que he visto por ejemplo es el tema de SCHEMAS que podría decirse que es como una sub base de datos dentro de una principal, o también puedo decir que es como una contenedora de tablas
Yo toda la vida he usado MySQL pero en un nuevo trabajo donde me he estado usan PostgreSQL 8.1. con una base de datos muy grande.

Voy a tratar de ser lo mas imparcial posible por lo tanto inicialmente diría que no se pueden comparar de igual a igual estas dos bases de datos, ya que yo diría que son para distintos segmentos. A que me refiero con esto, el tema es que PostgreSQL es una base de datos mas rica en robustez y cuando digo robustez no me estoy refiriendo a que puede manejar mas datos o que en mysql voy a perder algo sino que PostgreSQL tiene aspectos que todavía mysql no lo contempla como por ejemplo los SCHEMAS, el CHECK de una columna para no meter cualquier dato, las secuencias que en mysql son autoincrements, name spaces y lo mas lindo de todo la programación PL en donde puedo programar en PL o python, java y otros también.

Por el otro lado MySQL en que podrá ser mejor... bueno hace mucho tiempo que se viene diciendo que mysql es mas rápido que postgresql el tema es, hoy por hoy lo sigue siendo? por que mysql era rápido con las tablas Myisam pero con innodb? que podemos decir?, por el otro lado postgresql cada vez dicen que es mas rápido, entonces ahora seran iguales?. Este es el punto a analizar, si bien mysql no le gana a postgresql en la complejidad y administración de datos por lo arriba mencionado, seria ver si en velocidad si gana.

Para saciar mi intriga rápidamente he hecho una pequeña prueba inicial con lo que tenia a mano. Este primer test es uno rápido para ver resultados de forma rápida pero prometo uno de gran escala donde aca es donde pido la colaboración de la gente de postgres y la de mysql para que cada uno opine que prueba hacer en conjunto. Lo testeado fue bajo una computadora de escritorio Athlon 2800 que corre a 2.0ghz con 1gb de ram, sistema operativo Ubuntu 9.10 y disco sata, la tabla:

Código MySQL:

+--------------------+----------+------+-----+---------+----------------+
| Field              | Type     | Null | Key | Default | Extra          |
+--------------------+----------+------+-----+---------+----------------+
| transporte_id      | int(10)  | NO   |     | NULL    |                |
| tipo_transporte_id | int(1)   | NO   |     | NULL    |                |
| id_chofer          | int(10)  | NO   |     | NULL    |                |
| fecha_hora         | datetime | YES  |     | NULL    |                |
| user_id            | int(4)   | YES  |     | NULL    |                |
| mensaje_cod        | int(10)  | YES  |     | NULL    |                |
| id                 | int(11)  | NO   | PRI | NULL    | auto_increment |
+--------------------+----------+------+-----+---------+----------------+
 

dicha tabla fue llenada con un proceso de php el cual cargaba los campos con cadenas aleatorias por medio de random con el fin de llenar con datos. Los primeros 10 registros se cargaron asi:

Código MySQL:

+-----------------+--------------------+---------------+---------------------+---------+---------------+----+
| transporte_id   | tipo_transporte_id | id_chofer     | fecha_hora          | user_id | mensaje_cod   | id |
+-----------------+--------------------+---------------+---------------------+---------+---------------+----+
|        99723312 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      71714855 |  1 |
|        44066646 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      41812736 |  2 |
|        95105573 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      70944188 |  3 |
|        39528330 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      61580575 |  4 |
|        55750701 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      83530388 |  5 |
|         4539456 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      97842664 |  6 |
|        44997693 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      28547121 |  7 |
|        87048772 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      18199467 |  8 |
|        84745003 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      74514718 |  9 |
|        72368643 |                  1 |             0 | 2010-11-09 12:56:15 |       2 |      10890136 | 10 |
+-----------------+--------------------+---------------+---------------------+---------+---------------+----+
 

De esta manera llegue a cargar

Código MySQL:

+-----------+
| count(*)  |
+-----------+
| 275079589 |
+-----------+
 

Si dos cientos setenta y cinco millones setenta y nueve mil quinientos ochenta y nueve datos, no se si sera mucho pero no algo que se vea avitualmente. con estos datos hice inicialmente un par de pruebas, ojo toda esta data esta en mysql 5.0 y del otro lado tengo a postgres 8.1 con 980.000 datos, con similares tablas y un servidor similar.

El primer query que tire fue un count(*) el cual la primera vez que lo tire en mysql fue de s0,08 menos de un segundo (275 millones de datos) y en postgres 7 segundos. Luego volví a tirar el mismo query para ver como cacheaba donde mysql me devolvió s 0,00 segundos y postgres jamas bajo de 4 segundos, ojo no se olviden de la cantidad de datos que es algo diferente por no decir abismal.

Viendo esto que es increíble en su diferencia es que me propuse montar el ultimo mysql y el ultimo postgres y armar las mismas tablas para que se le puedan cargar la misma cantidad de datos, mi idea es 500 millones o si me alcanza el disco 1.500 millones de registros.

Lo que se probara es lo siguiente

count(*)
select like '%algo%' de un campo no indexado
select like '%algo%' de un campo indexado pero no como campo único
select a un ide donde el id este indexado y sea único
select where id in() trayendo muchos registros
select con un join entre dos tablas

Todos estos se harán primero con mysql y luego con postgres, iniciando la maquina de 0 para que la memoria no perjudique a ninguno de los dos. Además cada una de las consultas se tiraran 3 veces para ver cuanto tarda inicialmente y luego dos mas para ver como vuelve a traer los datos cuando ya están cacheados.



Bueno espero que les guste mi propuesta y que me tiren ideas para agregar a la prueba, esperare unos días para ver si alguien tira algo bueno, mientras yo voy cargando los datos por que lleva tiempo, saludos y estamos en contacto.




Santa

[GUILLHERMOSOFT]

Hola, me gustaria saber cómo vas con estas pruebas, ya que en este momento me encuentro en un dilema similar ¿MySQL ó PostgreSQL? en mi nuevo sitio de trabajo, pues debo desarrollar una aplicación bastante compleja y escalable con PHP.

[huesos52]

No habia visto este post y está interesante.

esperemos la respuesta de santa.

saludos

[santa]

Mira la verdad que me colgué y no reporte los resultados, los numero exactos te los debo, pero te comento que las bases de datos responden en casi el mismo tiempo, mysql es aproximadamente un 25% mas rápida, pero en lo que si me asombro es por ejemplo a la hora de hacer un select count(*) de la tabla.

En este caso MySql la primera vez tarda 0,08 segundos y las próximas veces ya es 0,00, pero postgres tanto la primera vez como las restantes el tiempo es de 4 a 5 minutos, no se que hará uno y que hará el otro pero ahí si hay una diferencia abismal. Igual a nadie le puede molestar eso ya que cuantas veces en un sistema haces un count?, en la mayoría son muy pocas.

Yo al final me tire a mysql primero por que es un toque mas rápida, segundo por que hay mas interfaces para manejar los datos mas amigablemente y por ultimo por que viendo quien usa una base y quien la otra me encontré que twitter hasta hace poco usaba mysql, claro esta que ahora usa casandra que es otra cosa, pero al ver esto pensé por que twitter eligio a mysql y no a postgres en su db?, si la mayoría de veces dicen que postgres es para grandes volúmenes de datos, nada es lo que parece amigo.


me parece que te deje mas incógnitas que respuestas pero bue me quede con mysql, y de acá saltare a casandra siguiendo los pasos de twitter, empresa que no creo que tenga inclinación en alguna base si no le da algún beneficio de performance.


Saludos

Santa

[santa]

si realmente gustan podremos hacer en conjunto un nuevo test tomando los conocimientos de la gente de postgres y de la gente de mysql, yo presto el fierro, solo nos tenemos que poner de acuerdo y armar el test, si gustan vemos, saludos



Santa

[santa]

puf esto parece un chat, las preliminares dan que mysql en velocidad es mas rápida pero si quieren números reales armamos los escenarios, saludosssssssssss


Santa

[huesos52]

De la empresa estamos esperando un fierrito nuevo. y empezaré con postgresql 9.0.

volveré en estos dias para que juguemos un poco. jajajajaja

[santa]

yo por lo que hice, estoy en posición de desafiar a cualquier base postgres 9 con mi mysql 5 en velocidad de recolección de datos, y es mas en mi trabajo usamos postres 8.4, saludos


Santa

[GUILLHERMOSOFT]

Santa, me dejas "BOQUIABIERTO" y sin palabras ufffffffff!!!!!!!!!!!!!! ¿qué puedo decir?, siempre tuve la esperanza de que el ganador iba a ser PostgreSQL (ya que siempre leo maravillas de este SGBD y de hecho hay quienes dicen que es el Oracle libre), el dato que das en relación a Twitter también me era conocido (de hecho también tengo entendido que forosdelweb usa MySQL, pues es la bd de vBulletin el CMS sobre el cual está esta web) más no me había detenido a pensar en esto, así mismo me gustaría saber porque huesos52 sigue con la firme decisión y determinación de usar PostgreSQL teniendo en cuenta los resultados de la prueba, me gustaría conocer sus argumentos, ya que creo que los debe tener y me gustaría conocer, pues a decir verdad me llama más la atención PostgreSQL, más luego del "benchmark" de Santa, no sé que pensar, así mismo el hecho de ver el número de karma que tiene huesos52, me hace suponer que debe tener muy BUENOS motivos, para seguir usando PostgreSQL.

Saludos.

[huesos52]

GUILLHERMOSOFT las pruebas que hizo santa son de velocidad. Y al parecer son muy buenas.
Pero un sistema de bases de datos no se puede medir solo por este requisito. Hay muchos aspecctos que tiene postgresql de los que carece mysql. Incluso en el primer post de santa menciona algunos. En cuanto a velocidad, también me habia causado curiosidad cuando hice un count de una abla con mas de 5 millones de registros. Postgresql tuvo que reconstruir durante 8 o 10 segundos la consulta. después de estar en caché, esto se reduce enormemente.

A mysql le tengo un enorme respeto. Incluso lo recomiendo para personas que empiezan. de igual forma, mysq es un SGBD que puede ser administrado de forma experta y con la capacidad de competir con cualquier gestor.

No olviden el hilo... el proximo martes entro de vacaciones y espero poder hacer las pruebas necesarias en el nuevo servidor.
Lo que podemos hacer desde ya, es armar un laboratorio con tablas estandar, numero de registros a ingresar, capacidad de las maquinas donde se corren los procesos, procedimientos almacenados para luego comparar unicamente las consultas.

saludos a todos

[santa]

Si me permiten, yo creo que acá juega mucho el tema del fanatismo, el tema es quien usa las dos bases de datos todos los días?, yo en casa y mis proyectos uso mysql y en el trabajo 8 hora postgres.

Lo que veo yo es que postgres es mucho mas completa y lo digo por el tema que se puede programar con pls, podes usar PLSQL, Python, C y otros en cambio en mysql no hay esto. Claro también me pongo a pensar cual seria el escenario en el cual yo necesitaría de eso?, por que si quisiera hacer un alta de un cliente donde solo le pase el nombre y apellido y la DB haga las tablas de cuenta corriente o de lo que sea automáticamente directamente lo meto en una función o procedimiento almacenado. En este caso es comparable a que me compre una ferrari para andar por capital, y cuando la puedo poner a fondo, nunca, entonces para que me la compre?.

Ojo me gustaría también comentarios serios de la gente de postgres, yo trato de ser lo mas objetivo posible, pero también me da por preguntar y quien usa postgres?. En favor a esta pregunta hace dos años había leído que skype usa postgres y para el 2010 pensaba administrar un millón de millones o sea un BIllon de registros, bueno caramba eso si que es mucho, pero fue solo ese el que encontré llevando la bandera del elefante.

Yo ofrezco una maquina la cual no es muy muy grande pero para el test esta perfecta, es un amd athlon 2800 que corre a 2000 mghz con 1gb de ram, para que no nos quedemos con yo opino esto y el otro lo otro, hagamos una prueba en el mismo hard y comparemos velocidades.

Yo particularmente no encuentro ningún beneficio con postgres, pero ojo si alguien groso en postgres me demuestra con hechos que posgtres es mucho mejor, amigos tiro mysql y me caso, pero nunca encontré a nadie que lo haga.

Un saludo grande amigos y seguimos hablando me parece muy piola este post para sacar el ganados a la luz siempre contando las diferencias en las que no podemos probarlos como en el campo de la programación con plsql, phyton y otros.



Saludos


Santa

[huesos52]

santa... yo también soy objetivo. Pero decirr que uno es mejor que el otro es un poco atrevido.

En mi eperiencia he tenido que usar ambos motores. Para las necesidades de mi empresa, postgresql se acomodó mejor a los requerimientos.
En mi ejemplo particular, hay un proceso que requiere una precisión muy alta. Si vamos a esto, la unidad minima de tiempo en mysql es el segundo. En postgresql es en milisegundos y hasta mas. Osea... en cuanto a concurrencia y exactitud postgresql se lleva a mysql por los cachos.

La programación es indspensable... aunque mysql también permite hacer, procedimientos, funciones triggers y muchos otras caracteristicas que tiene postgresql. En cuanto a los triggers por ejemplo, postgresql lleva trabajando este tema hace mas de 10 años y es mucho mas fuerte que mysql. Por ejemplo, en mysql si quieres diferenciar que evento disparó el trigger, no lo puedes hacer. debeshacer un trigger por cada evento. En postgresql existen unas variables que almacenan este tipo de cosas.

Otra gran caracteriitica es que es indispensable para el proyecto en el que trabajo es el manejo de vectores y arreglos. Mysql no los soporta.

Otra gran caracteristica que tiene postgresql es poder crear tipos de datos. (CREATE TYPE). Con esto es posible personalizar tipos de datos y utilizarlos en cualquier parte de la base de datos sin tener problemas.

Postgresql puede tener tablas maddre y heredar atributos a tablas hijas. maneja muy bien el concepto de herencia.
En la parte de seguridad es un poco mejor que mysql, al manejar esquemas e impedir visualizar otras bases de datos entre si.

Ahora vamos con mysql.
Mysql tiene una función para todo. Es muchisimo mas completo que postgresql en este sentido. Funciones como group_concat, time_to_sec, sec_to_time, todas las funciones de fecha y hora, de manejo de strings es mucho mas simple y completo.

La velocidad con las pruebas que das y por comentarios en general es mejor. Ahora que mysql es de oracle, han mejorado la velocidad de INNODB en un 200%. Hay que revisar la ultima versión de mysql.
Otra cosa que me parece muy buena de mysql es la sentencia UPDATE INNER JOIN. postgresql no cuenta con esta.

En general hay muchas diferencias en las que dependiendo del proyecto uno o el otro se acomodan mejor.

Lo que si quiero probar, es que tan superior es la velocidad en mysql contra postgresql en ambientes similares y de muchos datos.

[huesos52]

Como lo prometido es deuda, otra vez estoy por aca.

propongo el siguiente laboratorio.

Se tiene la misma tabla para postgresql y mysql.

tabla en postgres

Código SQL:

CREATE TABLE t_prueba_velocidad
                       (campo1 serial PRIMARY KEY,
                        campo2 INTEGER,
                        campo3 DATE
                       );
 

tabla en mysql

Código MySQL:

CREATE TABLE t_prueba_velocidad
                       (campo1 integer primary key auto_increment,
                        campo2 integer,
                        campo3 date
                      );
 

Con esta función en postgresql se generan numeros aleatorios entre rangos establecidos.

Código SQL:

CREATE OR REPLACE FUNCTION random(NUMERIC, NUMERIC)
RETURNS NUMERIC AS
$$
   SELECT ($1 + ($2 - $1) * random())::NUMERIC;
$$ LANGUAGE 'sql' VOLATILE;
 

y con esta se llena la tabla con 500'000,000 de registros.

Código SQL:

CREATE OR REPLACE FUNCTION llenar_tabla_masiva()
  RETURNS text AS
$BODY$
DECLARE
contador INTEGER;
BEGIN
FOR contador IN 1..500000000 LOOP
 INSERT INTO t_prueba_velocidad (campo2,campo3) VALUES(random(100000,999999)::INTEGER,now());
END LOOP;
RETURN 'LISTO'::text;
 
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
 

En mysql con esta función se llenan los mismos 500'000,000 de registros.

Código MySQL:

DELIMITER $$
 
CREATE FUNCTION llenar_tabla_masiva() RETURNS VARCHAR(50)
BEGIN
 DECLARE contador INTEGER;     
 DECLARE mensaje VARCHAR(50);
 
 SET contador=0;
 SET mensaje='LISTO';
  WHILE contador < 500000000 DO    
     INSERT INTO t_prueba_velocidad (campo2,campo3) VALUES((100000 + ROUND(RAND() * (999999 - 100000))),now());  
     SET contador = contador + 1;
  END while;
 
RETURN mensaje;
END$$
DELIMITER ;
 

Ambos ambientes cuentan con 3 campos, con los mismos tipos de datos y un indice creado con la primary key.
El segundo campo se genera de forma aleatoria un numero entero entre 100000 y 999999.

en estos momentos tengo corriendo la función en postgresql llenando la tabla. El hardware de mi maquina es una workstation dell con 4 nucleos y 4 GB de memoria RAM.
Ya me llegó un server con 16 GB de ram y 6 nucleos pero mientras instalo sistemas operativos y configuro servicios puede pasar un buen rato. Cuando lo tenga listo, podemos comprobar que tanto afecta la memoria ram en el rendimiento.

Santa (O cualquier usuario interesado)... la idea sería que crearas esta tabla en tu servidor mysql y corrieras la función. Cuando la tabla esté llena, podemos empezar a comparar tiempos de respuesta de ambos motores a las consultas que citaste en tu primer post.

Si tienes una maquina mas potente la cual también cuenta con postgresql puedes correr ambos ambientes y darnos tus resultados, para que no saquemos excusas que la maquina influyó bastante los tiempos de respuesta. La versión de postgresql que tengo el proceso es la 8.4.

saludos y atento a la respuesta.

EDITO: En la misma maquina he instalado mysql y he corrido la función de llenado. De esta forma, todas las pruebas son bajo el mismo hardware. El proceso en postgresql se demoró 6.2 horas en llenar... esperemos el tiempo de llenado de mysql

[darwinsn]

Esperando ansioamente, los resultados de las pruebas.

[mashter]

Comentarios super interesantes.

Me agradaria que comentaran la carga del servidor, cpu, iowait, memoria libre antes de comenzar la prueba, si usan configuracion por default u optimizada, etc.

eso nos ayudaria mucho a entender los beneficios y desempeño de ambos motores

-

Actualmente ando desarrollando un ERP+TPV+otras funciones con php/mysql y resulta muy util todo tipo de comentario.

seguimos en contacto

[huesos52]

En mysql se demoró 4.6 horas.

Esto tiene sentido desde el punto de vista que para postgresql, una función, paquete, procedimiento o trigger la toma como un bloque entero. Es decir, se inicia una transacción cuando se ejecuta la función. Entonces... toda esta información no va directamente a disco sino a buffers de memoria ram. Cuando esta se llena, esta información pasa a disco pero a un espacio temporal. Cuando la transacción finaliza, toda esta información es copiada en su totalidad a disco y se genera un commit implicito.

Por otro lado, mysql copia directamente a disco lo que lo hace mucho mas veloz. Con la desventaja de tener inconsistencia de los datos ante un fallo inesperado.

Una de las pruebas que hice, fue tener un bloque en ambos motores que insertara 20 registros. Al registro 18 generaba un error. En mysql revisaba la tabla y encontraba 17 registros insertados. Por el contrario en postgresql al encvontrar una excepción o un error, inmediatamente hace rollback de la operación dejando las cosas tal cual se encuentran.

No quiero que piensen que estoy defendiendo a postgresql (Aunque me gusta mas ) ya que esto que comento mysql fácilmente lo puede tratar con transacciones que también las trabaja bastante bien, solo quería dar una explicación del por que se da la diferencia de tiempos de inserción de registros.

Algo que no sabía, es que postgresql viene en su configuración por defecto para trabajar en un PC con 1GB de ram, un procesador basico y poco espacio en disco. Las configruaciones iniciales de mysql no las conozco. Unas pruebas que prometo hacer cuando tenga listo el servidor es afinar al maximo postgresql y hacer pruebas comparativas con la configuración basica por defecto.

Sería bueno que alguno de ustedes afinara bien un servidor mysql y hacer una competencia de motores

saludos a todos

[papitrillos]

Con respecto a lo que dice huesos... yo manejo todas es clase de motores y si el perfomance de postgresql tiene que ver mucho con la capacidad del hardware y software... y si configuramos postgresql al maximo... para tipos de consultas o de actualizacion son dos paradigmas muy distintos en los SGDB porque los perfomance se realizan para esos casos, funcionan de acuerdo a lo que necesitemos...

Entonces les aclaro algo postgres sql no hace paginado mientras que mysql si eso influye ya que el servidor de mysql no trae todo los dato a memoria mientras postgresq si pero eso va mucho amigos en el perfomance de cada motor actualment manejo SQLSERVER pero quiero probar muchas cosas en postgresql....


Entonces una cosa mas le digo pruebe Navicat como gestor de datos tanto para mysql como para postgres y verán los rendimientos de consultas que es lo mas importante en motores aunque no digo que la actualización no lo sea. pero los rendimientos se miden es en consultar no en inserción de datos...

buenos se cuidan y DIOS los bendiga...xD

[ARICARRARO]

Excelente post. Ya sabia que MySQL era más rápido que PostgreSQL, pero he visto que hay quines prefieren usar PostgreSQL según por que soporta más registros