# pihole-FTL (sqlite3 vs telnet API - requesting clarification)

**URL:** <https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736>\
**Category:** Beta 5.0\
**Created:** [May 8, 2020, 1:20pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736 "2020-05-08T13:20:37Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jpgpi250](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/jpgpi250/32/286_2.png) [@jpgpi250](https://discourse.pi-hole.net/u/jpgpi250)\
**Post date:** [May 8, 2020, 1:20pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/1 "2020-05-08T13:20:37Z")

</div>

`edit`  
updated the sample scripts to correct my own stupidity (see below)...  
`/edit`

Again, I'm currently analyzing [this](https://discourse.pi-hole.net/t/youtube-script-seems-to-be-working-very-well/31316) (youtube ad blocking) script, the result is raising some questions.  
This time, I'm looking at different ways to retrieve data.

Method 1: using sqlite3:

```auto
date="$(date --date="yesterday" '+%Y/%m/%d')"
time="$(date "+%R"; date "+%R" -d "1 hour ago")"

start=$( echo ${time} | cut --delimiter " " --fields 2)

begintm=$(TZ=CET date --date="$start" +"%s")

sqlite3 "/etc/pihole/pihole-FTL.db" "SELECT domain FROM "queries" WHERE domain LIKE 'r_%---sn-%.googlevideo.com' AND timestamp > $begintm;"

```

It looks like the data is retrieved from the database on disk. Since the data is committed every minute, there appears to be a one minute delay. To confirm this, I've increased the [setting](https://docs.pi-hole.net/ftldns/configfile/) from default DBINTERVAL=1.0 to DBINTERVAL=2. As expected the delay increases. There is a problem using this method, sometimes you get the message `database is locked`, thus no response, requiring additional logic to detect success.

Method 2: using [telnet API](https://docs.pi-hole.net/ftldns/telnet-api/):

```auto
#!/bin/bash

date="$(date --date="yesterday" '+%Y/%m/%d')"
time="$(date "+%R"; date "+%R" -d "1 hour ago")"

start=$( echo ${time} | cut --delimiter " " --fields 2)

begintm=$(TZ=CET date --date="$start" +"%s")
	
( echo ">getallqueries-time ${begintm}"; echo ">quit"; sleep 1; ) \
	| telnet 127.0.0.1 4711 \
	| cut --delimiter " " --fields 3 \
	| grep -e .*---sn-.*googlevideo.com

```

The result apparently is the same. There is a delay, this delay increases when increasing the DBINTERVAL setting. This method never appears to fail, as opposed to the sqlite3 method.

My question(s):

- which one is more efficient? The sqlite3 method or the telnet-api method. **I think** (but I'm wrong a lot of times) the telnet-api uses pihole-FTL as a man in the middle to get data from the database, thus sqlite3 being the better choice. **I would like this to be confirmed**.
- given the delay (data only saved once a minute - default), **is it possible to force a save to disk** , prior to the data request, thus eliminating the delay? **Is it safe to do that**?

Thanks for your clarifications.

---

<div class="post-metadata">

**Author:** ![yubiuser](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/yubiuser/32/12100_2.png) [@yubiuser](https://discourse.pi-hole.net/u/yubiuser)\
**Post date:** [May 8, 2020, 2:42pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/2 "2020-05-08T14:42:15Z")

</div>

For locked database try setting a timeout  
[https://dba.stackexchange.com/a/199807](https://dba.stackexchange.com/a/199807).

---

<div class="post-metadata">

**Author:** ![DL6ER](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dl6er/32/281_2.png) [@DL6ER](https://discourse.pi-hole.net/u/DL6ER)\
**Post date:** [May 8, 2020, 8:47pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/3 "2020-05-08T20:47:53Z")

</div>

> [@jpgpi250](#):
>
> **I would like this to be confirmed**.

No. FTL has all the data in memory and can immediately respond to your request without the need for a single I/O operation. However, the data is limited to the most recent 24 hours (whereas you can request as much as you like from the database).

Dumping is only possible on restart. But you will not need this.

---

<div class="post-metadata">

**Author:** ![jpgpi250](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/jpgpi250/32/286_2.png) [@jpgpi250](https://discourse.pi-hole.net/u/jpgpi250)\
**Post date:** [May 8, 2020, 8:56pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/4 "2020-05-08T20:56:42Z")

</div>

> [@DL6ER](#):
>
> FTL has all the data in memory and can immediately respond to your request

Not arguing here, as I said I configured `DBINTERVAL=2`. As soon as I load a youtube video, I can immediately see the request in the pihole log, I'm using [this](https://discourse.pi-hole.net/t/a-better-way-to-look-at-tail-the-pihole-log-windows/1633) method to look at the logs, so it's practically real time on my (windows) screen. According to your explanation, if I than immediately execute either of the above scripts, I should see the result, however, It takes two minutes before I get a result. **What am I missing?**

Thanks for your patience.

---

<div class="post-metadata">

**Author:** ![DL6ER](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dl6er/32/281_2.png) [@DL6ER](https://discourse.pi-hole.net/u/DL6ER)\
**Post date:** [May 9, 2020, 5:37am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/5 "2020-05-09T05:37:16Z")

</div>

> [@jpgpi250](#):
>
> According to your explanation, if I than immediately execute either of the above scripts, I should see the result, however, It takes two minutes before I get a result.

FTL knows everything immediately as it processes your queries. Similarly, `pihole.log` sees everything immediately as it is updated without any delay.  
The database, however, only gets updated every two minutes (with your setting) so there is a certain expected delay only in your first method.

---

<div class="post-metadata">

**Author:** ![jpgpi250](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/jpgpi250/32/286_2.png) [@jpgpi250](https://discourse.pi-hole.net/u/jpgpi250)\
**Post date:** [May 9, 2020, 9:19am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/6 "2020-05-09T09:19:25Z")

</div>

I agree the `DBINTERVAL=2` is responsible for the delay, using the sqlite3 method, however, I can't explain the delay with the telnet-api. Still wondering if I'm missing something...

---

<div class="post-metadata">

**Author:** ![DL6ER](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/dl6er/32/281_2.png) [@DL6ER](https://discourse.pi-hole.net/u/DL6ER)\
**Post date:** [May 9, 2020, 10:08am UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/7 "2020-05-09T10:08:08Z")

</div>

> [@jpgpi250](#):
>
> I can't explain the delay with the telnet-api

There is none. Read as: There should (and actually can) be none.

I just tested this like below:

```auto
$ echo ">getallqueries (1) >quit" | nc 127.0.0.1 4711 && date +%s
1589018751 AAAA qa.sockets.stackexchange.com 192.168.4.3 3 0 1 7 N/A -1 12034
1589018754

```

This gives the most recent query (`(1)` means: Only one domain should be returned). As you can see, the difference to the current timestamp (second line) is only three seconds. Similar results appear when I query later. There is never a delay building up for me.

---

<div class="post-metadata">

**Author:** ![jpgpi250](https://discourse-cdn.pi-hole.net/user_avatar/discourse.pi-hole.net/jpgpi250/32/286_2.png) [@jpgpi250](https://discourse.pi-hole.net/u/jpgpi250)\
**Post date:** [May 9, 2020, 12:06pm UTC](https://discourse.pi-hole.net/t/pihole-ftl-sqlite3-vs-telnet-api-requesting-clarification/31736/8 "2020-05-09T12:06:21Z")

</div>

> [@DL6ER](#):
>
> There is never a delay building up for me.

Found the problem, it's me (stupid).  
query between begintime and endtime leads to the problem (copied from an other script to retrieve yesterdays queries)  
example:  
begintime 14:25  
endtime 15:25  
query at 15:25:01 will not show up until script is executed at 15:26

I've updated the sample scripts above to eliminate creating the same problem for future users.

Apologies for wasting your time