performance - manager - sqlite vs mysql

¿Cómo realiza PostgreSQL las escrituras mucho más rápido que SQLite?

Question

Hice una prueba de rendimiento de actualización de entero simple. SQLite hizo solo 15 actualizaciones por segundo, mientras que PostgreSQL hizo 1500 actualizaciones por segundo.

El número con el caso SQLite parece ser normal .

Las FAQ en el sitio SQLite se explican como si fuera una limitación fundamental de un disco rotativo.

En realidad, SQLite hará fácilmente 50,000 o más declaraciones INSERT por segundo en una computadora de escritorio promedio. Pero solo hará unas pocas docenas de transacciones por segundo. La velocidad de transacción está limitada por la velocidad de rotación de la unidad de disco. Una transacción normalmente requiere dos rotaciones completas del disco, que en una unidad de disco de 7200 RPM lo limita a aproximadamente 60 transacciones por segundo. La velocidad de transacción está limitada por la velocidad de la unidad de disco porque (de forma predeterminada) SQLite realmente espera hasta que los datos se almacenan de forma segura en la superficie del disco antes de que se complete la transacción. De esa manera, si de repente pierde energía o si su sistema operativo falla, sus datos aún están seguros. Para más detalles, lea acerca de la confirmación atómica en SQLite ..

De forma predeterminada, cada instrucción INSERT es su propia transacción. Pero si rodea varias instrucciones INSERT con BEGIN ... COMMIT, entonces todas las inserciones se agrupan en una sola transacción. El tiempo necesario para confirmar la transacción se amortiza en todas las declaraciones de inserción adjuntas y, por lo tanto, el tiempo por instrucción de inserción se reduce considerablemente.

Otra opción es ejecutar PRAGMA synchronous = OFF. Este comando hará que SQLite no espere a que los datos alcancen la superficie del disco, lo que hará que las operaciones de escritura parezcan mucho más rápidas. Pero si pierde el poder en medio de una transacción, su archivo de base de datos podría dañarse.

¿Es esta descripción verdadera? Entonces, ¿cómo puede PostgreSQL realizar mucho más rápido que SQLite? (Configuré las opciones fsync y synchronous_commit en OngreSQL)

ACTUALIZAR:

Aquí está el código de prueba completo escrito en Clojure:

(defproject foo "0.1.0-SNAPSHOT"
:repositories {"sonatype-oss-public" "https://oss.sonatype.org/content/groups/public/"}
:dependencies [[org.clojure/clojure "1.5.1"]
[org.clojure/java.jdbc "0.3.0-SNAPSHOT"]
[com.mchange/c3p0 "0.9.2.1"]
[org.xerial/sqlite-jdbc "3.7.2"]
[postgresql "9.1-901.jdbc4"]])

(ns foo.core
(:require [clojure.java.jdbc :as jdbc]
[clojure.java.jdbc.ddl :as ddl])
(:import [com.mchange.v2.c3p0 ComboPooledDataSource]))
(def sqlite
(let [spec {:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "test.db"}]
{:datasource (doto (ComboPooledDataSource.)
(.setDriverClass (:classname spec))
(.setJdbcUrl (str "jdbc:" (:subprotocol spec) ":" (:subname spec)))
(.setMaxIdleTimeExcessConnections (* 30 60))
(.setMaxIdleTime (* 3 60 60)))}))
(def postgres
(let [spec {:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:subname "//localhost:5432/testdb"
:user "postgres"
:password "uiop"}]
{:datasource (doto (ComboPooledDataSource.)
(.setDriverClass (:classname spec))
(.setJdbcUrl (str "jdbc:" (:subprotocol spec) ":" (:subname spec)))
(.setUser (:user spec))
(.setPassword (:password spec))
(.setMaxIdleTimeExcessConnections (* 30 60))
(.setMaxIdleTime (* 3 60 60)))}))
(doseq [x [sqlite postgres]]
(jdbc/db-do-commands x
(ddl/create-table :foo [:id :int "PRIMARY KEY"] [:bar :int])))
(doseq [x [sqlite postgres]]
(jdbc/insert! x :foo {:id 1 :bar 1}))
(defmacro bench
[expr n]
`(dotimes [_# 3]
(let [start# (. System (nanoTime))]
(dotimes [_# ~n]
~expr)
(let [end# (. System (nanoTime))
elapsed# (/ (double (- end# start#)) 1000000.0)
operation-per-sec# (long (/ (double ~n) (/ (double (- end# start#)) 1000000000)))]
(prn (str "Elapsed time: " elapsed# " ms (" (format "%,d" operation-per-sec#) " ops)"))))))
(bench (jdbc/query sqlite ["select * from foo"]) 20000)
(bench (jdbc/execute! sqlite ["update foo set bar=bar+1 where id=?" 1]) 100)
(bench (jdbc/query postgres ["select * from foo"]) 20000)
(bench (jdbc/execute! postgres ["update foo set bar=bar+1 where id=?" 1]) 5000)

Y la salida es:

; Running "select * from foo" 20000 times in SQLite
"Elapsed time: 1802.426963 ms (11,096 ops)"
"Elapsed time: 1731.118831 ms (11,553 ops)"
"Elapsed time: 1749.842658 ms (11,429 ops)"
; Running "update foo set bar=bar+1 where id=1" 100 times in SQLite
"Elapsed time: 6362.829057 ms (15 ops)"
"Elapsed time: 6405.25075 ms (15 ops)"
"Elapsed time: 6352.943553 ms (15 ops)"
; Running "select * from foo" 20000 times in PostgreSQL
"Elapsed time: 2898.636079 ms (6,899 ops)"
"Elapsed time: 2824.77372 ms (7,080 ops)"
"Elapsed time: 2837.622659 ms (7,048 ops)"
; Running "update foo set bar=bar+1 where id=1" 5000 times in PostgreSQL
"Elapsed time: 3213.120219 ms (1,556 ops)"
"Elapsed time: 3564.249492 ms (1,402 ops)"
"Elapsed time: 3280.128708 ms (1,524 ops)"

pg_fsync_test resultado:

C:/temp>"C:/Program Files/PostgreSQL/9.3/bin/pg_test_fsync"
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux''s default)
open_datasync 81199.920 ops/sec 12 usecs/op
fdatasync n/a
fsync 45.337 ops/sec 22057 usecs/op
fsync_writethrough 46.470 ops/sec 21519 usecs/op
open_sync n/a
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux''s default)
open_datasync 41093.981 ops/sec 24 usecs/op
fdatasync n/a
fsync 38.569 ops/sec 25927 usecs/op
fsync_writethrough 36.970 ops/sec 27049 usecs/op
open_sync n/a
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
1 * 16kB open_sync write n/a
2 * 8kB open_sync writes n/a
4 * 4kB open_sync writes n/a
8 * 2kB open_sync writes n/a
16 * 1kB open_sync writes n/a
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 45.564 ops/sec 21947 usecs/op
write, close, fsync 33.373 ops/sec 29964 usecs/op
Non-Sync''ed 8kB writes:
write 889.800 ops/sec 1124 usecs/op

Answer 1

En realidad, cualquier escritura en un disco giratorio es del orden de magnitud de 10 ms (el número típico es 8 ms).

Esto significa un poco más de 100 escrituras por segundo, SI está escribiendo la misma posición en el disco, lo cual es un caso muy extraño para una base de datos. Consulte "No sabe acerca de los discos" en el ACM, normalmente un disco puede programar 10 lecturas o escrituras en una sola rotación.

http://queue.acm.org/detail.cfm?id=864058

Por lo tanto, una base de datos puede realizar 1,000 escrituras por segundo e incluso más. He visto aplicaciones que realizan 1,500 transacciones por segundo hace 10 años en computadoras de escritorio.

Answer 2

La respuesta de Denis tiene todos los enlaces que necesitas. Iré por una respuesta menos detallada pero posiblemente más comprensible.

Sqlite no utiliza ningún administrador de transacciones sofisticado, no hay lógica de multitarea avanzada oculta en él. Ejecuta lo que usted le dice que ejecute, exactamente en ese orden. En otras palabras: hace exactamente lo que usted le dice que haga. Si intentara usar la misma base de datos de dos procesos, tendría problemas.

PostgreSQL, por otro lado, es una base de datos muy compleja: soporta eficientemente múltiples lecturas y escrituras concurrentes. Piense en ello como un sistema asíncrono: usted solo programa el trabajo que debe realizarse, en realidad no lo controla en sus detalles; Postgres lo hace por usted.

¿Qué hacer con tu eficiencia? Únase a varios, docenas, cientos de actualizaciones / inserciones en una transacción. Para una tabla simple, obtendrás un muy buen rendimiento.

Answer 3

Se desglosa en cómo implementan el aislamiento de instantáneas.

SQLite utiliza el bloqueo de archivos como un medio para aislar transacciones, permitiendo que las escrituras se peguen solo una vez que se realizan todas las lecturas.

Postgres, en contraste, utiliza un enfoque más sofisticado llamado control de versiones de multiconcurrencia (mvcc), que permite que se realicen múltiples escrituras en paralelo con múltiples lecturas.

http://www.sqliteconcepts.org/SI_index.html

http://www.postgresql.org/docs/current/static/mvcc-intro.html

http://wiki.postgresql.org/wiki/MVCC

Answer 4

Suponiendo que está utilizando un disco duro normal (es decir, sin ssd), puede esperar un máximo de 50-100 escrituras por segundo. Parece que 15 escrituras por segundo es ligeramente baja, pero no imposible.

Entonces, si Postgres está haciendo 1500 actualizaciones por segundo, se escriben en algún búfer / caché o se colapsan en una sola actualización. Sin saber más acerca de la prueba real, es difícil decir cuál es la razón real, pero si tuviera que abrir una transacción, actualice una sola fila 1500 veces y confirme que Postgres debería ser lo suficientemente inteligente como para ejecutar solo una "real" escribir en el disco.

Answer 5

Tienes razón al ser sospechoso. PostgreSQL con la configuración que indique no debería poder realizar nada cerca de 1500 actualizaciones en transacciones secuenciales separadas por segundo a medios rotativos.

Probablemente hay algo en tu pila de IO que miente o tiene errores acerca de cómo implementa la sincronización. Lo que significa que sus datos corren el riesgo de sufrir daños graves después de un corte de energía inesperado o una falla del sistema operativo.

Mirando el resultado de pg_test_fsync, este es el caso. open_datasync, que es el valor predeterminado en Windows, parece ser demasiado rápido y por lo tanto debe ser inseguro. Veo lo mismo cuando ejecuto pg_test_fsync en mi máquina Windows7.