BAZY DANYCH – CIĄGŁOŚĆ DZIAŁANIA, SPÓJNOŚĆ DANYCH I DISASTER RECOVERY

Jan Kowalski i Seweryn Ożóg

*Źródło: PBI/Gemius, dane total, kwiecień 2017

generujących ponad 1 200 000 000 odsłon - TO JEST DUŻO ;)

To miesięcznie19 000 000 Realnych Użytkowników

• MySQL (400)• PostgreSQL (100)• Oracle (11)• NoSQL (100)• BigData (1,7 PT HDD, 2064 CORES, 9TB RAM)

DBA+

45000 queries/sec

600000 rows/sec

813 GB – database size

INFRASTRUCTURE

SALT

include: - role.mysql.v2.prod.base: defaults: stats_category: DB MYSQL

formula: db: enabled resolver: enabled daw: enabled cron: enabled

resolver: nameservers: - 10.10.10.53 dnscache: true

cron: jobs: purge_btl: cmd: "dbtool --maxbinarylogssize 25769803776" minute: 5,35

httpd_stats: checks: raid: command: /nagios/check_megaraid_sas

db: cron: base: '15 3,15 * * *' mysql: cnf: mysqld_safe: malloc-lib: /usr/lib64/libjemalloc.so.1 mysqld: server_id: 3 back_log: 32 general_log: 0 innodb_additional_mem_pool_size: 64M innodb_autoextend_increment: 16 innodb_buffer_pool_instances: 4 innodb_buffer_pool_size: 10G innodb_log_buffer_size: 8M innodb_log_file_size: 1G

include: - cluster.bigdata.prod.base

formula: pgsql: enabled

pgsql: cnf: max_connections: 250

DBTOOL

BACKUPY

Recovery Time Objective - How quickly the data must be restored.Recovery Point Objective - How much of the latest data can be lost.

Risks:● (Multiple) Host Failure● (Multiple) DC Failure● (Multiple) Database Failure● Human errors● Data Corruption/Loss

Solution:● RTO/RPO defined per Database.● Recent backups stored remotely and locally ● Backups stored in 2 DCs ● Full binary backups every 12 hours● Backup locally and rotate “binary/archive logs” to remote repository● Backup policy 7 days, 3 week, 3 month with single transaction RPO of up to 2 weeks● Backup recovery tests

DBTOOL

DBTOOL

CENTRAL DB

select instance_host, server_name, iac_start_date, iac_end_date, iac_time from inaccessibility_history join instances on iac_instance_id=instance_id join servers on server_id=instance_run_on_server_idwhere instance_role='master'order by iac_start_date desc;

CENTRAL DB

select instance_host, obh_schema, obh_name, obh_type, obh_action_datefrom object_history straight_join instances on obh_instance_id = instance_idwhere instance_role='master‚# and obh_action_type = 'MODIFICATION‚# and obh_type in ('TABLE', 'INDEX', 'SCHEMA')order by obh_id desc limit 10;

MONITORING

DBPROXY

- connection pooling- result caching- database status- aggregation- permanent sessions- master/slave/sharding- security