P ostgreSQ L B u fg ers Vi k Feari ng PG C onf. EU W arsaw O - - PowerPoint PPT Presentation

▶
p ostgresq l b u fg ers
SMART_READER_LITE
LIVE PREVIEW

P ostgreSQ L B u fg ers Vi k Feari ng PG C onf. EU W arsaw O - - PowerPoint PPT Presentation

Apr 07, 2024 175 likes •559 views

P ostgreSQ L B u fg ers Vi k Feari ng PG C onf. EU W arsaw O ctober 25, 20 17 V i k Feari n g Change and and to and. i rc: xocol atl Tw i tter: @ pg_ xocol atl P ostgreSQ L B u fg ers B acken d D ata D i

slide-1
SLIDE 1

P ostgreSQ L B u fg ers

Vi k Feari ng

PG C onf. EU W arsaw O ctober 25, 20 17

slide-2
SLIDE 2

V i k Feari n g

Change “and and” to “and”.

i rc: xocol atl Tw i tter: @ pg_ xocol atl

slide-3
SLIDE 3

P ostgreSQ L B u fg ers

slide-4
SLIDE 4

B acken d

slide-5
SLIDE 5

D ata D i rectory d a t a _ d i r e c t

  • r

y

slide-6
SLIDE 6

d a t a _ d i r e c t

  • r

y

slide-7
SLIDE 7

d a t a _ d i r e c t

  • r

y

slide-8
SLIDE 8

P age = B l

  • ck

= B u fg er8 kB

B L O C K S Z

slide-9
SLIDE 9

Sh ared B u fg ers s h a r e d _ b u f f e r s

slide-10
SLIDE 10

d a t a _ d i r e c t

  • r

y

slide-11
SLIDE 11

U sag e Cou n t 1 . . 5

slide-12
SLIDE 12

Cl

  • ckSw eep
slide-13
SLIDE 13

Whups!

slide-14
SLIDE 14

W ri te- A h ead Log (W A L) p g _ x l

  • g
slide-15
SLIDE 15

W ri te- A h ead Log (W A L) p g _ w a l

slide-16
SLIDE 16

d a t a _ d i r e c t

  • r

y p g _ w a l

slide-17
SLIDE 17

w a l _ b u f f e r s W A L B u fg ers

slide-18
SLIDE 18

d a t a _ d i r e c t

  • r

y

slide-19
SLIDE 19

Whups!

slide-20
SLIDE 20

W A L W ri ter w a l _ w r i t e r _ d e l a y

slide-21
SLIDE 21

d a t a _ d i r e c t

  • r

y p g _ w a l

slide-22
SLIDE 22

B ackgrou n d W ri ter b g w r i t e r _ l r u _ m u l t i p l i e r b g w r i t e r _ l r u _ m a x p a g e s b g w r i t e r _ d e l a y

slide-23
SLIDE 23

Ch eckp oi n ts c h e c k p

  • i

n t _ t i m e

  • u

t c h e c k p

  • i

n t _ c

  • m

p l e t i

  • n

_ t a r g e t m i n _ w a l _ s i z e

  • m

a x _ w a l _ s i z e

slide-24
SLIDE 24

d a t a _ d i r e c t

  • r

y p g _ w a l

slide-25
SLIDE 25

B u fg er R i n gs

slide-26
SLIDE 26

Tem p orary Tab l es t e m p _ b u f f e r s t e m p _ t a b l e s p a c e s t e m p _ f i l e _ l i m i t

slide-27
SLIDE 27
slide-28
SLIDE 28

d a t a _ d i r e c t

  • r

y p g _ w a l

slide-29
SLIDE 29

V i e w " p u b l i c . p g _ b u f f e r c a c h e " C

  • l

u m n | T y p e | M

  • d

i f i e r s

  • +
  • +
  • b

u f f e r i d | i n t e g e r | r e l f i l e n

  • d

e |

  • i

d | r e l t a b l e s p a c e |

  • i

d | r e l d a t a b a s e |

  • i

d | r e l f

  • r

k n u m b e r | s m a l l i n t | r e l b l

  • c

k n u m b e r | b i g i n t | i s d i r t y | b

  • l

e a n | u s a g e c

  • u

n t | s m a l l i n t | p i n n i n g _ b a c k e n d s | i n t e g e r |

slide-30
SLIDE 30

V i e w " p g _ c a t a l

  • g

. p g _ s t a t _ b g w r i t e r " C

  • l

u m n | T y p e | M

  • d

i f i e r s

  • +
  • +
  • c

h e c k p

  • i

n t s _ t i m e d | b i g i n t | c h e c k p

  • i

n t s _ r e q | b i g i n t | c h e c k p

  • i

n t _ w r i t e _ t i m e | d

  • u

b l e p r e c i s i

  • n

| c h e c k p

  • i

n t _ s y n c _ t i m e | d

  • u

b l e p r e c i s i

  • n

| b u f f e r s _ c h e c k p

  • i

n t | b i g i n t | b u f f e r s _ c l e a n | b i g i n t | m a x w r i t t e n _ c l e a n | b i g i n t | b u f f e r s _ b a c k e n d | b i g i n t | b u f f e r s _ b a c k e n d _ f s y n c | b i g i n t | b u f f e r s _ a l l

  • c

| b i g i n t | s t a t s _ r e s e t | t i m e s t a m p w i t h t i m e z

  • n

e |

slide-31
SLIDE 31

S E T t r a c k _ i

  • _

t i m i n g =

  • n

;

E X P L A I N ( A N A L Y Z E , B U F F E R S )

slide-32
SLIDE 32
  • >

N e s t e d L

  • p

L e f t J

  • i

n ( c

  • s

t = 3 6 5 7 1 8 . 9 2 . . 9 6 3 3 8 1 6 . 5 7 r

  • w

s = 1 2 7 6 3 2 w i d t h = 8 4 ) ( a c t u a l t i m e = 3 1 3 5 . 2 1 4 . . 7 5 8 3 3 7 7 3 . 6 7 8 r

  • w

s = 2 1 7 5 6 4 1 l

  • p

s = 1 ) J

  • i

n F i l t e r : ( u . x = v . y ) R

  • w

s R e m

  • v

e d b y J

  • i

n F i l t e r : 1 8 3 6 3 8 1 7 9 B u f f e r s : s h a r e d h i t = 6 3 3 4 8 7 1 2 r e a d = 2 3 7 8 7 4 6 d i r t i e d = 7 9 8 4 I / O T i m i n g s : r e a d = 9 6 1 4 5 4 . 6 5 1

slide-33
SLIDE 33

V i e w " p g _ c a t a l

  • g

. p g _ s t a t _ d a t a b a s e " C

  • l

u m n | T y p e | M

  • d

i f i e r s

  • +
  • +
  • d

a t i d |

  • i

d | d a t n a m e | n a m e | n u m b a c k e n d s | i n t e g e r | x a c t _ c

  • m

m i t | b i g i n t | x a c t _ r

  • l

l b a c k | b i g i n t | b l k s _ r e a d | b i g i n t | b l k s _ h i t | b i g i n t | t u p _ r e t u r n e d | b i g i n t | t u p _ f e t c h e d | b i g i n t | t u p _ i n s e r t e d | b i g i n t | t u p _ u p d a t e d | b i g i n t | t u p _ d e l e t e d | b i g i n t | c

  • n

f l i c t s | b i g i n t | t e m p _ f i l e s | b i g i n t | t e m p _ b y t e s | b i g i n t | d e a d l

  • c

k s | b i g i n t | b l k _ r e a d _ t i m e | d

  • u

b l e p r e c i s i

  • n

| b l k _ w r i t e _ t i m e | d

  • u

b l e p r e c i s i

  • n

| s t a t s _ r e s e t | t i m e s t a m p w i t h t i m e z

  • n

e |

slide-34
SLIDE 34

V i e w " p g _ c a t a l

  • g

. p g _ s t a t i

  • _

a l l _ t a b l e s " C

  • l

u m n | T y p e | M

  • d

i f i e r s

  • +
  • +
  • r

e l i d |

  • i

d | s c h e m a n a m e | n a m e | r e l n a m e | n a m e | h e a p _ b l k s _ r e a d | b i g i n t | h e a p _ b l k s _ h i t | b i g i n t | i d x _ b l k s _ r e a d | b i g i n t | i d x _ b l k s _ h i t | b i g i n t | t

  • a

s t _ b l k s _ r e a d | b i g i n t | t

  • a

s t _ b l k s _ h i t | b i g i n t | t i d x _ b l k s _ r e a d | b i g i n t | t i d x _ b l k s _ h i t | b i g i n t |

slide-35
SLIDE 35

V i e w " p g _ c a t a l

  • g

. p g _ s t a t i

  • _

a l l _ i n d e x e s " C

  • l

u m n | T y p e | M

  • d

i f i e r s

  • +
  • +
  • r

e l i d |

  • i

d | i n d e x r e l i d |

  • i

d | s c h e m a n a m e | n a m e | r e l n a m e | n a m e | i n d e x r e l n a m e | n a m e | i d x _ b l k s _ r e a d | b i g i n t | i d x _ b l k s _ h i t | b i g i n t |

slide-36
SLIDE 36

V i e w " p u b l i c . p g _ s t a t _ s t a t e m e n t s " C

  • l

u m n | T y p e | M

  • d

i f i e r s

  • +
  • +
  • u

s e r i d |

  • i

d | d b i d |

  • i

d | q u e r y i d | b i g i n t | q u e r y | t e x t | c a l l s | b i g i n t | t

  • t

a l _ t i m e | d

  • u

b l e p r e c i s i

  • n

| m i n _ t i m e | d

  • u

b l e p r e c i s i

  • n

| m a x _ t i m e | d

  • u

b l e p r e c i s i

  • n

| m e a n _ t i m e | d

  • u

b l e p r e c i s i

  • n

| s t d d e v _ t i m e | d

  • u

b l e p r e c i s i

  • n

| r

  • w

s | b i g i n t | s h a r e d _ b l k s _ h i t | b i g i n t | s h a r e d _ b l k s _ r e a d | b i g i n t | s h a r e d _ b l k s _ d i r t i e d | b i g i n t | s h a r e d _ b l k s _ w r i t t e n | b i g i n t | l

  • c

a l _ b l k s _ h i t | b i g i n t | l

  • c

a l _ b l k s _ r e a d | b i g i n t | l

  • c

a l _ b l k s _ d i r t i e d | b i g i n t | l

  • c

a l _ b l k s _ w r i t t e n | b i g i n t | t e m p _ b l k s _ r e a d | b i g i n t | t e m p _ b l k s _ w r i t t e n | b i g i n t | b l k _ r e a d _ t i m e | d

  • u

b l e p r e c i s i

  • n

| b l k _ w r i t e _ t i m e | d

  • u

b l e p r e c i s i

  • n

|

slide-37
SLIDE 37

V i k Feari n g

Change “and and” to “and”. i rc: xocol atl Tw i tter: @ pg_ xocol atl