SQLite using ops

[anishsapkota]

Hi,
I am trying to build SQLite image locally using the SQLite ELF.
I used this command: ops pkg from-run --name <name of pkg> --version <version> <path to ELF>

it created a local pkg for me:
which I tried executing using : ops pkg load --local <name_version> -a test.db

Terminal output:

running local instance
booting /home/ubuntu/.ops/images/sqlite3 ...
en1: assigned 10.0.2.15

so the sqlite cli is not running.. please help

[eyberg]

what exactly are you wanting to do?

you can do something like this:

eyberg@box:~$ ops run /usr/bin/sqlite -c config.json
running local instance
booting /home/eyberg/.ops/images/sqlite ...
en1: assigned 10.0.2.15
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             //bob.db
1    temp             //./sqlite_BeB6x9eMg6LH6uI
eyberg@box:~$ cat config.json
{
  "Args": ["bob.db", ".databases"],
  "Files": ["bob.db"]
}

but you should know that running sqlite (w/out sql) and just passing a test.db will put you into an interactive mode which we don't support as these are intended to run as servers/daemons or non-interactive jobs that do work and spin down

if you want to incorporate sqlite into your application all languages have bindings for that, if you want to run a sqlite server
you could run something like sqld - https://repo.ops.city/v2/packages/eyberg/sqld/0.1.0/x86_64/show

[anishsapkota]

.timer on works on normal sqlite3

[eyberg]

I believe the timer only works inside the sqlite shell itself, not passed as a command line arg

if you are benchmarking I'd encourage you to implement this functionality inside the language itself

[anishsapkota]

Hi,
is it possible to store bob.db file inside a volume and link it to a sqlite image? I could not figure it out.
I still have this issue that the changes made to the db are lost after the instance shuts down. :(
if I start a instance with state 1 and do some inserts and move it to state 2. I expect the new instance to start from state 2 not state 1.

[anishsapkota]

it seems like the changes made to the files passed as FILES : ["bob.db"], do not reflect on original "bob.db" file

[anishsapkota]

I am also trying to explore sqld, ops pkg get eyberg/sqld:0.1.0, but this command gets me package not found

[anishsapkota]

I tried a new approach with c, so this is csqlite.c file.
I compiled it with gcc csqlite.c -o csqlite -lsqlite3
It works as expected in normal environment.
so I packaged it using ops, : ops pkg from-run --name csqlite --version 1.1 csqlite

I tried to run it using ops pkg load --local csqlite_1.1 -c config.json

// config.json

{
  "BaseVolumeSz": "400m",
  "Args":  ["TPC-H-small.db"],
  "Files": ["TPC-H-small.db"],
  "Dirs":["TPCH-sqlite","outputs", "usr"]
}

usr folder :

. -lib -libsqlite3.so.0 1 directory, 1 file
// csqlite.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <sys/time.h>
#include <time.h>

#define NUM_QUERIES 22
#define QUERY_DIR "./TPCH-sqlite/queries/"
#define OUTPUT_NANOS "./outputs/nanos_sqlite/"

void execute_query(sqlite3 *db, const char *query, const char *output_file) {
    char *errMsg = 0;
    FILE *fp = fopen(output_file, "w");
    if (!fp) {
        fprintf(stderr, "Can't open output file %s\n", output_file);
        return;
    }

    // Callback to write query results to the output file
    int callback(void *data, int argc, char **argv, char **azColName) {
        for (int i = 0; i < argc; i++) {
            fprintf(fp, "%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
        }
        fprintf(fp, "\n");
        return 0;
    }

    struct timeval start, end;
    gettimeofday(&start, NULL);

    int rc = sqlite3_exec(db, query, callback, 0, &errMsg);
    
    gettimeofday(&end, NULL);
    fclose(fp);

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        sqlite3_free(errMsg);
    } else {
        long seconds = (end.tv_sec - start.tv_sec);
        long micros = ((seconds * 1000000) + end.tv_usec) - (start.tv_usec);
        printf("Query execution time: %ld seconds %ld microseconds\n", seconds, micros);
    }
}

int main(int argc, char *argv[]) {
    if (argc < 2) {
        fprintf(stderr, "Usage: %s [db_file]\n", argv[0]);
        return 1;
    }

    const char *db_file = argv[1];

    sqlite3 *db;
    int rc = sqlite3_open(db_file, &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return 1;
    } else {
        fprintf(stderr, "Opened database successfully\n");
    }

    char query_file[256];
    char query[4096];
    char output_file[256];

    for (int i = 1; i <= NUM_QUERIES; i++) {
        snprintf(query_file, sizeof(query_file), QUERY_DIR "query%d.sql", i);
        snprintf(output_file, sizeof(output_file), "%d.txt", i);

        FILE *fp = fopen(query_file, "r");
        if (!fp) {
            fprintf(stderr, "Can't open query file %s\n", query_file);
            continue;
        }

        // Read the query file into a string
        size_t len = fread(query, 1, sizeof(query), fp);
        query[len] = '\0';  // Ensure null termination
        fclose(fp);

	printf("Preparing....Query %d\n", i);
	printf("Executing query %d on sqlite3...\n", i);
	snprintf(output_file, sizeof(output_file), OUTPUT_NORMAL "query%d.txt", i);
	execute_query(db, query, output_file);
    }

    sqlite3_close(db);
    return 0;
}

I am getting this error:

`
ubuntu@ubuntu:~/DBPRO$ ops pkg load --local csqlite_1.1 -c config_1.json
running local instance
booting /home/ubuntu/.ops/images/csqlite ...
en1: assigned 10.0.2.15
Opened database successfully
Preparing....Query 1
Executing query 1 on sqlite3...
en1: assigned FE80::D029:89FF:FEA4:B3C

*** signal 11 received by tid 2, errno 0, code 2
fault address 0xffef69b8

*** Thread context:

 frame: 00ff800042200800
  type: thread
  spsr: 0000000060000800
   esr: 000000008200000f instruction abort in el0
   far: 00000000ffef69b8
   elr: 00000000ffef69b8
   far: 00000000ffef69b8

active_cpu: 00000000ffffffff
stack top: 0000000000000000

   x0: 0000000000000000  q0: 400057f1eb7464001c358203b0000000
   x1: 000000000000000a  q1: 40024000000000000000000000000000
   x2: 00000000004ba1a8  q2: 0000000000000000000000003f800000
   x3: 00000000004ba158  q3: f00ff00ff00ff00f000f0000000f0000
   x4: 00000000004b82b8  q4: f00ff00ff00ff00ff00ff00ff00ff00f
   x5: 000000000000017f  q5: ffffffff0f000f00ffffffff0f000f00
   x6: fefefefefefefeff  q6: 00000000ccccccc000000000ccccccc0
   x7: 7f7f7f7f7f7f7f7f
   x8: 0101010101010101
   x9: cccccccccccccccd
  x10: 000000000002414e
  x11: 0000000000000000
  x12: 3935343431303530
  x13: 3534353433363037
  x14: 0000000000000000
  x15: 0000000000000000
  x16: 0000061118441d68
  x17: 0000002edde921b0
  x18: 0000000100000000
  x19: 00000000004ba1f8
  x20: 000000000000000a
  x21: 00000000ffef69b8
  x22: 00000000004ba1a8
  x23: 00000000004ba158
  x24: 00000000004b8308
  x25: 0000000000000000
  x26: 00000000ffef6978
  x27: 00000000ffef6930
  x28: 000000000000000a
  x29: 00000000ffef68b0
  x30: 0000061118384b84
   sp: 00000000ffef68b0
 fpsr: 00ff000000022f38
 fpcr: 00ff000000000420

frame trace:
00000000ffef68b8: 000000000042de40
00000000ffef6948: 000000000042e0f8
00000000ffef69f8: 0000002edde373fc
00000000ffef7c58: 0000002edde374cc
00000000ffef7d68: 000000000042db70

kernel load offset ffffffffa14d8000

loaded klibs:

stack trace:
00000000ffef68b0: 00000000ffef6940
00000000ffef68b8: 000000000042de40
00000000ffef68c0: 00000000ffef7dc8
00000000ffef68c8: 0000000000000002
00000000ffef68d0: 000000000043ecf8
00000000ffef68d8: 0000160243f06040
00000000ffef68e0: 000000000042df04
00000000ffef68e8: 0000002eddfaa000
00000000ffef68f0: 0000000000000000
00000000ffef68f8: 00000000ffef7de0
00000000ffef6900: 000000000043ecf8
00000000ffef6908: 0000000000000000
00000000ffef6910: 00000000ffef7dc8
00000000ffef6918: 00000611183f4d90
00000000ffef6920: 000000000043ecf8
00000000ffef6928: 00000000004c1078
00000000ffef6930: 00000000ffef6eac
00000000ffef6938: fc23b15fe2df8d00
00000000ffef6940: 00000000ffef69f0
00000000ffef6948: 000000000042e0f8
00000000ffef6950: 2e2e2e336574696c
00000000ffef6958: 00000000ffef6b48
00000000ffef6960: 00000000ffef6c48
00000000ffef6968: 00000000004b8308
00000000ffef6970: 0000000000000000
00000000ffef6978: 0000000000000000
00000000ffef6980: f00ff00ff00ff00f
00000000ffef6988: f00ff00ff00ff00f
00000000ffef6990: 000000006650ec2d
00000000ffef6998: 0000000000010c00
00000000ffef69a0: ccccccc0cc000000
00000000ffef69a8: ccccccc0cc000000

core dump
`

[eyberg]

so there are a handful of issues you are running into here:

1. for 'pkg not found' on sqld - the vast majority of the stuff on
   repo.ops.city is x86 not arm64, so for instance on my mac:

➜  ~ ops pkg get eyberg/sqld:0.1.0
package not found
➜  ~ ops pkg get eyberg/sqld:0.1.0 --arch=amd64
 100% |████████████████████████████████████████|
➜  ~

you'll want to specify the proper arch for what the pkg is if it's not the architecture that you are currently on (being detected)

2. I think one of the issues you are running into is that when you do a
   'ops pkg load' you are creating both an image and an instance at the
   same time - any arguments you specify are put into the image when you
   run it; (there are ways around this, eg: https://docs.ops.city/ops/klibs#cloud-init-http-s-to-file-env-klib but that's a bit more complicated than what we should look at right now);

by default, while you can do a '-s' or '--skipbuild' that will prevent new
args from being applied; so what this means is that you can't simply
pass a 'insert' and then a 'select' on different runs and get diff
results; what you could possibly do is a 'insert' then do a 'ops image
cp' to cp the file .db back; to elaborate when you do a 'ops image
create' you are creating a new 'disk image' and when you do a 'ops
instance create' you are creating a new 'virtual machine' that is
completely separate from your local filesystem and your normal 'real'/physical machine;

you have to copy the files out of it to get your results; considering your use-case is that
you want to benchmark i'd put both of these under the same program and
run it together

3. the c example you pasted has a nested function which I don't think is
   going to work here by default; we disable stack exec by default and to turn it on
   you need to compile nanos specifically for it as shown in
   cannot enable executable stack as shared object requires: Permission denied nanos#1993 (comment) - basically you just need to flag that 'VMAP_FLAG_EXEC' - we disable this by default because it's heavily abused - w/out this i was able to repro what you saw but w/it on it worked

[anishsapkota]

Thank you, I actually took out the nested function making it static and it works now. Thank you very much . :)

[anishsapkota]

Hi,
I want to further more compare the boot up time of the nanos with other unikernals, the cold startup time for nanos seems to be very fast in comparison to other unikernals, but they way I am measuring it is using time function,
time ops pkg load ......

this actually gives me the whole lifecycle time of the vm, which is not good. I want to have just the boot up time.

is there any way I could do this, I could not find anything related to this on docs and discussion forum?

I went through this fork https://github.com/felixmoebius/nanos/tree/faas as one of the paper https://arxiv.org/abs/2403.00515 used this approach "The vm is passed a callback URL and when it gets ready, it sends a get request to the url. "

but the codebase seems to have changed a lot since then.

[eyberg]

Next time to help other people find this, I'd probably start a new discussion as this is somewhat different from your original post.

You are correct that using time here is not going to give you good results. For one, you are timing both the building of the image and the running of it and you really only want the running part. Furthermore, ops spawns a qemu process which you probably just want the booting portion of qemu itself - not the spawning.

The callback url option doesn't sound like a great idea either.

In the past we've used the perf tool for this as described in https://stefano-garzarella.github.io/posts/2019-08-24-qemu-linux-boot-time/ (Note: I had to build perf from kernel source as libtraceevent was not provided in the deb pkg for some reason - https://bugs.launchpad.net/ubuntu/+source/linux/+bug/2019247 ).

Essentially you'll need to modify nanos to write out to the i/o port when it hits like so:

eyberg@venus:~/go/src/github.com/nanovms/nanos$ git diff
diff --git a/platform/pc/boot/stage1.s b/platform/pc/boot/stage1.s
index 0e36f15f..0069967e 100644
--- a/platform/pc/boot/stage1.s
+++ b/platform/pc/boot/stage1.s
@@ -8,6 +8,9 @@ init:
        bits 16
        org base

+   mov al, 10
+   out 0xf4, al
+
         ;; serial setup
        mov ax, 0x00e3          ; AH = 0, AL = 9600 baud, 8N1
        xor dx, dx
diff --git a/src/unix/thread.c b/src/unix/thread.c
index 1a36438e..d1ca9537 100644
--- a/src/unix/thread.c
+++ b/src/unix/thread.c
@@ -1,6 +1,7 @@
 #include <unix_internal.h>
 #include <ftrace.h>
 #include <gdb.h>
+#include <x86_64/io.h>

 BSS_RO_AFTER_INIT thread dummy_thread;

@@ -215,6 +216,14 @@ static void thread_cputime_update(thread t)

 static void thread_pause(context ctx)
 {
+
+static boolean initialized = false;
+
+    if (!initialized) {
+        out8(0xf4, 200);
+        initialized = true;
+    }
+
     if (shutting_down & SHUTDOWN_ONGOING)
         return;
     thread t = (thread)ctx;

Here i'm writing to 0xf4 the values of 10 and 200 which translate to 0xa and 0xc8 respectively.

Using whatever guest program you want, such as a simple c hello world, first create your image with 'ops run myprogram' and then I just grabbed the output of that to plug into the qemu section of this script (for example):

#!/bin/bash

PERF_DATA="qemu_perf.data"
perf kvm record -a -e kvm:kvm_entry -e kvm:kvm_pio -e sched:sched_process_exec \
            -o $PERF_DATA &
PERF_PID=$!

sleep 5

qemu-system-x86_64 -machine q35 -device pcie-root-port,port=0x10,chassis=1,id=pci.1,bus=pcie.0,multifunction=on,addr=0x3 \
  -device pcie-root-port,port=0x11,chassis=2,id=pci.2,bus=pcie.0,addr=0x3.0x1 \
  -device pcie-root-port,port=0x12,chassis=3,id=pci.3,bus=pcie.0,addr=0x3.0x2 \
  -device virtio-scsi-pci,bus=pci.2,addr=0x0,id=scsi0 \
  -device scsi-hd,bus=scsi0.0,drive=hd0 -vga none -device isa-debug-exit -m 2G -device virtio-rng-pci \
  -machine accel=kvm:tcg -cpu host -cpu max \
  -drive file=/home/eyberg/.ops/images/main,format=raw,if=none,id=hd0 \
  -device virtio-net,bus=pci.3,addr=0x0,netdev=n0,mac=12:99:76:9d:0d:6a \
  -netdev user,id=n0 -display none -serial stdio

kill $PERF_PID

After is has gotten the data i convert to plain text:

 perf script -i qemu_perf.data > qemu_perf.data.txt

and now you can see your values:

eyberg@venus:~/qemu-boot-time$ grep 0xf4 qemu_perf.data.txt
 qemu-system-x86 3554013 [002] 16464943.610700:              kvm:kvm_pio: pio_write at 0x60 size 1 count 1 val 0xf4
 qemu-system-x86 3554013 [002] 16464943.623384:              kvm:kvm_pio: pio_write at 0xf4 size 1 count 1 val 0xa
 qemu-system-x86 3554013 [002] 16464943.692615:              kvm:kvm_pio: pio_write at 0xf4 size 1 count 1 val 0xc8

(16464943.692615 - 16464943.623384) * 1000 = ~69ms which seems about right to me (on this machine i'm on)

There are a handful of optimizations one can do to lower this number down.

While it is interesting to look at these numbers, I view the boot time, at a certain point to be a vanity metric because there are so few workloads that can actually make use of a lower number. In fact the majority of apps will have 'real' boot times measured in seconds (eg: setup a db connection, load some static assets, etc.) and that doesn't even account for the fact that most workloads want to run for more than a few seconds anyways (like hours or days), be it a persistent webserver or even a recurring one-shot cronjob. Also, if you are trying to go for a super low number (such as <10ms) you are probably starting to carve out a ton of essential capabilities that normal workloads might have so it would be wise to figure out what that list actually is.