Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add a Tcl script to run the performance tests described in README-server-edition.html. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | server-process-edition |
Files: | files | file ages | folders |
SHA3-256: |
1b3df8ffc551df0e4d8bcb633e1549ba |
User & Date: | dan 2017-08-29 17:52:14.719 |
Context
2018-03-28
| ||
15:41 | Update this branch with latest trunk changes. (check-in: df52e89fff user: dan tags: server-process-edition) | |
2017-08-29
| ||
17:52 | Add a Tcl script to run the performance tests described in README-server-edition.html. (check-in: 1b3df8ffc5 user: dan tags: server-process-edition) | |
2017-08-19
| ||
15:50 | Ensure that write-locks on pages are dropped at the end of each write transaction, even if there is still a read transaction open. (check-in: 2dd36ade9e user: dan tags: server-process-edition) | |
Changes
Changes to src/server.c.
︙ | ︙ | |||
713 714 715 716 717 718 719 720 721 722 723 724 725 726 | serverEndSingle(p); } p->eTrans = SERVER_TRANS_NONE; } return SQLITE_OK; } int sqlite3ServerPreCommit(Server *p, ServerPage *pPg){ ServerDb *pDb = p->pDb; int rc = SQLITE_OK; ServerPage *pIter; /* This should never be called in multi-process mode */ assert( pDb->pServerShm==0 ); | > > > > > > > > > > > | 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 | serverEndSingle(p); } p->eTrans = SERVER_TRANS_NONE; } return SQLITE_OK; } #if 0 static void dump_commit_list(ServerDb *pDb, int bRemove){ Server *pIter; printf("commitlist(%d):", bRemove); for(pIter=pDb->pCommit; pIter; pIter=pIter->pNext ){ printf(" %p", (void*)pIter); } printf("\n"); } #endif int sqlite3ServerPreCommit(Server *p, ServerPage *pPg){ ServerDb *pDb = p->pDb; int rc = SQLITE_OK; ServerPage *pIter; /* This should never be called in multi-process mode */ assert( pDb->pServerShm==0 ); |
︙ | ︙ | |||
771 772 773 774 775 776 777 | } } /* Add this connection to the list of current committers */ assert( p->pNext==0 ); p->pNext = pDb->pCommit; pDb->pCommit = p; | < | 782 783 784 785 786 787 788 789 790 791 792 793 794 795 | } } /* Add this connection to the list of current committers */ assert( p->pNext==0 ); p->pNext = pDb->pCommit; pDb->pCommit = p; sqlite3_mutex_leave(pDb->mutex); return rc; } /* ** Release all write-locks. */ |
︙ | ︙ | |||
797 798 799 800 801 802 803 | n -= ((p->iTransId + 1) << HMA_MAX_TRANSACTIONID); n |= ((u32)1 << p->iTransId); } if( o==n || serverCompareAndSwap(pSlot, o, n) ) break; } } if( pDb->pServerShm==0 ){ | | | 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 | n -= ((p->iTransId + 1) << HMA_MAX_TRANSACTIONID); n |= ((u32)1 << p->iTransId); } if( o==n || serverCompareAndSwap(pSlot, o, n) ) break; } } if( pDb->pServerShm==0 ){ Server **pp; /* If this connection is in the committers list, remove it. */ for(pp=&pDb->pCommit; *pp; pp = &((*pp)->pNext)){ if( *pp==p ){ *pp = p->pNext; break; } } |
︙ | ︙ |
Added tool/se_perf_test.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 | #!/usr/bin/tclsh # # This script is used to run the performance test cases described in # README-server-edition.html. # package require sqlite3 # Default values for command line switches: set O(-database) "" set O(-mode) "server" set O(-rows) [expr 5000000] set O(-tserver) "./tserver" set O(-seconds) 20 set O(-writers) 2 set O(-readers) 1 set O(-verbose) 0 proc error_out {err} { puts stderr $err exit -1 } proc usage {} { puts stderr "Usage: $::argv0 ?OPTIONS?" puts stderr "" puts stderr "Where OPTIONS are:" puts stderr " -database <database file> (default: test.$mode.db)" puts stderr " -mode server|begin-concurrent (default: server)" puts stderr " -rows <number of rows> (default: 5000000)" puts stderr " -tserver <path to tserver executable> (default: ./tserver)" puts stderr " -seconds <time to run for in seconds> (default: 20)" puts stderr " -writers <number of writer clients> (default: 2)" puts stderr " -readers <number of reader clients> (default: 1)" puts stderr " -verbose 0|1 (default: 0)" exit -1 } for {set i 0} {$i < [llength $argv]} {incr i} { set opt "" set arg [lindex $argv $i] set n [expr [string length $arg]-1] foreach k [array names ::O] { if {[string range $k 0 $n]==$arg} { if {$opt==""} { set opt $k } else { error_out "ambiguous option: $arg ($k or $opt)" } } } if {$opt==""} { usage } if {$i==[llength $argv]-1} { error_out "option requires an argument: $opt" } incr i set val [lindex $argv $i] switch -- $opt { -mode { if {$val != "server" && $val != "begin-concurrent" && $val != "wal" && $val != "persist" } { set xyz "\"server\", \"begin-concurrent\", \"wal\" or \"persist\"" error_out "Found \"$val\" - expected $xyz" } } } set O($opt) [lindex $argv $i] } if {$O(-database)==""} { set O(-database) "test.$O(-mode).db" } set O(-rows) [expr $O(-rows)] #-------------------------------------------------------------------------- # Create and populate the required test database, if it is not already # present in the file-system. # proc create_test_database {} { global O if {[file exists $O(-database)]} { sqlite3 db $O(-database) # Check the schema looks Ok. set s [db one { SELECT group_concat(name||pk, '.') FROM pragma_table_info('t1'); }] if {$s != "a1.b0.c0.d0"} { error_out "Database $O(-database) exists but is not usable (schema)" } # Check that the row count matches. set n [db one { SELECT count(*) FROM t1 }] if {$n != $O(-rows)} { error_out "Database $O(-database) exists but is not usable (row-count)" } db close } else { catch { file delete -force $O(-database)-journal } catch { file delete -force $O(-database)-wal } if {$O(-verbose)} { puts "Building database $O(-database)..." } sqlite3 db $O(-database) db eval { CREATE TABLE t1( a INTEGER PRIMARY KEY, b BLOB(16), c BLOB(16), d BLOB(400) ); CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c); WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<$O(-rows)) INSERT INTO t1 SELECT i-1, randomblob(16), randomblob(16), randomblob(400) FROM s; } if {$O(-mode)=="server"} { db eval "PRAGMA freelist_format = 2" } db close switch -- $O(-mode) { server { if {![file exists $O(-database)-journal]} { file mkdir $O(-database)-journal } } wal { sqlite3 db $O(-database) db eval {PRAGMA journal_mode = wal} db close } begin-concurrent { sqlite3 db $O(-database) db eval {PRAGMA journal_mode = wal} db close } } } } #------------------------------------------------------------------------- # Functions to start and stop the tserver process: # # tserver_start # tserver_stop # set ::tserver {} proc tserver_start {} { global O set ::tserver [open "|$O(-tserver) -vfs unix-excl $O(-database)"] fconfigure $::tserver -blocking 0 fileevent $::tserver readable tserver_data } proc tserver_data {} { global O if {[eof $::tserver]} { error_out "tserver has exited" } set line [gets $::tserver] if {$line != "" && $O(-verbose)} { puts "tserver: $line" } } proc tserver_stop {} { close $::tserver set fd [socket localhost 9999] puts $fd ".stop" close $fd } #------------------------------------------------------------------------- set ::nClient 0 set ::client_output [list] proc client_data {name fd} { global O if {[eof $fd]} { incr ::nClient -1 close $fd return } set str [gets $fd] if {[string trim $str]!=""} { if {[string range $str 0 3]=="### "} { lappend ::client_output [concat [list name $name] [lrange $str 1 end]] } if {$O(-verbose)} { puts "$name: $str" } } } proc client_launch {name script} { global O set fd [socket localhost 9999] fconfigure $fd -blocking 0 switch -- $O(-mode) { persist { puts $fd "PRAGMA journal_mode = PERSIST;" } } puts $fd "PRAGMA synchronous = OFF;" puts $fd ".repeat 1" puts $fd ".run" puts $fd $script puts $fd ".seconds $O(-seconds)" puts $fd ".run" puts $fd ".quit" flush $fd incr ::nClient fileevent $fd readable [list client_data $name $fd] } proc client_wait {} { while {$::nClient>0} {vwait ::nClient} } proc script_writer {} { global O set commit "COMMIT;" set begin "BEGIN;" if {$O(-mode)=="begin-concurrent" || $O(-mode)=="wal"} { set commit ".mutex_commit" set begin "BEGIN CONCURRENT;" } if {$O(-mode)=="server"} { set beginarg "READONLY" } set tail "randomblob(16), randomblob(16), randomblob(400));" return [subst -nocommands { $begin REPLACE INTO t1 VALUES(abs(random() % $O(-rows)), $tail REPLACE INTO t1 VALUES(abs(random() % $O(-rows)), $tail REPLACE INTO t1 VALUES(abs(random() % $O(-rows)), $tail REPLACE INTO t1 VALUES(abs(random() % $O(-rows)), $tail REPLACE INTO t1 VALUES(abs(random() % $O(-rows)), $tail $commit }] } proc script_reader {} { global O set beginarg "" if {$O(-mode)=="server"} { set beginarg "READONLY" } return [subst -nocommands { BEGIN $beginarg; SELECT * FROM t1 WHERE a>abs((random()%$O(-rows))) LIMIT 10; SELECT * FROM t1 WHERE a>abs((random()%$O(-rows))) LIMIT 10; SELECT * FROM t1 WHERE a>abs((random()%$O(-rows))) LIMIT 10; SELECT * FROM t1 WHERE a>abs((random()%$O(-rows))) LIMIT 10; SELECT * FROM t1 WHERE a>abs((random()%$O(-rows))) LIMIT 10; END; }] } create_test_database tserver_start for {set i 0} {$i < $O(-writers)} {incr i} { client_launch w.$i [script_writer] } for {set i 0} {$i < $O(-readers)} {incr i} { client_launch r.$i [script_reader] } client_wait set name(w) "Writers" set name(r) "Readers" foreach r $::client_output { array set a $r set type [string range $a(name) 0 0] incr x($type.ok) $a(ok); incr x($type.busy) $a(busy); incr x($type.n) 1 set t($type) 1 } foreach type [array names t] { set nTPS [expr $x($type.ok) / $O(-seconds)] set nC [expr $nTPS / $x($type.n)] set nTotal [expr $x($type.ok) + $x($type.busy)] set bp [format %.2f [expr $x($type.busy) * 100.0 / $nTotal]] puts "$name($type): $nTPS transactions/second ($nC per client) ($bp% busy)" } tserver_stop |
Changes to tool/tserver.c.
︙ | ︙ | |||
30 31 32 33 34 35 36 37 38 39 40 41 42 43 | ** .list Display all SQL statements in the list. ** .quit Disconnect. ** .run Run all SQL statements in the list. ** .repeats N Configure the number of repeats per ".run". ** .seconds N Configure the number of seconds to ".run" for. ** .mutex_commit Add a "COMMIT" protected by a g.commit_mutex ** to the current SQL. ** ** Example input: ** ** BEGIN; ** INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); ** INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); ** INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); | > | 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | ** .list Display all SQL statements in the list. ** .quit Disconnect. ** .run Run all SQL statements in the list. ** .repeats N Configure the number of repeats per ".run". ** .seconds N Configure the number of seconds to ".run" for. ** .mutex_commit Add a "COMMIT" protected by a g.commit_mutex ** to the current SQL. ** .stop Stop the tserver process - exit(0). ** ** Example input: ** ** BEGIN; ** INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); ** INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); ** INSERT INTO t1 VALUES(randomblob(10), randomblob(100)); |
︙ | ︙ | |||
65 66 67 68 69 70 71 72 73 74 75 76 77 78 | #define TSERVER_DEFAULT_CHECKPOINT_THRESHOLD 3900 /* Global variables */ struct TserverGlobal { char *zDatabaseName; /* Database used by this server */ char *zVfs; sqlite3_mutex *commit_mutex; /* The following use native pthreads instead of a portable interface. This ** is because a condition variable, as well as a mutex, is required. */ pthread_mutex_t ckpt_mutex; pthread_cond_t ckpt_cond; int nThreshold; /* Checkpoint when wal is this large */ int bCkptRequired; /* True if wal checkpoint is required */ | > | 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | #define TSERVER_DEFAULT_CHECKPOINT_THRESHOLD 3900 /* Global variables */ struct TserverGlobal { char *zDatabaseName; /* Database used by this server */ char *zVfs; sqlite3_mutex *commit_mutex; sqlite3 *db; /* Global db handle */ /* The following use native pthreads instead of a portable interface. This ** is because a condition variable, as well as a mutex, is required. */ pthread_mutex_t ckpt_mutex; pthread_cond_t ckpt_cond; int nThreshold; /* Checkpoint when wal is this large */ int bCkptRequired; /* True if wal checkpoint is required */ |
︙ | ︙ | |||
293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 | g.nWait--; } } pthread_mutex_unlock(&g.ckpt_mutex); } if( rc==SQLITE_OK ){ send_message(p, "ok (%d/%d SQLITE_BUSY)\n", nBusy, j); } clear_sql(p); pthread_mutex_lock(&g.ckpt_mutex); g.nRun--; pthread_mutex_unlock(&g.ckpt_mutex); | > > > > | 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 | g.nWait--; } } pthread_mutex_unlock(&g.ckpt_mutex); } if( rc==SQLITE_OK ){ int nMs = (int)(get_timer() - t0); send_message(p, "ok (%d/%d SQLITE_BUSY)\n", nBusy, j); if( p->nRepeat<=0 ){ send_message(p, "### ok %d busy %d ms %d\n", j-nBusy, nBusy, nMs); } } clear_sql(p); pthread_mutex_lock(&g.ckpt_mutex); g.nRun--; pthread_mutex_unlock(&g.ckpt_mutex); |
︙ | ︙ | |||
346 347 348 349 350 351 352 | rc = send_message(p, "ok (repeat=%d)\n", p->nRepeat); } else if( n>=2 && n<=3 && 0==strncmp(z, "run", n) ){ rc = handle_run_command(p); } | | > > > > > | 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 | rc = send_message(p, "ok (repeat=%d)\n", p->nRepeat); } else if( n>=2 && n<=3 && 0==strncmp(z, "run", n) ){ rc = handle_run_command(p); } else if( n>=2 && n<=7 && 0==strncmp(z, "seconds", n) ){ if( nArg ){ p->nSecond = strtol(zArg, 0, 0); if( p->nSecond>0 ) p->nRepeat = 0; } rc = send_message(p, "ok (repeat=%d)\n", p->nRepeat); } else if( n>=1 && n<=12 && 0==strncmp(z, "mutex_commit", n) ){ rc = handle_some_sql(p, "COMMIT;", 7); if( rc==SQLITE_OK ){ p->aPrepare[p->nPrepare-1].bMutex = 1; } } else if( n>=2 && n<=4 && 0==strncmp(z, "stop", n) ){ sqlite3_close(g.db); exit(0); } else{ send_message(p, "unrecognized dot command: %.*s\n" "should be \"list\", \"run\", \"repeats\", \"mutex_commit\" " "or \"seconds\"\n", n, z ); |
︙ | ︙ | |||
463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 | memmove(zCmd, &zCmd[nConsume], nCmd); } } }while( rc==SQLITE_OK && nConsume>0 ); } fprintf(stdout, "Client %d disconnects\n", ctx.fd); close(ctx.fd); clear_sql(&ctx); sqlite3_free(ctx.aPrepare); sqlite3_close(ctx.db); return 0; } static void usage(const char *zExec){ fprintf(stderr, "Usage: %s ?-vfs VFS? DATABASE\n", zExec); exit(1); } int main(int argc, char *argv[]) { | > < | 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 | memmove(zCmd, &zCmd[nConsume], nCmd); } } }while( rc==SQLITE_OK && nConsume>0 ); } fprintf(stdout, "Client %d disconnects\n", ctx.fd); fflush(stdout); close(ctx.fd); clear_sql(&ctx); sqlite3_free(ctx.aPrepare); sqlite3_close(ctx.db); return 0; } static void usage(const char *zExec){ fprintf(stderr, "Usage: %s ?-vfs VFS? DATABASE\n", zExec); exit(1); } int main(int argc, char *argv[]) { int sfd; int rc; int yes = 1; struct sockaddr_in server; /* Ignore SIGPIPE. Otherwise the server exits if a client disconnects ** abruptly. */ |
︙ | ︙ | |||
501 502 503 504 505 506 507 | g.zDatabaseName = argv[argc-1]; g.commit_mutex = sqlite3_mutex_alloc(SQLITE_MUTEX_FAST); g.nThreshold = TSERVER_DEFAULT_CHECKPOINT_THRESHOLD; pthread_mutex_init(&g.ckpt_mutex, 0); pthread_cond_init(&g.ckpt_cond, 0); | | | | | | 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 | g.zDatabaseName = argv[argc-1]; g.commit_mutex = sqlite3_mutex_alloc(SQLITE_MUTEX_FAST); g.nThreshold = TSERVER_DEFAULT_CHECKPOINT_THRESHOLD; pthread_mutex_init(&g.ckpt_mutex, 0); pthread_cond_init(&g.ckpt_cond, 0); rc = sqlite3_open_v2(g.zDatabaseName, &g.db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, g.zVfs ); if( rc!=SQLITE_OK ){ fprintf(stderr, "sqlite3_open(): %s\n", sqlite3_errmsg(g.db)); return 1; } rc = sqlite3_exec(g.db, "SELECT * FROM sqlite_master", 0, 0, 0); if( rc!=SQLITE_OK ){ fprintf(stderr, "sqlite3_exec(): %s\n", sqlite3_errmsg(g.db)); return 1; } sfd = socket(AF_INET, SOCK_STREAM, 0); if( sfd<0 ){ fprintf(stderr, "socket() failed\n"); return 1; |
︙ | ︙ | |||
553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 | int cfd = accept(sfd, NULL, NULL); if( cfd<0 ){ perror("accept()"); return 1; } fprintf(stdout, "Client %d connects\n", cfd); rc = pthread_create(&tid, NULL, handle_client, (void*)(intptr_t)cfd); if( rc!=0 ){ perror("pthread_create()"); return 1; } pthread_detach(tid); } return 0; } | > | 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 | int cfd = accept(sfd, NULL, NULL); if( cfd<0 ){ perror("accept()"); return 1; } fprintf(stdout, "Client %d connects\n", cfd); fflush(stdout); rc = pthread_create(&tid, NULL, handle_client, (void*)(intptr_t)cfd); if( rc!=0 ){ perror("pthread_create()"); return 1; } pthread_detach(tid); } return 0; } |