mysql配置优化
#1.mysql默认的最大连接数(默认本地为151) 服务器100 推荐1024
Max_connections=300
#mysql能暂存的连接数量 默认80 50 128
back_log=90
#2.3.wait_timeout:指的是mysql在关闭一个非交互的连接之前所要等待的秒数 interactive_timeoutz:指的是mysql在关闭一个交互的连接之前所需要等待的秒数,比如我们在终端上进行mysql管理,使用的即使交互的连接,这时候,如果没有操作的时间超过了interactive_time设置的时间就会自动的断开,默认的是28800,可调优为7200。wait_timeout:如果设置太小,那么连接关闭的就很快,从而使一些持久的连接不起作用 如果设置太大,容易造成连接打开时间过长,在show processlist时候,能看到很多的连接 一般希望wait_timeout尽可能低
28800 28800 100
wait_timeout=3600
#默认 28800 28800
interactive_timeout=7200
#4.key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度 8388608(8M) 8388608(8M) 2G内存推荐134217728(128MB)
key_buffer_size=256M
#5.query_cache_size(查询缓存简称QC) 1M 0 256M
query_cache_size=128M
#开启缓存
query_cache_type=1
#6.max_connect_errors是一个mysql中与安全有关的计数器值,它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况,当超过指定次数,mysql服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
#默认100 10 20
max_connect_errors=10
#7.每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或GROUP BY操作,Sort_Buffer_Size并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。列如:500个连接将会消耗500*sort_buffer_size(2M)=1G内存
#默认 0.25M 0.25M 2M
sort_buffer_size=0.75M
#8.4M 1M 32 Mmysql根据配置文件会限制,server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制,导致写入或者更新失败,更大值是1GB,必须设置1024的倍数
max_allowed_packet=8M
#9.用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。
#0.25 0.125 2M
join_buffer_size=0.5M
#10. 9 8 200 服务器线程缓存,这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大
thread_cache_size = 64
#11.######128 390 2048M 对于InnoDB表来说,innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%。
innodb_buffer_pool_size= 1024M
######12.主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0、1、2三个。0,表示当事务提交时,不做日志写入操作,而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次;1,则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作,确保了事务的ACID;设置为2,每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作
innodb_flush_log_at_trx_commit=1
#13. 0 9 8 此参数用来设置innodb线程的并发数量,默认值为0表示不限制,若要设置则与服务器的CPU核数相同或是cpu的核数的2倍,建议用默认设置,一般为8.
innodb_thread_concurrency = 9
#14. 8M 5M 32M此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,对于较大的事务,可以增大缓存大小。
innodb_log_buffer_size=16M
#15. 48M 195M 50M 此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能.
#innodb_log_file_size = 50M
#16. 2 2 3
###innodb_log_files_in_group=3
#17. 0.125 0.0625
read_buffer_size=0.5M
#18. 0.25 0.25 16M
read_rnd_buffer_size = 4M
#19. 128M 8M 64M
bulk_insert_buffer_size=128M
#未完善
binlog_cache_size=2M
max_binlog_cache_size=8M
expire_logs_days=7
query_cache_limit=50M
max_binlog_size=512M
#slow_query_log=1
#slow_query_log_file = /usr/local/mysql/data/slow_query_log
#long_query_time=1
#log-queries-not-using-indexes
#log-bin=mysql-bin
#server-id=1
slow_query_log=1
slow_query_log_file = /usr/local/mysql/data/slow_query_log
long_query_time=1
log-queries-not-using-indexes
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=256M
query_cache_size=256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
server-id=1
Max_connections
insert into temtable (SELECT
T.TYPE_ID,O.ORGTYPE_CODE
FROM
ML_INFO_TYPE T,
ML_BUSINESS_INFO B,
OM_ORGANIZATION O
WHERE
T.BID = B.BID
AND B.BDEPT = O.ORG_ID
AND B.BNAME = ‘待规范目录’
AND T.CHANGE_STATUS != ‘03’);
update ML_INFO_TYPE inner join temtable on ML_INFO_TYPE.TYPE_ID = temtable.TYPE_ID set ML_INFO_TYPE.TYPE_CODE1 = CONCAT(‘307013’,temtable.ORGTYPE_CODE);
UPDATE CATALOG_PROCESS C inner join ORG_EMPLOYEE O on C.CHECKER_ID= O.EMP_ID SET C.CHECKER_ID = O.OPERATOR_ID
update ds_apply D inner join ORG_ORGANIZATION O on D.DS_APPLY_ORG_ID = ORG_ID AND D.DS_APPLY_KIND =‘2’ set D.DS_APPLYER_NAME = O.ORG_NAME
– 服务插入统计表
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT left( CONCAT( uuid(),REPLACE( RAND(), ‘.’, ‘-’)),36), SERVICE_ID,‘1’ FROM DS_API_INFO;
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT API_ID, SERVICE_ID,‘1’ FROM DS_API_INFO;
ml_info_type
– 生成文件服务
– SELECT CONCAT(CATALOG_NAME,“的文件服务”) CATALOG_NAME FROM DS_FILE_SERVICE_DETAIL D ,CATALOG_INFO C WHERE D.SERVICE_ID=C.CATALOG_ID
– 文件触发器
DELETE FROM DS_SERVICE_DETAIL WHERE DS_SERVICE_DETAIL.DS_SERVICE_DETAIL_ID =OLD.DS_FS_DETAIL_ID
– 服务触发器
DELETE FROM DS_SERVICE_DETAIL WHERE DS_SERVICE_DETAIL.DS_SERVICE_DETAIL_ID =OLD.API_ID
– 服务表加索引
ALTER TABLE DATA_SERVICE_INFO
ADD INDEX SERVICE_ORG_ID ( SERVICE_ORG_ID
)
ALTER TABLE DATA_SERVICE_INFO
ADD INDEX CATALOG_ID ( CATALOG_ID
)
ALTER TABLE DATA_SERVICE_INFO
ADD INDEX SERVICE_KIND ( SERVICE_KIND
)
– 统计表加索引
ALTER TABLE DS_SERVICE_DETAIL
ADD INDEX SERVICE_ID ( SERVICE_ID
) ;
ALTER TABLE DS_SERVICE_DETAIL
ADD INDEX SERVICE_TYPE ( SERVICE_TYPE
) ;
ALTER TABLE ds_file_service_detail
ADD INDEX FILE_STATUS ( FILE_STATUS
) ;
ALTER TABLE ds_file_service_detail
ADD INDEX SERVICE_ID ( SERVICE_ID
) ;
ALTER TABLE DATA_SERVICE_INFO
ADD INDEX SERVICE_STATUS ( SERVICE_STATUS
) ;
ALTER TABLE DATA_SERVICE_INFO
ADD INDEX SERVICE_DELETE ( SERVICE_DELETE
) ;
ALTER TABLE ORG_ORGANIZATION
ADD INDEX ORG_ID ( ORG_ID
) ;
ALTER TABLE DATA_SERVICE_STATS
ADD INDEX SERVICE_ID ( SERVICE_ID
) ;
ALTER TABLE CATALOG_ITEM_INFO
ADD INDEX CATALOG_ID ( CATALOG_ID
) ;
– 文件插入统计表
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT left( CONCAT( uuid(),REPLACE( RAND(), ‘.’, ‘-’)),36), SERVICE_ID,‘3’ FROM DS_FILE_SERVICE_DETAIL;
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT DS_FS_DETAIL_ID, SERVICE_ID,‘3’ FROM DS_FILE_SERVICE_DETAIL;
–库表插入统计表
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT left( CONCAT( uuid(),REPLACE( RAND(), ‘.’, ‘-’)),36), SERVICE_ID,‘2’ FROM DS_FILE_SERVICE_DETAIL;
INSERT INTO DS_SERVICE_DETAIL (DS_SERVICE_DETAIL_ID, SERVICE_ID,SERVICE_TYPE)
SELECT DS_TS_DETAIL_ID, SERVICE_ID,‘2’ FROM ds_table_service_detail;
–查询服务目录状态
SELECT CATALOG_STATUS FROM CATALOG_INFO GROUP BY CATALOG_STATUS;
–更新 删除状态
UPDATE CATALOG_INFO SET LOGIC_DELETE =‘1’,CATALOG_STATUS=‘4’ WHERE CATALOG_STATUS=‘SC’;
–受影响的行: 1
–更新 区县审核状态
UPDATE CATALOG_INFO SET CATALOG_STATUS=‘2’ WHERE CATALOG_STATUS=‘qx’;
–受影响的行: 37
–待审审核删除
UPDATE CATALOG_INFO SET LOGIC_DELETE =‘1’, CATALOG_STATUS=‘2’ WHERE CATALOG_STATUS=’-1’ OR CATALOG_STATUS=’-2’;
–受影响的行: 1679
–查询服务状态 ps:若先抽取完目录紧接着执行以上sql 可省略此步骤
SELECT SERVICE_STATUS FROM DATA_SERVICE_INFO GROUP BY SERVICE_STATUS
–更新 区县审核状态
UPDATE DATA_SERVICE_INFO SET SERVICE_STATUS=‘2’ WHERE SERVICE_STATUS=‘qx’;
–受影响的行: 4
–待审审核删除
UPDATE DATA_SERVICE_INFO SET SERVICE_DELETE =‘1’, SERVICE_STATUS=‘2’ WHERE SERVICE_STATUS=’-1’ OR SERVICE_STATUS=’-2’;
–受影响的行: 160
– SELECT * FROM as_reg_service_apply AP LEFT JOIN apply_service_schemal SC ON AP.APPLY_ID=SC.applyid
–通知公告 文件资料 通过后台管理系统统一加上
–数据开放
–与省里同步发布状态
– 将UpdateCatalogStatus 放入 others模块的 com.sdyy.share.cascade.rest; 修改regionCode token intArray的参数
https://localhost:8003/others/rest/updateCatalogStatus/getCascadeInfo
370300,9D457AB08C63426D85CF9A225750FF2A,370300000000,1,5,"","",0,0,0,0
–根据上报的数据库表生成相关物化信息CATALOG_PHYSICAL_INFO
SELECT TABLE_NAME, DS_TS_DETAIL_ID,D.DATASOURCE_ID,CATALOG_ID,TIME_SCALE, I.SERVICE_CREATE_TIME FROM ds_table_service_detail D LEFT JOIN DATA_SERVICE_INFO I ON D.SERVICE_ID =I.SERVICE_ID
—生成物化信息之信息项
SELECT
TABLE_NAME,
DS_TS_DETAIL_ID,
D.DATASOURCE_ID,
I.CATALOG_ID,
TIME_SCALE,
I.SERVICE_CREATE_TIME,
(SELECT group_concat(CATALOG_ITEM_ID) FROM CATALOG_ITEM_INFO M WHERE M.CATALOG_ID= I.CATALOG_ID)
FROM
ds_table_service_detail D
LEFT JOIN DATA_SERVICE_INFO I ON D.SERVICE_ID = I.SERVICE_ID
GROUP BY I.CATALOG_ID
SELECT
TABLE_NAME,
DS_TS_DETAIL_ID,
D.DATASOURCE_ID,
I.CATALOG_ID,
TIME_SCALE,
I.SERVICE_CREATE_TIME,
(SELECT group_concat( concat(’’’’,CATALOG_ITEM_ID,’’’’)) FROM CATALOG_ITEM_INFO M WHERE M.CATALOG_ID= I.CATALOG_ID)
FROM
ds_table_service_detail D
LEFT JOIN DATA_SERVICE_INFO I ON D.SERVICE_ID = I.SERVICE_ID
GROUP BY I.CATALOG_ID
–开放
SELECT group_concat( concat(’’’’,CATALOG_NAME,’’’’)) FROM CATALOG_INFO_OPEN
UPDATE CATALOG_INFO SET IF_OPEN_BUTT =‘1’ WHERE CATALOG_NAME IN();
–更新文件级联
UPDATE ds_file_service_detail SET FILE_STATUS=‘4’ WHERE FILE_IS_CASCADE=‘1’
UPDATE ds_file_service_detail SET FILE_STATUS=‘4’ ,FILE_IS_CASCADE=‘1’ WHERE SERVICE_ID IN( SELECT group_concat( concat(’’’’,SERVICE_ID,’’’’)) FROM DATA_SERVICE_INFO WHERE SERVICE_STATUS=‘4’)
UPDATE DS_API_INFO SET API_IS_CASCADE=‘1’ WHERE SERVICE_ID IN( SELECT group_concat( concat(’’’’,SERVICE_ID,’’’’)) FROM DATA_SERVICE_INFO WHERE SERVICE_STATUS=‘4’ AND SERVICE_KIND=‘1’ )
SELECT CONCAT(“WHEN '”,APPLICATIONSYS_NAME,"’"), CONCAT(“THEN '”,APPLICATIONSYS_NAME,"’") FROM ML_INFO_TYPE GROUP BY APPLICATIONSYS_NAME
UPDATE catalog_info set CREATE_TIME =‘2018-12-26 08:48:43’ WHERE CREATE_TIME IS null
SELECT * FROM catalog_info WHERE CREATE_TIME IS NULL
– UPDATE catalog_info SET CREATE_TIME = ‘2018-11-26 09:02:59’ WHERE CREATE_TIME IS NULL