前言
某大型论坛使用PHPWIND系统，近几个月管理员反馈老是崩溃无法访问，要求优化
开始操作
因为忘记密码，所以先操作恢复root密码后登录
首先查看var/log/message查看异常日志
发现大量mysql爆内存的错误和CPU升高的报警
但是在后台监控发现CPU的升高有规律性，怀疑有计划任务
所以开始如下操作
1、查看cron任务
使用crontab -l 查看到的确有定时备份的sh任务
[root@izt4ngwebxot1lwz05nhobz admin]# crontab -l
#17 0 * * * "/root/.acme.sh"/acme.sh --cron --home "/root/.acme.sh" > /dev/null
30 23 * * * /usr/sbin/ntpdate us.pool.ntp.org
30 2 * * * /home/cron/mysql_bk.sh
0 6 * * * /home/cron/www_bk.sh
00 4 * * * /home/cron/attach_bk.sh
30 3 * * * /home/cron/mysqlbackup113.107.161.54.sh
59 23 * * * /home/cron/nginx_log.sh果然发现存在备份web程序、备份数据库、备份附件的SH
所以先注释掉附件和程序备份的SH，留下了数据库备份的SH并调整备份时间
2、优化message报错的mysql问题
Dec 31 19:58:29 izt4ngwebxot1lwz05nhobz kernel: Out of memory: Kill process 18840 (mysqld) score 648 or sacrifice child
Dec 31 19:58:29 izt4ngwebxot1lwz05nhobz kernel: Killed process 18840 (mysqld) total-vm:9969240kB, anon-rss:5972508kB, file-rss:0kB, shmem-rss:0kB
Dec 31 19:58:29 izt4ngwebxot1lwz05nhobz systemd: mysqld.service: main process exited, code=killed, status=9/KILL
Dec 31 19:58:29 izt4ngwebxot1lwz05nhobz systemd: Unit mysqld.service entered failed state.
Dec 31 19:58:29 izt4ngwebxot1lwz05nhobz systemd: mysqld.service failed.
Dec 31 19:58:29 izt4ngwebxot1lwz05nhobz systemd: Started Session 35672 of user root.
Dec 31 19:58:30 izt4ngwebxot1lwz05nhobz systemd: mysqld.service holdoff time over, scheduling restart.
Dec 31 19:58:30 izt4ngwebxot1lwz05nhobz systemd: Stopped MySQL Server.
Dec 31 19:58:30 izt4ngwebxot1lwz05nhobz systemd: Starting MySQL Server...
Dec 31 19:58:30 izt4ngwebxot1lwz05nhobz kernel: oom_reaper: reaped process 18840 (mysqld), now anon-rss:0kB, file-rss:8kB, shmem-rss:0kB
Dec 31 19:58:32 izt4ngwebxot1lwz05nhobz systemd: Started MySQL Server.调整MYSQL配置文件如下
# CACHES AND LIMITS #
sort_buffer_size = 4M
read_buffer_size = 4M
myisam_sort_buffer_size = 64M
tmp-table-size = 128M
max-heap-table-size = 64M
query-cache-type = 0
query-cache-size = 128M
max-connections = 1000
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-log-files-in-group = 3
innodb-log-file-size = 384M
innodb-flush-log-at-trx-commit = 1
innodb_flush_method=O_DIRECT
innodb-file-per-table = 1
innodb-buffer-pool-size = 4G
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_lock_wait_timeout = 60
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 50同时使用mysql -u root -p登录MYSQL登录数据库后查看超时设置
mysql> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 60 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 1800 |
| lock_wait_timeout | 255800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| tokudb_last_lock_timeout | |
| tokudb_lock_timeout | 4000 |
| wait_timeout | 1800 |
+-----------------------------+----------+
16 rows in set (0.00 sec)发现超时的时间设置有点大
修改优化设置
set global interactive_timeout=100;
set global wait_timeout=100;同时发现之前有报错要求mysqlupgrade，于是用mysql_upgrade -u root -p --force执行升级数据库操作
[root@izt4ngwebxot1lwz05nhobz admin]# mysql_upgrade -u root -p --force
Enter password:
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
enable.pcr_actions OK
enable.pcr_activity OK
enable.pcr_actmember OK
enable.pcr_adbbs OK
enable.pcr_administrators OK
enable.pcr_adminlog OK
enable.pcr_adminset OK
enable.pcr_advert OK
enable.pcr_announce OK
enable.pcr_argument OK
enable.pcr_attachbuy OK
enable.pcr_attachs OK
enable.pcr_bankplus OK
enable.pcr_banuser OK
enable.pcr_bbsftp OK
enable.pcr_bbsftplevel OK
enable.pcr_bbsftpserver OK
enable.pcr_bbsinfo OK
enable.pcr_block OK
enable.pcr_buyadvert OK
enable.pcr_cache OK
enable.pcr_cknum OK
enable.pcr_clientorder OK
enable.pcr_cmembers OK
enable.pcr_cmsmodule OK
enable.pcr_cnalbum OK
enable.pcr_cnclass OK
enable.pcr_cnphoto OK
enable.pcr_colonys OK
enable.pcr_config OK
enable.pcr_creditlog OK
enable.pcr_creditlog1 OK
enable.pcr_credits OK
enable.pcr_customfield OK
enable.pcr_datastate OK
enable.pcr_debateclass OK
enable.pcr_debatedata OK
enable.pcr_debateinfo OK
enable.pcr_debatereplys OK
enable.pcr_debates OK
enable.pcr_debatethreads OK
enable.pcr_draft OK
enable.pcr_elements OK
enable.pcr_extragroups OK
enable.pcr_favors OK
enable.pcr_feed OK
enable.pcr_focus OK
enable.pcr_forumdata OK
enable.pcr_forumlog OK
enable.pcr_forums OK
enable.pcr_forumsell OK
enable.pcr_forumsextra OK
enable.pcr_forumtype OK
enable.pcr_friends OK
enable.pcr_hack OK
enable.pcr_help OK
enable.pcr_home OK
enable.pcr_invitecode OK
enable.pcr_ipstates OK
enable.pcr_keyword OK
enable.pcr_ks OK
enable.pcr_medalinfo OK
enable.pcr_medalslogs OK
enable.pcr_medaluser OK
enable.pcr_membercredit OK
enable.pcr_memberdata OK
enable.pcr_memberinfo OK
enable.pcr_members OK
enable.pcr_memo OK
enable.pcr_modules OK
enable.pcr_msg OK
enable.pcr_msgc OK
enable.pcr_msglog OK
enable.pcr_nav OK
enable.pcr_online OK
enable.pcr_payadver OK
enable.pcr_permission OK
enable.pcr_pidtmp OK
enable.pcr_plan OK
enable.pcr_polls OK
enable.pcr_posts OK
enable.pcr_posts1 OK
enable.pcr_posts10 OK
enable.pcr_posts11 OK
enable.pcr_posts12 OK
enable.pcr_posts13 OK
enable.pcr_posts2 OK
enable.pcr_posts3 OK
enable.pcr_posts4 OK
enable.pcr_posts5 OK
enable.pcr_posts6 OK
enable.pcr_posts7 OK
enable.pcr_posts8 OK
enable.pcr_posts9 OK
enable.pcr_proclock OK
enable.pcr_qiandao OK
enable.pcr_recycle OK
enable.pcr_report OK
enable.pcr_reward OK
enable.pcr_safelog OK
enable.pcr_schcache OK
enable.pcr_selluid OK
enable.pcr_setform OK
enable.pcr_sharelinks OK
enable.pcr_singleright OK
enable.pcr_smiles OK
enable.pcr_sms OK
enable.pcr_sms_log OK
enable.pcr_song_apply OK
enable.pcr_song_data OK
enable.pcr_sqlcv OK
enable.pcr_stamp OK
enable.pcr_styles OK
enable.pcr_tagdata OK
enable.pcr_tags OK
enable.pcr_threads OK
enable.pcr_tmsgs OK
enable.pcr_tmsgs1 OK
enable.pcr_toollog OK
enable.pcr_tools OK
enable.pcr_trade OK
enable.pcr_tradeorder OK
enable.pcr_usergroups OK
enable.pcr_usertool OK
enable.pcr_viewbanklog OK
enable.pcr_voter OK
enable.pcr_warning OK
enable.pcr_warningf OK
enable.pcr_windcode OK
enable.pcr_wordfb OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
[root@izt4ngwebxot1lwz05nhobz admin]# 其他的可以按情况进行优化。
3、卸载jankens
发现论坛上还运行着jankens，发现没人使用，使用yum remove janknes删除之
4、卸载SS
发现论坛服务器上还挂着SS，使用pip uninstall shadowsocks-libv卸载完成
5、调整安全策略，清理
禁止22端口登录、删除两年前的备份、yum update一次
优化后效果，可以看到5号中午后CPU曲线下降明显，同时论坛论坛反应速度增加，无反应时间减少
优化结束
参考文章:
https://cloud.tencent.com/developer/article/1508290
https://imysql.com/2012/09/21/mysql-faq-setup-innodb-quickly.html
https://imysql.com/node/181