- Sybase 성능 분석 리포트 결과를 편집하는 쉘

isql 에서 sp_sysmon 을 실행하면 많은 항목들에 대한 결과가 나오는데

이중에서 내가 필요한 항목은 달랑 3개 (Engine Utilization, Data Cache Management, Procedure Cache Management)

정기적으로 보고 하는 자료이기에 매번 수동 편집하지 않고 실행만 하면 자동 편집되는 쉘을 만들었습니다.

비슷한 요구사항의 작업 시에 참고하기실...

grep -n 으로 원하는 내용의 라인수 출력,

파일 한줄씩 읽어들여 배열에 저장,

sed -n 으로 원하는 부분만 편집 등을 사용했습니다. 

01_exec_sysmon.sh  : isql 에서 sp_sysmon 실행

----------------------------------------------------------

DATE=`date +%Y%m%d%H%M`

echo $DATE

isql -Usa -w400 -P1111 << 'EOF' >> ./logs/sysmon.log.$DATE
sp_sysmon '00:05:00'
go
exit
EOF
----------------------------------------------------------

02_make_editsh.sh : 분석로그를 편집하는 쉘을 만들기 위한 전 단계 (sed 에 변수($) 처리가 잘 안되서

                              echo 를 redirect 해서 결과를 쉘로 만들기

----------------------------------------------------------

echo '\n'
echo '\n'
echo '### beginning of the shell ###'
echo '\n'

filename=`ls -l ./logs/sysmon.log.* | grep "^-" | tail -1 | awk '{print $9 }'`

echo 'The name of the result file is :' $filename
echo '\n'
echo '\n'

grep -n "Engine Utilization" $filename | awk '{print $1}' > filename.tmp
grep -n "Data Cache Management" $filename | awk '{print $1}' >> filename.tmp
grep -n "Procedure Cache Management" $filename | awk '{print $1}' >> filename.tmp

#cat filename.tmp

sed -e s/:/'  '/g filename.tmp | awk '{print $1}' > filename2.tmp

#cat filename2.tmp

index=0
while read line ; do
    ary[$index]="$line"
    index=$(($index+1))
done < filename2.tmp

a=${ary[0]}
b=${ary[1]}
c=${ary[2]}

aa=`expr $a + 23`
bb=`expr $b + 13`
cc=`expr $c + 7`

echo 'sed -n '\'$a','$aa'p'\' $filename '> engine_busy.log' > edit_log.sh
echo 'sed -n '\'$b','$bb'p'\' $filename '> cache_hit.log'   >> edit_log.sh
echo 'sed -n '\'$c','$cc'p'\' $filename '> procedure_cache.log' >> edit_log.sh

echo '\n'
echo '#### before executing editing shell ####'
echo '\n'

sh ./edit_log.sh

echo '#### done executing the editing shell ####'
echo '\n'

03_print_result.sh  : 편집된 결과 출력

----------------------------------------------------------

echo '  ######################################################################### '
echo '\n'
cat engine_busy.log
echo '\n'
echo '  ######################################################################### '
echo '\n'
cat cache_hit.log
echo '\n'
echo '  ######################################################################### '
echo '\n'
cat procedure_cache.log
echo '\n'

select t.HOST명, t.디바이스명, t.물리적디바이스명, t.DB명, t.할당, t.가용, t.사용율, t.SegMap설명, t.crdate
from
(
SELECT      
"HOST명"=@@servername,     
"디바이스명" = substring(d.name, 1, 20),     
"물리적디바이스명"=d.phyname,      
"DB명" = db_name(u.dbid),     
"할당" = round(u.size /(512.0/(@@maxpagesize/@@pagesize)),2),     
"가용"=  round(curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs)/ (512.0/(@@maxpagesize/@@pagesize)),2) ,     
"사용율"= round(  ((u.size-curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs))*100.0/u.size),2) ,     
"SegMap설명" = case segmap when 4 then "LOG"     
               else "DATA" end,   
convert(varchar(8),u.crdate,112) as crdate,1 col
FROM      
master.dbo.sysdevices d,      
master.dbo.sysusages u      
WHERE   u.vdevno = d.vdevno
and d.phyname not in ('/dev/rmt4','/dev/rst0')
and db_name(u.dbid) not in ('master','sybsystemdb','sybsystemprocs','tempdb','tempdb2')     
union all
SELECT      
"HOST명"=@@servername,     
"디바이스명" = substring(d.name, 1, 20),     
"물리적디바이스명"=d.phyname,      
"DB명" = db_name(u.dbid),     
"할당" = round(u.size /(512.0/(@@maxpagesize/@@pagesize)),2),     
"가용"=  round(curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs)/ (512.0/(@@maxpagesize/@@pagesize)),2) ,     
"사용율"= round(  ((u.size-curunreservedpgs(u.dbid,u.lstart,u.unreservedpgs))*100.0/u.size),2) ,     
"SegMap설명" = case segmap when 4 then "LOG"     
               else "DATA" end,   
convert(varchar(8),u.crdate,112) as crdate, 2 col   
FROM      
master.dbo.sysdevices d,      
master.dbo.sysusages u      
WHERE   u.vdevno = d.vdevno
and d.phyname not in ('/dev/rmt4','/dev/rst0')
and db_name(u.dbid) in ('master','sybsystemdb','sybsystemprocs','tempdb','tempdb2')     
) t
ORDER BY col, DB명

sybase 15.7 기준

isql -Usa -w400     

2) lock session check

sp_lock

-- lock check

sp_who

-- session check

spid 확인

3) kill 세션

kill 'spid'

# ASE

-- 건수제한

sp_modify_resource_limit '계정', NULL,'at all times',row_count,건수,2,4,1

-- 시간제한

sp_modify_resource_limit ‘계정’, NULL,'at all times',elapsed_time,시간,2,4,6

​

ex) sp_modify_resource_limit 'testuser', NULL,'at all times',row_count,200,2,4,1

​

확인 : sp_help_resource_limit 

​

#IQ

​

​​sp_iqcheckoptions

set option 계정.QUERY_ROWS_RETURNED_LIMIT ='1000'

​

-- 컬럼 타입 변경
alter table TESTTB modify colA VARCHAR(1)

-- 컬럼 이름 변경 
sp_rename 'TESTTB.colA', 'colB'

=> colA 를 colB 로 변경

-- 컬럼 추가
ALTER TABLE sample.dbo.employee ADD new_col char(25) DEFAULT '22' NOT NULL

-- 특정 컬럼이름 찾기

select distinct object_name(id)
from syscolumns
where name = 'colA'
order by object_name(id)

* 테이블 생성

--drop table TESTTB

create table TESTTB (
colA CHAR(2) not null,
colB CHAR(4) not null,
colC CHAR(6) not null,
colD VARCHAR(20) not null
)
lock datarows
with dml_logging = full
on 'default'
go

Grant Insert on dbo.TESTTB to test_user_role Granted by dbo
go

* 특정계정 정보

sp_displaylogin userA

* 계정별 가진 권한 확인

use master
go

select t1.name, t3.name
from syslogins t1, sysloginroles t2, syssrvroles t3
where t2.suid = t1.suid and
t3.srid = t2.srid
order by 1,2
go

--------- -------------- 
userA      sa_role

* 특정 role을 누가 가지고 있는지 확인

use master
go

select sr.name, sl.name
from syssrvroles sr, syslogins sl, sysloginroles slr
where sl.suid = slr.suid
and sr.srid = slr.srid
and sr.name like "sa_role"

------- ---------
sa_role sa
 

* Role 부여

Syntax

sp_role {"grant" | "revoke"}, rolename, loginame

ex) sp_role 'grant', test_user_role, userB

      또는 grant create table to test_user_role

* role에 부여된 권한 확인

sp_helprotect test_user_role

1. 복제 설정

1.1 GRANT REPLICATION SLAVE

@all

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*

TO replicant@'%'

IDENTIFIED BY 'replicant';

1.2 my.cnf 편집

 @Master

 cd /var/log

 mkdir /var/log/mysql

 chown –R mysql.mysql ./mysql

 service mysqld stop

  vi /etc/my.cnf

log_bin = /var/log/mysql/mariadb-bin

[mysqld]

server_id = 101

relay_log = db01-relay-binlog

@Slave

service mysqld stop

vi /etc/my.cnf

[mysqld]

server_id = 102

read_only
 

@Master, Slave

service mysqld start

1.3 복제 설정

@Master

Ifconfig

mysql -u root -p

show master status;

@Slave

CHANGE MASTER TO MASTER_HOST='10.10.10.51',

MASTER_USER = 'replicant',

MASTER_PASSWORD = 'replicant',

MASTER_PORT=33060,

MASTER_LOG_FILE = 'mariadb-bin.000002',

MASTER_LOG_POS = 330;

START SLAVE; 

2. 복제 동작 확인

2.1show slave status 로 확인

@Slave

SHOW SLAVE STATUS;

Slave_IO_Running | Slave_SQL_Running  => yes 인가?

2.2 global_status 에서 확인

use mysql

SELECT variable_value

FROM information_schema.global_status

WHERE variable_name='SLAVE_RUNNING';

3. 복제 테스트

3.1 기본 설정

@Master

CREATE DATABASE IF NOT EXISTS temp;

USE temp;

CREATE TABLE doctors(

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

given_names varchar(255),

surname varchar(255),

birthdate date);

INSERT INTO doctors VALUES(1,'William','Hartnell','1908-01-08');

INSERT INTO doctors VALUES(2,'Patrick','Troughton','1920-03-25');

INSERT INTO doctors VALUES(3,'John','Pertwee','1919-07-07');

INSERT INTO doctors VALUES(4,'Tom','Baker','1934-01-20');

@Slave

show databases;

use temp

show tables;

select * from doctors;

3.2 Replicate_ignore_db

-     stop slave, 확인

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT variable_value

               -> FROM information_schema.global_status

               -> WHERE variable_name='SLAVE_RUNNING';

+----------------+

| variable_value |

+----------------+

| OFF            |

+----------------+

1 row in set (0.00 sec)

MariaDB [(none)]> exit

Bye

-     my.cnf 에 replicate_ignore_db 추가

[mysql@localhost ~]$ vi /etc/my.cnf

...

[mysqld]

replicate_ignore_db='information_schema'

replicate_ignore_db='performance_schema'

replicate_ignore_db='mysql'

...

-    mysqld 재기동, slave 재시작

[mysql@localhost ~]$ service mysqld restart

[mysql@localhost ~]$ sql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 10.2.7-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> start slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> stop slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status;

-     복제 테스트 ( ignore 확인)

@Master

MariaDB [temp]> use mysql

Database changed

MariaDB [mysql]> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| column_stats              |

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| gtid_slave_pos            |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| index_stats               |

| innodb_index_stats        |

| innodb_table_stats        |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| reptest                   |

| roles_mapping             |

| servers                   |

| slow_log                  |

| table_stats               |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

31 rows in set (0.00 sec)

MariaDB [mysql]> create table aaaa(a int);

Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> use mysql

Database changed

MariaDB [mysql]> show tables;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| column_stats              |

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| gtid_slave_pos            |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| index_stats               |

| innodb_index_stats        |

| innodb_table_stats        |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| reptest                   |

| roles_mapping             |

| servers                   |

| slow_log                  |

| table_stats               |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

31 rows in set (0.00 sec)

-    복제 테스트 ( 나머지는 되는가 확인)

MariaDB [mysql]> use temp

Database changed

MariaDB [temp]> show tables;

+----------------+

| Tables_in_temp |

+----------------+

| a              |

| doctors        |

+----------------+

2 rows in set (0.00 sec)

MariaDB [temp]> create table aaa(a int);

Query OK, 0 rows affected (0.02 sec)

MariaDB [mysql]> use temp;

Database changed

MariaDB [temp]> show tables;

+----------------+

| Tables_in_temp |

+----------------+

| a              |

| aaa            |

| doctors        |

+----------------+

3 rows in set (0.00 sec)