checking on the heap allocation greedy-ness

test_sqlite5.cc

#define _MULTI_THREADED

#include <sqlite3.h>
#include <stdio.h>
#include <unistd.h>
#include <pthread.h>
#include <stdlib.h>

int print_row_cb(void *NotUsed, int argc, char **argv, char **azColName) {
  NotUsed = 0;

  for (int i = 0; i < argc; i++) {
    printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
  }

  return 0;
}

int empty_rows_cb(void *NotUsed, int argc, char **argv, char **azColName) {
  return 0;
}


void check(const char *str, int err) {
  if (err) {
    fprintf(stderr, "[%d] %s: err=%d\n", gettid(), str, err);
    exit(1);
  }
}

void sqlite_check(const char *str, int err) {
  if (err != SQLITE_OK) {
    fprintf(stderr, "[%d] %s: err=%d\n", gettid(), str, err);
    exit(1);
  }
}

void print_mem_usage() {
  static const char *names[] = {
    "SQLITE_STATUS_MEMORY_USED"       ,
    "SQLITE_STATUS_PAGECACHE_USED"     ,
    "SQLITE_STATUS_PAGECACHE_OVERFLOW" ,
    "SQLITE_STATUS_SCRATCH_USED"       ,
    "SQLITE_STATUS_SCRATCH_OVERFLOW"   ,
    "SQLITE_STATUS_MALLOC_SIZE"        ,
    "SQLITE_STATUS_PARSER_STACK"       ,
    "SQLITE_STATUS_PAGECACHE_SIZE"     ,
    "SQLITE_STATUS_SCRATCH_SIZE"       ,
    "SQLITE_STATUS_MALLOC_COUNT"
  };

  static int ops[] = {
    SQLITE_STATUS_MEMORY_USED        ,
    SQLITE_STATUS_PAGECACHE_USED     ,
    SQLITE_STATUS_PAGECACHE_OVERFLOW ,
    SQLITE_STATUS_SCRATCH_USED       ,
    SQLITE_STATUS_SCRATCH_OVERFLOW   ,
    SQLITE_STATUS_MALLOC_SIZE        ,
    SQLITE_STATUS_PARSER_STACK       ,
    SQLITE_STATUS_PAGECACHE_SIZE     ,
    SQLITE_STATUS_SCRATCH_SIZE       ,
    SQLITE_STATUS_MALLOC_COUNT
  };

  int cur, high;

  printf("Current status:\n");
  for (size_t i = 0; i < 10; i++) {
    sqlite_check(names[i], sqlite3_status(ops[i], &cur, &high, 0));
    printf("  %s = %d / %d\n", names[i], cur, high);
  }
}

int exec(sqlite3 *db, const char *sql, int (*callback)(void*,int,char**,char**)) {
  char *err_msg = 0;

  int rc = sqlite3_exec(db, sql, callback, 0, &err_msg);
  if (rc != SQLITE_OK) {
    fprintf(stderr, "[%d] SQL %s error: %s\n", gettid(), sql, err_msg);

    sqlite3_free(err_msg);
    sqlite3_close(db);

    return 1;
  }

  return 0;
}

// clang -O3 -lsqlite3 ./test_sqlite_int.cc && /usr/bin/time ./a.out
int main(void) {
  sqlite3 *db;

  int rc = sqlite3_open("/tmp/test.db", &db);
  if (rc != SQLITE_OK) {
    fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
    sqlite3_close(db);
    return 1;
  }

  // print_mem_usage();

  // for (size_t i = 0; i < 100000; i ++) {
  //   char str[256];
  //   sprintf(str, "SELECT * FROM t WHERE id = %zu", i);
  //   exec(db, str, NULL);
  // }

  print_mem_usage();

  if (exec(db, "PRAGMA soft_heap_limit=1048576", print_row_cb)) return 1;
  if (exec(db, "PRAGMA hard_heap_limit=10485760", print_row_cb)) return 1;
  if (exec(db, "PRAGMA soft_heap_limit", print_row_cb)) return 1;
  if (exec(db, "PRAGMA hard_heap_limit", print_row_cb)) return 1;

  print_mem_usage();

  for (size_t i = 0; i < 100000; i ++) {
    char str[256];
    sprintf(str, "SELECT * FROM t WHERE id = %zu", i);
    exec(db, str, NULL);
  }

  print_mem_usage();

  exec(db, "SELECT * FROM t", empty_rows_cb);

  print_mem_usage();

  exec(db, "select * from t as A cross join t as B limit 100000", empty_rows_cb);

  print_mem_usage();

  exec(db,
    "WITH RECURSIVE"
    " fibo (curr, next) "
    "AS "
    "( SELECT 1,1"
    "  UNION ALL"
    "  SELECT next, curr+next FROM fibo"
    "  LIMIT  67108864 ) "
    "SELECT curr, next FROM fibo LIMIT 1 OFFSET 67108864-1"
    , empty_rows_cb);

  print_mem_usage();

  exec(db,
    "WITH RECURSIVE"
    " fibo (curr, next) "
    "AS "
    "( SELECT 1,1"
    "  UNION ALL"
    "  SELECT next, curr+next FROM fibo"
    "  LIMIT  67108864 ) "
    "SELECT curr, next FROM fibo"
    , empty_rows_cb);

  print_mem_usage();

  sqlite3_close(db);

  return 0;
}

rb companion:

require 'tempfile'
require 'sqlite3'

# Thread.DEBUG=ENV["THREAD_DEBUG"].to_i

def sql_fibo(n)
  <<-SQL
    WITH RECURSIVE
      fibo (curr, next)
    AS
    ( SELECT 1,1
      UNION ALL
      SELECT next, curr+next FROM fibo
      LIMIT #{n} )
    SELECT curr, next FROM fibo LIMIT 1 OFFSET #{n}-1;
  SQL
end

def exec(db, sql)
  puts "#{sql}: #{db.execute(sql)}"
end

def fire(x)
  t = Thread.new do
    previous_time = Time.now
    300.times do |i|
      puts("started - #{i}")
      sleep(1);
      puts("slept - #{i}")
      time_now = Time.now
      time_diff = (previous_time - time_now)
      if time_diff > 3
         puts("waiter- #{i} - thread unscheduled for #{time_diff}")
      end
      previous_time = time_now
    end
  end

  f = "/tmp/test.db" # ":memory:"
  if File.exists? f
    db = SQLite3::Database.open f
  else
    db = SQLite3::Database.new f
    puts "executing at #{f}"

    exec(db, "PRAGMA soft_heap_limit=#{1*1024*1024}")
    exec(db, "PRAGMA hard_heap_limit=#{10*1024*1024}")

    exec(db, "PRAGMA soft_heap_limit")
    exec(db, "PRAGMA hard_heap_limit")

    exec(db, "CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, s TEXT);")

    val = "x" * 1024
    sql = "INSERT INTO t (s) VALUES ('#{val}');" * 1024
    (1024).times do |i|
      puts "#{i}..."
      db.execute_batch(sql)
      puts `ps u #{Process.pid} | grep #{Process.pid}`
    end
  end

  exec(db, "PRAGMA soft_heap_limit=#{1*1024*1024}")
  exec(db, "PRAGMA hard_heap_limit=#{1024*1024*1024}")

  exec(db, "PRAGMA soft_heap_limit")
  exec(db, "PRAGMA hard_heap_limit")

  puts "inited"

  exec(db, "SELECT * FROM t as A INNER JOIN t AS B ORDER BY s DESC LIMIT 100")

  # while true do
    # exec(db, "SELECT * FROM t WHERE id = #{(rand * 1024*1024).to_i}")
    # exec(db, "SELECT * FROM t WHERE s = '#{(rand * 1024*1024).to_i}'")
  # end

  puts "done"
ensure
  t.kill
  puts "closed"
end

fire 2**26