►
From YouTube: Database Office Hours - 2019-06-27
Description
* 0:00': Process to become database reviewer/maintainer and discussion around it
* 8:50' : Proposal to extend ChatOps to allow engineers to access a read/write copy of the production database for code reviews for insights (without actually having access to the data itself) [1]
* 22:50': Monitor transaction length [2], disallow unrelated IO during database transaction, improve transaction length generally
* 28:27': Normalized schema for storing timezones [3]
* 38:00': Example of high IO related to storing a lot of text in a column [4]
* 41:09': Review large query in a MR - how do approach it? [5]
Agenda and notes (internal to GitLab only):
https://docs.google.com/document/d/1wgfmVL30F8SdMg-9yY6Y8djPSxWNvKmhR5XmsvYX1EI/edit#
[1] https://gitlab.com/gitlab-com/www-gitlab-com/merge_requests/24156
[2] https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/29928
[3] https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28339/diffs#note_185204007
[4] https://gitlab.com/gitlab-com/gl-infra/production/issues/908
[5] https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28887#note_185007453
B
A
Well,
I
was
looking
for
a
process
yesterday
of
how
to
become
a
database.
Maintainer
and
I
found
a
lot
of
issues
like
spread
around
and
then
I
came
across.
The
merge
request
that
to
put
in
the
agenda
and
well
I,
think
I
think
we
should
like
try
to
merge
it
as
soon
as
possible
to
establish
a
process
and
I
was
also
searching
for
this,
because
I
am
very
interesting
on
becoming
an
database.
Reviewer
yeah
just
dot
great.
B
B
Maybe
we
should
move
move
that
in
there
and
I
think
you
also
made
a
great
point
on
the
EMR,
pointing
out
that
the
that
it's
quite
a
bit
different
from
from
the
back
end
or
the
way
we
do
back
and
training
for
maintainer
x',
and
we
should
also
move
the
issue
template
to
to
the
same
place.
Maybe
just
adopt
the
same
thing
as
we
do
for
back
end.
So
you
totally
right
about
that.
A
C
A
B
B
B
Thank
you,
and
actually
great
I,
mean
I
think
was
brought
up
yesterday
in
the
in
the
retrospective
that
we
should
increase
the
number
of
database
maintainer,
so
I
put
a
link
to
the
issue
that
was
created
there
as
well
and
yeah.
I
totally
agree,
because
currently
we
have
like
two,
a
few
maintain
Ares
and
I
found
and
I
put
a
bit
of
statistic
in
there
as
well
and
I.
E
B
I
mean
and
generally
I
feel
like
the
database
reviews
and
the
role
of
a
database.
Maintainer
can
also
be
something
that
actually
belongs
to
back
end,
because
database
is
like
an
inherent
part
of
back-end
development.
I,
just
I
just
want
to
point
out
that,
maybe
we
don't
even
like
need
a
separate
database
review
process.
Maybe
this
is
something
that
we
can
sort
of
put
back
into
into
the
role
of
a
back-end
reviews
and
maintainer
as
well.
I.
D
Think
it
depends,
I
mean
there
are
several
okay.
This
is
kind
of
skill
instead
of
a
project,
but
for
example,
we
don't
have
the
same
reviewers
or
maintainer
thing
in
that
work,
horse
or
italy.
Despite
of
being
backing
work,
for
example,
I
know
this
is
a
different
thing,
because
this
is
part
of
your
skill
set,
but
I
think
we
still
need
to
be
specialized
on
being
a
database,
reviewer
and
maintainer.
I
think
training
is
important
for
backing
engineers,
but
we
still
need
people
focus
on
this
time
and
these
tasks.
Yes
just
alone.
B
Yeah
I
mean
I
think.
Ideally
we
wouldn't
need
that
separation,
like
I,
think
you
you
can
look
at
that
from
a
back
in
perspective
and
also
include
the
database
stuff,
but
yeah
moment
momentarily
will
like
kept
that
separated
and
I
agreed
sort
of
a
focus
area.
You
can
have
and
also
something
that
when
you,
when
you
look
at
the
Lacombe
database,
that
is,
there
is
a
lot
of
insight
in
there
and
and
focusing
on
that
and
is,
is
quite
quite
interesting,
I
wrong.
D
Feel
that
it
still
is,
it
is
still
long
the
process
between
when
it
starts
and
when
it's
finally
merged
or
approved
in
this
case
it's
just
a
feeling,
maybe
because
now
we
have
more
engineers
working
the
company.
Now
there
are
more
title
based
related
label,
but
still
the
funnel
of
the
database.
Reviewing
processes
is
really
nervous.
It's
very
small.
A
B
B
So
that
would
sort
of
allow
you
to
do
quite
a
bit
more
work
in
a
self-service
manner
than
we
would
have
that
today.
Think
today,
if
you,
for
example,
if
you
just
have
a
simple
change
that
would
introduce
a
column
or
an
index
or
something
like
that,
and
you
want
to
know
about
the
query
plans.
There
is
no
way
you
can
get
that
from
chat
ops,
because
you
can't
modify
the
data
way
through
tariffs.
B
This
is
you
can
just
use
it
to
get
plans
for
queries
that
match
just
the
schema
that
we
currently
have
on
production.
So
in
many
of
those
cases
you
would
reach
out
to
somebody
with
database
access
and
they
would
sort
of
try
to
figure
that
out,
and
that
is
something
that's.
The
proposal
fixes
in
a
sense
that
you
would
be
able
to
talk
to
tariffs
and
get
a
get
on
a
production
copy
of
the
sorry.
B
D
Wanted
to
ask
something,
because
I
saw
that
this
tool,
you
still
have
to
execute
their
goal
back
of
your
changes.
Is
there
a
way
in
which
we
can
put
all
our
database
changes?
Where
is
all
this
stuff
like
like
inside
inside
a
chat,
ops,
transaction
and
once
it
is
executed,
it
automatically
roll
back?
The
changes.
F
F
It
will
be
stored
automatically,
if
you
don't
communicate,
say
like
15
minutes
right.
Oh
I'm
still
understand
what
is
the
best
approach
in
terms
of
like
to
have
session,
because
if
one
developer
works
with
it,
other
developers
cannot
work
with
it
because
because
I
made
some
changes
and
stall
and
session
can
be
closed
manually.
If
I
am
done
right
or
automatically
under
someone
after
some
idle
time,
I
think
it
can
be
configurable
and
so
on.
I'm
going
to
describe
this
in
in
like
design
document
as
it
was.
F
Do
you
think
it's
it's
also
useful?
It
would
be
also
useful
to
have
some
kind
of
CLI
instead
of
slag,
because
it's
like
it's
like
human
right
or
maybe
maybe
a
web
interface.
I,
know
that
chat.
Ops
right
now
has
ability
to
display
plants
in
explain
diapers.com,
which
is
quite
popular,
actually
explain.
The
episode
Kong
has
open
source
repository,
so
we
can
use
use
it
internally,
because
maybe
it's
not
a
good
idea
to
publish
plants
constantly
somewhere,
but
so
there
are
options
here.
F
B
B
If
we
instead
would
use
like
a
postcode
transaction
and
let
you
like
you-
can
still
interface
with
chat
ops
where
CLI
and
you
can,
you
can
basically
queue
up.
A
few
commands
that
you
would
like
to
run
like
creating
an
index
getting
a
plan
for
some
query,
adding
a
table
or
you
know
what
not
and
then,
when
you're
done
with
that
plan,
you
would
commit
that
to
to
that
database
and
would
just
run
a
transaction
and
since,
like
PDL,
is
sort
of
it's
completely
supported
by
the
post
goes
to
to
wrap
that
into
a
transaction.
F
B
Right
I
mean,
since
you
would
sort
of
be
planning
out
what
you
want
to
do
and
then
then
sort
of
submit
that
and
wait
for
the
results
probably
doesn't
take
forever
to
execute
that
and
and
would
actually
we
talked
about
like
multiplexing
that
solution
where
you,
how
do
you
use
that
instance,
where
multiple
people
are
moved,
so
they
don't
step
on
each
other's
toes
and
the
first
natural
is
certain
so
sort
of
exclusive
to
one
engineer
using
it.
Maybe.
E
F
C
F
Huge
part
of
the
table
and
then
roll
back
board
increased,
unlike
dead
tuples
count
increased
and
then
wall
decreased
and
it
affects
performance
also,
but
in
general,
with
that
offense
approach,
it's
not
possible
to
move
multiplex
with
single
volume.
Here,
of
course,
there
are
some
some
ways
to
multiplex,
especially
if
you
see
that
you're
blocked
by
some
other
session.
It's
okay.
If
it's
transparent,
is
my
memory.
Well,
it
would
be
convenient
I'll
think
about
it.
Maybe.
F
F
Also
interested
in
seeing
the
results
of
like
all
may
be
participating
in
testing
of
Google
snapshots.
Jesse
has
some
shots
because,
instead
of
data
fast,
we
could
use
them
if
they
are
so
good,
and
in
this
case
we
could
provision
it
when
it's
really
needed.
We
could
provision
multiple
instances
right
also.
B
D
F
Also
thinkin's
at
some
point
we
probably
would
achieve
the
state
when
not
request
are
tested
automatically,
because
if
you,
if
you
have
such
instance
and
the
provisioning,
if
was
fully
automated,
you
can
provision
github
instance
as
well
run
rush
request,
not
miss
requested
the
database
migration
run
it
and
check
everything
like
timing
and
how
much
water
is
generated.
How
many?
How
many
checkpoints
happened
during
this
change
and
so
on?
B
B
Mean
I
don't
see
why
they
wouldn't
work,
like
we
discussed
recently
said
of
us
versus
GCP
snapshots
right
and
the
one
concern
that
was
racist
when
you,
when
you
run
a
database
and
you
snapshot
default
system
that
you
may
run
into
like
inconsistency
on
the
file
system
level.
But
in
fact
you
can.
If
that
was
a
valid
concern,
that
even
then
you
can
like
stop
the
world
on
the
database.
Replica,
take
a
snapshot
and
go
with
that.
All.
F
F
F
B
That
is
where,
once
we
have
that,
we
will
be
able
to
monitor
the
transaction
length
for
the
database
transaction
length
and
across,
like
all
the
controllers
and
and
psychic
shops
that
we
have,
and
that
is
interesting,
because
we
noticed
that
we
have
like
a
lot
of
or
quite
a
bit
of
la
congestión,
going
on,
for
example,
on
the
internal
ID
records.
So
that
is
when
you
create
a
lot
of
CI
problems,
pipelines
at
the
same
time.
They
would
they
would
all
of
these
transactions.
B
They
would
acquire
a
lock
on
internal
IDs
on
one
of
those
records,
and
then
you
can
see
those
transactions
taking
a
long
time
because
they
were
related
to
CI
and
there
is
a
lot
of
stuff
going
on
in
there
and
I
would
for
that
time.
That
would
hold
on
to
that
lock
and
that's
that
produces
that
congestion
that
we
would
see
where,
if
there
are
quite
a
few
of
those
transactions
going
on
concurrently,
that
would
they
would
block
each
other
out
until
they
finish.
B
That
is
something
that
we
would
have.
We
really
have
to
be
aware
of
that.
The
longer
those
transactions
gets,
especially
for
the
web
transactions,
the
worst
that
is
for
us
in
terms
of
like
concurrency,
and
currently
we're
not
we're
not
even
like
able
to
track
down
which
parts
of
the
application
suffer
from
from
that
the
most
in
a
systematic
way
and
what
Andrew
proposes
to
monitor
the
transaction
length
and
that
that
allows
us
to
identify
which
parts
of
the
code,
which
would
are
likely
to
create
a
long-running
transaction.
B
And
what
is
interesting
about
that
is
you.
He
Andrew
took
another
step
further,
and
that
was
adding
a
check
to
to
the
code
where,
when
you
open
a
database
transaction
and
then
you
make
unrelated
io
in
that
case,
if
you
would
make
calls
to
get
early,
those
cold
should
fail,
and
he
added
NMR
I,
put
also
put
a
link
on
the
agenda
and
to
fail
those
get
early
calls
and
apparently
that
is
sort
of
all
over
the
place.
B
So
quite
a
few
tests
were
failing
because
of
that,
and
that
is
an
indicator
that
we
we
we
have.
We
actually
do
get
early
calls
and
maybe
also
calls
to
other
Web
API
sore,
even
like
the
local
file
systems
is
a
problem.
While
we
have
a
pending
database
transaction
open
and
that's
really
going
to
hurt
the
more
we
the
more
we
scale
yeah
once
we
know
where,
where
those
areas
are,
we
we
can
definitely
improve
in
that
area.
I
think.
F
B
Yeah
I
think
and
resume
arm
turns
that
around
and
actually
measures
the
transaction
time
per
controller
action,
for
example,
and
reports
that
Prometheus.
So
in
the
end,
we
would
be
able
to
say:
oh
this
on
average
or
whatever
this
control
Direction
takes
a
lot
of
time
and
not
open
database
transaction.
E
B
D
Mean
this
is
not
the
difficulty
or
the
complexity
of
the
EMR,
but
they
did
a
mind
set.
So
the
problem
is
that
we
are
starting
to
store
in
the
user
preferences
model
a
lot
of
time
zone
info,
for
example,
like
the
time
zone
name
the
time
so
identifier
on
some
others.
So
we
are
thinking
about
moving
moving
it
to
a
different
table
and
address
suggested
to
have
like
route
time,
some
table
that
really
store
the
time
zone,
identifier
and
maybe
also
the
time
zone
name
and
the
user
preferences.
D
D
For
example,
when
we
populate
combo
box
with
our
select
box
with
time
some
information,
we
populated
with
rails
libraries,
it
may
come
from
rails
itself
or
from
a
gem,
so
I
don't
know
if,
from
the
front
end
side,
we
also
have
some
or
library
that
also
can
give
these
time
zone
information.
So
my
thinking
is
that
we
are.
D
We
have
now
maybe
one
or
two
sources
for
populating
this
data
and
we
are
going
to
add
a
new
one
to
that
which,
which
will
be
the
database
this
table.
So
what
we
want
to
publish,
for
example,
a
select
a
cell
at
bot
which
data
will
be
use,
the
data
in
the
database,
it's
the
done,
Ruby
library
or
maybe
the
fronton
library,
because
they
may
not
be
aligned
with
each
other
I
know.
Imagine
that
we
are
going
to
create
a
new
time
zone.
We
are
going
to
create
a
new
time
setting
the
world.
B
I
think
I
understand
it.
I
think
we
were
talking
like
about
two
different
things,
maybe
but
I
also
totally
I'm,
really
bad
at
understanding
time
zone.
So
I
was
just
looking
at
it
from
from
the
database
perspective
and
a
respect.
Maybe
we
can
we
can
talk
about
it
separately
like
there
is
the
topic
of
how
do
we
store
those
user
preferences
in
the
database,
and
then
there
is
the
second
topic
of
how
how
do
we
like?
B
C
D
B
D
B
D
B
Right
about
that
totally
but
like
currently,
we
are
like
four
million
users
and
there
is
in
a
way
we're
going
to
have
like
each
user
is
going
to
live
in
some
time
time
zone
right,
probably
so,
really
just
from
a
database
perspective.
What
we're
doing
is
we
put
strings
on
those
table
for
each
user
and
then
we
put
the
time
zone
name
in
there
right.
B
There
is
probably
going
to
be
a
lot
of
duplication
and
what
we
could
do
instead
is
create
another
table
for
the
time
zones
and
maybe
the
distinct
combinations
that
we
see
for
time,
zone
name
and
times
an
ID
or
whatever.
Whatever
attributes
we
were
trying
to
manage
there
and
they're,
rather
those
come
from
like,
doesn't
really
matter
it,
and
then
you
would,
from
the
user
preferences
table
you,
which
is
reference,
the
other
table
reference.
C
C
Then
it
will
create
it
in
that
table
and
then
all
you
don't
talk
about
is
some
kind
of
attribute
in
the
table
to
indicate
what
is
currently
active
and
not
so
if
some
time
zone
goes
away,
it's
no
longer
selectable
and
dropdowns,
and
things
like
that
or
you
could
use
a
driver,
the
library
as
the
source
of
truth
for
the
drop
downs.
And
then,
if
there's
extra
things
in
the
table,
it
doesn't
really
matter.
D
Okay,
so
my
didn't
we,
this
is
a
difficult
situation,
but
imagine
that
we
are
going
to.
We
will
have
a
new
time
you're
going
to
have
the
moon
types
on
now
and
we
don't
have
it
in
the
database
delivery
populates,
they
top
the
drop
down.
We
have
that
information.
What
will
happen
in
that
situation,
whether
you
should
say
these
time
song
these
moon
times
I
mean
he's
his
preferences.
C
D
D
D
D
B
D
C
With
in
reality
right,
they
don't
really
change
very
rarely.
There's
a
time
zone
come
and
go,
and
so
for
that
specific
case,
I
think
it
would
probably
be
over
engineering
to
use
the
library
for
that
I
think
it
would
be
fine
to
use
the
table.
I
mean
yes,
it
might
be
a
lot
of
lookup
from
the
application
that
populated
drop-down,
but
it's
a
table
with
like
30
rows
or
something
would
be
very
fast.
B
And
KC
you
mentioned
like
there
is
the
problem
with
just
storing
those
strings
on
that.
Rather
large
table
is
also
I/o
and
I
wanted
to
add,
and
inside
we
also
got
from
the
from
the
incident
investigation
we
did.
Last
week
there
was,
there
was
a
table
that
was,
it
was
actually
the
project's
table,
which
is
also
really
white,
and
what
we
were
seeing
on
the
logs
and
Postgres
was
that
sometimes
the
primary
key
lookup
would
take
maybe
up
to
three
seconds
right.
B
B
B
Put
like
30
megabytes
of
text
into
that,
because
the
stack
trace
was
so
big
or
I,
don't
know,
and
then,
whenever
you
try
to
read
that
you
you,
you
have
to
like
read
a
lot
of
pages
and
they
may
even
be
on
some
other
place.
So
it's
really
really
hurting
performance.
Obviously,
like
time,
some
things
are
not
30,
megabytes
and
and.
C
That's
just
the
direct
impact
that
was
when
you
do
like
a
sliced
phone
in
that
row
and
then
because
there's
30
Meg's
of
data,
it
takes
some
time
a
little
bathroom
disk.
But
another
thing
that
happens
right
is
every
everything
that
you're
working
with
every
every
table.
Access
is,
is
loading
those
pages
off
of
disk
into
memory
and
there's
a
temporary
caching
going
on,
and
so
even
if
you're,
not
selecting
that
and
you're
select
seems
fast,
because
the
rows
that
wide
is
getting
pushed
into
memory,
which
is
pushing
other
things
out.
B
C
D
D
D
B
Way
he
approached
it
today
was
to
ask
for
the
original
query,
because
they
gives
me
a
bit
more
time
to
figure
out
what's
happening
now.
I
think
that
that's
that
makes
it
easier
to
understand
what
what
the
change
was
actually
because,
like
just
just
looking
at
the
at
a
large
query
itself
like
how
how
it
will
look
like
after
the
changes.
B
D
Because
I
think
that
we
are
creating
several
times
the
the
member
table,
I
didn't
know
if
we
could
do
something
to
organize
it
or
maybe
because
we
are
requiring
the
members
table
for
group
members
and
also
we
are
unique,
performing
a
union
of
the
same
table,
but
only
with
project
members.
So
do
you
know
if
we
could
do
something
to
avoid
wearing
that
table
three
or
four
times
yeah.
B
D
B
Yeah
I
think
this
is
really
difficult
to
look
at.
There
is
a
creep
and
that
was
posted
on
DMR
already.
So
this
is
something
you
can
go
to,
but
the
challenge
with
that
is
obviously,
like
you
have
this
large
query
and
first
of
all,
you
would
have
to
make
sure
that
this
is
the
query
plan
or
the
XG
actual
career.
B
Okay,
but
it
looks
like
in
this
case
we
use
namespace,
seven,
which
sort
of
makes
me
think
that
that
was
that
was
coming
from
a
local
development
instance.
And
then
we
take
that
query
and
the
production
crew,
a
plan
that
was
posted
as
still
for
namespace
seven.
So
in
this
case,
I
would
want
to
pick
the
or
and
give
the
board
name
space
instead
and
look
at
the
query
plan
for
that,
because
there
might
be
like
totally
different.
B
And
the
next
question
is,
then:
if
you
want
to
do
you
want
to
review
the
change?
So
that's
where
I
was
asking
for
the
original
query
so
that
we
can
look
at
what
changed
actually
and
one
way
to
do.
That
is
really
fine
to
find
a
good.
A
good
example
of
parameter
so
pick
the
github
project,
for
example,
and
then
really
just
compare
their
career
plans
for
before
and
after,
and
you
know
this
is
this-
is
how
to
look
at
the
change
right
and
the
other
perspective
you
can
have.
B
Oh,
this
is
a
large
query
and
maybe
the
performance
isn't
great
before
and
even
after
will
change.
How
can
we
improve
that?
That
is
a
much
more
difficult
part,
but
if
it's
just
about
making
sure
that
the
change
we
ship
here
make
sense
or
doesn't
like
make
things
worse,
performance
wise
I
would
just
compare
the
query
plans.
I
guess.
B
B
But
it's
also.
This
is
really
difficult
because
when
you
I
remember
doing
like
small
career
changes
to
some
permission,
query
and
the
things
I
looked
at
that
day
made
sense.
Like
performance
was
better,
it
was
great,
and
then
we
shipped
that
and
since
those
permission
queries
are
like
used
all
over
the
place.
B
E
B
Right
so
we're
at
the
end
of
that
call,
I'm
told
thanks
thanks
friend,
I
wanted
to
just
shout
out
one
more
thing
which
I'm
really
excited
about,
and
that
is
that
we
dropped
my
sequel
support.
There
is
currently
we're
working
on
a
on
a
blog,
post
and
I
think
it's.
There
is
still
room
to
contribute
to
that
blog
post,
where
we
make
arguments
of
why
we
drop
that,
and
that
is
that
was
highly
needed.