(12cR2)
CentOS7
mmm181: シャード・カタログ シャード・ディレクタ
mmm182: シャード・ノード
mmm183: シャード・ノード
メモリ:2G
ディスク:40G
--1.OS設定[mmm181,mmm182,mmm183]
yum update -y
systemctl stop firewalld
systemctl disable firewalld
sed -i.bak "/SELINUX/s/enforcing/disabled/g" /etc/selinux/config
yum -y install \
binutils \
compat-libcap1 \
compat-libstdc++-33.i686 \
compat-libstdc++-33 \
glibc.i686 \
glibc \
glibc-devel.i686 \
glibc-devel \
ksh \
libaio.i686 \
libaio \
libaio-devel.i686 \
libaio-devel \
libgcc.i686 \
libgcc \
libstdc++.i686 \
libstdc++ \
libstdc++-devel.i686 \
libstdc++-devel \
libxcb.i686 \
libxcb \
libX11.i686 \
libX11 \
libXau.i686 \
libXau \
libXi.i686 \
libXi \
libXtst.i686 \
libXtst \
make \
net-tools \
nfs-utils \
smartmontools \
sysstat
vim /etc/sysctl.conf
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
sysctl -p
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba oracle
echo oracle | passwd --stdin oracle
mkdir -p /u01/app/oracle
mkdir -p /oradata/cat/arc
chown -R oracle:oinstall /u01
chown -R oracle:oinstall /oradata
chmod -R 775 /u01
chmod -R 775 /oradata
vim /etc/pam.d/login
session required pam_limits.so
vim /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
vim /home/oracle/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
umask 022
export NLS_LANG=American_America.AL32UTF8
alias cdh='cd $ORACLE_HOME'
alias cdb='cd $ORACLE_BASE'
alias sql='sqlplus / as sysdba'
GRUB_CMDLINE_LINUXの項目に設定追記(transparent_hugepage=never)します
grub2-mkconfig -o /boot/grub2/grub.cfg
reboot
cat /sys/kernel/mm/transparent_hugepage/enabled
vim /etc/hosts
192.168.137.181 mmm181
192.168.137.182 mmm182
192.168.137.183 mmm183
--2.Oracleソフトウェアインストール[mmm181,mmm182,mmm183]
--oracleユーザで実行
unzip linuxx64_12201_database.zip
cd database
LANG=C ./runInstaller
--3.シャード・カタログ用非CDBデータベース作成[mmm181]
export ORACLE_SID=cat
export NLS_LANG=American_America.AL32UTF8
LANG=C dbca
アーカイブ有効化→する
アーカイブの位置→/oradata/cat/arc
--4.シャード・ディレクタ(GSM)ソフトウェアのインストール[mmm181]
unzip linuxx64_12201_gsm.zip
cd gsm
LANG=C ./runInstaller
シャード・ディレクタソフトウェアの場所→/u01/app/oracle/product/12.2.0/gsmhome_1
--5.SDBのデプロイ
--5.1.シャード・カタログのセットアップ[mmm181]
env |grep ORA
export ORACLE_SID=cat
lsnrctl start listener_cat
lsnrctl status listener_cat
alter system set db_create_file_dest='/oradata/cat' scope=both;
alter system set open_links=16 scope=spfile;
alter system set open_links_per_instance=16 scope=spfile;
shutdown immediate
startup
alter user gsmcatuser account unlock;
alter user gsmcatuser identified by oracle;
create user mysdbadmin identified by oracle;
grant connect, create session, gsmadmin_role to mysdbadmin;
grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
env |grep ORA
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsmhome_1
export PATH=$ORACLE_HOME/bin:$PATH
gdsctl
--5.2.シャード・カタログ作成[mmm181]
create shardcatalog -database mmm181:1521/cat -user mysdbadmin/oracle -region region1 -agent_password oracle -force
--5.3.シャード・ディレクタ作成[mmm181]
add gsm -gsm sharddirector1 -pwd oracle -catalog mmm181:1521/cat -region region1
--5.4.シャード・ディレクタ起動[mmm181]
start gsm -gsm sharddirector1
--5.5.オペレーティング・システムの資格証明を設定[mmm181]
add credential -credential shard_cred -osaccount oracle -ospassword oracle
--5.6.リモート・スケジューラ・エージェントの登録[mmm182,mmm183]
schagent -start
schagent -status
schagent -registerdatabase mmm181 8080
mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fast_recovery_area
--5.7.プライマリ・シャードのシャードグループを追加[mmm181]
gdsctl
set gsm -gsm sharddirector1
connect mysdbadmin/oracle
add shardgroup -shardgroup shard_group1 -region region1 -shardspace SHARDSPACEORA -deploy_as PRIMARY
--5.8.スタンバイ・シャードのシャードグループを追加[mmm181]
add shardgroup -shardgroup shard_group2 -region region1 -shardspace SHARDSPACEORA -deploy_as ACTIVE_STANDBY
--5.9.シャードの作成[mmm181]
add invitednode -catalog mmm181:1521/cat -user mysdbadmin/oracle mmm182
create shard -shardgroup shard_group1 -destination MMM182 -credential shard_cred -sys_password oracle -system_password oracle
add invitednode -catalog mmm181:1521/cat -user mysdbadmin/oracle mmm183
create shard -shardgroup shard_group2 -destination MMM183 -credential shard_cred -sys_password oracle -system_password oracle
--5.10.シャード・ディレクタから構成を確認[mmm181]
config
config shardgroup
config shard
--5.11.シャードとレプリカを作成[mmm181]
deploy
--5.12.すべてのシャードがデプロイされたことを確認[mmm181]
config shard
--5.13.すべてのシャードが登録されたことを確認[mmm181]
databases
--5.14.シャードの構成を確認[mmm181]
config shard -shard sh1
--5.15.すべてのプライマリ・シャードで実行されるグローバル・サービスを追加[mmm181]
add service -service oltp_rw_srvc -preferred_all -role PRIMARY
config service
--5.16.oltp_rw_srvcグローバル・サービスを開始[mmm181]
start service -service oltp_rw_srvc
status service
--5.17.スタンバイ・シャードで実行する読取り専用のワークロードのためのグローバル・サービスを追加[mmm181]
add service -service oltp_ro_srvc -preferred_all -role PHYSICAL_STANDBY
config service
--5.18.読取り専用サービスを開始[mmm181]
start service -service oltp_ro_srvc
status service
--6.システム管理のSDBのスキーマ作成[mmm181]
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
sqlplus / as sysdba
alter session enable shard ddl;
create user app_schema identified by oracle;
grant all privileges to app_schema;
grant gsmadmin_role to app_schema;
grant select_catalog_role to app_schema;
grant connect, resource to app_schema;
grant dba to app_schema;
grant execute on dbms_crypto to app_schema;
CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto);
CREATE TABLESPACE products_tsp datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;
CONNECT app_schema/oracle
ALTER SESSION ENABLE SHARD DDL;
CREATE SHARDED TABLE Customers
(
CustId VARCHAR2(60) NOT NULL,
FirstName VARCHAR2(60),
LastName VARCHAR2(60),
Class VARCHAR2(10),
Geo VARCHAR2(8),
CustProfile VARCHAR2(4000),
Passwd RAW(60),
CONSTRAINT pk_customers PRIMARY KEY (CustId),
CONSTRAINT json_customers CHECK (CustProfile IS JSON)
) TABLESPACE SET TSP_SET_1
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;
CREATE SHARDED TABLE Orders
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
OrderDate TIMESTAMP NOT NULL,
SumTotal NUMBER(19,4),
Status CHAR(4),
CONSTRAINT pk_orders PRIMARY KEY (CustId, OrderId),
CONSTRAINT fk_orders_parent FOREIGN KEY (CustId)
REFERENCES Customers ON DELETE CASCADE
) PARTITION BY REFERENCE (fk_orders_parent);
CREATE SEQUENCE Orders_Seq;
CREATE SHARDED TABLE LineItems
(
OrderId INTEGER NOT NULL,
CustId VARCHAR2(60) NOT NULL,
ProductId INTEGER NOT NULL,
Price NUMBER(19,4),
Qty NUMBER,
CONSTRAINT pk_items PRIMARY KEY (CustId, OrderId, ProductId),
CONSTRAINT fk_items_parent FOREIGN KEY (CustId, OrderId)
REFERENCES Orders ON DELETE CASCADE
) PARTITION BY REFERENCE (fk_items_parent);
CREATE DUPLICATED TABLE Products
(
ProductId INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
Name VARCHAR2(128),
DescrUri VARCHAR2(128),
LastPrice NUMBER(19,4)
) TABLESPACE products_tsp;
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsmhome_1
gdsctl
show ddl
config shard -shard sh1
config chunks
----------------------------
--各シャードで確認
select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name;
set linesize 140
column table_name format a20
column tablespace_name format a20
column partition_name format a20
show parameter db_unique_name
select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name;
----------------------------
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
sqlplus / as sysdba
set echo off
SELECT a.name Shard, COUNT(b.chunk_number) Number_of_Chunks
FROM gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b
WHERE a.database_num=b.database_num
GROUP BY a.name
ORDER BY a.name;
conn app_schema/oracle
select table_name from user_tables;
----------------------------
--プライマリシャードで確認
dgmgrl
connect sys/oracle
show configuration
show database sh1
show database sh21
show fast_start failover
----------------------------
SELECT observer_state FROM gsmadmin_internal.broker_configs;
Spiderストレージエンジン
http://sweng.web.fc2.com/ja/database/mysql/spider-engine.html
http://www.otwo.jp/blog/mysql-spider/
http://d.hatena.ne.jp/abcb2/20111010/1318224266
http://nippondanji.blogspot.com/2010/04/spider.html
https://launchpad.net/spiderformysql
mmm121: CentOS7,MySQL5.5 Spider Spiderノード
mmm122: CentOS7,MySQL5.6 innodb データノード
mmm123: CentOS7,MySQL5.6 innodb データノード
--1.Spiderエンジンのインストール
mkdir /usr/local/mysql
yum -y install make cmake automake gcc gcc-c++
yum -y install ncurses-devel
tar zxvf mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95.tgz
cd mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95
rm -rf CMakeCache.txt
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
mv storage/q4m ~/tmp/
make
make install
cd /usr/local/mysql
useradd mysql
echo mysql | passwd --stdin mysql
rm -rf /var/lib/mysql
./scripts/mysql_install_db --user=mysql
--起動
./bin/mysqld --user=mysql &
--パスワード設定
./bin/mysqladmin -u root --socket=/var/lib/mysql/mysql.sock password 'Aaa!1234'
--接続
./bin/mysql -u root -p --socket=/var/lib/mysql/mysql.sock
--停止
./bin/mysqladmin -u root -p --socket=/var/lib/mysql/mysql.sock shutdown
export PATH=$PATH:/usr/local/mysql/bin
source /usr/local/mysql/share/install_spider.sql
show engines;
--2.データノードへのmysqlインストール
インストールの項参照
grant all on *.* to 'root'@'192.168.137.121' identified by 'Aaa!1234';
flush privileges;
--3.シャーディングの構築
--mmm121で実行
CREATE SERVER mmm122 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', PASSWORD 'Aaa!1234', HOST '192.168.137.122', PORT 3306);
CREATE SERVER mmm123 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'root', PASSWORD 'Aaa!1234', HOST '192.168.137.123', PORT 3306);
SELECT * FROM mysql.servers;
drop database test;
create database test;
use test;
CREATE TABLE `tab1` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`level` int(10) unsigned DEFAULT NULL,
`exp` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE = SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(user_id) (
PARTITION p1 comment 'server "mmm122", table "tab1"',
PARTITION p2 comment 'server "mmm123", table "tab1"'
);
---mmm122で実行
create database test;
use test;
CREATE TABLE `tab1` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`level` int(10) unsigned DEFAULT NULL,
`exp` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
---mmm123で実行
create database test;
use test;
CREATE TABLE `tab1` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`level` int(10) unsigned DEFAULT NULL,
`exp` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
--mmm121で実行
INSERT INTO tab1(name, profile, level, exp) VALUES
('NAME01', 'PROF01', '1', '101')
,('NAME02', 'PROF02', '2', '102')
,('NAME03', 'PROF03', '3', '103')
,('NAME04', 'PROF04', '4', '104')
,('NAME05', 'PROF05', '5', '105')
,('NAME06', 'PROF06', '6', '106')
,('NAME07', 'PROF07', '7', '107')
,('NAME08', 'PROF08', '8', '108')
,('NAME09', 'PROF09', '9', '109')
,('NAME10', 'PROF10', '10', '110');
--mmm121,mm122,mm123で実行
SELECT * FROM tab1;
SELECT COUNT(*) FROM tab1;
SELECT SUM(level) FROM tab1;
citus DB
https://docs.citusdata.com/en/v8.1/installation/multi_machine_rhel.html
http://vidaisuki.hatenablog.com/entry/2016/12/22/105150
mmm121 coordinator node
mmm122 worker node
mmm123 worker node
--1. Add repository[全ノードで実施]
curl https://install.citusdata.com/community/rpm.sh | sudo bash
--2. Install PostgreSQL + Citus and initialize a database[全ノードで実施]
yum install -y citus81_11
/usr/pgsql-11/bin/postgresql-11-setup initdb
echo "shared_preload_libraries = 'citus'" | tee -a /var/lib/pgsql/11/data/postgresql.conf
--3. Configure connection and authentication[全ノードで実施]
vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = '*'
vim /var/lib/pgsql/11/data/pg_hba.conf
host all all 192.168.137.0/24 trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
--4. Start database servers, create Citus extension[全ノードで実施]
systemctl start postgresql-11
systemctl status postgresql-11
su - postgres
psql -c "CREATE EXTENSION citus;"
--5. Add worker node information[coordinator nodeで実施]
psql -c "SELECT * from master_add_node('mmm122.example.com', 5432);"
psql -c "SELECT * from master_add_node('mmm123.example.com', 5432);"
--6. Verify that installation has succeeded[coordinator nodeで実施]
psql -c "SELECT * FROM master_get_active_worker_nodes();"
--7.動作確認[coordinator nodeで実施]
wget http://examples.citusdata.com/github_archive/github_events-2015-01-01-{0..5}.csv.gz
gzip -d github_events-2015-01-01-*.gz
CREATE TABLE github_events
(
event_id bigint,
event_type text,
event_public boolean,
repo_id bigint,
payload jsonb,
repo jsonb,
actor jsonb,
org jsonb,
created_at timestamp
);
show citus.shard_count;
show citus.shard_replication_factor;
SELECT create_distributed_table('github_events', 'repo_id');
\d
\copy github_events from '/var/lib/pgsql/github_events-2015-01-01-0.csv' WITH (format CSV);
select count(*) from github_events;
analyze github_events;
select avg(repo_id) from github_events;
explain select avg(repo_id) from github_events;
調べた限り未対応の模様
