test

Size: px

Start display at page:

Download "test"

さやとくやす
7 years ago
Views:

1 PostgreSQL CTO

2 JOIN

4 4

6 6

8 Location Base ( )

9 PostgreSQL

10 x PostgreSQL 2011/8 MySQL MongoDB PostgreSQL GIS 2011/9 Launch PostgreSQL 9.0/PostGIS /1 9.1 upgrade Hstore 2013/9 9.2/PostGIS 2.0 upgrade JSON 9.3

11 PostGIS GPS - (ST_Distance) - (ST_Within) - JOIN Filter

12 PostGIS Postgis GEO 2009 PGCons

13 PostGIS GoogleMap PostGIS

14 DB GIS

15 Hstore URL URL / ALTER Hstore Key/Value

16 Hstore Extension 9.1 Hstore - RDB KVS - INDEX(gist) hstore - Hstore Hstore - TEXT JSON (PostgreSQL 9.3

17 MessageQueue select * from mq where case when (schedule is null or schedule <= current_timestamp)) then pg_try_advisory_lock(tableoid::int, id) else false end order by id limit 1; SELECT dequeue) App INSERT(enqueue) PGSQL 1 Proc PostgreSQL Queue - ( PostgreSQL - - Scheduled Queue Python

18 3. Listen Event Maneger 4. Fork Fire Event Event Runner API 1. Enqueue 2. Notify DB Execute event Event Runner 5. Dequeue Event Runner Listen/Notify JobQueue

20 Streaming Replication PostgreSQL hot standby SlaveDB WAL-e Heroku AWS postgresql Python OpenSouce Wal-e S3 WAL AWS S3 Glacier Glacier 0.01 /GB S3 Glacier

21 WAL S3 Backup Amazon S3 Glacier Glacir Datacenter WAL WAL Streaming replication Primary DB Secondary DB Analytics DB StreamingReplication Wal-e S3 Analytics DB WAL Glacier

22 Glacier WAL S3 Backup Amazon S3 Datacenter WAL WAL Recovery Streaming replication Primary DB Secondary DB Analytics DB StreamingReplication Primary DB WAL

23 Ansible Puppet Chef Python Playbook Playbook VM Jenkins CI Alembic migration Alembic

24 Python Alembic DB DB Alembic DB 3. DB 2.

25 $ alembic revision -m "Add user table" $ ls alembic/versions/ 1b4b2e259907_add_user_table.py git DSL SQL DB def upgrade(): op.create_table( table_name, Column('id', Integer, primary_key = True), Column('name', String), ) def downgrade(): op.drop_table(table_name) DB $ alembic upgrade head App Deploy DB

26 $ alembic $ ls alembic/versions/ INFO [alembic.migration] Context impl PostgresqlImpl. INFO [alembic.migration] Will assume transactional DDL. Current revision for postgres://test@localhost/test: 4feb86442f82 -> 37a38bba9dfd (head), drop obsolete table and columns $ alembic history 4feb86442f82 -> 37a38bba9dfd (head), add user table DB 4feb86442f82 -> 57afagasduw4 (head), add shop table 39624ab9474b -> 4feb86442f82(branchpoint), change aaaaaa None -> 39624ab9474b, initial create tables

27 NewRelic (PHP,Perl,Python etc ) - DB EnterpriseDB NewRelicPlugin ( Slow

29 - SQL 9/9 9:00-21:00 NTV NewRelic Index - DB Buffer - DB Warming -Master : m3.2xlarge standard 1000 IOPS -Slave : m1.large - APP - ELB PreWarming

30 - 3,000req/sec (API - 3,000 TPS - 16,000query/sec SELECT INSERT UPDATE AppServer DB PostgreSQL

31 Amazon RDS 2013/11 RDS PostgreSQL - PostgreSQL PostGIS - Hstore/JSON - - IO ( 30,000 IOPS) - AWS RDS

32 7 PostgreSQL PostGIS Hstore, JSON AWS (RDS)

33 db tech showcase

1,.,,,., RDBM, SQL. OSS,, SQL,,.

1,.,,,., RDBM, SQL. OSS,, SQL,,. 3 10 10 OSS RDBMS SQL 11 10.1 OSS RDBMS............................ 11 10.1.1 PostgreSQL................................. 11 10.1.2 MySQL...................................