巨型MYSQL数据库配置设定

 

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

 

基本信息如下：

 

 

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

运行信息

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

流量 ø 每小时已收到1,146 GB199 MB送出118 TB20 GB统计119 TB21 GB连接ø 每小时%max. concurrent connections1,501 --- --- 尝试失败334 k56.72 0.04%中止89 M15.11 k9.37%统计950 M161.24 k100.00%

---

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

统计ø 每小时ø 每分钟ø 每秒11 G1.81 M30.13 k502.22

 

 

 服务器变量和设置 变量会话值 / 全局值 auto increment increment1auto increment offset1automatic sp privilegesONback log1,000basedir/usr/bdb cache size8,388,600bdb home/var/lib/mysql/bdb log buffer size32,768bdb logdir bdb max lock10,000bdb shared dataOFFbdb tmpdir/tmp/binlog cache size32,768bulk insert buffer size8,388,608character set clientutf8(全局值)latin1character set connectionutf8(全局值)latin1character set databaselatin1character set filesystembinarycharacter set resultsutf8(全局值)latin1character set serverlatin1character set systemutf8character sets dir/usr/share/mysql/charsets/collation connectionutf8_unicode_ci(全局值)latin1_swedish_cicollation databaselatin1_swedish_cicollation serverlatin1_swedish_cicompletion type0concurrent insert1connect timeout10datadir/var/lib/mysql/date format%Y-%m-%ddatetime format%Y-%m-%d %H:%i:%sdefault week format0delay key writeONdelayed insert limit100delayed insert timeout300delayed queue size1,000div precision increment4keep files on createOFFengine condition pushdownOFFexpire logs days14flushOFFflush time0ft boolean syntax+ -><()~*:""&|ft max word len84ft min word len4ft query expansion limit20ft stopword file(built-in)group concat max len1,024have archiveNOhave bdbYEShave blackhole engineNOhave compressYEShave cryptYEShave csvNOhave dynamic loadingYEShave example engineNOhave federated engineNOhave geometryYEShave innodbYEShave isamNOhave merge engineYEShave ndbclusterNOhave opensslDISABLEDhave sslDISABLEDhave query cacheYEShave raidNOhave rtree keysYEShave symlinkYEShostnamedfwwpd1.ch3.sourceforge.cominit connect init file init slave innodb additional mem pool size1,048,576innodb autoextend increment8innodb buffer pool awe mem mb0innodb buffer pool size8,388,608innodb checksumsONinnodb commit concurrency0innodb concurrency tickets500innodb data file pathibdata1:10M:autoextendinnodb data home dir innodb doublewriteONinnodb fast shutdown1innodb file io threads4innodb file per tableOFFinnodb flush log at trx commit1innodb flush method innodb force recovery0innodb lock wait timeout50innodb locks unsafe for binlogOFFinnodb log arch dir innodb log archiveOFFinnodb log buffer size1,048,576innodb log file size5,242,880innodb log files in group2innodb log group home dir./innodb max dirty pages pct90innodb max purge lag0innodb mirrored log groups1innodb open files300innodb rollback on timeoutOFFinnodb support xaONinnodb sync spin loops20innodb table locksONinnodb thread concurrency8innodb thread sleep delay10,000interactive timeout28,800join buffer size131,072key buffer size8,589,934,592key cache age threshold300key cache block size1,024key cache division limit100language/usr/share/mysql/english/large files supportONlarge page size0large pagesOFFlc time namesen_USlicenseGPLlocal infileONlocked in memoryOFFlogOFFlog binONlog bin trust function creatorsOFFlog error log queries not using indexesOFFlog slave updatesOFFlog slow queriesONlog warnings1long query time1low priority updatesOFFlower case file systemOFFlower case table names0max allowed packet8,387,584max binlog cache size18446744073709551615max binlog size1,073,741,824max connect errors1,000max connections1,500max delayed threads20max error count64max heap table size4,294,967,296max insert delayed threads20max join size18446744073709551615max length for sort data1,024max prepared stmt count16,382max relay log size0max seeks for key18446744073709551615max sort length1,024max sp recursion depth0max tmp tables32max user connections0max write lock count18446744073709551615multi range count256myisam data pointer size6myisam max sort file size9223372036854775807myisam recover optionsOFFmyisam repair threads1myisam sort buffer size8,388,608myisam stats methodnulls_unequalnet buffer length16,384net read timeout60net retry count100net write timeout120newOFFold passwordsOFFopen files limit7,500optimizer prune level1optimizer search depth62pid file/var/run/mysqld/mysqld.pidport3,306preload buffer size32,768profilingOFFprofiling history size15protocol version10query alloc block size8,192query cache limit1,048,576query cache min res unit4,096query cache size33,554,432query cache typeONquery cache wlock invalidateOFFquery prealloc size8,192range alloc block size2,048read buffer size131,072read onlyOFFread rnd buffer size262,144relay log purgeONrelay log space limit0rpl recovery rank0secure authOFFsecure file priv server id7,003skip external lockingONskip networkingOFFskip show databaseOFFslave compressed protocolOFFslave load tmpdir/tmp/slave net timeout3,600slave skip errorsOFFslave transaction retries10slow launch time2socket/var/lib/mysql/mysql.socksort buffer size2,097,144sql big selectsONsql mode sql notesONsql warningsOFFssl ca ssl capath ssl cert ssl cipher ssl key storage engineMyISAMsync binlog0sync frmONsystem time zoneUTCtable cache64table lock wait timeout50table typeMyISAMthread cache size128thread stack262,144time format%H:%i:%stime zoneSYSTEMtimed mutexesOFFtmp table size33,554,432tmpdir/tmp/transaction alloc block size8,192transaction prealloc size4,096tx isolationREPEATABLE-READupdatable views with limitYESversion5.0.51a-logversion bdbSleepycat Software: Berkeley DB 4.1.24: (January 11, 2008)version commentSource distributionversion compile machinex86_64version compile osredhat-linux-gnuwait timeout301