Database Drivers: Performance Perspectives by Piotr Sarna

Unlock the full potential of database drivers! Dive deep into their design, uncover how they work under the hood, and learn to tweak their performance to meet your needs. Perfect for developers eager to optimize their database interactions. #TechTalk #DatabaseDrivers

1. A ScyllaDB Community
Database Drivers:
Performance Perspectives
Piotr Sarna
Founding Engineer at poolside

2. Piotr Sarna
Founding Engineer at poolside
■Wannabe goat farmer
■Distributed systems hacker
■Open-source contributor & maintainer
■Book co-author
●Database Performance at Scale
●Writing for Developers: Blogs That Get Read
(Use code P992024 for 50% off)

3. Drivers
Communication tools for database users
●binary protocols
●HTTP
●gRPC
●GraphQL

4. # ScyllaDB Bash Driver 2.1.37

if [ "$#" -ne 3 ]; then
echo "Usage: $0 HOST PORT QUERY"
exit 1
fi

{
echo -ne "\x04\x00\x00\x00\x01\x00\x00\x00\x16\x00\x01\x00\x0b\x43\x51\x4c\x5f\x56\x45 …"
echo -ne "\x04\x00\x00\x01\x0b\x00\x00\x00\x31\x00\x03\x00\x0f\x54\x4f\x50\x4f\x4c\x4f …"
query_len=$(printf "%08x" ${#3})
packet_len=$(printf "%08x" $((${#3} + 13)))
echo-ne "\x 04\x00\x00\x03\x07"
echo -ne "\x${packet_len:0:2}\x${packet_len:2:2}\x${packet_len:4:2}\x${packet_len:6:2}"
echo -ne "\x${query_len:0:2}\x${query_len:2:2}\x${query_len:4:2}\x${query_len:6:2}"
echo -ne "$3"
echo -ne "\x00\x01\x14\x00\x00\x00\x64\x00\x08"
} | nc -N $1 $2 | xxd -p | xxd -r -p

5. # selects
$ ./microcql.sh 172.17.0.2 9042 'SELECT * FROM abc.test'
��/abctestid

# concurrent inserts!
$ for i in {1..10}
$ do ./microcql.sh 172.17.0.2 9042 "INSERT INTO abc.test (id) VALUES ('$i')" &
$ done; wait

6. Driver-as-a-Database (DaaD)
Sometimes the driver is the database
●SQLite
●libSQL
●DuckDB
●RocksDB

7. Clients vs Servers
7

8. Clients vs Servers
Clients Servers
want stuff done as fast as possible want to have things to do all the time
(but not too many things!)
don’t care much about other clients cooperate with other fellow servers
don’t want the servers to crash don’t want to crash

9. Workload Types
a.k.a. types of annoying users
interactive:
“webpage users”

batch:
“Spark job”

10. Throughput vs Goodput
Throughput:
measure of how much data you can squeeze through

Goodput:
measure of much useful data you can squeeze through

11. Throughput vs Goodput
Throughput:
1MiB of data, retried 10 times, 10MiB worth of error logs => 20 MiB/s!

Goodput:
1MiB of data, retried 10 times, 10MiB worth of error logs => 1 MiB/s

12. Timeouts
User-side timeouts:send and forget
Server-side timeouts:receive and stop executing

13. User-side timeouts
Tricky.
A server doesn’t necessarily know the user gave up.
May trigger a retry, which just makes the load worse.
Drivers can’t assume the servers know.

14. Server-side timeouts
Not tricky.
A nice backpressure mechanism.
Server should clearly inform why the request was refused.

15. Context
15

16. Topology
Some databases expose their topology, some don’t.
For those who do, drivers should track topology changes.

17. Topology
Ways of learning topology
●polling servers for topology information
●subscribing to topology change events
●taking part in the topology change process
○gossiping
○consensus algorithms

18. Metadata
●load statistics
○current load
○historical load
●priority
○prefer servers with faster disks
○prefer servers with more memory
●physical distance

19. Caching
19

20. Caching
■prepared statements
■query results
■database topology and schema

21. Caching prepared statements
Example algorithms:
■LRU
○with multiple pools to avoid thrashing
■LFU

Do not prepare oneshot statements.

22. Caching query results
Local cache -> relaxed consistency guarantees
Usually better to be done in the app, not in the driver

23. Caching topology and schema
Clients cannot assume they are up-to-date with topology or schema.
On failed assumption:
●client tries to contact a dead node
●client contacts a node that doesn’t have certain data anymore
●client contacts a node that is not yet up-to-date with all data
Server reaction:
●refuse to execute, ask to refresh topology and/or schema
●refuse to execute, reroute to the right node
●refuse to execute, send updated topology/schema info
●act as proxy

24. Retries
24

25. Retries (a.k.a. users being even more annoying than usual)

To retry, or not to retry?
●timeouts
○read timeouts
○write timeouts
●temporary errors
○overload
○dead node
○schema mismatch
●permanent errors
○bad syntax
○authentication/authorization error

26. Idempotence
Can a query be safely executed multiple times?


Query OK to retry?
SELECT * FROM t; yes
CREATE TABLE IF NOT EXISTS t(v int PRIMARY KEY); sure
INSERT INTO T(v) VALUES (42) USING TIMESTAMP 123; yessir*
UPDATE t SET v = v + 42; nope*

27. Retries
Sometimes the retry logic is nowhere in the driver at all.
err = retry.Do(
func() error {
_, err := db.Exec("INSERT INTO t (id, v) VALUES (21, 37)")
return err
},
retry.Attempts(5),
retry.Delay(2*time.Second),
retry.OnRetry(func(n uint, err error) {
slog.Warn("let's retry, crossing my fingers the query is idempotent ?????? ")
}),
)
if err != nil {
return nil, err
}

28. Retry policies
●do not retry
●retry on the same server
●retry on another server
●retry after some delay
●retry speculatively

29. Paging
29

30. Paging
Serving results in smaller bits.
●protects server memory
●protects clients' memory
●improves overall latency

●can be hidden behind driver API: e.g. result streams
●users can tweak parameters: page size

31. Concurrency
31

32. Concurrency
Capable of doing multiple things at a time.
High == good, but to a degree.
Modern hardware loves concurrency.

33. Thank you! Let’s connect.
Piotr Sarna
[email protected]@sarna.dev
@sarna_dev
https://bio.sarna.dev