Jmeter RDBMS 테스트용 쿼리문 정리

Jmeter 테스트용 쿼리문 정리

Jmeter 에서 RDBMS를 벤치마크하기 위해 사용한 쿼리문들이다.

jdbc:cubrid://220.117.238.110:3306/testdb

220.117.238.110

db외부 접근 과정

커넥션 설정(커넥터가 세팅됨)

-> 외부 접근 허용 설정(ip, 계정) -> 

• 테이블 생성 SQL문

CREATE TABLE INVENTORY

(

idInventory Date,

idChar UInt32,

nameChar String,

nameItem String,

typeItem String,

numItem UInt32

) ENGINE = MergeTree(idInventory,  idChar, 8192);

CREATE TABLE INVENTORY

(

idInventory INT PRIMARY KEY AUTO_INCREMENT,

idChar INT,

nameChar VARCHAR(20),

nameItem VARCHAR(20),

typeItem VARCHAR(20),

numItem INT

);

postgre 문법

postgre dialect - auto increment -> serial

CREATE TABLE INVENTORY

(

idInventory SERIAL PRIMARY KEY NOT NULL ,

idChar INT,

nameChar VARCHAR(20),

nameItem VARCHAR(20),

typeItem VARCHAR(20),

numItem INT

);

-----

• Insert 문

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

777,

'${__RandomString(${__Random(3,8,)},abcdefghijklmnopqrstuvwxyz0123456789,)}' ,

'빨간약' ,

'소모품' ,

${__Random(1,100,)} );

# INSERT TEST : 아이템 획득하여 인벤토리 테이블에 추가하기

# 랜덤한 캐릭터 id, 캐릭터명, 아이템 갯수 생성

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

${__Random(1,1000,)},

'${__RandomString(${__Random(3,8,)},abcdefghijklmnopqrstuvwxyz0123456789,)}' ,

'redpotion' ,

'typePotion' ,

${__Random(1,100,)} );

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

777,

'nameNova' ,

'redpotion' ,

'typePotion' ,

55 );

#UPDATE TEST : 아이템 갯수 업데이트하기

#인벤토리 id를 임의로가져온 후, 임의 갯수(1~100)만큼 추가

UPDATE INVENTORY SET

numItem = numItem + ${__Random(1,100,)}

WHERE idInventory = ${__Random(1,10000,)};

• select : 아이템 목록 가져오기

#SELECT TEST : 캐릭터의 인벤토리 불러오기

#임의로 캐릭터의 id값을 가진 인벤토리를 불러온다.

SELECT * FROM INVENTORY WHERE idChar = ${__Random(1,1000,)};

SELECT idInventory, idChar, nameItem, typeItem, numItem  FROM INVENTORY WHERE idInventory = ${__Random(1,1000,)};

DROP PROCEDURE myFunction;

DELIMITER $$

CREATE PROCEDURE myFunction()

BEGIN

DECLARE i INT DEFAULT 1;

WHILE (i < 1000) DO

START TRANSACTION;

UPDATE INVENTORY SET

numItem = numItem + i

WHERE idInventory = i;

UPDATE INVENTORY SET

numItem = numItem + i + 5

WHERE idInventory = i + 5;

INSERT INTO INVENTORY ( idChar, nameChar, nameItem, typeItem, numItem)

VALUES (

777,

CONCAT('item', i ) ,

'redpotion' ,

'potiontype' ,

i );

COMMIT;

SET i = i + 1;

END WHILE;

END$$

DELIMITER ;

CALL myFunction();

CREATE PROCEDURE insert_data(a integer, b integer)

LANGUAGE SQL

AS $$

INSERT INTO tbl VALUES (a);

INSERT INTO tbl VALUES (b);

$$;

CALL insert_data(1, 2);

#

#

# START TRANSACTION;

#

# UPDATE INVENTORY SET

# numItem = numItem +2

# WHERE idInventory =3;

#

# UPDATE INVENTORY SET

# numItem = numItem +2

# WHERE idInventory =3;

#

# UPDATE INVENTORY SET

# numItem = numItem +2

# WHERE idInventory =3;

#

# COMMIT;

#

#SELECT TEST : 인벤토리 테이블에서 특정 유저의 아이템 목록 가져오기

# SELECT nameItem, typeItem, numItem FROM INVENTORY

# WHERE nameChar = '캐릭터명';

#

#

#

# #

# DELETE FROM INVENTORY;

# ALTER TABLE INVENTORY AUTO_INCREMENT = 1;

# SELECT *from TB_MEMBER;

# INSERT INTO TB_MEMBER (emailMember, pwMember, nameMember)

# VALUES ('test@nova.com', '1234', 'nova');

# INSERT INTO TB_CHARACTER ( idMember, nameChar, jobChar)

# VALUES (4, 'jamsya', 'warrior');

#

# DELETE FROM TB_MEMBER WHERE idMember = 2;

#

# #

# INSERT INTO MEMBER (emailMember, pwMember, nameMember)

# VALUES (

# # 랜덤 이메일을 생성하는 파트

# concat( substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# '@',

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand()*36+1, 1),

# '.com'),

#

# # 랜덤 패스워드를 생성하는 파트

#

# concat('pw', substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1)

# )

# ,

# # 랜덤 회원 이름을 생성하는 파트

# concat('nova',substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1),

# substring('abcdefghijklmnopqrstuvwxyz0123456789', rand() * 36 + 1, 1)

# ));

#

#