Giter Site home page Giter Site logo

Sporadic read latencies about esqlite HOT 13 CLOSED

mmzeeman avatar mmzeeman commented on September 23, 2024
Sporadic read latencies

from esqlite.

Comments (13)

mmzeeman avatar mmzeeman commented on September 23, 2024 1

I assumed all functions which open/close and do query processing are io bound, but maybe that is not the case in practice.

I haven't tested this, it was just my best effort assumption.

Having a representative dataset combined with some sort of workload on the node will probably help to improve. 👍

from esqlite.

mmzeeman avatar mmzeeman commented on September 23, 2024 1

Would it be an idea to use the sample database found here: https://github.com/lerocha/chinook-database/tree/master and get some query timings under some sort of load and check the request times and standard deviation?

from esqlite.

mmzeeman avatar mmzeeman commented on September 23, 2024 1

I wonder if it could be some kind of caching inside sqlite itself.

I see the increased read latency mostly after a bit of time has passed.

147> timer:tc(esqlite3,q, [Conn, "SELECT id FROM purchases WHERE ((user = 'bernard') AND (((status = 0) OR (status = 2)) OR (status = 1))) ORDER BY creation_ts DESC LIMIT 200015, 5;"]).
{409850,
 ...
148> timer:tc(esqlite3,q, [Conn, "SELECT id FROM purchases WHERE ((user = 'bernard') AND (((status = 0) OR (status = 2)) OR (status = 1))) ORDER BY creation_ts DESC LIMIT 200015, 5;"]).
{204779,
 ...

from esqlite.

mmzeeman avatar mmzeeman commented on September 23, 2024 1

Indeed... similar behavior

sqlite> .timer ON
sqlite> SELECT id FROM purchases WHERE ((user = 'bernard') AND (((status = 0) OR (status = 2)) OR (status = 1))) ORDER BY creation_ts DESC LIMIT 200015, 5;
  ...
Run Time: real 0.426 user 0.159040 sys 0.048730
sqlite> SELECT id FROM purchases WHERE ((user = 'bernard') AND (((status = 0) OR (status = 2)) OR (status = 1))) ORDER BY creation_ts DESC LIMIT 200015, 5;
   ...
Run Time: real 0.195 user 0.173158 sys 0.021259

from esqlite.

mmzeeman avatar mmzeeman commented on September 23, 2024 1

It sure is strange. If you set caching to 0 you get similar behavior, with occasional long latencies. So I think it isn't SQLite caching either. It could also be the os level disk cache.

from esqlite.

mmzeeman avatar mmzeeman commented on September 23, 2024

I haven't really done any profiling for things like these.

Do you have a test setup so I can take a look?

Maybe one of the functions of the NIF need a ERL_NIF_DIRTY_JOB_CPU_BOUND or ERL_NIF_DIRTY_JOB_IO_BOUND flag.

from esqlite.

Zabrane avatar Zabrane commented on September 23, 2024

@mmzeeman Let me prepare a db after anonymizing the data set.

In the meantime, can you please tell me which functions I should test (ERL_NIF_DIRTY_JOB_CPU_BOUND + ERL_NIF_DIRTY_JOB_IO_BOUND) first?

from esqlite.

Zabrane avatar Zabrane commented on September 23, 2024

Hi @mmzeeman

Here you are if you'd like to test it from command line:

$ gunzip slow_db.sqlite.gz
$ sqlite3 ./slow_db.sqlite

Then:

SELECT id FROM purchases WHERE ((user = 'bernard') AND (((status = 0) OR (status = 2)) OR (status = 1))) ORDER BY creation_ts DESC LIMIT 200015, 5;

The database is pretty big (+130MiB uncompressed) and GitHub limits upload to 25MiB.
You can download it from here:
https://we.tl/t-5OuKaSeb8r

Now, the read speed using ESQLite driver and sqlite3 command is pretty much the same in average (~ 190msec), but both suffer from this random latency (~ 340msec).

Let me know if you need anything else.
Thanks

from esqlite.

Zabrane avatar Zabrane commented on September 23, 2024

Would it be an idea to use the sample database found here: https://github.com/lerocha/chinook-database/tree/master and get some query timings under some sort of load and check the request times and standard deviation?

@mmzeeman Very good idea. Didn't know about chinook database before. Thanks for sharing.

from esqlite.

Zabrane avatar Zabrane commented on September 23, 2024

@mmzeeman glad you were able to reproduce the timings ;-)

This behavior's puzzling us for more than 2 weeks.
Could it be disk i/o ?

Would be great if we can find out what's causing this.

from esqlite.

Zabrane avatar Zabrane commented on September 23, 2024

@mmzeeman when trying the DB in RAM only, there is no occasional latencies.
Everything is super stable.

from esqlite.

mmzeeman avatar mmzeeman commented on September 23, 2024

Good to hear. So it is something out of the scope of the sqlite nif.

from esqlite.

Zabrane avatar Zabrane commented on September 23, 2024

@mmzeeman definitely not a NIF issue.
Keep up the good work and many thanks for your support.

from esqlite.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.