-- max_connections = 500
-- aborted_connects = 0
-- connections = 5862081
-- max_ued_connections = 345
-- threads_connected = 5
-- aborted_clients = 162
-- Threads_created = 398116

-- Threads_created / connections = 398116 / 5862081 = 6.79%;
-- aborted_connects / max_ued_connections = 0 / 345 = 0% (connection miss)
-- Threads_connected / max_connections = 5 / 500 = 1%
show variables like 'max_conn%'; -- 500
show status like '%connect%';
show status like '%clients%';
show status like '%thread%';


show status like '%table_open_cache%';

show variables like '%thread_cache_size%';

-- key_buffer_size = 메모리 25%
-- table_open_cache = 테이블 갯수
-- thread_concurrency = cpu 갯수 * 2


select 8388608 / (1024 * 1024);

-- innodb_buffer_pool_size = 70 ~ 80% 메모리
-- innodb_log_file_size = 128 ~ 256M
-- innodb_log_files_in_group = 3
-- innodb_flush_log_at_trx_commit = 2
-- innodb_doublewrite off
-- sync_binlog = 0
show variables like '%key_buffer_size%';

show status like '%select%';
show variables like 'innodb%';
set global max_connections=500;

SHOW VARIABLES LIKE 'slow_query_%';

-- 리그 포인트 갱신
select * from cookie_master.tbl_account where nick_name like '111%';
select * from tbl_user where user_idx = 3;
select * from tbl_user_oven where user_idx = 3;

call spTcpUpdateUserInfo(441210, 170, 0, 0, 0, 1);


-- 쿠키 사용 금지
-- UPDATE `cookie_static_gameshard`.`static_tbl_cookie` SET c_disable = 1 WHERE `c_id` not in (1,2,4,6,7,11,16,23,3,5,8,13,19,26,27,12,20) and`c_level`>='1';
-- 상점에서 보여줄지 여부 결정
-- UPDATE `cookie_static_gameshard`.`static_tbl_shop_item_list` SET shop_item_show_option = 0 WHERE `shop_item_id` not in (1,2,4,6,7,11,16,23,3,5,8,13,19,26,27,12,20) and shop_item_id < 100;

-- 쿠키 사용 금지
UPDATE `cookie_static_gameshard`.`static_tbl_cookie` SET c_disable = 0;
UPDATE `cookie_static_gameshard`.`static_tbl_cookie` SET c_disable = 1 WHERE `c_id` in (18, 28, 29, 30);

-- 상점에서 보여줄지 여부 결정
UPDATE `cookie_static_gameshard`.`static_tbl_shop_item_list` SET shop_item_show_option = 1 WHERE shop_item_id < 100;
UPDATE `cookie_static_gameshard`.`static_tbl_shop_item_list` SET shop_item_show_option = 0 WHERE `shop_item_id` in (18, 28, 29, 30) and shop_item_id < 100;

-- 레어러티 변경시
UPDATE `cookie_static_gameshard`.`static_tbl_shop_item_list` shop, cookie_static_gameshard.static_tbl_cookie cookie
	SET `shop_item_l_grade`=cookie.c_league_grade, `shop_item_display_order`=cookie.c_rarity - 1, `shop_item_sell_price`=case cookie.c_rarity when 1 then 2 when 2 then 20 when 3 then 800 when 4 then 10000 end, `shop_item_show_option`=if(cookie.c_rarity = 4, 0, 1)
	WHERE `shop_item_id`>='1' and shop_item_id < 100 and shop.shop_item_type_id = cookie.c_id;

-- 유저 상점 히스토리 갱신
UPDATE tbl_user_shop set refresh_time = DATE_SUB(refresh_time, interval 2 day) where user_idx > 0;


-- 유저 카운팅
select user_idx, pub_id, nick_name, `status`, login_type, phone_type, create_date, last_login_date from tbl_account where user_idx > 10000 and time_zone = 12;
select user_idx, pub_id, nick_name, `status`, login_type, phone_type, create_date, last_login_date from tbl_account where user_idx > 10000 and time_zone != 9;
select user_idx, pub_id, nick_name, world_id, prev_room_id, prev_room_time, login_type, time_zone, phone_type from tbl_account where user_idx > 10000 and `status` = 1;
select world_id, ip, port, name, max_user, current_user_count from tbl_server_list;


-- 유저 테이블 초기화
SET SQL_SAFE_UPDATES =0;
truncate cookie_master.tbl_account;
truncate cookie_master.tbl_club;
truncate cookie_master.tbl_user_rank;
truncate cookie_master.tbl_club_rank;

truncate cookie_gameshard1.tbl_user;
truncate cookie_gameshard1.tbl_user_club;
truncate cookie_gameshard1.tbl_user_club_history;
truncate cookie_gameshard1.tbl_user_cookie;
truncate cookie_gameshard1.tbl_user_deck;
truncate cookie_gameshard1.tbl_user_event_history;
truncate cookie_gameshard1.tbl_user_formation;
truncate cookie_gameshard1.tbl_user_friend;
truncate cookie_gameshard1.tbl_user_inventory;
truncate cookie_gameshard1.tbl_user_mailbox;
truncate cookie_gameshard1.tbl_user_mission;
truncate cookie_gameshard1.tbl_user_mission_status;
truncate cookie_gameshard1.tbl_user_oven;
truncate cookie_gameshard1.tbl_user_payment;
truncate cookie_gameshard1.tbl_user_provider;
truncate cookie_gameshard1.tbl_user_shop;

truncate cookie_gameshard3.tbl_user;
truncate cookie_gameshard3.tbl_user_club;
truncate cookie_gameshard3.tbl_user_club_history;
truncate cookie_gameshard3.tbl_user_cookie;
truncate cookie_gameshard3.tbl_user_deck;
truncate cookie_gameshard3.tbl_user_event_history;
truncate cookie_gameshard3.tbl_user_formation;
truncate cookie_gameshard3.tbl_user_friend;
truncate cookie_gameshard3.tbl_user_inventory;
truncate cookie_gameshard3.tbl_user_mailbox;
truncate cookie_gameshard3.tbl_user_mission;
truncate cookie_gameshard3.tbl_user_mission_status;
truncate cookie_gameshard3.tbl_user_oven;
truncate cookie_gameshard3.tbl_user_payment;
truncate cookie_gameshard3.tbl_user_provider;
truncate cookie_gameshard3.tbl_user_shop;


truncate cookie_gameshard2.tbl_user;
truncate cookie_gameshard2.tbl_user_club;
truncate cookie_gameshard2.tbl_user_club_history;
truncate cookie_gameshard2.tbl_user_cookie;
truncate cookie_gameshard2.tbl_user_deck;
truncate cookie_gameshard2.tbl_user_event_history;
truncate cookie_gameshard2.tbl_user_formation;
truncate cookie_gameshard2.tbl_user_friend;
truncate cookie_gameshard2.tbl_user_inventory;
truncate cookie_gameshard2.tbl_user_mailbox;
truncate cookie_gameshard2.tbl_user_mission;
truncate cookie_gameshard2.tbl_user_mission_status;
truncate cookie_gameshard2.tbl_user_oven;
truncate cookie_gameshard2.tbl_user_payment;
truncate cookie_gameshard2.tbl_user_provider;
truncate cookie_gameshard2.tbl_user_shop;

truncate cookie_gameshard4.tbl_user;
truncate cookie_gameshard4.tbl_user_club;
truncate cookie_gameshard4.tbl_user_club_history;
truncate cookie_gameshard4.tbl_user_cookie;
truncate cookie_gameshard4.tbl_user_deck;
truncate cookie_gameshard4.tbl_user_event_history;
truncate cookie_gameshard4.tbl_user_formation;
truncate cookie_gameshard4.tbl_user_friend;
truncate cookie_gameshard4.tbl_user_inventory;
truncate cookie_gameshard4.tbl_user_mailbox;
truncate cookie_gameshard4.tbl_user_mission;
truncate cookie_gameshard4.tbl_user_mission_status;
truncate cookie_gameshard4.tbl_user_oven;
truncate cookie_gameshard4.tbl_user_payment;
truncate cookie_gameshard4.tbl_user_provider;
truncate cookie_gameshard4.tbl_user_shop;


truncate cookie_log.tbl_blacklist;
truncate cookie_log.tbl_error_log;
truncate cookie_log.tbl_level_log;
truncate cookie_log.tbl_play_log;
truncate cookie_log.tbl_reward_log;
truncate cookie_log.tbl_tcp_error_log;
truncate cookie_log.tbl_transfered_log;




-- 리그 조정
UPDATE tbl_user SET l_grade = 1 WHERE l_point <= 299;
UPDATE tbl_user SET l_grade = 2 WHERE l_point >= 300 and l_point <= 699;
UPDATE tbl_user SET l_grade = 3 WHERE l_point >= 700 and l_point <= 1499;
UPDATE tbl_user SET l_grade = 4 WHERE l_point >= 1500 and l_point <= 2799;
UPDATE tbl_user SET l_grade = 5 WHERE l_point >= 2800 and l_point <= 10000;


-- 출석 체크 초기화
UPDATE tbl_user SET attend_count = 0, attend_recv_date = '1900-01-01 00:00:00' WHERE user_idx > 10000;	-- QA
UPDATE tbl_user SET attend_count = 0, attend_recv_date = '0000-00-00 00:00:00' WHERE user_idx > 10000;	-- LIVE
