test/kvtest.c

   1 /*
   2 ** 2016-12-28
   3 **
   4 ** The author disclaims copyright to this source code.  In place of
   5 ** a legal notice, here is a blessing:
   6 **
   7 **    May you do good and not evil.
   8 **    May you find forgiveness for yourself and forgive others.
   9 **    May you share freely, never taking more than you give.
  10 **
  11 *************************************************************************
  12 **
  13 ** This file implements "key-value" performance test for SQLite.  The
  14 ** purpose is to compare the speed of SQLite for accessing large BLOBs
  15 ** versus reading those same BLOB values out of individual files in the
  16 ** filesystem.
  17 **
  18 ** Run "kvtest" with no arguments for on-line help, or see comments below.
  19 **
  20 ** HOW TO COMPILE:
  21 **
  22 ** (1) Gather this source file and a recent SQLite3 amalgamation with its
  23 **     header into the working directory.  You should have:
  24 **
  25 **          kvtest.c       >--- this file
  26 **          sqlite3.c      \___ SQLite
  27 **          sqlite3.h      /    amlagamation & header
  28 **
  29 ** (2) Run you compiler against the two C source code files.
  30 **
  31 **    (a) On linux or mac:
  32 **
  33 **        OPTS="-DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION"
  34 **        gcc -Os -I. $OPTS kvtest.c sqlite3.c -o kvtest
  35 **
  36 **             The $OPTS options can be omitted.  The $OPTS merely omit
  37 **             the need to link against -ldl and -lpthread, or whatever
  38 **             the equivalent libraries are called on your system.
  39 **
  40 **    (b) Windows with MSVC:
  41 **
  42 **        cl -I. kvtest.c sqlite3.c
  43 **
  44 ** USAGE:
  45 **
  46 ** (1) Create a test database by running "kvtest init" with appropriate
  47 **     options.  See the help message for available options.
  48 **
  49 ** (2) Construct the corresponding pile-of-files database on disk using
  50 **     the "kvtest export" command.
  51 **
  52 ** (3) Run tests using "kvtest run" against either the SQLite database or
  53 **     the pile-of-files database and with appropriate options.
  54 **
  55 ** For example:
  56 **
  57 **       ./kvtest init x1.db --count 100000 --size 10000
  58 **       mkdir x1
  59 **       ./kvtest export x1.db x1
  60 **       ./kvtest run x1.db --count 10000 --max-id 1000000
  61 **       ./kvtest run x1 --count 10000 --max-id 1000000
  62 */
  63 static const char zHelp[] =
  64 "Usage: kvhelp COMMAND ARGS...\n"
  65 "\n"
  66 "   kvhelp init DBFILE --count N --size M --pagesize X\n"
  67 "\n"
  68 "        Generate a new test database file named DBFILE containing N\n"
  69 "        BLOBs each of size M bytes.  The page size of the new database\n"
  70 "        file will be X\n"
  71 "\n"
  72 "   kvhelp export DBFILE DIRECTORY\n"
  73 "\n"
  74 "        Export all the blobs in the kv table of DBFILE into separate\n"
  75 "        files in DIRECTORY.\n"
  76 "\n"
  77 "   kvhelp run DBFILE [options]\n"
  78 "\n"
  79 "        Run a performance test.  DBFILE can be either the name of a\n"
  80 "        database or a directory containing sample files.  Options:\n"
  81 "\n"
  82 "             --asc                Read blobs in ascending order\n"
  83 "             --blob-api           Use the BLOB API\n"
  84 "             --cache-size N       Database cache size\n"
  85 "             --count N            Read N blobs\n"
  86 "             --desc               Read blobs in descending order\n"
  87 "             --max-id N           Maximum blob key to use\n"
  88 "             --random             Read blobs in a random order\n"
  89 "             --start N            Start reading with this blob key\n"
  90 ;
  91
  92 /* Reference resources used */
  93 #include <stdio.h>
  94 #include <stdlib.h>
  95 #include <sys/types.h>
  96 #include <sys/stat.h>
  97 #include <assert.h>
  98 #include <string.h>
  99 #include "sqlite3.h"
 100
 101 #ifndef _WIN32
 102 # include <unistd.h>
 103 #else
 104   /* Provide Windows equivalent for the needed parts of unistd.h */
 105 # include <io.h>
 106 # define R_OK 2
 107 # define S_ISREG(m) (((m) & S_IFMT) == S_IFREG)
 108 # define S_ISDIR(m) (((m) & S_IFMT) == S_IFDIR)
 109 # define access _access
 110 #endif
 111
 112
 113 /*
 114 ** Show thqe help text and quit.
 115 */
 116 static void showHelp(void){
 117   fprintf(stdout, "%s", zHelp);
 118   exit(1);
 119 }
 120
 121 /*
 122 ** Show an error message an quit.
 123 */
 124 static void fatalError(const char *zFormat, ...){
 125   va_list ap;
 126   fprintf(stdout, "ERROR: ");
 127   va_start(ap, zFormat);
 128   vfprintf(stdout, zFormat, ap);
 129   va_end(ap);
 130   fprintf(stdout, "\n");
 131   exit(1);
 132 }
 133
 134 /*
 135 ** Check the filesystem object zPath.  Determine what it is:
 136 **
 137 **    PATH_DIR     A directory
 138 **    PATH_DB      An SQLite database
 139 **    PATH_NEXIST  Does not exist
 140 **    PATH_OTHER   Something else
 141 */
 142 #define PATH_DIR     1
 143 #define PATH_DB      2
 144 #define PATH_NEXIST  0
 145 #define PATH_OTHER   99
 146 static int pathType(const char *zPath){
 147   struct stat x;
 148   int rc;
 149   if( access(zPath,R_OK) ) return PATH_NEXIST;
 150   memset(&x, 0, sizeof(x));
 151   rc = stat(zPath, &x);
 152   if( rc<0 ) return PATH_OTHER;
 153   if( S_ISDIR(x.st_mode) ) return PATH_DIR;
 154   if( (x.st_size%512)==0 ) return PATH_DB;
 155   return PATH_OTHER;
 156 }
 157
 158 /*
 159 ** Return the size of a file in bytes.  Or return -1 if the
 160 ** named object is not a regular file or does not exist.
 161 */
 162 static sqlite3_int64 fileSize(const char *zPath){
 163   struct stat x;
 164   int rc;
 165   memset(&x, 0, sizeof(x));
 166   rc = stat(zPath, &x);
 167   if( rc<0 ) return -1;
 168   if( !S_ISREG(x.st_mode) ) return -1;
 169   return x.st_size;
 170 }
 171
 172 /*
 173 ** A Pseudo-random number generator with a fixed seed.  Use this so
 174 ** that the same sequence of "random" numbers are generated on each
 175 ** run, for repeatability.
 176 */
 177 static unsigned int randInt(void){
 178   static unsigned int x = 0x333a13cd;
 179   static unsigned int y = 0xecb2adea;
 180   x = (x>>1) ^ ((1+~(x&1)) & 0xd0000001);
 181   y = y*1103515245 + 12345;
 182   return x^y;
 183 }
 184
 185 /*
 186 ** Do database initialization.
 187 */
 188 static int initMain(int argc, char **argv){
 189   char *zDb;
 190   int i, rc;
 191   int nCount = 1000;
 192   int sz = 10000;
 193   int pgsz = 4096;
 194   sqlite3 *db;
 195   char *zSql;
 196   char *zErrMsg = 0;
 197
 198   assert( strcmp(argv[1],"init")==0 );
 199   assert( argc>=3 );
 200   zDb = argv[2];
 201   for(i=3; i<argc; i++){
 202     char *z = argv[i];
 203     if( z[0]!='-' ) fatalError("unknown argument: \"%s\"", z);
 204     if( z[1]=='-' ) z++;
 205     if( strcmp(z, "-count")==0 ){
 206       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 207       nCount = atoi(argv[++i]);
 208       if( nCount<1 ) fatalError("the --count must be positive");
 209       continue;
 210     }
 211     if( strcmp(z, "-size")==0 ){
 212       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 213       sz = atoi(argv[++i]);
 214       if( sz<1 ) fatalError("the --size must be positive");
 215       continue;
 216     }
 217     if( strcmp(z, "-pagesize")==0 ){
 218       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 219       pgsz = atoi(argv[++i]);
 220       if( pgsz<512 || pgsz>65536 || ((pgsz-1)&pgsz)!=0 ){
 221         fatalError("the --pagesize must be power of 2 between 512 and 65536");
 222       }
 223       continue;
 224     }
 225     fatalError("unknown option: \"%s\"", argv[i]);
 226   }
 227   rc = sqlite3_open(zDb, &db);
 228   if( rc ){
 229     fatalError("cannot open database \"%s\": %s", zDb, sqlite3_errmsg(db));
 230   }
 231   zSql = sqlite3_mprintf(
 232     "DROP TABLE IF EXISTS kv;\n"
 233     "PRAGMA page_size=%d;\n"
 234     "VACUUM;\n"
 235     "BEGIN;\n"
 236     "CREATE TABLE kv(k INTEGER PRIMARY KEY, v BLOB);\n"
 237     "WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<%d)"
 238     " INSERT INTO kv(k,v) SELECT x, randomblob(%d) FROM c;\n"
 239     "COMMIT;\n",
 240     pgsz, nCount, sz
 241   );
 242   rc = sqlite3_exec(db, zSql, 0, 0, &zErrMsg);
 243   if( rc ) fatalError("database create failed: %s", zErrMsg);
 244   sqlite3_free(zSql);
 245   sqlite3_close(db);
 246   return 0;
 247 }
 248
 249 /*
 250 ** Implementation of the "writefile(X,Y)" SQL function.  The argument Y
 251 ** is written into file X.  The number of bytes written is returned.  Or
 252 ** NULL is returned if something goes wrong, such as being unable to open
 253 ** file X for writing.
 254 */
 255 static void writefileFunc(
 256   sqlite3_context *context,
 257   int argc,
 258   sqlite3_value **argv
 259 ){
 260   FILE *out;
 261   const char *z;
 262   sqlite3_int64 rc;
 263   const char *zFile;
 264
 265   zFile = (const char*)sqlite3_value_text(argv[0]);
 266   if( zFile==0 ) return;
 267   out = fopen(zFile, "wb");
 268   if( out==0 ) return;
 269   z = (const char*)sqlite3_value_blob(argv[1]);
 270   if( z==0 ){
 271     rc = 0;
 272   }else{
 273     rc = fwrite(z, 1, sqlite3_value_bytes(argv[1]), out);
 274   }
 275   fclose(out);
 276   printf("\r%s   ", zFile); fflush(stdout);
 277   sqlite3_result_int64(context, rc);
 278 }
 279
 280 /*
 281 ** Export the kv table to individual files in the filesystem
 282 */
 283 static int exportMain(int argc, char **argv){
 284   char *zDb;
 285   char *zDir;
 286   sqlite3 *db;
 287   char *zSql;
 288   int rc;
 289   char *zErrMsg = 0;
 290
 291   assert( strcmp(argv[1],"export")==0 );
 292   assert( argc>=3 );
 293   zDb = argv[2];
 294   if( argc!=4 ) fatalError("Usage: kvtest export DATABASE DIRECTORY");
 295   zDir = argv[3];
 296   if( pathType(zDir)!=PATH_DIR ){
 297     fatalError("object \"%s\" is not a directory", zDir);
 298   }
 299   rc = sqlite3_open(zDb, &db);
 300   if( rc ){
 301     fatalError("cannot open database \"%s\": %s", zDb, sqlite3_errmsg(db));
 302   }
 303   sqlite3_create_function(db, "writefile", 2, SQLITE_UTF8, 0,
 304                           writefileFunc, 0, 0);
 305   zSql = sqlite3_mprintf(
 306     "SELECT writefile(printf('%s/%%06d',k),v) FROM kv;",
 307     zDir
 308   );
 309   rc = sqlite3_exec(db, zSql, 0, 0, &zErrMsg);
 310   if( rc ) fatalError("database create failed: %s", zErrMsg);
 311   sqlite3_free(zSql);
 312   sqlite3_close(db);
 313   printf("\n");
 314   return 0;
 315 }
 316
 317 /*
 318 ** Read the content of file zName into memory obtained from sqlite3_malloc64()
 319 ** and return a pointer to the buffer. The caller is responsible for freeing
 320 ** the memory.
 321 **
 322 ** If parameter pnByte is not NULL, (*pnByte) is set to the number of bytes
 323 ** read.
 324 **
 325 ** For convenience, a nul-terminator byte is always appended to the data read
 326 ** from the file before the buffer is returned. This byte is not included in
 327 ** the final value of (*pnByte), if applicable.
 328 **
 329 ** NULL is returned if any error is encountered. The final value of *pnByte
 330 ** is undefined in this case.
 331 */
 332 static unsigned char *readFile(const char *zName, int *pnByte){
 333   FILE *in;               /* FILE from which to read content of zName */
 334   sqlite3_int64 nIn;      /* Size of zName in bytes */
 335   size_t nRead;           /* Number of bytes actually read */
 336   unsigned char *pBuf;    /* Content read from disk */
 337
 338   nIn = fileSize(zName);
 339   if( nIn<0 ) return 0;
 340   in = fopen(zName, "rb");
 341   if( in==0 ) return 0;
 342   pBuf = sqlite3_malloc64( nIn );
 343   if( pBuf==0 ) return 0;
 344   nRead = fread(pBuf, nIn, 1, in);
 345   fclose(in);
 346   if( nRead!=1 ){
 347     sqlite3_free(pBuf);
 348     return 0;
 349   }
 350   if( pnByte ) *pnByte = nIn;
 351   return pBuf;
 352 }
 353
 354 /*
 355 ** Return the current time in milliseconds since the beginning of
 356 ** the Julian epoch.
 357 */
 358 static sqlite3_int64 timeOfDay(void){
 359   static sqlite3_vfs *clockVfs = 0;
 360   sqlite3_int64 t;
 361   if( clockVfs==0 ) clockVfs = sqlite3_vfs_find(0);
 362   if( clockVfs->iVersion>=2 && clockVfs->xCurrentTimeInt64!=0 ){
 363     clockVfs->xCurrentTimeInt64(clockVfs, &t);
 364   }else{
 365     double r;
 366     clockVfs->xCurrentTime(clockVfs, &r);
 367     t = (sqlite3_int64)(r*86400000.0);
 368   }
 369   return t;
 370 }
 371
 372 /* Blob access order */
 373 #define ORDER_ASC     1
 374 #define ORDER_DESC    2
 375 #define ORDER_RANDOM  3
 376
 377 /*
 378 ** Run a performance test
 379 */
 380 static int runMain(int argc, char **argv){
 381   int eType;                  /* Is zDb a database or a directory? */
 382   char *zDb;                  /* Database or directory name */
 383   int i;                      /* Loop counter */
 384   int rc;                     /* Return code from SQLite calls */
 385   int nCount = 1000;          /* Number of blob fetch operations */
 386   int nExtra = 0;             /* Extra cycles */
 387   int iKey = 1;               /* Next blob key */
 388   int iMax = 1000;            /* Largest allowed key */
 389   int iPagesize = 0;          /* Database page size */
 390   int iCache = 1000;          /* Database cache size in kibibytes */
 391   int bBlobApi = 0;           /* Use the incremental blob I/O API */
 392   int eOrder = ORDER_ASC;     /* Access order */
 393   sqlite3 *db = 0;            /* Database connection */
 394   sqlite3_stmt *pStmt = 0;    /* Prepared statement for SQL access */
 395   sqlite3_blob *pBlob = 0;    /* Handle for incremental Blob I/O */
 396   sqlite3_int64 tmStart;      /* Start time */
 397   sqlite3_int64 tmElapsed;    /* Elapsed time */
 398   int nData = 0;              /* Bytes of data */
 399   sqlite3_int64 nTotal = 0;   /* Total data read */
 400   unsigned char *pData;       /* Content of the blob */
 401
 402
 403   assert( strcmp(argv[1],"run")==0 );
 404   assert( argc>=3 );
 405   zDb = argv[2];
 406   eType = pathType(zDb);
 407   if( eType==PATH_OTHER ) fatalError("unknown object type: \"%s\"", zDb);
 408   if( eType==PATH_NEXIST ) fatalError("object does not exist: \"%s\"", zDb);
 409   for(i=3; i<argc; i++){
 410     char *z = argv[i];
 411     if( z[0]!='-' ) fatalError("unknown argument: \"%s\"", z);
 412     if( z[1]=='-' ) z++;
 413     if( strcmp(z, "-count")==0 ){
 414       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 415       nCount = atoi(argv[++i]);
 416       if( nCount<1 ) fatalError("the --count must be positive");
 417       continue;
 418     }
 419     if( strcmp(z, "-max-id")==0 ){
 420       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 421       iMax = atoi(argv[++i]);
 422       if( iMax<1 ) fatalError("the --max-id must be positive");
 423       continue;
 424     }
 425     if( strcmp(z, "-start")==0 ){
 426       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 427       iKey = atoi(argv[++i]);
 428       if( iKey<1 ) fatalError("the --start must be positive");
 429       continue;
 430     }
 431     if( strcmp(z, "-cache-size")==0 ){
 432       if( i==argc-1 ) fatalError("missing argument on \"%s\"", argv[i]);
 433       iCache = atoi(argv[++i]);
 434       continue;
 435     }
 436     if( strcmp(z, "-random")==0 ){
 437       eOrder = ORDER_RANDOM;
 438       continue;
 439     }
 440     if( strcmp(z, "-asc")==0 ){
 441       eOrder = ORDER_ASC;
 442       continue;
 443     }
 444     if( strcmp(z, "-desc")==0 ){
 445       eOrder = ORDER_DESC;
 446       continue;
 447     }
 448     if( strcmp(z, "-blob-api")==0 ){
 449       bBlobApi = 1;
 450       continue;
 451     }
 452     fatalError("unknown option: \"%s\"", argv[i]);
 453   }
 454   tmStart = timeOfDay();
 455   if( eType==PATH_DB ){
 456     char *zSql;
 457     rc = sqlite3_open(zDb, &db);
 458     if( rc ){
 459       fatalError("cannot open database \"%s\": %s", zDb, sqlite3_errmsg(db));
 460     }
 461     zSql = sqlite3_mprintf("PRAGMA cache_size=%d", iCache);
 462     sqlite3_exec(db, zSql, 0, 0, 0);
 463     sqlite3_free(zSql);
 464     pStmt = 0;
 465     sqlite3_prepare_v2(db, "PRAGMA page_size", -1, &pStmt, 0);
 466     if( sqlite3_step(pStmt)==SQLITE_ROW ){
 467       iPagesize = sqlite3_column_int(pStmt, 0);
 468     }
 469     sqlite3_finalize(pStmt);
 470     sqlite3_prepare_v2(db, "PRAGMA cache_size", -1, &pStmt, 0);
 471     if( sqlite3_step(pStmt)==SQLITE_ROW ){
 472       iCache = sqlite3_column_int(pStmt, 0);
 473     }else{
 474       iCache = 0;
 475     }
 476     sqlite3_finalize(pStmt);
 477     pStmt = 0;
 478     sqlite3_exec(db, "BEGIN", 0, 0, 0);
 479   }
 480   for(i=0; i<nCount; i++){
 481     if( eType==PATH_DIR ){
 482       /* CASE 1: Reading blobs out of separate files */
 483       char *zKey;
 484       zKey = sqlite3_mprintf("%s/%06d", zDb, iKey);
 485       nData = 0;
 486       pData = readFile(zKey, &nData);
 487       sqlite3_free(zKey);
 488       sqlite3_free(pData);
 489     }else if( bBlobApi ){
 490       /* CASE 2: Reading from database using the incremental BLOB I/O API */
 491       if( pBlob==0 ){
 492         rc = sqlite3_blob_open(db, "main", "kv", "v", iKey, 0, &pBlob);
 493         if( rc ){
 494           fatalError("could not open sqlite3_blob handle: %s",
 495                      sqlite3_errmsg(db));
 496         }
 497       }else{
 498         rc = sqlite3_blob_reopen(pBlob, iKey);
 499       }
 500       if( rc==SQLITE_OK ){
 501         nData = sqlite3_blob_bytes(pBlob);
 502         pData = sqlite3_malloc( nData+1 );
 503         if( pData==0 ) fatalError("cannot allocate %d bytes", nData+1);
 504         rc = sqlite3_blob_read(pBlob, pData, nData, 0);
 505         if( rc!=SQLITE_OK ){
 506           fatalError("could not read the blob at %d: %s", iKey,
 507                      sqlite3_errmsg(db));
 508         }
 509         sqlite3_free(pData);
 510       }
 511     }else{
 512       /* CASE 3: Reading from database using SQL */
 513       if( pStmt==0 ){
 514         rc = sqlite3_prepare_v2(db,
 515                "SELECT v FROM kv WHERE k=?1", -1, &pStmt, 0);
 516         if( rc ){
 517           fatalError("cannot prepare query: %s", sqlite3_errmsg(db));
 518         }
 519       }else{
 520         sqlite3_reset(pStmt);
 521       }
 522       sqlite3_bind_int(pStmt, 1, iKey);
 523       rc = sqlite3_step(pStmt);
 524       if( rc==SQLITE_ROW ){
 525         nData = sqlite3_column_bytes(pStmt, 0);
 526         pData = (unsigned char*)sqlite3_column_blob(pStmt, 0);
 527       }else{
 528         nData = 0;
 529       }
 530     }
 531     if( eOrder==ORDER_ASC ){
 532       iKey++;
 533       if( iKey>iMax ) iKey = 1;
 534     }else if( eOrder==ORDER_DESC ){
 535       iKey--;
 536       if( iKey<=0 ) iKey = iMax;
 537     }else{
 538       iKey = (randInt()%iMax)+1;
 539     }
 540     nTotal += nData;
 541     if( nData==0 ){ nCount++; nExtra++; }
 542   }
 543   if( pStmt ) sqlite3_finalize(pStmt);
 544   if( pBlob ) sqlite3_blob_close(pBlob);
 545   if( db ) sqlite3_close(db);
 546   tmElapsed = timeOfDay() - tmStart;
 547   if( nExtra ){
 548     printf("%d cycles due to %d misses\n", nCount, nExtra);
 549   }
 550   if( eType==PATH_DB ){
 551     printf("SQLite version: %s\n", sqlite3_libversion());
 552   }
 553   printf("--count %d --max-id %d", nCount-nExtra, iMax);
 554   if( eType==PATH_DB ){
 555     printf(" --cache-size %d", iCache);
 556   }
 557   switch( eOrder ){
 558     case ORDER_RANDOM:  printf(" --random\n");  break;
 559     case ORDER_DESC:    printf(" --desc\n");    break;
 560     default:            printf(" --asc\n");     break;
 561   }
 562   if( iPagesize ) printf("Database page size: %d\n", iPagesize);
 563   printf("Total elapsed time: %.3f\n", tmElapsed/1000.0);
 564   printf("Microseconds per BLOB read: %.3f\n", tmElapsed*1000.0/nCount);
 565   printf("Content read rate: %.1f MB/s\n", nTotal/(1000.0*tmElapsed));
 566   return 0;
 567 }
 568
 569
 570 int main(int argc, char **argv){
 571   if( argc<3 ) showHelp();
 572   if( strcmp(argv[1],"init")==0 ){
 573     return initMain(argc, argv);
 574   }
 575   if( strcmp(argv[1],"export")==0 ){
 576     return exportMain(argc, argv);
 577   }
 578   if( strcmp(argv[1],"run")==0 ){
 579     return runMain(argc, argv);
 580   }
 581   showHelp();
 582   return 0;
 583 }