performance - databases - sqlite3 bash commands
¿Se puede hacer esta consulta SQLite mucho más rápido? (1)
Necesita un índice agrupado, o si está utilizando una versión de SQLite que no es compatible con uno, un índice de cobertura.
Sqlite 3.8.2 y superior
Utilice esto en SQLite 3.8.2 y superior:
create table recording (
camera_id integer references camera (id) not null,
sample_file_bytes integer not null check (sample_file_bytes > 0),
-- The starting time of the recording, in 90 kHz units since
-- 1970-01-01 00:00:00 UTC.
start_time_90k integer not null check (start_time_90k >= 0),
-- The duration of the recording, in 90 kHz units.
duration_90k integer not null
check (duration_90k >= 0 and duration_90k < 5*60*90000),
video_samples integer not null check (video_samples > 0),
video_sync_samples integer not null check (video_samples > 0),
video_sample_entry_id integer references video_sample_entry (id),
--- here is the magic
primary key (camera_id, start_time_90k)
) WITHOUT ROWID;
Versiones mas antiguas
En versiones anteriores de SQLite puede usar este tipo de cosas para crear un índice de cobertura. Esto debería permitir a SQLite extraer los valores de los datos del índice, evitando obtener una página separada para cada fila:
create index recording_camera_start on recording (
camera_id, start_time_90k,
sample_file_bytes, duration_90k, video_samples, video_sync_samples, video_sample_entry_id
);
Discusión
Es probable que el costo sea IO (independientemente de lo que usted dijo que no era) porque recuerde que IO requiere una CPU, ya que los datos deben copiarse hacia y desde el bus.
Sin un índice agrupado, las filas se insertan con un rowid y pueden no estar en ningún orden razonable. Eso significa que por cada fila de 26 bytes que solicite, es posible que el sistema tenga que recuperar una página de 4 KB de la tarjeta SD, lo que supone una gran sobrecarga.
Con un límite de 8 cámaras, un simple índice agrupado en id para asegurar que aparezcan en el disco en el orden insertado probablemente le proporcionará un aumento de velocidad de aproximadamente 10x al garantizar que la página buscada contenga las siguientes 10-20 filas que serán necesarias.
Un índice agrupado tanto en la cámara como en el tiempo debe garantizar que cada página obtenida contenga 100 o más filas.
Tengo una base de datos que representa los metadatos de una cámara de seguridad NVR. Hay una fila de recording 26 bytes por cada segmento de video de 1 minuto. (Si tiene curiosidad, aquí hay un documento de diseño). Mis límites de diseño son 8 cámaras, 1 año (~ 4 millones de filas, medio millón por cámara). He falsificado algunos datos para probar el rendimiento. Esta consulta es más lenta de lo que esperaba:
select
recording.start_time_90k,
recording.duration_90k,
recording.video_samples,
recording.sample_file_bytes,
recording.video_sample_entry_id
from
recording
where
camera_id = ?
order by
recording.start_time_90k;
Eso es solo escanear todos los datos de una cámara, usando un índice para filtrar otras cámaras y hacer pedidos. El índice se ve así:
create index recording_camera_start on recording (camera_id, start_time_90k);
explain query plan ve como se espera:
0|0|0|SEARCH TABLE recording USING INDEX recording_camera_start (camera_id=?)
Las filas son bastante pequeñas.
$ sqlite3_analyzer duplicated.db
...
*** Table RECORDING w/o any indices *******************************************
Percentage of total database...................... 66.3%
Number of entries................................. 4225560
Bytes of storage consumed......................... 143418368
Bytes of payload.................................. 109333605 76.2%
B-tree depth...................................... 4
Average payload per entry......................... 25.87
Average unused bytes per entry.................... 0.99
Average fanout.................................... 94.00
Non-sequential pages.............................. 1 0.0%
Maximum payload per entry......................... 26
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1488
Primary pages used................................ 138569
Overflow pages used............................... 0
Total pages used.................................. 140057
Unused bytes on index pages....................... 188317 12.4%
Unused bytes on primary pages..................... 3987216 2.8%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4175533 2.9%
*** Index RECORDING_CAMERA_START of table RECORDING ***************************
Percentage of total database...................... 33.7%
Number of entries................................. 4155718
Bytes of storage consumed......................... 73003008
Bytes of payload.................................. 58596767 80.3%
B-tree depth...................................... 4
Average payload per entry......................... 14.10
Average unused bytes per entry.................... 0.21
Average fanout.................................... 49.00
Non-sequential pages.............................. 1 0.001%
Maximum payload per entry......................... 14
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1449
Primary pages used................................ 69843
Overflow pages used............................... 0
Total pages used.................................. 71292
Unused bytes on index pages....................... 8463 0.57%
Unused bytes on primary pages..................... 865598 1.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 874061 1.2%
...
Me gustaría que algo como esto (tal vez solo un mes a la vez, en lugar de un año completo) se ejecute cada vez que se golpea una página web en particular, así que quiero que sea bastante rápido. Pero en mi computadora portátil, toma casi un segundo, y en la Raspberry Pi 2 que me gustaría apoyar, es demasiado lento. Tiempos (en segundos) abajo; está enlazado a la CPU (usuario + tiempo sys ~ = tiempo real):
laptop$ time ./bench-profiled
trial 0: time 0.633 sec
trial 1: time 0.636 sec
trial 2: time 0.639 sec
trial 3: time 0.679 sec
trial 4: time 0.649 sec
trial 5: time 0.642 sec
trial 6: time 0.609 sec
trial 7: time 0.640 sec
trial 8: time 0.666 sec
trial 9: time 0.715 sec
...
PROFILE: interrupts/evictions/bytes = 1974/489/72648
real 0m20.546s
user 0m16.564s
sys 0m3.976s
(This is Ubuntu 15.10, SQLITE_VERSION says "3.8.11.1")
raspberrypi2$ time ./bench-profiled
trial 0: time 6.334 sec
trial 1: time 6.216 sec
trial 2: time 6.364 sec
trial 3: time 6.412 sec
trial 4: time 6.398 sec
trial 5: time 6.389 sec
trial 6: time 6.395 sec
trial 7: time 6.424 sec
trial 8: time 6.391 sec
trial 9: time 6.396 sec
...
PROFILE: interrupts/evictions/bytes = 19066/2585/43124
real 3m20.083s
user 2m47.120s
sys 0m30.620s
(This is Raspbian Jessie; SQLITE_VERSION says "3.8.7.1")
Probablemente termine haciendo algún tipo de datos desnormalizados, pero primero me gustaría ver si puedo lograr que esta simple consulta funcione tan bien como pueda. Mi punto de referencia es bastante simple; prepara la declaración por adelantado y luego hace un bucle sobre esto:
void Trial(sqlite3_stmt *stmt) {
int ret;
while ((ret = sqlite3_step(stmt)) == SQLITE_ROW) ;
if (ret != SQLITE_DONE) {
errx(1, "sqlite3_step: %d (%s)", ret, sqlite3_errstr(ret));
}
ret = sqlite3_reset(stmt);
if (ret != SQLITE_OK) {
errx(1, "sqlite3_reset: %d (%s)", ret, sqlite3_errstr(ret));
}
}
Hice un perfil de CPU con gperftools . Imagen:
$ google-pprof bench-profiled timing.pprof
Using local file bench-profiled.
Using local file timing.pprof.
Welcome to pprof! For help, type ''help''.
(pprof) top 10
Total: 593 samples
154 26.0% 26.0% 377 63.6% sqlite3_randomness
134 22.6% 48.6% 557 93.9% sqlite3_reset
83 14.0% 62.6% 83 14.0% __read_nocancel
61 10.3% 72.8% 61 10.3% sqlite3_strnicmp
41 6.9% 79.8% 46 7.8% sqlite3_free_table
26 4.4% 84.1% 26 4.4% sqlite3_uri_parameter
25 4.2% 88.4% 25 4.2% llseek
13 2.2% 90.6% 121 20.4% sqlite3_db_config
12 2.0% 92.6% 12 2.0% __pthread_mutex_unlock_usercnt (inline)
10 1.7% 94.3% 10 1.7% __GI___pthread_mutex_lock
Esto parece lo suficientemente extraño como para darme la esperanza de que pueda mejorarse. Tal vez estoy haciendo algo tonto. Soy particularmente escéptico de las operaciones sqlite3_randomness y sqlite3_strnicmp :
- los documentos dicen que
sqlite3_randomnessse usa para insertar rowids en algunas circunstancias, pero solo estoy haciendo una consulta de selección. ¿Por qué lo estaría usando ahora? Desde el código fuente de skll sqlite3, veo que se usa en select parasqlite3ColumnsFromExprListpero eso parece ser algo que sucedería al preparar la declaración. Lo estoy haciendo una vez, no en la parte que está siendo comparada. -
strnicmpes para comparaciones de cadenas que no distinguen entre mayúsculas y minúsculas. Pero cada campo en esta tabla es un entero. ¿Por qué estaría usando esta función? ¿Qué está comparando? - y en general, no sé por qué
sqlite3_resetsería caro o por qué se llamaría desdesqlite3_step.
Esquema:
-- Each row represents a single recorded segment of video.
-- Segments are typically ~60 seconds; never more than 5 minutes.
-- Each row should have a matching recording_detail row.
create table recording (
id integer primary key,
camera_id integer references camera (id) not null,
sample_file_bytes integer not null check (sample_file_bytes > 0),
-- The starting time of the recording, in 90 kHz units since
-- 1970-01-01 00:00:00 UTC.
start_time_90k integer not null check (start_time_90k >= 0),
-- The duration of the recording, in 90 kHz units.
duration_90k integer not null
check (duration_90k >= 0 and duration_90k < 5*60*90000),
video_samples integer not null check (video_samples > 0),
video_sync_samples integer not null check (video_samples > 0),
video_sample_entry_id integer references video_sample_entry (id)
);
He marcado mis datos de prueba + programa de prueba; Puedes descargarlo here .
Edición 1:
Ahh, mirando a través del código SQLite, veo una pista:
int sqlite3_step(sqlite3_stmt *pStmt){
int rc = SQLITE_OK; /* Result from sqlite3Step() */
int rc2 = SQLITE_OK; /* Result from sqlite3Reprepare() */
Vdbe *v = (Vdbe*)pStmt; /* the prepared statement */
int cnt = 0; /* Counter to prevent infinite loop of reprepares */
sqlite3 *db; /* The database connection */
if( vdbeSafetyNotNull(v) ){
return SQLITE_MISUSE_BKPT;
}
db = v->db;
sqlite3_mutex_enter(db->mutex);
v->doingRerun = 0;
while( (rc = sqlite3Step(v))==SQLITE_SCHEMA
&& cnt++ < SQLITE_MAX_SCHEMA_RETRY ){
int savedPc = v->pc;
rc2 = rc = sqlite3Reprepare(v);
if( rc!=SQLITE_OK) break;
sqlite3_reset(pStmt);
if( savedPc>=0 ) v->doingRerun = 1;
assert( v->expired==0 );
}
Parece que sqlite3_step llama a sqlite3_reset en el cambio de esquema. ( Entrada de preguntas frecuentes ) No sé por qué habría un cambio de esquema desde que se preparó mi declaración ...
Edición 2:
Descargué la "amalgación" de SQLite 3.10.1 y la compilé con símbolos de depuración. Ahora tengo un perfil bastante diferente que no parece tan extraño, pero no es más rápido. Tal vez los resultados extraños que vi antes se debieron a un pliegue de código idéntico o algo.
Edición 3:
Al probar la solución de índice agrupado de Ben a continuación, es aproximadamente 3.6X más rápido. Creo que esto es lo mejor que voy a hacer con esta consulta. El rendimiento de la CPU de SQLite es de unos 700 MB / s en mi computadora portátil. Aparte de reescribirlo para usar un compilador JIT para su máquina virtual o algo así, no lo haré mejor. En particular, creo que las llamadas extrañas que vi en mi primer perfil no estaban sucediendo realmente; gcc debe haber escrito información de depuración engañosa debido a optimizaciones o algo así.
Incluso si se mejorara el rendimiento de la CPU, ese rendimiento es más que lo que mi almacenamiento puede hacer en la lectura en frío ahora, y creo que lo mismo ocurre en el Pi (que tiene un bus USB 2.0 limitado para la tarjeta SD).
$ time ./bench
sqlite3 version: 3.10.1
trial 0: realtime 0.172 sec cputime 0.172 sec
trial 1: realtime 0.172 sec cputime 0.172 sec
trial 2: realtime 0.175 sec cputime 0.175 sec
trial 3: realtime 0.173 sec cputime 0.173 sec
trial 4: realtime 0.182 sec cputime 0.182 sec
trial 5: realtime 0.187 sec cputime 0.187 sec
trial 6: realtime 0.173 sec cputime 0.173 sec
trial 7: realtime 0.185 sec cputime 0.185 sec
trial 8: realtime 0.190 sec cputime 0.190 sec
trial 9: realtime 0.192 sec cputime 0.192 sec
trial 10: realtime 0.191 sec cputime 0.191 sec
trial 11: realtime 0.188 sec cputime 0.188 sec
trial 12: realtime 0.186 sec cputime 0.186 sec
trial 13: realtime 0.179 sec cputime 0.179 sec
trial 14: realtime 0.179 sec cputime 0.179 sec
trial 15: realtime 0.188 sec cputime 0.188 sec
trial 16: realtime 0.178 sec cputime 0.178 sec
trial 17: realtime 0.175 sec cputime 0.175 sec
trial 18: realtime 0.182 sec cputime 0.182 sec
trial 19: realtime 0.178 sec cputime 0.178 sec
trial 20: realtime 0.189 sec cputime 0.189 sec
trial 21: realtime 0.191 sec cputime 0.191 sec
trial 22: realtime 0.179 sec cputime 0.179 sec
trial 23: realtime 0.185 sec cputime 0.185 sec
trial 24: realtime 0.190 sec cputime 0.190 sec
trial 25: realtime 0.189 sec cputime 0.189 sec
trial 26: realtime 0.182 sec cputime 0.182 sec
trial 27: realtime 0.176 sec cputime 0.176 sec
trial 28: realtime 0.173 sec cputime 0.173 sec
trial 29: realtime 0.181 sec cputime 0.181 sec
PROFILE: interrupts/evictions/bytes = 547/178/24592
real 0m5.651s
user 0m5.292s
sys 0m0.356s
Puede que tenga que mantener algunos datos desnormalizados. Afortunadamente, estoy pensando que puedo mantenerlo en la memoria RAM de mi aplicación ya que no será demasiado grande, el inicio no tiene que ser increíblemente rápido, y solo el proceso se escribe en la base de datos.