Benchmark MySQL vs PostgreSQL vs SQLite vs MSAccess (vs ruby)

Por razones que no vienen al caso, me he encontrado hoy con un increíble WTF? usando MySQL (la lista no es corta, pero este era sorprendente, digno de Access o peor). He decidido hacer una comparativa con uno de sus competidores directos, PostgreSQL. Además probaré a intentar resolver el problema usando código en vez de dejar todo el “trabajo” a la base de datos, en concreto lo haré todo en ruby, no por eficiencia sino por comodidad (que sino me canso).

[Actualización 6 abril 2008 @ 19h] Ya puestos, he añadido también SQLite y Microsoft Access.

Presentemos el problema, tenemos dos tablas, A y B, cada una de ellas tiene una clave primaria compuesta por dos campos. Queremos averiguar las tuplas de la primera tabla que tienen como valor en uno de sus campos, valores que no se encuentran en ninguna tupla de la segunda tabla. Es decir una resta simplemente. Poniéndolo decentemente sería tal que:

  • Tablas:
    • A(id, otro_id)
    • B(id, otro_id)
  • Objetivo: tuplas de A para las cuales no existe ningún elemento en B cuyo valor del campo otro_id sea igual al campo otro_id de A
    • Formalmente: x ɛ A. ∀y ɛ B y.otro_id != x.otro_id
    • SQL: Lo más intuitivo y simple sería
      SELECT * FROM A WHERE A.otro_id NOT IN (SELECT B.otro_id FROM B)

      o usando LEFT JOINs también es simple expresarlo

      SELECT * FROM A LEFT JOIN B USING (otro_id) WHERE B.otro_id IS NULL

Creo que es algo bastante evidente y simple de entender. Aplicado al MundoReal® puede surgir bastantes veces, no es que estemos antes un tipo de consulta retorcida ni nada por el estilo. Habría que resaltar que estamos usando parte de la clave primaria, en ambas tablas involucradas, por lo que en principio la intuición y nuestros conocimientos de bases de datos relaciones nos sugieren que esto va a ir más rápido que el correcaminos.

¿Cómo resolverías esta consulta?, No me hagas pensar, veamos qué nos dice Postgre:

foo=# EXPLAIN SELECT * FROM A WHERE A.otro_id NOT IN (SELECT B.otro_id FROM B);
                          QUERY PLAN                           
 Seq Scan ON a  (cost=189.91..379.84 ROWS=4877 width=24)
   FILTER: (NOT (hashed subplan))
   SubPlan
     ->  Seq Scan ON b  (cost=0.00..165.53 ROWS=9753 width=12)

Parece buen plan, primero hará un hash de los valores que se le indican en B, le da un coste de menos de 2 décimas (son milésimas los valores) y calcula que devolverá 9753 columnas (que son todas las que hay), luego dice que filtrará todos los de A que no se encuentren en ese hash. Es lo que le hemos pedido, correcto. A este segundo paso le da una estimación de menos de 2 décimas también, y cree que saldrán 4877 resutlados (esto son todo estimaciones, postgre ahora mismo no ha ejecutado nada, solo nos cuenta su vida).

MySQL es un poco más tímido y no da tanto detalle, pero también podemos pedir que nos explique qué va a hacer:

mysql> EXPLAIN SELECT * FROM A WHERE A.otro_id NOT IN (SELECT B.otro_id FROM B) \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: A
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 178
          ref: NULL
         rows: 9754
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: B
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 178
          ref: NULL
         rows: 9753
        Extra: Using where; Using index

Básicamente dice que si, que va a usar clave primaria para ambas partes y punto. ¡Qué sabiduría!.

Ok, pues vamos a comparar. Podía simplemente ejecutar la consulta y santas pascuas, pero como me apetece comparar también cual es el coste a realizar trabajo de la base de datos vía código, he hecho un pequeño script en ruby, usando ActiveRecord para manejar las conexiones a la base de datos de forma simple. El código entero lo pongo targzeado al final, ahora pongo aquí la parte que interesa:

Benchmark.bmbm{ |b|
   # uno! El brikindans!, digo consulta en MySQL "normal" con el NOT IN
   b.report("(1) Consulta en MySQL") do
      execute_with MySQL::A, :normal
   end
   # La misma que en (1) pero con PostgreSQL
   b.report("(2) Consulta en PostgreSQL") do
      execute_with PostgreSQL::A, :normal
   end
   # La misma que en (1) pero con SQLite3
   b.report("(3) Consulta en SQLite3") do
      execute_with Sqlite3::A, :normal
   end
   # Consulta con MySQL pero esta vez usando LEFT JOIN
   b.report("(4) Consulta en MySQL (LEFT JOIN)") do
      execute_with MySQL::A, :left_join
   end
   # La misma que en (4) pero con PostgreSQL
   b.report("(5) Consulta en PostgreSQL (LEFT JOIN)") do
      execute_with PostgreSQL::A, :left_join
   end
   # La misma que en (4) pero con SQLite3
   b.report("(6) Consulta en SQLite3 (LEFT JOIN)") do
      execute_with Sqlite3::A, :left_join
   end
   # Hacemos el proceso en código de forma penosa, con un coste Ɵ(n^2)
   b.report("(7) Consulta en código (noob mode)") do
      as = MySQL::A.find :all
      bs = MySQL::B.find(:all).map{|i| i.otro_id }
      as.select{|i| !bs.include?(i.otro_id) }.size
   end
   # Hacemos el proceso en código pero de forma decente, restando conjuntos
   b.report("(8) Consulta en código (MySQL)") do
      (MySQL::A.find(:all).map{|i| i.otro_id } - 
       MySQL::B.find(:all).map{|i| i.otro_id }).size
   end
   # La misma que en (8) pero con PostgreSQL
   b.report("(9) Consulta en código (PostgreSQL)") do
      (PostgreSQL::A.find(:all).map{|i| i.otro_id } - 
       PostgreSQL::B.find(:all).map{|i| i.otro_id }).size
   end
   # La misma que en (8) pero con SQLite3
   b.report("(10) Consulta en código (SQLite3)") do
      (Sqlite3::A.find(:all).map{|i| i.otro_id } - 
       Sqlite3::B.find(:all).map{|i| i.otro_id }).size
   end
}

He aquí los resultados en mi pc, con MySQL 5.0.45, PostgreSQL 8.2.7 y SQLite 3.4.2 (sin tunear ninguna, tal y cual vienen en Ubuntu 7.10) y unos 10k registros en cada tabla.

$ ./mysqlVsPostgresqlVsSQLite3.rb 
Rehearsal --------------------------------------------------------------------------
(1) Consulta en MySQL                    0.020000   0.000000   0.020000 ( 54.567416)
(2) Consulta en PostgreSQL               0.000000   0.000000   0.000000 (  0.100255)
(3) Consulta en SQLite3                  0.180000   0.030000   0.210000 (  0.295534)
(4) Consulta en MySQL (LEFT JOIN)        0.000000   0.000000   0.000000 ( 54.656340)
(5) Consulta en PostgreSQL (LEFT JOIN)   0.000000   0.000000   0.000000 (  0.080631)
(6) Consulta en SQLite3 (LEFT JOIN)     47.980000   0.360000  48.340000 ( 54.249483)
(7) Consulta en código (noob mode)      21.660000   0.200000  21.860000 ( 30.060858)
(8) Consulta en código (MySQL)           0.520000   0.040000   0.560000 (  0.696692)
(9) Consulta en código (PostgreSQL)      0.960000   0.060000   1.020000 (  1.203388)
(10) Consulta en código (SQLite3)        3.410000   0.190000   3.600000 (  4.141494)
---------------------------------------------------------------- total: 75.610000sec
 
                                             user     system      total        real
(1) Consulta en MySQL                    0.160000   0.010000   0.170000 (  0.200753)
(2) Consulta en PostgreSQL               0.000000   0.000000   0.000000 (  0.027388)
(3) Consulta en SQLite3                  0.140000   0.010000   0.150000 (  0.148729)
(4) Consulta en MySQL (LEFT JOIN)        0.010000   0.000000   0.010000 (  0.000826)
(5) Consulta en PostgreSQL (LEFT JOIN)   0.000000   0.000000   0.000000 (  0.025892)
(6) Consulta en SQLite3 (LEFT JOIN)     51.810000   0.370000  52.180000 ( 69.148641)
(7) Consulta en código (noob mode)      21.340000   0.200000  21.540000 ( 24.971090)
(8) Consulta en código (MySQL)           0.420000   0.010000   0.430000 (  0.503471)
(9) Consulta en código (PostgreSQL)      0.970000   0.070000   1.040000 (  2.188352)
(10) Consulta en código (SQLite3)        3.170000   0.120000   3.290000 (  3.581979)

Adicionalmente he hecho también la prueba usando Microsoft Access 2003, gracias a este connector (que lo he usado para cargar todos los datos). No lo he incluido en las pruebas porque solo funciona bajo Windows, así que he ejecutado las consulta a mano. La primera de ellas le cuesta unos 200 seg aproximadamente mientras que el left join lo ejecuta casi al instante, no más de 0.25 segundos.

Mirando entonces todos los resultados, podemos observar como a MySQL le cuesta dos veces más que la ineficiente consulta con código y 700 veces más que a Postgre. Curioso, oye. En la segunda pasada MySQL ha cacheado el resultado (pero si haces otras consultas y volvemos a hacer ésta, tardaría de nuevo su tiempo “normal”) y es bastante más rápido.

En resumen se podría decir que es totalmente inaceptable (al menos para esta simple consulta de dos tablas) usar:

  • MySQL (en cualquier caso)
  • SQLite usando LEFT JOINs (pero funciona perfectamente con subconsulta)
  • MS Access usando subconsulta (pero funciona perfectamente con LEFT JOINs)
  • Hacer el trabajo en código de forma estúpida (es lo que tiene)

Por lo tanto, según mi propia interpretación, diría que nos quedan solo tres opciones (si consideramos únicamente estas 4 opciones como base de datos a usar) :

  • Usar PostgreSQL
  • Usar la base de datos ‘X’ para un X distinto a MySQL y probar nuestras consultas para ver si le gustan o no al SGBD
  • Usar la base de datos ‘X’, para un X cualquiera, emplear consultas triviales y filtrar en código de forma decente. (opción poco viable para entornos reales cuando tengamos millones de registros y no solo 10 mil)

Y hasta aquí todo, que cada uno saque sus propias conclusiones. No voy a decir que MySQL es una puta mierda, o que PostgreSQL suele ser más lento que el caballo del malo, a decir verdad lo que si que diré es que los benchmark siempre son muy limitados y comparan cosas en un ámbito restringido y controlado, por lo que no sirven para nada, así que no sé para qué narices he escrito todo esto, a decir verdad no sé ni para que estás leyéndolo, pero allá tú.

El código, los dump para postgre y mysql listos para ser cargados en sus respectivas bases de datos, la base de datos en access y la base de datos de sqlite3 los dejó en este fichero por si quieres jugar un rato. Se requiere (aparte de las base de datos obviamente) active record y composite primary keys (ambos instalables como gemas, gem install composite_primary_keys, por ejemplo).

13 Thoughts on “Benchmark MySQL vs PostgreSQL vs SQLite vs MSAccess (vs ruby)

  1. juas! me haces leer todo esto para terminar con tan magistral conclusión! xD xD ya en serio para mi postgre, en estos momentos es mejor, pero hay que ver como evoluciona mySQL de la mano de la Sun ahora no?

    Saludos

  2. El problema aqui es que aunque EXPLAIN dice que usa el indice, no es del todo cierto. MySQL solo podria usar el indice para busquedas sobre id, o sobre id y otro_id, ya que solo puede usar el indice empezando por la izquierda.

    Lo que aparece en explain es que se leera el indice en lugar de la tabla ya que todos los datos que pedimos estan en el indice. Supongo que si creas un indice sobre otro_id, o la clave primaria es (otro_id,id) mejorara mucho la cosa

    Otro tema, si el explain lo terminas con \G en lugar de ; sale con mejor formato 🙂

  3. Cómo sabe ese teacher!

    En el explain, el rows te dice cuántas filas va a mirar. En este caso serán 9754 * 9753. Mas o menos te da una idea de lo optimizada que está la consulta.

    /me ahora va de pro de MySQL, pero es todo fachada.

  4. Un SGBD debería optimizar la consulta él solo. Mas que nada, por definición de los lenguajes de cuarta generación, como lo es SQL, así debería de ser. Yo digo qué quiero, y no cómo. El interprete de dicho lenguaje (el SGBD) es quién debería preocuparse por hacerlo de la forma correcta.

    Obviamente esto no es así en la realidad, no de forma literal, pero los mejores SGBD son aquellos que, entre otras cosas, minimizan la diferencia entre la teoría y la práctica.

    En este caso concreto, me parece una barbaridad que algo que se considera un SGBD lo haga tan estrepitosamente mal. Cuando me aburra voy a hacer la prueba con MSAccess y sqllite, y me extrañaría que lo hiciesen tan estrepitosamente mal como MySQL. (¡Joder!, si se hace más rápido haciéndolo desde un lenguaje de script, relativamente malo en eficiencia, de una forma que no lo haría ni un estudiante sin idea alguna de programación, hago esto en un lenguaje eficiente y sería incluso mucho mayor la diferencia, totalmente inaceptable)

    @Golo, ¿qué sentido tiene mirar el número de rows en un explain para saber si está o no optimizada una consulta? Sin pensar mucho, pero si tienes consultas donde el único condicional esté condicionado por los valores de subconsultas, vas a tener ahí tantas rows como existan en la tabla, si o si. Pero para resolver un problema donde necesitas analizar n*m registros, hay formas y formas de resolverlo. Podemos usar algoritmos O(n^2) o podemos hacerlo de mejores formas, por eso he puesto los dos casos en código. Aparte que 10k registros es una puta mierda, eso es una broma para un SGBD “serio”, ni me quiero imaginar esto en un entorno real.

  5. Me has dejado a cuadros :O

    PS: ¿Cómo resaltas los colorines del código?

  6. @DraXus, este plugin, va bastante bien y tienes para un montón de lenguajes.

  7. Pues, qué voy a hacer… el canelo… 2 cosas que me sorprenden:

    1) MySQL siempre ha tenido el sanbenito de la velocidad, así que se me hace raro que sea 700 veces más lento que Postgre…

    2) Que metas Access en una comparativa de bases de datos… joder, tío, ¡¿Access?!

  8. @Lek, lo he metido básicamente porque el comportamiento de MySQL me lo ha recordado. Es tan triste que por eso he metido MS Access y sqlite que, en teoría, están varios y numerosos escalones por debajo de MySQL y Postgre.

    Si no te crees los resultados (yo no lo me los creía hasta que he probado en varios ordenadores y varias instalaciones de MySQL) baja el targz y pruebalo. Si no tienes instalado ruby, simplemente pilla el dump de mysql, lo metes y ejecutas la consulta por la consola, de los 30seg no te bajará sea el pc que sea.

  9. Eh, que no es que no me lo crea, es que me sorprende. Es como si te digo que me hago un programa en C y su equivalente en Java es 700 veces más rápido…….. pues sorprende 😀

  10. Hay que decir que si se crean índices (en MySQL) sobre los campos “otro_id”, la consulta ya se resuelve igual que en PostgreSQL (al instante vamos).

    Pero no deja de sorprender que aún teniendo ya el índice de la clave primaria compuesta, haga falta (en mysql solo, postgre lo hace bien) crear explícitamente de nuevo otra clave. Lo cual puede traducirse a la regla (en MySQL) “crea una clave por cada columna de una clave primaria que no esté situada en la primera posición de ésta”.

  11. Marian on 13 May, 2008 at 2:20 said:

    (Básicamente dice que si, que va a usar clave primaria para ambas partes y punto. ¡Qué sabiduría!. )
    Sobre el parrafo arriba mencionado, se esta mal interpretando lo que manda mysql claramente esta diciendo que no va usar la llave primaria, ni indices
    en (possible_keys: NULL ) y esto es debido por el acomodo que se le esta dando a la consulta. Cuando va ser uso de llaves o indices aparece de la siguiente forma: possible_keys:
    PRIMARY,Fecha_Asignada
    PRIMARY,Numero_Operador
    PRIMARY

  12. @Marian, al principio también pensé eso, pero según la documentación de MySQL (copy-paste):

    key

    The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

    It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index

  13. Daniel G. on 23 March, 2011 at 0:06 said:

    Escribo por si alguien pasa por aca para que no quede tan confundido.

    Se compararon peras con manzanas

    Mysql tiene varios motores y ¿cual viene por defecto en linux para esta version?.

    Innodb, MyIsam, etc. El segundo es extremadamente rapido para hacer select y sólo eso.
    InnoDb aguanta Pk Fk y otras cosas de bases relacionales pero es mas lento.

    Ni se te ocurra usar mysql sin indices.

    ¿Y que tal anda la configuracion por defecto que viene en linux para postgre y mysql ?

    Si usas los key buffer en mysql con más memoria de la que tiene la instalación por defecto el rendimiento mejora mucho.

    Si se afina la configuración de memoria para postgre su rendimiento tambien mejora mucho.

    Si alguien tiene la idea de usar access (no es base de datos) tenga en cuenta que hasta algunas versiones atras tenia limite de tamaño y este no es superior a 1,7 gigas(me refiero al tamaño del archivo).
    Access es muy util para vincular distintos origenes de datos y explotarlos con excel, esto es de gran ayuda para los no expertos pero no para generar un modelo de datos ni menos usarlo como base.

    Los Benchmark indican claramente que Mysql es muy veloz pero en consultas simples (Select) y a Postgre lo dejan muy bien parado cuando lo sobrecargan y aumentan el hardware.

    Postgre => Escalable, buen rendimiento si se sabe configurar.

    MySql=> Mientras más datos tiene más baja su rendimiento. Con pocos datos y en consultas de selección nadie le gana en entornos multiusuarios.Si se sabe configurar su rendimiento puede mejorar para subconsultas, join y esas cosas, sin indices es deficiente.

    El asunto es saber elegir y luego configurar.

Post Navigation