PowerPoint 演示文稿

Size: px

Start display at page:

Download "PowerPoint 演示文稿"

外玚金
5 years ago
Views:

1 Happy hacking in Tantan using PostgreSQL PostgreSQL in Tantan 张文升

2 个人简介 - 张文升探探科技 PostgreSQL DBA 曾任职去哪儿网 PostgreSQL DBA 有多年的项目开发经验, 曾参与多个大中型项目建设因为对 PostgreSQL 的爱好与热爱投入 PG 开源社区的怀抱, 成为 PostgreSQL 社区核心成员及社区志愿者, 多次在 PostgreSQL 中国大会各地分会场企业及高校分享心得, 服务多家使用 PostgreSQL 的企业

3 How Tantan works

4 Relationships in Tantan swipes passbys 9000 亿的 relationships 总量 9 亿滑动 / 天 2000 亿 passby 总量最高突破 10 亿滑动 / 天 (July 02)

5 Why PostgreSQL The world s most advanced open source database It has more than 15 years of active development It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (reliability, data integrity, and correctness) PostGIS for Location Based Services (PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL) Nearby users Passby users Distance with a user

6 Agenda Scaling Swipes PostgreSQL in Tantan

7 Scaling Swipes

8 #1 How Swipe works A B A A A liked B B liked A A liked B B liked A B B A A A disliked B B disliked A B B

9 #2 Product Requirements One user should only swipe another user once Mutual likes will create a Match Calculating nearby users Liked users should be ranked higher Disliked users must be filtered out

10 #3 Sharding Principles Scalability Starting with fewer servers Scaling to more servers with less effort Performance Nearby users filtering in real-time Large amount of swipes Simplicity Easy to understand and implement Sharding by user id

11 #4 Sharding Physical Shards (Databases) #1 #2 #64 Logical Shards (Schemas) #128 #256 #8192 Object ID (bigint) timestamp in milliseconds logical shard ID sequ ence Swipes Redundancy Reference: Sharding & ID at Instagram INSERT INTO shard1.swipes (user_id, other_user_id, status) VALUES (100, 200, 'liked'); INSERT INTO shard2.swipes (user_id, other_user_id, other_status) VALUES (200, 100, 'liked'); 41 bits 13 bits 10 bits

12 #5 Sharding (continued)

13 #6 Shards Split

14 #7 Swipes in Tantan

15 PostgreSQL in Tantan

16 #1 Overview core locati on chat shard s shard s shard s 1M9S 1M2S 1M2S 1M1S x 64

17 #2 PostGiS GiST-based R-Tree spatial indexes Rich functions for analysis and processing of GIS objects ST_Point, ST_Distance, ST_Contains etc. Scenarios Find nearby users Calculate distance between users Construct regions database

18 #3 Partial indexes Avoid indexing common values Speed up update operations Scenarios Find nearby users based on seach gender CREATE INDEX ON users USING gist (location) WHERE location IS NOT NULL AND gender = 'female'; CREATE INDEX ON users USING gist (location) WHERE location IS NOT NULL AND gender = 'male' AND (search_gender = ANY(ARRAY['both', 'male']));

19 #4 Stored Procedures Separation of concerns Save extra round trips between client and server Flexibility in using PL/pgSQL Monitoring

20 #5 No Downtime Operations Create or replace a function Add a new column Add a new non-nullable column with a default value (4 steps) Add a default value to an existing column Add an index concurrently Drop a column Drop a constraint etc.

21 应用特点高并发, 小事务, 查询多, 写入多, 更新多, 删除少性能要求毫秒以下各 shards 间数据紧耦合单个 shard 出现性能问题影响到所有 shards 调整应用逻辑拆解复杂函数 ; 冗余数据过多减少冗余, 降低维护难度优化设计, 提升性能批量写, 减少写次数维护窗口每天增长年龄近 3 亿凌晨 2:00~6:00 只有 4 个小时单个 pgbouncer 的负载瓶颈缓存 HA

22 users && profiles core ios,android,web contacts user s contacts location user s location shard shard shard shard shards passby relationships

23 potential hotstandby master shards shards shards shards slave

24 fontend / webservice fontend / webservice pgbouncer pgbouncer master shards

26 扫码关注 GIAC 公众号

untitled

untitled LBS Research and Application of Location Information Management Technology in LBS TP319 10290 UDC LBS Research and Application of Location Information Management Technology in LBS , LBS PDA LBS