Comp 305-001/488-305: Database Administration

Spring 2018: Tuesdays, 4:15-6:45, Comm 013

Week 12

April 10

Homework 4

Using information_schema and pg_catalog

Review of schemas (here and here)

Review of roles


    Facebook timeline and denormalization:


transaction notes

    conflict-serializability algorithm

    isolation rules

Postgres and MVCC

select txid_current();

    select * from pg_locks where transactionid = X;

pgbench: simple transaction simulator

When I run

    pgbench -c 25 -j 25 -t 400 pgbench    # 25 threads, 400 transactions per thread

I get

    tps = 441

That's one transaction in 2.26 ms.

My transaction demo

The version is here.

Experiment 1: run with NUMTHREADS=1, PERTHREAD=1000. Try different isolation levels? Try mysql? Try sorting vs no sorting?


Try with (25,40): lots of deadlock

Try with (2,500): still some deadlock (can we reproduce one of these deadlocks manually?)

Let's try asking for the locks upfront, with REQLOCKS, with/without NOWAIT.

NOWAIT false: works, but takes 20-40 sec!

NOWAIT true (and that one failure line commented out): handful of lock failures, but ~10 sec

Now let's enable SORTING. No problems! 250 tps.


250 tps is not bad. But now does Amazon do it? How could you speed this up?

    My transactions DB:

    Typical transaction based on input custid, part1, quan1, part2, quan2:

insert into invoice(ordertime, custid) values (now(), custid);
insert into invitem values (invnum, part1, quan1);
update part set quan_in_stock = quan_in_stock - quan1 where partnum = part1;
insert into invitem values (invnum, part2, quan2);
update part set quan_in_stock = quan_in_stock - quan2 where partnum = part2;