How does a backend developer know that a SQL query will perform well in production? In large or rapidly growing companies, not everyone has access to the "product". And even with access, not all requests can be painlessly checked, and creating a database copy often takes hours. To solve these problems, we created an artificial DBA - Joe. It has already been successfully implemented in several companies and has been helping dozens of developers.
Video:
Hello! My name is Anatoly Stansler. I work for Postgres.ai company . We are committed to speeding up the development process by removing Postgres delays from developers, DBAs, and QAs.
We have cool clients, and today part of the report will be devoted to the cases that we have met while working with them. I'll tell you how we helped them solve some pretty serious problems.
, : « ?». review, , DBA-. – .
, , , . , . , , .
- prod - ? . , ? . , .
– prod. , , , - . prod, .
, . , .
?
staging - prod. prod, . , , staging.
- , . . prod.
?
- , staging . , - , – , . Staging . , . . , .
- , , , . . .
, , , - , , . - , , .
, , , - production.
, ? , , .
, ? , .
, , production, , .
, , , , , , . . .
, – . , cloud. 200 . , , . .
, prod .
? , .
.
, , . , 10 , 10 . , , . .
:
– 4,5 .
30 .
, . . , .
. .
OpenZFS.
OpenZFS – copy-on-write , . . . .
:
LVM,
(, Pure Storage).
Database Lab, , . . OpenZFS, LVM .
? , , , , , , .
, - , : «, , - ».
. , .
. , , , , .
, :
– , . production. , , . WAL-E, WAL-G Barman. , - Cloud-, , RDS Cloud SQL, . - , , , production, , .
– , Database Lab. Cloud, On-premise. , ZFS . .
, , , - dev. dev . , 4,5 , ZFS 3,5 . . dev .
.
, DBA , .
QA- , prod. environment QA , . , , , , - , .
. , , .
:
«», .
, .
, . . , .
. prod. .
. .
- , .
, ?
.
, , , . .
, . , ? .
? , , Slack-. .
? . . . . . , - , . .
Slack . , thread , , DBA, .
, , . , , , , .
, - .
, . . . .
production, .
Postgres . . Postgres, . . Shared Buffer Cache.
, Shared Buffer Cache Postgres , .
.
, , . - Shared Buffer Cache – 25 % , .
, , 4 instance, . . 4 . , , , .
, Buffer Cache , , . . , . , .
, prod , Postgres . , SeqScan. , ?
, Postgres Shared Buffer , effective_cache_size.
Effective_cache_size – , , . . Buffer Cache . . .
Postgres, , - , . , production.
. , , :
, prod.
.
, , , .
, , prod, , , , . , . . , , – ( ) , .
, Joe .
. – 1 . , 10 .
, . , 2,5 . , .
B Joe , .
, , . - , , .
, . , , . , 142 .
, , index scan , , ( ), .
- , .
, .
, , 2,5 156 , . 6 .
index only scan.
, - . Flame Graphs.
, . Flame Graphs : , , . . .
. , , .
, explain.depesz.com. , - , , .
, , explain.depesz.com, , .
– explain.dalibo.com. , . , , , -, .
, , Slack . , , , , thread Slack .
, . Update Database Lab, open source. Joe . . .
, - , Delphix, enterprise-. , . Postgres. open source. !
. !
! ! , , . . , .
, ? , . , 10 , , 10 . , . . , , ?
. . - , , , , fail-.
, . . . ? . ?
- ttl . , ttl.
, ?
1 , . . idle – 1 . , . , . , – .
, , , . ZFS? LVM? , c LVM . ? , , .
ZFS? , , . . instances . , . , . . LVM?
LVM meetup. – . ZFS . . . ZFS , Linux . . . . ZFS . , , . , , . . . . , . community, . ZFS – .
: ? , . , – . Pure Storage . .
, . – , , . . , .
ZFS . DelPhix, 300 . fortune 100 — 50 , . . NASA . . . open source Core. , open source. , . , . , . SELECT , . , DBA . . , . , . ZFS, . community , open source . .*
! ! . . . ? : , , - , - job . CPU , IO, ? .
. , ZFS , prod ZFS, ext4, . ?
. , . . , staging . , - , - . . prod. - , - – , , , - .
, , . , , , -, . , . , - .
. . , production , , ? - ? – - MySQL?
. , . Joe, , . ? Joe . , . , – . , .
MySQL. , state . Postgres, Postgres. . Postgres. , , . .
, MySQL. . , . .. MySQL . , ZFS.
! . , , , . , ?
, . , Postgres. ?
, , . , , , prod . - . prod, , prod , .
, , . , , .
?
, , , . . . , .
, , , , . , . . .
. cloud- , , Google, Amazon . .
. MySQL . , , , , . , , , , Postgres, , , instance.
. , . , , , . . , . 100 , . ? , , , , ?
, ZFS . , . , . , .
, , , , ?
, .
, , , ?
, .
. ?
, . - . . , . , . . data corruption . , , . . , .
, ! Joe . , . , Explain Analyze, .
. , : «SELECT FROM WHERE email = ». . . , - . . . , , , . - , .
! ! . Slack , - instances, ?
Now there is a binding to Slack, that is, there is no other messenger, but I really want to support other messengers too. What can you do? You can deploy DB Lab without Joe, walk using the REST API or using our platform and create clones and connect with PSQL. But you can do this if you are ready to give your developers access to data, since there will no longer be any screen.
I don't need this layer, but I need such an opportunity.
Then - yes, it can be done.