MySQL 性能、监控与灾难恢复

监控方案： up.time http://www.uptimesoftware.com/ 收费 Cactihttp://www.cacti.net/ KDE System Guard(KSysGuard) http://docs.kde.org/stable/en/kdebase-workspace/ksysguard/index.html Gnome System Monitor http://library.gnome.org/users/gnome-system-monitor/ Nagios http://www.nagios.org/ Shinken http://www.shinken-monitoring.org/ (个人觉得比nagios好用) Sun Management Center http://www.sun.com/software/products/sunmanagementcenter/index.xml MySQL Enterprise Monitor http://www.mysql.com/products/enterprise/monitor.html Linux和UNIX系统监控工具ps 系统进程top cpu使用率排序的活动进程vmstat 显示分页、内存、块传输、cpu活动相关uptime 显示系统运行时间及1、5、15分钟系统平均负载free 显示内存使用率iostat 显示平均磁盘活动和处理器负载 centos： yum install sysstatsar 系统活动报告、允许收集和报告各种系统活动pmap 显示各种进程分别占用内存情况mpstat 多处理器系统的cpu使用率 centos： yum install sysstatnetstat 网络活动cron 定时进程执行系统（计划任务）设置、读取系统变量 SHOW [GLOBAL | SESSION] VARIABLES; SET [GLOBAL | SESSION] <variable_name> = <value>; SET [@@global. | @@session. | @@] <variable_name>=<value>; SHOW STATUS; SHOW SESSION STATUS; SHOW GLOBAL STATUS;SQL 命令 SHOW INDX FROM <table> //显示指定表的索引基数统计信息 SHOW PLUGINS //显示所有已知插件列表 SHOW [FULL] PROCESSLIST //显示系统上运行的所有线程。 SHOW [GLOBAL | SESSION] STATUS //显示所有系统变量值 SHOW TABLE [FROM <db>] STATUS //显示给定数据库的表的详情 SHOW [GLOBAL | SESSION] VARIABLES //显示系统变量为了查看较少的信息，可使用 like ’<pattern>’ 从句，like 语句中可使用标准MySQL正则表达式符号和控制符 SHOW ENGINE <engine_name> LOGS //显示指定存储引擎的日志信息 SHOW ENGINE <engine_name> STATUS //显示指定存储引擎状态信息 SHOW ENGINES//显示所有可用的存储引擎的列表及状态 SHOW BINARY LOGS//显示服务器二进制LOG列表 SHOW RELAYLOG EVENTS [IN ’<log_file>’] [FROM <pos>] [LIMIT [<offset>, ] <row count>] //仅限制查看Slave上的中继日志 SHOW MASTER STATUS //显示Master当前配置，显示当前二进制日志文件，文件位置等 SHOW SLAVE HOSTS//使用--report-host 选项显示连接到Master的slave列表 SHOW SLAVE STATUS //显示复制中slave的系统状态 MySQL GUI工具 MySQL Administrator MySQL Query Browser MySQL Migration Toolkit //用于自动从其他数据库系统上迁移数据 第三方工具 MySAR 系统活动报告 mytop 监控线程统计信息和Mysql常规性能统计信息 innotop 用于监控InnoDB性能和MySQL服务器 MONyog （MySQL Monitor and Advisor) mysql监控工具，主动监控方案 MySQL Benchmark套件 MySQL基准测试./run-all-tests --server=mysql --cmp=mysql --user=root --socket=<socket>测量数据库性能 EXPLAIN 分析关于如何执行SELECT语句的信息 ANALYZE [ LOCAL | NO_WRUTE_TO_BINLOG ] TABLE #[ LOCAL | NO_WRUTE_TO_BINLOG ] 可防止命令写入二进制日志。 SHOW INDEX FROM TABLE OPTIMIZE [ LOCAL | NO_WRUTE_TO_BINLOG ] TABLE <table_list>#重构一个或多个表的数据结构，整理存储空间 SHOW FULL PROCESSLIST; 查看所有进程信息数据库优化 1、谨慎而有效的使用索引 2、使用规范化（范式），但不要过头 3、使用正确的存储引擎 alter table t1 ENGINE=MEMORY;提高性能的最佳实践 1、一切都很慢（检查问题）* 检查硬件* 改善硬件环境（如添加硬盘）* 考虑将数据迁移到独立的磁盘上* 检查操作系统配置是否正确* 考虑将有些应用迁移到其他服务器上* 考虑可以向外扩展的复制* 优化服务器性能 2、慢查询* 规范化数据库模式* 使用EXPLAIN识别丢失的或不正确的索引* 使用benchmark() 函数测试部分查询* 考虑重写查询* 对标准查询使用视图* 启用Query Cache 3、慢应用* 开启 Query Cache* 考虑并优化存储引擎* 确认是否是服务器或操作系统的问题* 定义应用程序的基准，并将它与已知基准比较* 检查内部（在应用程序 内部编写的）查询，并最大化他们的性能* 分而治之——一次只检查一个部分* 使用划分类分散数据* 检查各个分区的索引 4、慢复制* 确保网络运行状况最佳* 确保服务器配置正确* 优化数据库* 限制 Master 的更新* 将数据读取划分到多个Slave中* 检查Slave 的复制延迟* 定期维护日志（二进制日志和中继日志）* 在带宽有限的情况下，使用压缩* 使用包容性和排他性日志选项，最小化复制内容数据引擎的优化和监控 MyISAM实用工具* myisam_ftdump: 显示全文索引信息* myisamchk : 执行MyISAM 表的分析（只能在脱机状态使用）* myisamlog : 查看MyISAM 表的更改日志* myisampack：压缩表以减少存储量 Key Cache1、预加载Key Cache #将salary表的索引加载到key cache中，ignore leaves: 表明只加载索引的非叶子节点，可通过修改表的方式从Key cache中移除相关索引 mysql> load index into cache salaries ignore leaves;2、使用多个Key Cache #使用耳机主缓存 mysql> set global emp_cache.key_buffer_size = 128*1024; //128K mysql> cache index salaries in emp_cache; mysql> set global emp_cache.key_buffer_size=0; #确认一个二级缓存是否存在 mysql> select @@global.emp_cache.key_buffer_size; 重启而不丢失二级缓存配置的方式：将配置语句保存在一个文件中，使用mysql的配置文件的[mysqld]部分的 init-file=<patch_to_file>命令执行该文件。来自：http://blog.csdn.net/hellyhe/article/details/8288696