mysql的优化分为三个部分
1.物理服务器硬件的优化
mysql每秒钟都进行大量的，复杂的查询操作，对磁盘的1/O有很高的要求，通常认为1/O是制约mysql性能的最大因素。
2.mysql安装时的编译优化
--static 静态链接提高13%性能
--with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static
--pgcc 如果是Inter处理器，使用pgcc提高1%性能
CFLAGS="-O3 -mpentiumpro -mstack-align-double" CXX=gcc \
CXXFLAGS="-O3 -mpentiumpro -mstack-align-double \
--felide-constructors -fno-exceptions -fno-rtti"
Unix Socket 使用unix套接字链接提高7.5%性能，所以在windows下mysql性能肯定不如unix下面
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock
--enable-assembler允许使用汇编模式(优化性能)
--without-debug 编译安装时默认已debug模式生成二进制代码，而该方式会给mysql带来很大的性能损失
3.自身配置文件的优化 见后面的配置文档
------------------------------------------------------------------------
操作系统及其mysql配置文件说明
系统：Centos5.5
mysql:mysql-5.1.58.tar.gz源码包
安装目录：/usr/local/mysql/
数据目录：/data/mysql/3306/data/
二进制日志：/data/msyql/3306/binlog/
relay日志：/data/mysql/3306/relaylog/
配置文件：/data/mysql/3306/my.cnf
mysql.sock文件：/data/mysql/3306/mysql.sock
------------------------------------------------------------------------
1.安装gcc ncurses-devel
yum -y install gcc ncurses-devel
2.创建用户
/usr/sbin/groupadd mysql
/usr/sbin/useradd -s /sbin/nologin -g mysql -M mysql
3.安装
tar zxvf mysql-5.1.58.tar.gz
cd mysql-5.1.58
./configure --prefix=/usr/local/mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-plugins=partition,innobase --with-plugin-PLUGIN --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-unix-socket-path=/data/mysql/3306/mysql.sock
make
Entering directory `/root/mysql-5.1.48/mysql-test' 这里需要很长时候
make install
设置目录权限
chown -R mysql:mysql /usr/local/mysql
创建mysql数据存放目录
mkdir -p /data/mysql/3306/data
mkdir -p /data/mysql/3306/binlog/
mkdir -p /data/mysql/3306/relaylog/
chown -R mysql:mysql /data/mysql/
初始化mysql数据库
/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data --user=mysql
创建mysql配置文件
vim /data/mysql/3306/my.cnf
[client]
default-character-set=utf8
port = 3306
socket = /data/mysql/3306/mysql.sock
[mysqld]
default-character-set=utf8
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
user = mysql
port = 3306
socket = /data/mysql/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid
open_files_limit = 10240
back_log = 600
max_connections = 3000
max_connect_errors = 6000
table_cache = 4096
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 10M
join_buffer_size = 2M
thread_cache_size = 300
thread_concurrency = 16
query_cache_size = 512M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
default-storage-engine = InnoDB
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 2048M
max_heap_table_size = 256M
long_query_time = 1
log-slave-updates
log_long_format
log-short-format
log-slow-queries=/data/mysql/3306/slow-log.log
#slow_query_log=/data/mysql/3306/slow-log.log
log-bin = /data/mysql/3306/binlog/binlog
binlog_cache_size = 4M
binlog_format = MIXED
max_binlog_cache_size = 8M
max_binlog_size = 1G
relay-log-index = /data/mysql/3306/relaylog/relaylog
relay-log-info-file = /data/mysql/3306/relaylog/relaylog
relay-log = /data/mysql/3306/relaylog/relaylog
expire_logs_days = 30
key_buffer_size = 1024M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
interactive_timeout = 120
wait_timeout = 120
skip-name-resolve
#master-connect-retry = 10
slave-skip-errors = 1032,1062,126,1114,1146,1048,1396
#master-host = 192.168.0.1
#master-user = username
#master-password = password
#master-port = 3306
server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 64M
------------------------------------------------------------------------
创建mysql启动脚本文件
cp support-files/mysql.server.sh /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
/etc/init.d/mysqld start
查看mysql是否启动成功
[root@mail opt]# netstat -antl |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
为了安全起见，可以自己手动编写mysql启动脚本，上面步骤可以省略
----------------------------------------------------- -----------------
mysql 安装出错总结
1.在 CentOS 下编译安装MySQL时出错：
/bin/rm: cannot remove `libtoolt': No such file or directory
需要安装libtool软件包
2.[root@mail 3306]# /etc/init.d/mysqld restart
/etc/init.d/mysqld: line 272: @HOSTNAME@: command not found
/etc/init.d/mysqld: line 281: @HOSTNAME@: command not found
MySQL manager or server PID file could not be found! [失败]
需要把mysql配置文件my.cnf放到/etc/目录下面，并且在mysqld指定下面目录
basedir=/usr/local/mysql
datadir=/usr/local/mysql/3306/data/
登录数据库的时候报下面的错误：
[root@nagios]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
为了安全性，在编译数据库的时候修改了mysql.sock文件的存放位置，mysql.sock文件默认放在/tmp目录里面，所以在登录数据库的时候要指定修改后的mysql.sock文件的存放位置
[root@nagios]# locate mysql.sock
/etc/mysql/mysql.sock
[root@nagios]# /usr/local/mysql/bin/mysql -uroot -S /etc/mysql/mysql.sock -p
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-11-20 10:57:34 |
赞(
操作
