test/tt3_stress.c

   1 /*
   2 ** 2014 December 9
   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 **
  14 */
  15
  16
  17 /*
  18 ** Thread 1. CREATE and DROP a table.
  19 */
  20 static char *stress_thread_1(int iTid, void *pArg){
  21   Error err = {0};                /* Error code and message */
  22   Sqlite db = {0};                /* SQLite database connection */
  23
  24   opendb(&err, &db, "test.db", 0);
  25   while( !timetostop(&err) ){
  26     sql_script(&err, &db, "CREATE TABLE IF NOT EXISTS t1(a PRIMARY KEY, b)");
  27     clear_error(&err, SQLITE_LOCKED);
  28     sql_script(&err, &db, "DROP TABLE IF EXISTS t1");
  29     clear_error(&err, SQLITE_LOCKED);
  30   }
  31   closedb(&err, &db);
  32   print_and_free_err(&err);
  33   return sqlite3_mprintf("ok");
  34 }
  35
  36 /*
  37 ** Thread 2. Open and close database connections.
  38 */
  39 static char *stress_thread_2(int iTid, void *pArg){
  40   Error err = {0};                /* Error code and message */
  41   Sqlite db = {0};                /* SQLite database connection */
  42   while( !timetostop(&err) ){
  43     opendb(&err, &db, "test.db", 0);
  44     sql_script(&err, &db, "SELECT * FROM sqlite_schema;");
  45     clear_error(&err, SQLITE_LOCKED);
  46     closedb(&err, &db);
  47   }
  48   print_and_free_err(&err);
  49   return sqlite3_mprintf("ok");
  50 }
  51
  52 /*
  53 ** Thread 3. Attempt many small SELECT statements.
  54 */
  55 static char *stress_thread_3(int iTid, void *pArg){
  56   Error err = {0};                /* Error code and message */
  57   Sqlite db = {0};                /* SQLite database connection */
  58
  59   int i1 = 0;
  60   int i2 = 0;
  61
  62   opendb(&err, &db, "test.db", 0);
  63   while( !timetostop(&err) ){
  64     sql_script(&err, &db, "SELECT * FROM t1 ORDER BY a;");
  65     i1++;
  66     if( err.rc ) i2++;
  67     clear_error(&err, SQLITE_LOCKED);
  68     clear_error(&err, SQLITE_ERROR);
  69   }
  70   closedb(&err, &db);
  71   print_and_free_err(&err);
  72   return sqlite3_mprintf("read t1 %d/%d attempts", i2, i1);
  73 }
  74
  75 /*
  76 ** Thread 5. Attempt INSERT statements.
  77 */
  78 static char *stress_thread_4(int iTid, void *pArg){
  79   Error err = {0};                /* Error code and message */
  80   Sqlite db = {0};                /* SQLite database connection */
  81   int i1 = 0;
  82   int i2 = 0;
  83   int iArg = PTR2INT(pArg);
  84
  85   opendb(&err, &db, "test.db", 0);
  86   while( !timetostop(&err) ){
  87     if( iArg ){
  88       closedb(&err, &db);
  89       opendb(&err, &db, "test.db", 0);
  90     }
  91     sql_script(&err, &db,
  92         "WITH loop(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM loop LIMIT 200) "
  93         "INSERT INTO t1 VALUES(randomblob(60), randomblob(60));"
  94     );
  95     i1++;
  96     if( err.rc ) i2++;
  97     clear_error(&err, SQLITE_LOCKED);
  98     clear_error(&err, SQLITE_ERROR);
  99   }
 100   closedb(&err, &db);
 101   print_and_free_err(&err);
 102   return sqlite3_mprintf("wrote t1 %d/%d attempts", i2, i1);
 103 }
 104
 105 /*
 106 ** Thread 6. Attempt DELETE operations.
 107 */
 108 static char *stress_thread_5(int iTid, void *pArg){
 109   Error err = {0};                /* Error code and message */
 110   Sqlite db = {0};                /* SQLite database connection */
 111   int iArg = PTR2INT(pArg);
 112
 113   int i1 = 0;
 114   int i2 = 0;
 115
 116   opendb(&err, &db, "test.db", 0);
 117   while( !timetostop(&err) ){
 118     i64 i = (i1 % 4);
 119     if( iArg ){
 120       closedb(&err, &db);
 121       opendb(&err, &db, "test.db", 0);
 122     }
 123     execsql(&err, &db, "DELETE FROM t1 WHERE (rowid % 4)==:i", &i);
 124     i1++;
 125     if( err.rc ) i2++;
 126     clear_error(&err, SQLITE_LOCKED);
 127   }
 128   closedb(&err, &db);
 129   print_and_free_err(&err);
 130   return sqlite3_mprintf("deleted from t1 %d/%d attempts", i2, i1);
 131 }
 132
 133
 134 static void stress1(int nMs){
 135   Error err = {0};
 136   Threadset threads = {0};
 137
 138   setstoptime(&err, nMs);
 139   sqlite3_enable_shared_cache(1);
 140
 141   launch_thread(&err, &threads, stress_thread_1, 0);
 142   launch_thread(&err, &threads, stress_thread_1, 0);
 143
 144   launch_thread(&err, &threads, stress_thread_2, 0);
 145   launch_thread(&err, &threads, stress_thread_2, 0);
 146
 147   launch_thread(&err, &threads, stress_thread_3, 0);
 148   launch_thread(&err, &threads, stress_thread_3, 0);
 149
 150   launch_thread(&err, &threads, stress_thread_4, 0);
 151   launch_thread(&err, &threads, stress_thread_4, 0);
 152
 153   launch_thread(&err, &threads, stress_thread_5, 0);
 154   launch_thread(&err, &threads, stress_thread_5, (void*)1);
 155
 156   join_all_threads(&err, &threads);
 157   sqlite3_enable_shared_cache(0);
 158
 159   print_and_free_err(&err);
 160 }
 161
 162 /**************************************************************************
 163 ***************************************************************************
 164 ** Start of test case "stress2"
 165 */
 166
 167
 168
 169 /*
 170 ** 1.  CREATE TABLE statements.
 171 ** 2.  DROP TABLE statements.
 172 ** 3.  Small SELECT statements.
 173 ** 4.  Big SELECT statements.
 174 ** 5.  Small INSERT statements.
 175 ** 6.  Big INSERT statements.
 176 ** 7.  Small UPDATE statements.
 177 ** 8.  Big UPDATE statements.
 178 ** 9.  Small DELETE statements.
 179 ** 10. Big DELETE statements.
 180 ** 11. VACUUM.
 181 ** 14. Integrity-check.
 182 ** 17. Switch the journal mode from delete to wal and back again.
 183 ** 19. Open and close database connections rapidly.
 184 */
 185
 186 #define STRESS2_TABCNT 5          /* count1 in SDS test */
 187
 188 #define STRESS2_COUNT2 200        /* count2 in SDS test */
 189 #define STRESS2_COUNT3  57        /* count2 in SDS test */
 190
 191 static void stress2_workload1(Error *pErr, Sqlite *pDb, int i){
 192   int iTab = (i % (STRESS2_TABCNT-1)) + 1;
 193   sql_script_printf(pErr, pDb,
 194       "CREATE TABLE IF NOT EXISTS t%d(x PRIMARY KEY, y, z);", iTab
 195   );
 196 }
 197
 198 static void stress2_workload2(Error *pErr, Sqlite *pDb, int i){
 199   int iTab = (i % (STRESS2_TABCNT-1)) + 1;
 200   sql_script_printf(pErr, pDb, "DROP TABLE IF EXISTS t%d;", iTab);
 201 }
 202
 203 static void stress2_workload3(Error *pErr, Sqlite *pDb, int i){
 204   sql_script(pErr, pDb, "SELECT * FROM t0 WHERE z = 'small'");
 205 }
 206
 207 static void stress2_workload4(Error *pErr, Sqlite *pDb, int i){
 208   sql_script(pErr, pDb, "SELECT * FROM t0 WHERE z = 'big'");
 209 }
 210
 211 static void stress2_workload5(Error *pErr, Sqlite *pDb, int i){
 212   sql_script(pErr, pDb,
 213       "INSERT INTO t0 VALUES(hex(random()), hex(randomblob(200)), 'small');"
 214   );
 215 }
 216
 217 static void stress2_workload6(Error *pErr, Sqlite *pDb, int i){
 218   sql_script(pErr, pDb,
 219       "INSERT INTO t0 VALUES(hex(random()), hex(randomblob(57)), 'big');"
 220   );
 221 }
 222
 223 static void stress2_workload7(Error *pErr, Sqlite *pDb, int i){
 224   sql_script_printf(pErr, pDb,
 225       "UPDATE t0 SET y = hex(randomblob(200)) "
 226       "WHERE x LIKE hex((%d %% 5)) AND z='small';"
 227       ,i
 228   );
 229 }
 230 static void stress2_workload8(Error *pErr, Sqlite *pDb, int i){
 231   sql_script_printf(pErr, pDb,
 232       "UPDATE t0 SET y = hex(randomblob(57)) "
 233       "WHERE x LIKE hex(%d %% 5) AND z='big';"
 234       ,i
 235   );
 236 }
 237
 238 static void stress2_workload9(Error *pErr, Sqlite *pDb, int i){
 239   sql_script_printf(pErr, pDb,
 240       "DELETE FROM t0 WHERE x LIKE hex(%d %% 5) AND z='small';", i
 241   );
 242 }
 243 static void stress2_workload10(Error *pErr, Sqlite *pDb, int i){
 244   sql_script_printf(pErr, pDb,
 245       "DELETE FROM t0 WHERE x LIKE hex(%d %% 5) AND z='big';", i
 246   );
 247 }
 248
 249 static void stress2_workload11(Error *pErr, Sqlite *pDb, int i){
 250   sql_script(pErr, pDb, "VACUUM");
 251 }
 252
 253 static void stress2_workload14(Error *pErr, Sqlite *pDb, int i){
 254   sql_script(pErr, pDb, "PRAGMA integrity_check");
 255 }
 256
 257 static void stress2_workload17(Error *pErr, Sqlite *pDb, int i){
 258   sql_script_printf(pErr, pDb,
 259       "PRAGMA journal_mode = %q", (i%2) ? "delete" : "wal"
 260   );
 261 }
 262
 263 static char *stress2_workload19(int iTid, void *pArg){
 264   Error err = {0};                /* Error code and message */
 265   Sqlite db = {0};                /* SQLite database connection */
 266   const char *zDb = (const char*)pArg;
 267   while( !timetostop(&err) ){
 268     opendb(&err, &db, zDb, 0);
 269     sql_script(&err, &db, "SELECT * FROM sqlite_schema;");
 270     clear_error(&err, SQLITE_LOCKED);
 271     closedb(&err, &db);
 272   }
 273   print_and_free_err(&err);
 274   return sqlite3_mprintf("ok");
 275 }
 276
 277
 278 typedef struct Stress2Ctx Stress2Ctx;
 279 struct Stress2Ctx {
 280   const char *zDb;
 281   void (*xProc)(Error*, Sqlite*, int);
 282 };
 283
 284 static char *stress2_thread_wrapper(int iTid, void *pArg){
 285   Stress2Ctx *pCtx = (Stress2Ctx*)pArg;
 286   Error err = {0};                /* Error code and message */
 287   Sqlite db = {0};                /* SQLite database connection */
 288   int i1 = 0;
 289   int i2 = 0;
 290
 291   while( !timetostop(&err) ){
 292     int cnt;
 293     opendb(&err, &db, pCtx->zDb, 0);
 294     for(cnt=0; err.rc==SQLITE_OK && cnt<STRESS2_TABCNT; cnt++){
 295       pCtx->xProc(&err, &db, i1);
 296       i2 += (err.rc==SQLITE_OK);
 297       clear_error(&err, SQLITE_LOCKED);
 298       i1++;
 299     }
 300     closedb(&err, &db);
 301   }
 302
 303   print_and_free_err(&err);
 304   return sqlite3_mprintf("ok %d/%d", i2, i1);
 305 }
 306
 307 static void stress2_launch_thread_loop(
 308   Error *pErr,                    /* IN/OUT: Error code */
 309   Threadset *pThreads,            /* Thread set */
 310   const char *zDb,                /* Database name */
 311   void (*x)(Error*,Sqlite*,int)   /* Run this until error or timeout */
 312 ){
 313   Stress2Ctx *pCtx = sqlite3_malloc(sizeof(Stress2Ctx));
 314   pCtx->zDb = zDb;
 315   pCtx->xProc = x;
 316   launch_thread(pErr, pThreads, stress2_thread_wrapper, (void*)pCtx);
 317 }
 318
 319 static void stress2(int nMs){
 320   struct Stress2Task {
 321     void (*x)(Error*,Sqlite*,int);
 322   } aTask[] = {
 323     { stress2_workload1 },
 324     { stress2_workload2 },
 325     { stress2_workload3 },
 326     { stress2_workload4 },
 327     { stress2_workload5 },
 328     { stress2_workload6 },
 329     { stress2_workload7 },
 330     { stress2_workload8 },
 331     { stress2_workload9 },
 332     { stress2_workload10 },
 333     { stress2_workload11 },
 334     { stress2_workload14 },
 335     { stress2_workload17 },
 336   };
 337   const char *zDb = "test.db";
 338
 339   int i;
 340   Error err = {0};
 341   Sqlite db = {0};
 342   Threadset threads = {0};
 343
 344   /* To make sure the db file is empty before commencing */
 345   opendb(&err, &db, zDb, 1);
 346   sql_script(&err, &db,
 347       "CREATE TABLE IF NOT EXISTS t0(x PRIMARY KEY, y, z);"
 348       "CREATE INDEX IF NOT EXISTS i0 ON t0(y);"
 349   );
 350   closedb(&err, &db);
 351
 352   setstoptime(&err, nMs);
 353   sqlite3_enable_shared_cache(1);
 354
 355   for(i=0; i<sizeof(aTask)/sizeof(aTask[0]); i++){
 356     stress2_launch_thread_loop(&err, &threads, zDb, aTask[i].x);
 357   }
 358   launch_thread(&err, &threads, stress2_workload19, (void*)zDb);
 359   launch_thread(&err, &threads, stress2_workload19, (void*)zDb);
 360
 361   join_all_threads(&err, &threads);
 362   sqlite3_enable_shared_cache(0);
 363   print_and_free_err(&err);
 364 }