/ Check-in [d904d293]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Change notify2.test to check that sqlite3_blocking_step() uses CPU more efficiently than sqlite3_step(), not that it results in greater overall throughput for any specific number of threads.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: d904d29354a5ed85d33bafe4a7143f3c5ecee790
User & Date: dan 2014-12-30 12:03:35
Context
2014-12-30
13:04
Make SQLITE_CONFIG_PCACHE_HDRSZ accurate (not an over-estimate) on 32-bit systems. check-in: 340b3477 user: drh tags: trunk
12:03
Change notify2.test to check that sqlite3_blocking_step() uses CPU more efficiently than sqlite3_step(), not that it results in greater overall throughput for any specific number of threads. check-in: d904d293 user: dan tags: trunk
00:57
Round all object sizes that go into computing SQLITE_CONFIG_PCACHE_HDRSZ up to a multiple of 8 bytes. check-in: b28ce75f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/notify2.test.

97
98
99
100
101
102
103


104
105
106
107
108
109
110
...
124
125
126
127
128
129
130

131
132
133
134
135
136
137
...
150
151
152
153
154
155
156

157
158
159
160
161
162
163
164
165
166
167
168
169
170
...
200
201
202
203
204
205
206
207


208
209
210
211
212
213
214
...
221
222
223
224
225
226
227
228








229
230
231
232
233
234
235
236
237


238









239
240
241
242
  opendb

  #after 2000

  # This loop runs for ~20 seconds.
  #
  set iStart [clock_seconds]


  while { ([clock_seconds]-$iStart) < $nSecond } {

    # Each transaction does 3 operations. Each operation is either a read
    # or write of a randomly selected table (t1, t2 or t3). Set the variables
    # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
    # each operation.
    #
................................................................................
            DROP INDEX IF EXISTS yyy.xxx_i;
      }
      ]]
    }

    # Execute the SQL transaction.
    #

    set rc [catch { execsql_blocking $::DB "
        BEGIN;
          $SQL(1);
          $SQL(2);
          $SQL(3);
        COMMIT;
      "
................................................................................
      # Hit some other kind of error. This is a malfunction.
      error $msg
    } else {
      # No error occurred. Check that any SELECT statements in the transaction
      # returned "1". Otherwise, the invariant was false, indicating that
      # some malfunction has occurred.
      foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }

    }
  }

  # Close the database connection and return 0.
  #
  sqlite3_close $::DB
  expr 0
}

foreach {iTest xStep xPrepare} {
  1 sqlite3_blocking_step sqlite3_blocking_prepare_v2
  2 sqlite3_step          sqlite3_nonblocking_prepare_v2
} {
  forcedelete test.db test2.db test3.db
................................................................................
  unset -nocomplain finished
  for {set ii 0} {$ii < $nThread} {incr ii} {
    thread_spawn finished($ii) $ThreadSetup $ThreadProgram
  }
  for {set ii 0} {$ii < $nThread} {incr ii} {
    do_test notify2-$iTest.2.$ii {
      if {![info exists finished($ii)]} { vwait finished($ii) }
      set finished($ii)


    } {0}
  }

  # Count the total number of succesful writes.
  do_test notify2-$iTest.3.1 {
    sqlite3 db test.db
    execsql {
................................................................................
           + (SELECT max(a) FROM t3)
    }]
    db close
  } {}
}

# The following tests checks to make sure sqlite3_blocking_step() is
# faster than sqlite3_step().  blocking_step() is always faster on








# multi-core and is usually faster on single-core.  But sometimes, by
# chance, step() will be faster on a single core, in which case the
# following test will fail.
#
puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()"
puts "interface helps multi-core systems to run faster.  This test sometimes"
puts "fails on single-core machines."
puts [array get anWrite]
do_test notify2-3 {


  expr {$anWrite(sqlite3_blocking_step) > $anWrite(sqlite3_step)}









} {1}

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test







>
>







 







>







 







>






|







 







|
>
>







 







|
>
>
>
>
>
>
>
>
|
|



|
|


>
>
|
>
>
>
>
>
>
>
>
>




97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
...
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
...
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
...
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
...
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
  opendb

  #after 2000

  # This loop runs for ~20 seconds.
  #
  set iStart [clock_seconds]
  set nOp 0
  set nAttempt 0
  while { ([clock_seconds]-$iStart) < $nSecond } {

    # Each transaction does 3 operations. Each operation is either a read
    # or write of a randomly selected table (t1, t2 or t3). Set the variables
    # $SQL(1), $SQL(2) and $SQL(3) to the SQL commands used to implement
    # each operation.
    #
................................................................................
            DROP INDEX IF EXISTS yyy.xxx_i;
      }
      ]]
    }

    # Execute the SQL transaction.
    #
    incr nAttempt
    set rc [catch { execsql_blocking $::DB "
        BEGIN;
          $SQL(1);
          $SQL(2);
          $SQL(3);
        COMMIT;
      "
................................................................................
      # Hit some other kind of error. This is a malfunction.
      error $msg
    } else {
      # No error occurred. Check that any SELECT statements in the transaction
      # returned "1". Otherwise, the invariant was false, indicating that
      # some malfunction has occurred.
      foreach r $msg { if {$r != 1} { puts "Invariant check failed: $msg" } }
      incr nOp
    }
  }

  # Close the database connection and return 0.
  #
  sqlite3_close $::DB
  list $nOp $nAttempt
}

foreach {iTest xStep xPrepare} {
  1 sqlite3_blocking_step sqlite3_blocking_prepare_v2
  2 sqlite3_step          sqlite3_nonblocking_prepare_v2
} {
  forcedelete test.db test2.db test3.db
................................................................................
  unset -nocomplain finished
  for {set ii 0} {$ii < $nThread} {incr ii} {
    thread_spawn finished($ii) $ThreadSetup $ThreadProgram
  }
  for {set ii 0} {$ii < $nThread} {incr ii} {
    do_test notify2-$iTest.2.$ii {
      if {![info exists finished($ii)]} { vwait finished($ii) }
      incr anSuccess($xStep) [lindex $finished($ii) 0]
      incr anAttempt($xStep) [lindex $finished($ii) 1]
      expr 0
    } {0}
  }

  # Count the total number of succesful writes.
  do_test notify2-$iTest.3.1 {
    sqlite3 db test.db
    execsql {
................................................................................
           + (SELECT max(a) FROM t3)
    }]
    db close
  } {}
}

# The following tests checks to make sure sqlite3_blocking_step() is
# faster than sqlite3_step(). "Faster" in this case means uses fewer
# CPU cycles. This is not always the same as faster in wall-clock time 
# for this type of test. The number of CPU cycles per transaction is 
# roughly proportional to the number of attempts made (i.e. one plus the 
# number of SQLITE_BUSY or SQLITE_LOCKED errors that require the transaction 
# to be retried). So this test just measures that a greater percentage of
# transactions attempted using blocking_step() succeed.
#
# The blocking_step() function is almost always faster on multi-core and is
# usually faster on single-core.  But sometimes, by chance, step() will be
# faster on a single core, in which case the
# following test will fail.
#
puts "The following test seeks to demonstrate that the sqlite3_unlock_notify()"
puts "interface helps multi-core systems to run more efficiently.  This test"
puts "sometimes fails on single-core machines."
puts [array get anWrite]
do_test notify2-3 {
  set blocking [expr {
    double($anSuccess(sqlite3_blocking_step)) /
    double($anAttempt(sqlite3_blocking_step)) 
  }]
  set non [expr {
    double($anSuccess(sqlite3_step)) /
    double($anAttempt(sqlite3_step)) 
  }]
  puts -nonewline [format " blocking: %.1f%% non-blocking %.1f%% ..." \
    [expr $blocking*100.0] [expr $non*100.0]]

  expr {$blocking > $non}
} {1}

sqlite3_enable_shared_cache $::enable_shared_cache
finish_test