MySQL 性能
- - 谁主沉浮这里罗列了一些基本的 MySQL 性能提示，但不是放之四海而皆准，需要根据实际的应用情况而决定. 使用标准化设计（数据库三范式），记住表的联合查询(join)性能不会差. 选择合适的字符集，虽然UTF16无所不能，但需要两倍的存储；UTF8适合各种字符，但比latin1慢，尽可能选用latin1(此条不适合中文).
MySQL性能建议者：mysqltuner.pl
标签：
mysql
性能
mysqltuner
| 发表时间：2013-10-23 08:13 | 作者：linwaterbin
出处：http://blog.csdn.net
[root@even ~]# wget http://mysqltuner.pl/mysqltuner.pl
[root@even ~]# chmod +x mysqltuner.pl
[root@even ~]# cp mysqltuner.pl /home/mysql/
一些简单的命令可从 --help 查阅
[mysql@even ~]$ ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.16-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7B (Tables: 2)
[--] Data in InnoDB tables: 184M (Tables: 7)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 5
-------- Security Recommendations -------------------------------------------
[!!] User '@even.example.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User '[email protected]' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User '[email protected]' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10s (9 q [0.900 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 176.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 411.9M (38% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/98.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 184.8M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 184M)
[root@even ~]# chmod +x mysqltuner.pl
[root@even ~]# cp mysqltuner.pl /home/mysql/
一些简单的命令可从 --help 查阅
[mysql@even ~]$ ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.16-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7B (Tables: 2)
[--] Data in InnoDB tables: 184M (Tables: 7)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 5
-------- Security Recommendations -------------------------------------------
[!!] User '@even.example.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User '[email protected]' has no password set.
[!!] User 'root@::1' has no password set.
[!!] User '[email protected]' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10s (9 q [0.900 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 176.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 411.9M (38% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/98.0K
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 184.8M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 184M)
输出结果简单明了，读者有兴趣可参阅官网博客
By 迦夜
2013-10-23
Happy Databasing
作者：linwaterbin 发表于2013-10-23 0:13:37 原文链接
阅读：147 评论：0 查看评论
相关 [mysql 性能 mysqltuner] 推荐：
MySQL性能优化
- sun - IT程序员面试网在笔试面试中，尤其是像百度，淘宝这些数据量非常大，而且用LAMP架构的公司，数据库优化方面就显得特别重要了. 此外，除了数据库索引之外，在LAMP结果如此流行的今天，数据库（尤其是MySQL）性能优化也是海量数据处理的一个热点. 下面就结合自己的经验，聊一聊MySQL数据库优化的几个方面. 首先，在数据库设计的时候，要能够充分的利用索引带来的性能提升，至于如何建立索引，建立什么样的索引，在哪些字段上建立索引，上面已经讲的很清楚了，这里不在赘述.
Mysql性能优化
- - 数据库 - ITeye博客MySQL性能优化. 性能优化是通过某些有效的方法来提高MySQL的运行速度，减少占用的磁盘空间. 性能优化包含很多方面，例如优化查询速度，优化更新速度和优化MySQL服务器等. 数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能. 语法：SHOW STATUE LIKE ‘value’；其中value参数是常用的几个统计参数.
mysql 5.6性能vs mysql 5.5的讨论
- - ITeye博客这几天老外就mysql 5.6和5.5,MariaDB的benchmark讨论的很热烈，参考http://t.cn/zYoXNND一文，而法国朋友身先士卒，在http://t.cn/zYKgDmk中一连做了多个评测. 1） 在大名顶顶的percona公司的Peter Zaitsev 一文中（http://www.mysqlperformanceblog.com/2013/02/18/is-mysql-5-6-slower-than-mysql-5-5/）.
mysql性能优化教程
- coolzsb - caoz的和谐blogmysql性能最大化
- - CSDN博客数据库推荐文章#跳过外部锁定 只对MyISAM. #禁用tcp socket连接，只能使用unix/linux socket连接. #禁用DNS解析，提高连接速度. #跳过授权表，忘记root密码时使用. #服务器关闭一个交互连接前等待的秒数，默认28800. #线程缓存数，直接利用空闲连接来创建新连接，1G —> 8 2G —> 16 3G —> 32 >3G —> 64.
MySQL性能建议者：mysqltuner.pl
- - CSDN博客数据库推荐文章一些简单的命令可从 --help 查阅. 输出结果简单明了，读者有兴趣可参阅官网博客. 作者：linwaterbin 发表于2013-10-23 0:13:37 原文链接. 阅读：147 评论：0 查看评论.
MySQL性能医生：OrzDBA
- - CSDN博客数据库推荐文章A trunk/orzdba工具使用说明.pdf. 需要在代码160行左右配置MySQL的相关验证信息,如username,password,host，port，sock等. ㈠ 查看MySQL响应时间(rt). 在源码161行左右把 /usr/bin/tcprstat 加入默认读取的位置. 再解压orzdba_rt_depend_perl_module.tar.gz，安装rt所依赖的perl模块.
mysql 性能调优（转）
- - 数据库 - ITeye博客转自：http://suhuanzheng7784877.iteye.com/blog/2047209. Mysql高性能学习笔记-01. Mysql整体的架构模块如下图所示. 模块1：连接客户端，这个一般是指mysql基于mysql通讯协议的驱动程序. 比如mysql的Java的JDBC驱动程序——mysql-connector-java-5.1.26-bin.jar.
[转]MySQL性能优化
- - IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段. 2.降低 CPU 计算. 除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了.