巨型MYSQL数据库配置设定

 

该服务器服务于全球alexa 170位左右的网站，供大家参考!

 

基本信息如下：

 

 

此 MySQL 服务器已经运行了 245 天 12 小时，12 分 51 秒，启动时间为 2009 年 03 月 05 日 19:18。

运行信息

服务器流量：这些表显示了此 MySQL 服务器自启动以来的网络流量统计。

流量		ø 每小时
已收到	1,146 GB	199 MB
送出	118 TB	20 GB
统计	119 TB	21 GB

连接		ø 每小时	%
max. concurrent connections	1,501	---	---
尝试失败	334 k	56.72	0.04%
中止	89 M	15.11 k	9.37%
统计	950 M	161.24 k	100.00%

---

查询统计：自从启动后，服务器共收到了 10,653,108,965 次查询。

统计	ø 每小时	ø 每分钟	ø 每秒
11 G	1.81 M	30.13 k	502.22

 

 

 服务器变量和设置

变量	会话值 / 全局值
auto increment increment	1
auto increment offset	1
automatic sp privileges	ON
back log	1,000
basedir	/usr/
bdb cache size	8,388,600
bdb home	/var/lib/mysql/
bdb log buffer size	32,768
bdb logdir
bdb max lock	10,000
bdb shared data	OFF
bdb tmpdir	/tmp/
binlog cache size	32,768
bulk insert buffer size	8,388,608
character set client	utf8
(全局值)	latin1
character set connection	utf8
(全局值)	latin1
character set database	latin1
character set filesystem	binary
character set results	utf8
(全局值)	latin1
character set server	latin1
character set system	utf8
character sets dir	/usr/share/mysql/charsets/
collation connection	utf8_unicode_ci
(全局值)	latin1_swedish_ci
collation database	latin1_swedish_ci
collation server	latin1_swedish_ci
completion type	0
concurrent insert	1
connect timeout	10
datadir	/var/lib/mysql/
date format	%Y-%m-%d
datetime format	%Y-%m-%d %H:%i:%s
default week format	0
delay key write	ON
delayed insert limit	100
delayed insert timeout	300
delayed queue size	1,000
div precision increment	4
keep files on create	OFF
engine condition pushdown	OFF
expire logs days	14
flush	OFF
flush time	0
ft boolean syntax	+ -><()~*:""&|
ft max word len	84
ft min word len	4
ft query expansion limit	20
ft stopword file	(built-in)
group concat max len	1,024
have archive	NO
have bdb	YES
have blackhole engine	NO
have compress	YES
have crypt	YES
have csv	NO
have dynamic loading	YES
have example engine	NO
have federated engine	NO
have geometry	YES
have innodb	YES
have isam	NO
have merge engine	YES
have ndbcluster	NO
have openssl	DISABLED
have ssl	DISABLED
have query cache	YES
have raid	NO
have rtree keys	YES
have symlink	YES
hostname	dfwwpd1.ch3.sourceforge.com
init connect
init file
init slave
innodb additional mem pool size	1,048,576
innodb autoextend increment	8
innodb buffer pool awe mem mb	0
innodb buffer pool size	8,388,608
innodb checksums	ON
innodb commit concurrency	0
innodb concurrency tickets	500
innodb data file path	ibdata1:10M:autoextend
innodb data home dir
innodb doublewrite	ON
innodb fast shutdown	1
innodb file io threads	4
innodb file per table	OFF
innodb flush log at trx commit	1
innodb flush method
innodb force recovery	0
innodb lock wait timeout	50
innodb locks unsafe for binlog	OFF
innodb log arch dir
innodb log archive	OFF
innodb log buffer size	1,048,576
innodb log file size	5,242,880
innodb log files in group	2
innodb log group home dir	./
innodb max dirty pages pct	90
innodb max purge lag	0
innodb mirrored log groups	1
innodb open files	300
innodb rollback on timeout	OFF
innodb support xa	ON
innodb sync spin loops	20
innodb table locks	ON
innodb thread concurrency	8
innodb thread sleep delay	10,000
interactive timeout	28,800
join buffer size	131,072
key buffer size	8,589,934,592
key cache age threshold	300
key cache block size	1,024
key cache division limit	100
language	/usr/share/mysql/english/
large files support	ON
large page size	0
large pages	OFF
lc time names	en_US
license	GPL
local infile	ON
locked in memory	OFF
log	OFF
log bin	ON
log bin trust function creators	OFF
log error
log queries not using indexes	OFF
log slave updates	OFF
log slow queries	ON
log warnings	1
long query time	1
low priority updates	OFF
lower case file system	OFF
lower case table names	0
max allowed packet	8,387,584
max binlog cache size	18446744073709551615
max binlog size	1,073,741,824
max connect errors	1,000
max connections	1,500
max delayed threads	20
max error count	64
max heap table size	4,294,967,296
max insert delayed threads	20
max join size	18446744073709551615
max length for sort data	1,024
max prepared stmt count	16,382
max relay log size	0
max seeks for key	18446744073709551615
max sort length	1,024
max sp recursion depth	0
max tmp tables	32
max user connections	0
max write lock count	18446744073709551615
multi range count	256
myisam data pointer size	6
myisam max sort file size	9223372036854775807
myisam recover options	OFF
myisam repair threads	1
myisam sort buffer size	8,388,608
myisam stats method	nulls_unequal
net buffer length	16,384
net read timeout	60
net retry count	100
net write timeout	120
new	OFF
old passwords	OFF
open files limit	7,500
optimizer prune level	1
optimizer search depth	62
pid file	/var/run/mysqld/mysqld.pid
port	3,306
preload buffer size	32,768
profiling	OFF
profiling history size	15
protocol version	10
query alloc block size	8,192
query cache limit	1,048,576
query cache min res unit	4,096
query cache size	33,554,432
query cache type	ON
query cache wlock invalidate	OFF
query prealloc size	8,192
range alloc block size	2,048
read buffer size	131,072
read only	OFF
read rnd buffer size	262,144
relay log purge	ON
relay log space limit	0
rpl recovery rank	0
secure auth	OFF
secure file priv
server id	7,003
skip external locking	ON
skip networking	OFF
skip show database	OFF
slave compressed protocol	OFF
slave load tmpdir	/tmp/
slave net timeout	3,600
slave skip errors	OFF
slave transaction retries	10
slow launch time	2
socket	/var/lib/mysql/mysql.sock
sort buffer size	2,097,144
sql big selects	ON
sql mode
sql notes	ON
sql warnings	OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine	MyISAM
sync binlog	0
sync frm	ON
system time zone	UTC
table cache	64
table lock wait timeout	50
table type	MyISAM
thread cache size	128
thread stack	262,144
time format	%H:%i:%s
time zone	SYSTEM
timed mutexes	OFF
tmp table size	33,554,432
tmpdir	/tmp/
transaction alloc block size	8,192
transaction prealloc size	4,096
tx isolation	REPEATABLE-READ
updatable views with limit	YES
version	5.0.51a-log
version bdb	Sleepycat Software: Berkeley DB 4.1.24: (January 11, 2008)
version comment	Source distribution
version compile machine	x86_64
version compile os	redhat-linux-gnu
wait timeout	301