¿Se puede hacer esta consulta SQLite mucho más rápido?

Tengo una base de datos que representa metadatos de una cámara de seguridad NVR. Hay una fila de 26 bytes recording por cada segmento de 1 minuto de video. (Si tienes curiosidad, un documento de diseño está en progreso aquí. 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 simplemente escanear todos los datos para una cámara, usando un índice para filtrar otras cámaras y ordenar. El índice se ve así: 

create index recording_camera_start on recording (camera_id, start_time_90k);

explain query plan se ve como se esperaba: 

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, se tarda más de un segundo, y en la Raspberry Pi 2 me gustaría apoyar, es demasiado lento. Tiempos (en segundos) a continuación; es CPU-bound (user+sys time ~ = real tiempo):

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 terminaré haciendo algún tipo de datos desnormalizados, pero primero me gustaría ver si puedo hacer que esta simple consulta funcione tan bien como pueda. Mi punto de referencia es bastante simple; prepara la declaración de antemano y luego gira 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:

Gráfico de perfil de CPU

$ 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 esperanza de que pueda ser mejorado. Tal vez estoy haciendo algo tonto. Soy particularmente escéptico de las operaciones sqlite3_randomness y sqlite3_strnicmp:

  • los documentos dicen que sqlite3_randomness se usa para insertar rowids en algunas circunstancias, pero solo estoy haciendo una consulta select. ¿Por qué lo estaría usando ahora? Al revisar el código fuente de sqlite3, veo que se usa en select for sqlite3ColumnsFromExprList, pero eso parece ser algo que ocurriría al preparar la instrucción. Estoy haciendo eso una vez, no en la parte que se compara.
  • strnicmp es para comparaciones de cadenas insensibles a 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 se por qué sqlite3_reset sería caro o por qué se llamaría desde sqlite3_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 enlatado mis datos de prueba + programa de prueba; puede descargarlo aquí.

Editar 1:

Ahh, mirando a través del código SQLite, veo un 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 al cambiar de esquema. (FAQ entry ) No se por qué habría un cambio de esquema desde que mi declaración fue preparada...

Editar 2:

Descargué el SQLite 3.10.1 "amalgation" y compilé contra él con símbolos de depuración. Ahora tengo un perfil bastante diferente que no se ve tan raro, pero no es más rápido. Tal vez los resultados extraños que vi antes se debieron a Plegamiento de Código Idéntico o algo.

introduzca la descripción de la imagen aquí

Editar 3:

Probando la solución de índice agrupado de Ben a continuación, es aproximadamente 3.6 veces 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 aproximadamente ~700 MB / s en mi computadora portátil. A falta de reescribirlo para usar un compilador JIT para su máquina virtual o algo así, no voy a hacerlo 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 de lo que mi almacenamiento puede hacer en lectura en frío ahora, y creo que lo mismo es cierto 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 dado que no será demasiado grande, el inicio no tiene que ser increíblemente rápido, y solo el único proceso escribe en la base de datos.

Author: Scott Lamb, 2016-01-18
Source

1 answers

Necesita un índice agrupado, o si está utilizando una versión de SQLite que no admite uno, un índice de cobertura.

Sqlite 3.8.2 y superior

Use 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 anteriores

En versiones anteriores de SQLite puedes usar este tipo de cosas para crear un índice de cobertura. Esto debería permitir que SQLite extraiga los valores de datos del índice, evitando obtener una página separada para cada uno 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 que usted haya dicho que no lo era) porque recuerde que IO requiere 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 sensible. Eso significa que por cada fila de 26 bytes que solicite, el sistema puede tener que obtener una página de 4 KB de la tarjeta SD, lo que es una gran sobrecarga.

Con un límite de 8 cámaras, un simple clustered index on id para asegurarse de que aparecen en el disco en orden insertado, probablemente le daría un aumento de velocidad de aproximadamente 10 veces al asegurarse de que la página recuperada contenga las siguientes 10-20 filas que se requerirán.

Un índice agrupado tanto en la cámara como en la hora debe garantizar que cada página recuperada contenga 100 o más filas.

 2
Author: Ben,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-01-19 12:57:18