Home >
Article > Database > MySQL performance, monitoring and disaster recovery
MySQL performance, monitoring and disaster recovery
黄舟Original
2017-02-21 10:27:191108browse
Monitoring solution:
up.time http://www.php.cn/ 收费
Cacti http://www.php.cn/
KDE System Guard(KSysGuard)
http://www.php.cn/
Gnome System Monitor
http://www.php.cn/
Nagios http://www.php.cn/
Shinken http://www.php.cn/ (个人觉得比nagios好用)
Sun Management Center
http://www.php.cn/
MySQL Enterprise Monitor
http://www.php.cn/
SHOW [GLOBAL | SESSION] VARIABLES;
SET [GLOBAL | SESSION] = ;
SET [@@global. | @@session. | @@] =;
SHOW STATUS;
SHOW SESSION STATUS;
SHOW GLOBAL STATUS;
SQL command
SHOW INDX FROM
//显示指定表的索引基数统计信息
SHOW PLUGINS //显示所有已知插件列表
SHOW [FULL] PROCESSLIST //显示系统上运行的所有线程。
SHOW [GLOBAL | SESSION] STATUS //显示所有系统变量值
SHOW TABLE [FROM ] STATUS //显示给定数据库的表的详情
SHOW [GLOBAL | SESSION] VARIABLES //显示系统变量
In order To view less information, you can use the like 'a6f114c758be113d08282881423b4728' clause. Standard MySQL regular expression symbols and control symbols can be used in the like statement
SHOW ENGINE LOGS //显示指定存储引擎的日志信息
SHOW ENGINE STATUS //显示指定存储引擎状态信息
SHOW ENGINES //显示所有可用的存储引擎的列表及状态
SHOW BINARY LOGS //显示服务器二进制LOG列表
SHOW RELAYLOG EVENTS [IN ''] [FROM ] [LIMIT [, ] ]
//仅限制查看Slave上的中继日志
SHOW MASTER STATUS //显示Master当前配置，显示当前二进制日志文件，文件位置等
SHOW SLAVE HOSTS //使用--report-host 选项显示连接到Master的slave列表
SHOW SLAVE STATUS //显示复制中slave的系统状态
MySQL GUI Tool
MySQL Administrator
MySQL Query Browser
MySQL Migration Toolkit //用于自动从其他数据库系统上迁移数据
Third-party tools
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=
##Measure database performance
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
#重构一个或多个表的数据结构，整理存储空间
SHOW FULL PROCESSLIST; 查看所有进程信息
Database Optimization 1. Use indexes carefully and effectively 2. Use normalization (normalization), but don’t overdo it 3. Use the correct storage engine Alter table t1 ENGINE=MEMORY;
Best practices to improve performance 1 , everything is slow (check the problem) * Check the hardware * Improve the hardware environment (such as adding a hard disk)
* Consider migrating data to a separate disk
* Check whether the operating system configuration is correct
* Consider migrating some applications to other servers
* Consider Replication that can be scaled out
* Optimize server performance
2. Slow query
* Normalized database schema
* Use EXPLAIN to identify missing or incorrect indexes
* Use the benchmark() function to test partial queries
* Consider rewriting the query
* Use views for standard queries
* Enable Query Cache
3. Slow application
* Enable Query Cache
* Consider and optimize storage Engine
* Confirm if it is a server or operating system issue
* Define a baseline for the application and compare it to a known baseline
) queries written inside the program and maximize their performance
* Divide and conquer - check only one part at a time
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;
#to confirm whether a second -level cache exists
This cache In a file, use the init-file=579760867874dd6b866a2ef9c2539347 command in the [mysqld] section of the mysql configuration file to execute the file.
The above is the content of MySQL performance, monitoring and disaster recovery. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
