机器A,Centos6.3 x64,机器B, reahat 5.5 x86,两台机器上都是用rpm安装的MySql,区别就是A机器是64位的,B机器是32位的,两者的硬件软件配置见下图所示
现在问题是,两台服务器都处于局域网环境,用ssh远程连上,用mysql自带的客户端连上mysql,用source命令执行内容完全一样的sql文件,sql文件都放在各自服务器的本地硬盘上,性能差距巨大,A机器按说应该更快,却比B机器慢至少三倍的速度,A机器执行insert命令时,显示的执行时间每条约0.03-0.06sec,但是B机器执行时显示的时间几乎全是0.00,连0.01都很难看见。二者的配置文件完全相同,我自安装后就没动过性能上的设置,只是改了utf-8# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[mysqld]
character_set_server = utf8[client]
default-character-set=utf8
init_connect='SET NAMES utf8'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
现在问题是,两台服务器都处于局域网环境,用ssh远程连上,用mysql自带的客户端连上mysql,用source命令执行内容完全一样的sql文件,sql文件都放在各自服务器的本地硬盘上,性能差距巨大,A机器按说应该更快,却比B机器慢至少三倍的速度,A机器执行insert命令时,显示的执行时间每条约0.03-0.06sec,但是B机器执行时显示的时间几乎全是0.00,连0.01都很难看见。二者的配置文件完全相同,我自安装后就没动过性能上的设置,只是改了utf-8# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html[mysqld]
character_set_server = utf8[client]
default-character-set=utf8
init_connect='SET NAMES utf8'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
解决方案 »
- mysql客户端查询不到最新数据的问题
- mysql 集群,应用程序应该怎么样连接
- mysql 中能使用with as 吗?
- UPDATE下不能SELECT本表的解决方法
- 遇到問題了,MYSQL查詢結果里面,如何使某列重復的數據保留一條,其他為空?
- mysql数据库存储位置转移发生的奇怪问题。请教各位!
- 怎么用select语句显示表s中2到4条记录?
- jdbc 事务,两条insert语句之间的事务关系
- MYSQL如何设置两个两之间关联
- 关于时间计算问题
- Mysql Innodb数据库误删除了文件,怎么恢复?
- 如图:安装Mysql时为什么会出现配置文件的模板:E:\MYSQL\my-template.ini 无法处理,并写入到E:\MYSQL\my.ini.中
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 0 |
+--------------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.02 sec)mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
这还是不能解释为什么同样参数下64位慢这么多我隐约怀疑是不是有些mysql的配置参数的值在两台机器下不太一样?包括默认值。
你能将show variables 所有变量的值都dump出来,做一个比较吗?从硬件配置上来看,实在看不出两台机器有性能上的差异。
x64下,多了这个参数:
| innodb_adaptive_max_sleep_delay | 150000 |
sql_mode也不一样:
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
x64下,
| sql_mode | NO_ENGINE_SUBSTITUTION
还有一些别的参数。你用 winmerge之类的比较工具,比较一下这两个文本文件,很快能找出一些参数值的不同。
应该能定位问题的根源。
:)