[sqlite] Why SQLite take lower performance in multi-threadSELECTing?

[sanhua.zh]

it seems that this mail list will filter my code. I re-send it now.
It?s written by Objective-C and C, but it?s quite simple to understand.


void sqliteLog(void* userInfo, int retCode, const char* text)
{
  if (retCode != SQLITE_OK) {
    NSLog(@"SQLITE FAILED errCode=%d, errMsg=%s", retCode, text);
  }
}


@implementation TestCase {
  NSString* _path;
  NSUInteger _threadCount;
}


- (id)init
{
  if (self = [super init]) {
    NSString* document = 
NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, 
YES)[0];
    _path = [document stringByAppendingPathComponent:@"test.sqlite"];


    _threadCount = 4;
  }
  return self;
}


- (void)clear
{
  //remove existing DB file
  NSFileManager* fm = [NSFileManager defaultManager];
  NSArray* paths = @[_path, [_path stringByAppendingString:@"-shm"], [_path 
stringByAppendingString:@"-wal"], [_path stringByAppendingString:@"-journal"]];
  for (NSString* path in paths) {
    if ([fm fileExistsAtPath:path]) {
      NSLog(@"file exists %@", path);
      NSError* error;
      if (![fm removeItemAtPath:path error:error]) {
        NSLog(@"remove error %@", error);
        exit(0);
      }
    }
  }
}


- (void)prepare
{
  [self clear];


  sqlite3_config(SQLITE_CONFIG_LOG, sqliteLog, NULL);
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


  sqlite3* handle;
  sqlite3_open(_path.UTF8String, handle);
  //pragma
  sqlite3_exec(handle, "PRAGMA LOCKING_MODE=NORMAL;", NULL, NULL, NULL);
  sqlite3_exec(handle, "PRAGMA JOURNAL_MODE=WAL;", NULL, NULL, NULL);
  sqlite3_exec(handle, "PRAGMA SYNCHRONOUS=FULL;", NULL, NULL, NULL);
  //create table
  sqlite3_exec(handle, "CREATE TABLE testtable (id INT PRIMARY KEY);", NULL, 
NULL, NULL);
  //pre insert
  sqlite3_exec(handle, "BEGIN", NULL, NULL, NULL);
  sqlite3_stmt* stmt;
  sqlite3_prepare_v2(handle, "INSERT INTO testtable (id) VALUES (?);", -1, 
stmt, NULL);
  for (int i = 0; i  1000000; i++) {
    sqlite3_bind_int(stmt, 1, i);
    sqlite3_step(stmt);
    sqlite3_reset(stmt);
  }
  sqlite3_exec(handle, "COMMIT", NULL, NULL, NULL);
  sqlite3_finalize(stmt);


  sqlite3_close(handle);
}


- (void)test
{
  [self prepare];


  NSRecursiveLock* lock = [[NSRecursiveLock alloc] init];
  __block NSUInteger threadWait = _threadCount;
  for (int i = 0; i  _threadCount; i++) {
    NSString* threadname = [NSString stringWithFormat:@"test_thread_%d", i];
    dispatch_async(dispatch_queue_create(threadname.UTF8String, 
DISPATCH_QUEUE_CONCURRENT), ^{
      NSString* start = [NSString stringWithFormat:@"%@ start", [NSThread 
currentThread]];
      NSString* end = [NSString stringWithFormat:@"%@ end", [NSThread 
currentThread]];


      sqlite3* handle;
      sqlite3_open(_path.UTF8String, handle);


      //wait for all thread
      [lock lock];
      threadWait--;
      [lock unlock];
      while (threadWait);

      //begin
      [Ticker tickWithInfo:start];
      sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL);
      [Ticker stopWithInfo:end];


      sqlite3_close(handle);
    });
  }
}


@end






????
???:Richard Hippdrh at sqlite.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
????:2015?11?1?(??)?03:45
??:Re: [sqlite] Why SQLite take lower performance in multi-threadSELECTing?


On 10/30/15, sanhua.zh sanhua.zh at foxmail.com wrote:  Hi, all  I use SQLite 
in iOS. I found that SQLite will take lower performancein  multi-thread 
SELECTing.  Here is my test result,  It costs 0.11s to select 100,0000 
elements,in 1-thread SELECTing:  But the sameIn 4-thread SELECTing, it costs 
0.2s avg.    This test run on iPhone 6s.  You can see that 4-thread is take 
almost twice slower than 1-thread.  I know multi-thread might costs the system 
resource, but this result is much  slower than what I excepted.  Is there some 
race condition in SQLite or I write the wrong code ?  You *might* be doing 
something wrong. It is hard to say without seeing your code. But the 2x 
slowdown might be entirely due to thread contention and mutexing. Remember that 
the flash memory filesystem on iOS has finite bandwidth, and all the threads in 
the world will not increase that bandwidth. The extra threads just cause extra 
overhead which ends up slowing everything down. -- D. Richard Hipp drh at 
sqlite.org _______________________________________________ sqlite-users mailing 
list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users