Server variables and settings

Can anybody make any recomendations to my settings below? I am using MyISAM & mainly running SELECT queries.

Variable Session value / Global valueauto increment increment 1auto increment offset 1automatic sp privileges ONback log 50basedir C:\Program Files\Parallels\Plesk\Databases\MySQL\binlog cache size 32,768bulk insert buffer size 8,388,608character set client utf8(Global value) latin1character set connection utf8(Global value) latin1character set database latin1character set filesystem binarycharacter set results utf8(Global value) latin1character set server latin1character set system utf8character sets dir C:\Program Files\Parallels\Plesk\Databases\MySQL\share\charsets\collation connection utf8_general_ci(Global value) latin1_swedish_cicollation database latin1_swedish_cicollation server latin1_swedish_cicompletion type 0concurrent insert 1connect timeout 5datadir C:\Program Files\Parallels\Plesk\Databases\MySQL\Data\date format %Y-%m-%ddatetime format %Y-%m-%d %H:%i:%sdefault week format 0delay key write ONdelayed insert limit 100delayed insert timeout 300delayed queue size 1,000div precision increment 4engine condition pushdown OFFexpire logs days 0flush OFFflush time 1,800ft boolean syntax + -><()~*:""&|ft max word len 84ft min word len 4ft query expansion limit 20ft stopword file (built-in)group concat max len 1,024have archive YEShave bdb NOhave blackhole engine YEShave compress YEShave crypt NOhave csv NOhave dynamic loading YEShave example engine NOhave federated engine YEShave geometry YEShave innodb YEShave isam NOhave merge engine YEShave ndbcluster NOhave openssl DISABLEDhave ssl DISABLEDhave query cache YEShave raid NOhave rtree keys YEShave symlink YEShostname svctag-7y61s3jinit connect init file init slave innodb additional mem pool size 2,097,152innodb autoextend increment 8innodb buffer pool awe mem mb 0innodb buffer pool size 16,777,216innodb checksums ONinnodb commit concurrency 0innodb concurrency tickets 500innodb data file path ibdata1:10M:autoextendinnodb data home dir innodb doublewrite ONinnodb fast shutdown 1innodb file io threads 4innodb file per table OFFinnodb flush log at trx commit 1innodb flush method innodb force recovery 0innodb lock wait timeout 50innodb locks unsafe for binlog OFFinnodb log arch dir innodb log archive OFFinnodb log buffer size 1,048,576innodb log file size 10,485,760innodb log files in group 2innodb log group home dir .\innodb max dirty pages pct 90innodb max purge lag 0innodb mirrored log groups 1innodb open files 300innodb rollback on timeout OFFinnodb support xa ONinnodb sync spin loops 20innodb table locks ONinnodb thread concurrency 8innodb thread sleep delay 10,000interactive timeout 28,800join buffer size 131,072key buffer size 8,388,600key cache age threshold 300key cache block size 1,024key cache division limit 100language C:\Program Files\Parallels\Plesk\Databases\MySQL\share\english\large files support ONlarge page size 0large pages OFFlc time names en_USlicense GPLlocal infile ONlog OFFlog bin OFFlog bin trust function creators OFFlog error C:\Program Files\Parallels\Plesk\Databases\MySQL\Data\svctag-7y61s3j.errlog queries not using indexes OFFlog slave updates OFFlog slow queries OFFlog warnings 1long query time 10low priority updates OFFlower case file system ONlower case table names 1max allowed packet 1,048,576max binlog cache size 4,294,967,295max binlog size 1,073,741,824max connect errors 10max connections 400max delayed threads 20max error count 64max heap table size 16,777,216max insert delayed threads 20max join size 4,294,967,295max length for sort data 1,024max prepared stmt count 16,382max relay log size 0max seeks for key 4,294,967,295max sort length 1,024max sp recursion depth 0max tmp tables 32max user connections 30max write lock count 4,294,967,295multi range count 256myisam data pointer size 6myisam max sort file size 2,147,483,647myisam recover options OFFmyisam repair threads 1myisam sort buffer size 8,388,608myisam stats method nulls_unequalnamed pipe OFFnet buffer length 16,384net read timeout 30net retry count 10net write timeout 30new OFFold passwords ONopen files limit 2,000optimizer prune level 1optimizer search depth 62pid file C:\Program Files\Parallels\Plesk\Databases\MySQL\Data\svctag-7y61s3j.pidport 3,306preload buffer size 32,768profiling OFFprofiling history size 15protocol version 10query alloc block size 8,192query cache limit 1,048,576query cache min res unit 4,096query cache size 0query cache type ONquery cache wlock invalidate OFFquery prealloc size 8,192range alloc block size 2,048read buffer size 1,044,480read only OFFread rnd buffer size 258,048relay log purge ONrelay log space limit 0rpl recovery rank 0secure auth OFFsecure file priv shared memory OFFshared memory base name MYSQLserver id 0skip external locking ONskip networking OFFskip show database OFFslave compressed protocol OFFslave load tmpdir C:\WINDOWS\TEMP\slave net timeout 3,600slave skip errors OFFslave transaction retries 10slow launch time 2sort buffer size 262,136sql big selects ONsql mode sql notes ONsql warnings OFFssl ca ssl capath ssl cert ssl cipher ssl key storage engine InnoDBsync binlog 0sync frm ONsystem time zone GMT Standard Timetable cache 64table lock wait timeout 50table type InnoDBthread cache size 4thread stack 196,608time format %H:%i:%stime zone SYSTEMtimed mutexes OFFtmp table size 33,554,432tmpdir C:\WINDOWS\TEMP\transaction alloc block size 8,192transaction prealloc size 4,096tx isolation REPEATABLE-READupdatable views with limit YESversion 5.0.45-community-ntversion comment MySQL Community Edition (GPL)version compile machine ia32version compile os Win32wait timeout 28,800

I’ve read that by increasing the key_buffer_size will help with running large queries??

Yes, increasing the key_buffer would be the first thing I’d do.

How much RAM do you have? Ideally, you want the key_buffer big enough to hold all the indexes, but not so big that you prevent the data from being cached by the operating system.

If you’re not using InnoDB at all, add “skip-innodb” to your configuration file.


I have 2GB DDR SDRAM. Also I’m a bit of a newbie with all this ( , where do I locate the config file to make the changes??

I read that by doing it with the SET GLOBAL … only does it for the current session & reverts back to default settings after.



Its hard to say, without knowing the particulars of your application, but one thing that stuck out was sort buffer looked pretty low and your query cache is off, and that can be a big thing. Since your not using innodb, I would suggest settings its memory allocations to 0, and perhaps set up some dedicated index caches for your main tables.