环境
- confluence 7.6
- mysql 8.0.29
- RHEL7.9
故障
发现confluence性能低下. 修改数据经常等几秒.
尝试
- 重建confluence的索引.效果不大.
- mysql数据库 关闭innodb log writer thread 禁用binlog, 效果不大. 修改后的my.cnf
long_query_time=1
slow_query_log=ON
innodb_log_writer_threads=NO
skip_log_bin
对mysql的表做analze后速度提升明显
1. 找到confluence的所有表.
show tables. 前面加analyze table, 后面加分号.
生成一个sql文件 analyze.sql
[root@confluence scripts]# cat analyze.sql
analyze table AO_187CCC_SIDEBAR_LINK ;
analyze table AO_21D670_WHITELIST_RULES ;
analyze table AO_21F425_MESSAGE_AO ;
analyze table AO_21F425_MESSAGE_MAPPING_AO ;
analyze table AO_21F425_USER_PROPERTY_AO ;
analyze table AO_26DB7F_ENTITIES_TO_ROOMS ;
analyze table AO_26DB7F_ENTITIES_TO_ROOM_CFG ;
analyze table AO_38321B_CUSTOM_CONTENT_LINK ;
analyze table AO_42E351_HEALTH_CHECK_ENTITY ;
analyze table AO_4789DD_DISABLED_CHECKS ;
analyze table AO_4789DD_HEALTH_CHECK_STATUS ;
analyze table AO_4789DD_HEALTH_CHECK_WATCHER ;
analyze table AO_4789DD_PROPERTIES ;
analyze table AO_4789DD_READ_NOTIFICATIONS ;
analyze table AO_4789DD_SHORTENED_KEY ;
analyze table AO_4789DD_TASK_MONITOR ;
analyze table AO_54C900_CONTENT_BLUEPRINT_AO ;
analyze table AO_54C900_C_TEMPLATE_REF ;
analyze table AO_54C900_SPACE_BLUEPRINT_AO ;
analyze table AO_563AEE_ACTIVITY_ENTITY ;
analyze table AO_563AEE_ACTOR_ENTITY ;
analyze table AO_563AEE_MEDIA_LINK_ENTITY ;
analyze table AO_563AEE_OBJECT_ENTITY ;
analyze table AO_563AEE_TARGET_ENTITY ;
analyze table AO_59F889_ZDU_CLUSTER_NODES ;
analyze table AO_5F3884_FEATURE_DISCOVERY ;
analyze table AO_5FB9D7_AOHIP_CHAT_LINK ;
analyze table AO_5FB9D7_AOHIP_CHAT_USER ;
analyze table AO_6384AB_DISCOVERED ;
analyze table AO_6384AB_FEATURE_METADATA_AO ;
analyze table AO_7B47A5_EVENT ;
analyze table AO_7B47A5_SETTINGS ;
analyze table AO_7CDE43_EVENT ;
analyze table AO_7CDE43_FILTER_PARAM ;
analyze table AO_7CDE43_NOTIFICATION ;
analyze table AO_7CDE43_NOTIFICATION_SCHEME ;
analyze table AO_7CDE43_RECIPIENT ;
analyze table AO_7CDE43_SERVER_CONFIG ;
analyze table AO_7CDE43_SERVER_PARAM ;
analyze table AO_81F455_PERSONAL_TOKEN ;
analyze table AO_8752F1_DATA_PIPELINE_JOB ;
analyze table AO_88263F_HEALTH_CHECK_STATUS ;
analyze table AO_88263F_PROPERTIES ;
analyze table AO_88263F_READ_NOTIFICATIONS ;
analyze table AO_88BB94_BATCH_NOTIFICATION ;
analyze table AO_92296B_AORECENTLY_VIEWED ;
analyze table AO_9412A1_AONOTIFICATION ;
analyze table AO_9412A1_AOREGISTRATION ;
analyze table AO_9412A1_AOTASK ;
analyze table AO_9412A1_AOUSER ;
analyze table AO_9412A1_USER_APP_LINK ;
analyze table AO_950DC3_TC_CUSTOM_EV_TYPES ;
analyze table AO_950DC3_TC_DISABLE_EV_TYPES ;
analyze table AO_950DC3_TC_EVENTS ;
analyze table AO_950DC3_TC_EVENTS_EXCL ;
analyze table AO_950DC3_TC_EVENTS_INVITEES ;
analyze table AO_950DC3_TC_JIRA_REMI_EVENTS ;
analyze table AO_950DC3_TC_REMINDER_SETTINGS ;
analyze table AO_950DC3_TC_REMINDER_USERS ;
analyze table AO_950DC3_TC_SUBCALS ;
analyze table AO_950DC3_TC_SUBCALS_IN_SPACE ;
analyze table AO_950DC3_TC_SUBCALS_PRIV_GRP ;
analyze table AO_950DC3_TC_SUBCALS_PRIV_USR ;
analyze table AO_950DC3_TC_SUBCALS_PROPS ;
analyze table AO_954A21_PUSH_NOTIFICATION_AO ;
analyze table AO_A0B856_DAILY_COUNTS ;
analyze table AO_A0B856_HIST_INVOCATION ;
analyze table AO_A0B856_WEBHOOK ;
analyze table AO_A0B856_WEBHOOK_CONFIG ;
analyze table AO_A0B856_WEBHOOK_EVENT ;
analyze table AO_A0B856_WEB_HOOK_LISTENER_AO ;
analyze table AO_AC3877_RL_USER_COUNTER ;
analyze table AO_AC3877_SETTINGS_VERSION ;
analyze table AO_AC3877_SYSTEM_RL_SETTINGS ;
analyze table AO_AC3877_USER_RL_SETTINGS ;
analyze table AO_BAF3AA_AOINLINE_TASK ;
analyze table AO_C77861_AUDIT_ENTITY ;
analyze table AO_DC98AE_AOHELP_TIP ;
analyze table AO_ED669C_SEEN_ASSERTIONS ;
analyze table ATTACHMENTDATA ;
analyze table AUDITRECORD ;
analyze table AUDIT_AFFECTED_OBJECT ;
analyze table AUDIT_CHANGED_VALUE ;
analyze table BANDANA ;
analyze table BODYCONTENT ;
analyze table CLUSTERSAFETY ;
analyze table CONFANCESTORS ;
analyze table CONFVERSION ;
analyze table CONFZDU ;
analyze table CONTENT ;
analyze table CONTENTPROPERTIES ;
analyze table CONTENT_LABEL ;
analyze table CONTENT_PERM ;
analyze table CONTENT_PERM_SET ;
analyze table CONTENT_RELATION ;
analyze table DECORATOR ;
analyze table DENORMALISED_CONTENT ;
analyze table DENORMALISED_CONTENT_CHANGE_LOG ;
analyze table DENORMALISED_CONTENT_VIEW_PERMISSIONS ;
analyze table DENORMALISED_SERVICE_LOCK ;
analyze table DENORMALISED_SID ;
analyze table DENORMALISED_SPACE_CHANGE_LOG ;
analyze table DENORMALISED_SPACE_EDIT_PERMISSIONS ;
analyze table DENORMALISED_SPACE_VIEW_PERMISSIONS ;
analyze table DENORMALISED_STATE ;
analyze table DENORMALISED_STATE_CHANGE_LOG ;
analyze table DIAGNOSTICS_ALERTS ;
analyze table EVENTS ;
analyze table FOLLOW_CONNECTIONS ;
analyze table IMAGEDETAILS ;
analyze table INDEXQUEUEENTRIES ;
analyze table KEYSTORE ;
analyze table LABEL ;
analyze table LIKES ;
analyze table LINKS ;
analyze table MIG_ANALYTICS_EVENT ;
analyze table MIG_APP_ACCESS_SCOPE ;
analyze table MIG_APP_ASSESSMENT_INFO ;
analyze table MIG_ATTACHMENT ;
analyze table MIG_CHECK_OVERRIDE ;
analyze table MIG_CHECK_RESULT ;
analyze table MIG_CLOUD_SITE ;
analyze table MIG_DB_CHANGELOG ;
analyze table MIG_DB_CHANGELOG_LOCK ;
analyze table MIG_DETECTED_EVENT_LOG ;
analyze table MIG_EXCLUDE_APP ;
analyze table MIG_PLAN ;
analyze table MIG_SEQUENCES ;
analyze table MIG_STATS ;
analyze table MIG_STEP ;
analyze table MIG_TASK ;
analyze table MIG_WORK_ITEM ;
analyze table MOST_USED_LABELS_CACHE ;
analyze table NOTIFICATIONS ;
analyze table OS_PROPERTYENTRY ;
analyze table PAGETEMPLATES ;
analyze table PLUGINDATA ;
analyze table SECRETS ;
analyze table SNAPSHOTS ;
analyze table SPACEPERMISSIONS ;
analyze table SPACES ;
analyze table THISWILLNOTBECREATED ;
analyze table TRUSTEDAPP ;
analyze table TRUSTEDAPPRESTRICTION ;
analyze table USERCONTENT_RELATION ;
analyze table USER_RELATION ;
analyze table cwd_app_dir_group_mapping ;
analyze table cwd_app_dir_mapping ;
analyze table cwd_app_dir_operation ;
analyze table cwd_application ;
analyze table cwd_application_address ;
analyze table cwd_application_attribute ;
analyze table cwd_directory ;
analyze table cwd_directory_attribute ;
analyze table cwd_directory_operation ;
analyze table cwd_group ;
analyze table cwd_group_attribute ;
analyze table cwd_membership ;
analyze table cwd_synchronisation_status ;
analyze table cwd_synchronisation_token ;
analyze table cwd_tombstone ;
analyze table cwd_user ;
analyze table cwd_user_attribute ;
analyze table cwd_user_credential_record ;
analyze table hibernate_unique_key ;
analyze table journalentry ;
analyze table logininfo ;
analyze table remembermetoken ;
analyze table scheduler_clustered_jobs ;
analyze table scheduler_run_details ;
analyze table storageenginetest ;
analyze table user_mapping ;
[root@confluence scripts]#
2. 建脚本run_analyze.sh
[root@confluence scripts]# cat run_analyze.sh
#!/usr/bin/env bash
/usr/local/mysql/bin/mysql -D confluence < /root/scripts/analyze.sql
[root@confluence scripts]#
3.crontab 定期执行
设置每天 15点,在备份前执行.备份记录到日志 logs/analyze.log
[root@confluence logs]# crontab -l
00 16 * * * /root/scripts/manual_backup_confluence.sh > /root/logs/manual_backup.log 2>&1
00 15 * * * /root/scripts/run_analyze > /root/logs/analyze.log 2>&1
[root@confluence logs]#
4. 清理日志
[root@confluence logs]# cd /etc/logrotate.d/
[root@confluence logrotate.d]# cat analyze_mysql
/root/logs/analyze.log {
daily
rotate 7
compress
missingok
notifempty
copytruncate
}
[root@confluence logrotate.d]#