How not to step on elephant’s trunk

Transcript

1. HOW NOT TO STEP ON ELEPHANT’S TRUNK Sergey Ponomarev Based on true stories
2. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 2 @sponomarev @bufo_alvarius Sergey Ponomarev
3. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 3
4. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 4
5. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 5 evilmartians.com
6. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup evilmartians.com/chronicles 6
7. ELEPHANTS
8. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TYPES 8
9. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup FACTS ‣ The largest land animal in the world ‣ They are fast ‣ They are clever 9
10. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TRUNK IS THE FEATURE ‣ Extremely functional ‣ Extremely complex ‣ Essential for elephants’ life 10
11. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 11
12. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 12
13. POSTGRESQL
14. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup The World's Most Advanced Open Source Relational Database 14
15. @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
16. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup ACID ‣ Atomic ‣ Consistent ‣ Isolated ‣ Durable 16
17. @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
18. @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
19. @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
20. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DURABILITY BEGIN; INSERT INTO test VALUES (4); COMMIT; -- COMMIT -- Time: 3.089 ms • Сбой в оборудовании • Отключение электричества 
 20
21. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 21
22. LOST IN DISK SPACE Story 1
23. @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
24. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DELETE FROM logs WHERE id < 100000000; -- DELETE 1000000000 24
25. @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
26. @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
27. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Isolation/Atomicity trade-off 27
28. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Multi version concurrency control 28 MVCC
29. @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
30. @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
31. @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;
32. @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);

33. DEEP CLEANING Story 2
34. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup VACUUM is a garbage collector for PostgreSQL data 34
35. @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 …………
36. @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
37. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup DISK UTILISATION 37 DISK 0 % 25 % 50 % 75 % 100 % Reusable Data Freeable
38. @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. …………
39. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 39
40. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SOLUTIONS ‣ Copy to another table ‣ pg_repack ‣ AUTOVACUUM tuning for prevention ‣ 40
41. BOOST Story 3
42. @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
43. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup INDEX 43 CREATE INDEX ON users(account_id);
44. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 44
45. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Consistency trade-off 45
46. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup CONCURRENT INDEX 46 CREATE INDEX CONCURRENTLY ON users(account_id);
47. @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
48. GOT STUCK Story 4
49. @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
50. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup REFACTORING ALTER TABLE users ALTER COLUMN trial SET NOT NULL; 50
51. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 51
52. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Consistency trade-off 52
53. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup TABLE-LEVEL LOCKS 53
54. @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
55. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 55 DEAL WITH IT
56. @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
57. @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
58. NOT A MANAGER’S FAULT Story 5
59. @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
60. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SCENARIO ‣ $ rails -c sandbox ‣ User.count ‣ ☎ ‣ … 60
61. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup SCENARIO ‣ $ rails -c sandbox ‣ User.count ‣ ☎ ‣ ALTER TABLE users ADD COLUMN lol VARCHAR; 61
62. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup 62
63. MAIN SENSE
64. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Know your tools 64
65. @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
66. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Think twice 66
67. @bufo_alvarius @sponomarev Tver.IO Philosophy Meetup Be ready for worst-case scenario 67
68. @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
69. CULT OF MARTIANS.COM
70. https://www.worldwildlife.org/species/elephant
71. THANKS! Sergey Ponomarev evilmartians.com/chronicles @sponomarev @bufo_alvarius @evilmartians
72. @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