Share
Embed
How not to step on elephant’s trunk
Sergey Ponomarev
November 15, 2018
Transcript
72 slides
-
HOW NOT TO STEP ON ELEPHANT’S TRUNK Sergey Ponomarev Based
on true stories -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup FACTS ‣ The largest land
animal in the world ‣ They are fast ‣ They are clever 9 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TRUNK IS THE FEATURE ‣
Extremely functional ‣ Extremely complex ‣ Essential for elephants’ life 10 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup The World's Most Advanced Open
Source Relational Database 14 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup RELATIONAL DATABASE ‣ records are
organised into rows ‣ group of records are organised into tables ‣ attributes are stored in table columns ‣ there is an ability to define relationships ‣ query language is provided, usually SQL 15 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ACID ‣ Atomic ‣ Consistent
‣ Isolated ‣ Durable 16 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ATOMICITY CREATE TABLE test (id
SERIAL PRIMARY KEY); BEGIN; INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); COMMIT; BEGIN; INSERT INTO test VALUES (3); INSERT INTO test VALUES (4); ROLLBACK; SELECT * FROM test; -- id -- ---- -- 1 -- 2 -- (2 rows) 17 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup CONSISTENCY Column | Type |
Modifiers --------+---------+--------------------------------------------------- id | integer | not null default nextval('test_id_seq' ::regclass) Indexes: "test_pkey" PRIMARY KEY, btree (id) BEGIN; INSERT INTO test VALUES (1); INSERT INTO test VALUES (2); COMMIT; BEGIN; INSERT INTO test VALUES (2); -- ERROR: duplicate key value violates unique constraint "test_pkey" -- DETAIL: Key (id)=(2) already exists. ROLLBACK; 18 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ISOLATION BEGIN; INSERT INTO test
VALUES (1); INSERT INTO test VALUES (2); -- Concurrent call proceeds here COMMIT; -- Concurrent call SELECT COUNT(*) FROM test; -- 0 19 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DURABILITY BEGIN; INSERT INTO test
VALUES (4); COMMIT; -- COMMIT -- Time: 3.089 ms • Сбой в оборудовании • Отключение электричества 20 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup $ df -h Filesystem Size
Used Avail Use% Mounted on /dev/sda1 500G 467G 33G 93% / udev 64M 0 64M 0% /dev tmpfs 1000M 0 1000M 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm tmpfs 1000M 0 1000M 0% /proc/acpi tmpfs 1000M 0 1000M 0% /sys/firmware 23 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DELETE FROM logs WHERE id
< 100000000; -- DELETE 1000000000 24 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ❓❓❓ $ df -h Filesystem
Size Used Avail Use% Mounted on /dev/sda1 500G 467G 33G 93% / udev 64M 0 64M 0% /dev tmpfs 1000M 0 1000M 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm tmpfs 1000M 0 1000M 0% /proc/acpi tmpfs 1000M 0 1000M 0% /sys/firmware 25 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ALTERNATIVE REALITY Schema | Name
| Type | Owner | Size --------+-------------------+-------+----------+------- public | records | table | app | 100G 26 UPDATE records SET type = 'legacy'; -- UPDATE 3133731337 Schema | Name | Type | Owner | Size --------+-------------------+-------+----------+------- public | records | table | app | 197G -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DISK LAYOUT ‣ 8kb pages
‣ One page consist of one or more records/rows = tuples ‣ Tuple is “immutable” ‣ UPDATE = INSERT + DELETE 29 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TUPLE ‣ ctid – page
id and index on that page ‣ xmin – insert transaction id ‣ xmax – “delete” transaction id 30 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TUPLE 31 BEGIN; SELECT txid_current();
-- 31337 INSERT INTO test VALUES (DEFAUL, 30); SELECT ctid, *, xmin, xmax FROM test; -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TUPLE 32 -- Simply SELECT
* FROM test; -- Is equal SELECT * FROM test WHERE xmin <= txid_current() AND (xmax = 0 OR txid_current() < xmax); -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM is a garbage collector
for PostgreSQL data 34 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM 35 VACUUM VERBOSE records;
INFO: vacuuming "public.records" INFO: index "records_pkey" now contains 471203743 row versions in 2352308 pages DETAIL: 0 index row versions were removed. 999999 index pages have been deleted, 999999 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_records_user_id" now contains 471203743 row versions in 23423 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 2425.00s/0.00u sec elapsed 2525.00 sec. VACUUM ………… -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ❓❓❓ $ df -h Filesystem
Size Used Avail Use% Mounted on /dev/sda1 500G 467G 33G 93% / udev 64M 0 64M 0% /dev tmpfs 1000M 0 1000M 0% /sys/fs/cgroup shm 64M 0 64M 0% /dev/shm tmpfs 1000M 0 1000M 0% /proc/acpi tmpfs 1000M 0 1000M 0% /sys/firmware 36 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DISK UTILISATION 37 DISK 0
% 25 % 50 % 75 % 100 % Reusable Data Freeable -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM FULL 38 VACUUM FULL
VERBOSE records; INFO: vacuuming "public.records" INFO: index "records_pkey" now contains 471203743 row versions in 2352308 pages DETAIL: 0 index row versions were removed. 999999 index pages have been deleted, 999999 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_records_user_id" now contains 471203743 row versions in 23423 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. ………… -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SOLUTIONS ‣ Copy to another
table ‣ pg_repack ‣ AUTOVACUUM tuning for prevention ‣ 40 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup EXPLAIN SELECT * FROM records
WHERE user_id = 31337; QUERY PLAN ------------------------------------------------------------------- ---- Seq Scan on records (cost=0.00 ..239783.28 rows=174 width=195) Filter: (user_id = 31337) 42 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup CONCURRENT INDEX 46 CREATE INDEX
CONCURRENTLY ON users(account_id); -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ✅ ✅ ✅ EXPLAIN SELECT
* FROM records WHERE user_id = 31337; QUERY PLAN ------------------------------------------------------------- Index Scan using index_records_user_id on records (cost=0.43 ..524.33 rows=174 width=195) Index Cond: (user_id = 31337) 47 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PROBLEM \d users; Table "public.users"
Column | Type | Collation | Nullable | Default --------+------------------------+-----------+----------+--------- id | integer | | not null | email | character varying(256) | | not null | trial | boolean | | | Three states of ‘trial’ – true, false, NULL 49 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup REFACTORING ALTER TABLE users ALTER
COLUMN trial SET NOT NULL; 50 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TABLE-LEVEL LOCKS ‣ ACCESS SHARE
// SELECT ‣ ROW SHARE // SELECT FOR UPDATE, FOR SHARE ‣ ROW EXCLUSIVE // UDPATE, DELETE, INSERT ‣ SHARE UPDATE EXCLUSIVE // VACUUM, ALTER, CREATE INDEX CONCURRENTLY ‣ SHARE // CREATE INDEX ‣ SHARE ROW EXCLUSIVE // ALTER ‣ EXCLUSIVE // REFRESH MATERIALIZED VIEW ‣ ACCESS EXCLUSIVE // DROP, TRUNCATE, VACUUM FULL, ALTER TABLE 54 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SOLUTION ALTER TABLE users ADD
CONSTRAINT not_null_trial CHECK (trial IS NOT NULL) NOT VALID; ALTER TABLE users VALIDATE CONSTRAINT not_null_trial; 56 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup RESULT \d users; Table "public.users"
Column | Type | Collation | Nullable | Default --------+------------------------+-----------+----------+--------- id | integer | | not null | email | character varying(256) | | not null | trial | boolean | | | Check constraints: "not_null_trial" CHECK (trial IS NOT NULL) 57 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PARANOID MODE ON 59 $
rails c --sandbox Loading development environment in sandbox (Rails 5.2.0) Any modifications you make will be rolled back on exit [1] (rails_new) main: 0> User.count (17.7ms) SELECT COUNT(*) FROM "users" => 1 [2] (rails_new) main: 0> User.destroy_all User Load (0.4ms) SELECT "users".* FROM "users" (1.5ms) SAVEPOINT active_record_1 User Destroy (7.4ms) DELETE FROM "users" WHERE "users"."id" = $1 [["id", 1]] (0.7ms) RELEASE SAVEPOINT active_record_1 => [#] [3] (rails_new) main: 0> User.count (0.3ms) SELECT COUNT(*) FROM "users" => 0 [4] (rails_new) main: 0> (0.8ms) ROLLBACK -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SCENARIO ‣ $ rails -c
sandbox ‣ User.count ‣ ☎ ‣ … 60 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SCENARIO ‣ $ rails -c
sandbox ‣ User.count ‣ ☎ ‣ ALTER TABLE users ADD COLUMN lol VARCHAR; 61 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PG BIBLES ‣ Alexey Vasiliev
– Safe and unsafe operations for high volume PostgreSQL ‣ Илья Космодемьянский – Внутреннее устройство PostgreSQL для практикующих инженеров ‣ Tom Lane – Transaction Processing in PostgreSQL ‣ Dmitri Fountaine – Mastering PostgreSQL in Application Development 65 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup PARANOID MODE -- Limit potentially
dangerous operations -- (inside current transaction) SET lock_timeout = X -- default: 0 -- Logging slow locks (server level) SET log_lock_waits = true -- default: false SET deadlock_timeout = 1s -- default: 1s 68 -
@bufo_alvarius @sponomarev Tver.IO Philosophy Meetup CREDITS 72 Tusks | The.Rohit
| Flickr Elephant bath | Tambako The Jaguar | Flickr Elephant, riding an elephant, man and asia HD photo by Jordi Ganduxe (@gandu) on Unsplash Not Wassana photo by Céline Haeberly (@celinehaeberly) on Unsplash elephant | elephant, tanzania | Andy Lederer | Flickr Elephants | Subash BGK | Flickr Elephant, animal, tusk and trunk HD photo by Jason Briscoe (@jbriscoe) on Unsplash Grey Elephant Throwing Sand With Trunk Near Green Trees · Free Stock Photo Elephant | Got a little too much soil with that one. | C. E. Timothy Paine | Flickr Elephant at Work-1& | Elephant at Work | John | Flickr