v0112358

MongoDB with replica sets before switchover:

mongodb-01: {role: primary, priority: 9, hidden: 0}
mongodb-02: {role: secondary, priority: 1, hidden: 0}
mongodb-03: {role: secondary, priority: 1, hidden: 0}
mongodb-04: {role: secondary, priority: 7, hidden: 0}
mongodb-05: {role: secondary, priority: 1, hidden: 0}

MongoDB with replica sets after switchover:

v0112358

1/ Check index size

SELECT concat(table_schema,'.',table_name) tables,
concat(round(table_rows/1000000,2),'M') rows,
concat(round(data_length/(1024*1024*1024),2),'G') data_size,
concat(round(index_length/(1024*1024*1024),2),'G') index_size,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) index_data_ratio
FROM information_schema.TABLES
WHERE table_name like "%PRIVILEGES%"

v0112358

1/ On master

CREATE TABLE event_store (
id bigint DEFAULT nextval('public.eid_seq_1'::regclass) NOT NULL,
topic character varying(255),
created_at timestamp(6) without time zone DEFAULT now()
) PARTITION BY RANGE (created_at);

v0112358

- How many transaction does the database handle per table, per hour? 
- Which table has the most inserts? 
- Do we delete data from this table? 
- Which hour has the highest peak of updates, and on which tables?

v0112358

Odssey

Advanced multi-threaded PostgreSQL connection pooler and request router.

How to build odyssey on CentOS 7

yum install git cmake postgresql11-devel openssl-devel zlib-devel gcc -y
PATH=$PATH:/usr/pgsql-11/bin/
export PATH
LD_LIBRARY_PATH=/usr/pgsql-11/lib/

v0112358

• Change the relay_log variable in the my.cnf to relay_log = /mnt/relay/mysql_relay_bin

• Stop mysqld

systemctl stop mysqld

• Move all relay_log to new location:

v0112358

rmmod iptable_nat
rmmod nf_nat_ipv4
rmmod nf_nat
rmmod nf_conntrack_ipv4
rmmod nf_conntrack

v0112358

• Bizcloud simple storage

s3fs cdn_simple /bizfly -o use_cache=/tmp/ -o parallel_count=25 -o passwd_file=/root/.passwd-bizfly -o sigv2 -o url=https://ss-hn-1.vccloud.vn

• Viettel Cloudstorage

s3fs cdn_simple /viettel -o sigv2 -o use_path_request_style -o use_cache=/tmp/ -o parallel_count=25 -o passwd_file=/root/.passwd-viettel -o url=http://s1.cloudstorage.com.vn

v0112358

Numbers & types of operations (selects, updates, inserts, rollbacks, etc)
Cache hit rates and sizing recommendations
Query response times
Table scans and index usage
Temporary disk tables
Abnormal connection events
Replication status

v0112358

Note: You may wish to convert MyISAM tables to InnoDB tables before you proceed.

innodb_file_per_table is by default ON Mysql 5.6.6 and onwards. There is plenty of stuff on Google about pros & cons ofinnodb_file_per_table.

This post details how to enable innodb_file_per_table on an existing database. Because innodb_file_per_table affects new tables only, created after innodb_file_per_table is enabled, we need to recreate old databases to force innodb_file_per_table on old tables and reclaim some disk space.

Server info

• Percona Server 5.5.
• innodb_file_per_table was not enable.
• innodb_data_file_path = ibdata1:12G:autoextend
• Size of ibdata1 is 25GB. It's too big and we can lose our data when the server crash.