Small performance optimization in sqlite3VdbeRecordCompareWithSkip() for
[sqlite.git] / test / tt3_stress.c
blobcdfab9c09ccc93add56840271bcd9c8b8ac25ed5
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.
11 *************************************************************************
18 ** Thread 1. CREATE and DROP a table.
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 */
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);
31 closedb(&err, &db);
32 print_and_free_err(&err);
33 return sqlite3_mprintf("ok");
37 ** Thread 2. Open and close database connections.
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_master;");
45 clear_error(&err, SQLITE_LOCKED);
46 closedb(&err, &db);
48 print_and_free_err(&err);
49 return sqlite3_mprintf("ok");
53 ** Thread 3. Attempt many small SELECT statements.
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 */
59 int i1 = 0;
60 int i2 = 0;
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);
70 closedb(&err, &db);
71 print_and_free_err(&err);
72 return sqlite3_mprintf("read t1 %d/%d attempts", i2, i1);
76 ** Thread 5. Attempt INSERT statements.
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);
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);
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));"
95 i1++;
96 if( err.rc ) i2++;
97 clear_error(&err, SQLITE_LOCKED);
98 clear_error(&err, SQLITE_ERROR);
100 closedb(&err, &db);
101 print_and_free_err(&err);
102 return sqlite3_mprintf("wrote t1 %d/%d attempts", i2, i1);
106 ** Thread 6. Attempt DELETE operations.
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);
113 int i1 = 0;
114 int i2 = 0;
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);
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);
128 closedb(&err, &db);
129 print_and_free_err(&err);
130 return sqlite3_mprintf("deleted from t1 %d/%d attempts", i2, i1);
134 static void stress1(int nMs){
135 Error err = {0};
136 Threadset threads = {0};
138 setstoptime(&err, nMs);
139 sqlite3_enable_shared_cache(1);
141 launch_thread(&err, &threads, stress_thread_1, 0);
142 launch_thread(&err, &threads, stress_thread_1, 0);
144 launch_thread(&err, &threads, stress_thread_2, 0);
145 launch_thread(&err, &threads, stress_thread_2, 0);
147 launch_thread(&err, &threads, stress_thread_3, 0);
148 launch_thread(&err, &threads, stress_thread_3, 0);
150 launch_thread(&err, &threads, stress_thread_4, 0);
151 launch_thread(&err, &threads, stress_thread_4, 0);
153 launch_thread(&err, &threads, stress_thread_5, 0);
154 launch_thread(&err, &threads, stress_thread_5, (void*)1);
156 join_all_threads(&err, &threads);
157 sqlite3_enable_shared_cache(0);
159 print_and_free_err(&err);
162 /**************************************************************************
163 ***************************************************************************
164 ** Start of test case "stress2"
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.
186 #define STRESS2_TABCNT 5 /* count1 in SDS test */
188 #define STRESS2_COUNT2 200 /* count2 in SDS test */
189 #define STRESS2_COUNT3 57 /* count2 in SDS test */
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
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);
203 static void stress2_workload3(Error *pErr, Sqlite *pDb, int i){
204 sql_script(pErr, pDb, "SELECT * FROM t0 WHERE z = 'small'");
207 static void stress2_workload4(Error *pErr, Sqlite *pDb, int i){
208 sql_script(pErr, pDb, "SELECT * FROM t0 WHERE z = 'big'");
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');"
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');"
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';"
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';"
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
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
249 static void stress2_workload11(Error *pErr, Sqlite *pDb, int i){
250 sql_script(pErr, pDb, "VACUUM");
253 static void stress2_workload14(Error *pErr, Sqlite *pDb, int i){
254 sql_script(pErr, pDb, "PRAGMA integrity_check");
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"
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_master;");
270 clear_error(&err, SQLITE_LOCKED);
271 closedb(&err, &db);
273 print_and_free_err(&err);
274 return sqlite3_mprintf("ok");
278 typedef struct Stress2Ctx Stress2Ctx;
279 struct Stress2Ctx {
280 const char *zDb;
281 void (*xProc)(Error*, Sqlite*, int);
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;
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++;
300 closedb(&err, &db);
303 print_and_free_err(&err);
304 return sqlite3_mprintf("ok %d/%d", i2, i1);
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 */
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);
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 },
337 const char *zDb = "test.db";
339 int i;
340 Error err = {0};
341 Sqlite db = {0};
342 Threadset threads = {0};
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);"
350 closedb(&err, &db);
352 setstoptime(&err, nMs);
353 sqlite3_enable_shared_cache(1);
355 for(i=0; i<sizeof(aTask)/sizeof(aTask[0]); i++){
356 stress2_launch_thread_loop(&err, &threads, zDb, aTask[i].x);
358 launch_thread(&err, &threads, stress2_workload19, (void*)zDb);
359 launch_thread(&err, &threads, stress2_workload19, (void*)zDb);
361 join_all_threads(&err, &threads);
362 sqlite3_enable_shared_cache(0);
363 print_and_free_err(&err);