Your settings doesn't seem optimized much.

So here first question, do you use 32bits or 64 bits platform? If you
have 64 bits platform with 64 bits mysql and os you can boost most the
settings to use almost the 8G of ram you have on the server. If you are
using 32bits you will have to do some calculation so you don't go over
~2.6G (why not 4Gb?, go read on that on the net)

So the 2 most importants settings are:
key_buffer_size (mainly myisam table)
and/or
innodb_buffer_pool_size (innodb table)

Depending if you're using more innodb or myisam (or a mix) you'll tweak
those pamareters differently, it's usually however not recommended to go
over 4Gb for the key_buffer_size. MyIsam only stores the key into that
buffer, so you don't have much index, not worth taking it too big for no
reason. Innodb however can cache data as well, and will benefit from the
biggest value possible.

The server generate statistic that you can look to know the effect of
that. If you are using phpmyadmin in the variables and status part you
can see the index usage to guide you.


You can have a look at the different my.cnf that comes with mysql
distribution they put comment in there with interesting value for thumbs
rule. Here the except for key_buffer_size and innodb_buffer_pool_size:
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=2G

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=2G

Regards,
--
Mathieu Bruneau
aka ROunofF

===
GPG keys available @ http://rounoff.darktech.org

Justin a écrit :
> Ok.. Straight to the point.. Here is what I currently have.
>
> MySQL Ver 14.12 Distrib 5.0.27
> RHEL vs 5
> 584GB Raid 5 storage
> 8GB of RAM
> and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon)
>
> what my question is.. is am I utilizing the servers potential with the
> following as my settings. The server is a dedicated MySQL server so I
> want all power to go to the server. It just seems to be laggy at times.
> And I want to be sure I've optimized to the fullest potential
>
> My biggest issue is with FT searches. Tables get locked during larger
> queries and I can't select anything when that happens. Is there any way
> not to lock the tables on a Full Text search? (does that make sense?)
>
> thanks again for any insight
>
> Justin.
>
> Here's a dump of the my.cnf and the phpmyadmin dump of vars.
> ------------
> /etc/my.cnf
>
> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> wait_timeout=60
> default-character-set=utf8
> max_allowed_packet = 3000M
> max_connections = 5000
> ft_min_word_len=3
>
> server-id=1
> log-error = /var/log/mysql/error.log
> expire_logs_days = 3
>
>
> # Default to using old password format for compatibility with mysql 3.x
> # clients (those using the mysqlclient10 compatibility package).
> old_passwords=0
>
> [mysql.server]
> user=mysql
>
> [mysqld_safe]
> err-log=/var/log/mysql/mysqld.log
> pid-file=/var/run/mysqld/mysqld.pid
> ------------
>
> auto increment increment 1
> auto increment offset 1
> automatic sp privileges ON
> back log 50
> basedir /
> binlog cache size 32,768
> bulk insert buffer size 8,388,608
> character set client utf8
> character set connection utf8
> character set database utf8
> character set filesystem binary
> character set results utf8
> character set server utf8
> character set system utf8
> character sets dir /usr/share/mysql/charsets/
> collation connection utf8_general_ci
> collation database utf8_general_ci
> collation server utf8_general_ci
> completion type 0
> concurrent insert 1
> connect timeout 5
> 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
> engine condition pushdown OFF
> expire logs days 3
> flush OFF
> flush time 0
> ft boolean syntax + -><()~*:""&|
> ft max word len 84
> ft min word len 3
> ft query expansion limit 20
> ft stopword file (built-in)
> group concat max len 1,024
> have archive YES
> have bdb NO
> 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 query cache YES
> have raid NO
> have rtree keys YES
> have symlink YES
> 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 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,388,600
> 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 OFF
> log bin trust function creators OFF
> log error /var/log/mysql/error.log
> log queries not using indexes OFF
> log slave updates OFF
> log slow queries OFF
> log warnings 1
> long query time 10
> low priority updates OFF
> lower case file system OFF
> lower case table names 0
> max allowed packet 1,073,740,800
> max binlog cache size 4,294,967,295
> max binlog size 1,073,741,824
> max connect errors 10
> max connections 5,000
> max delayed threads 20
> max error count 64
> max heap table size 16,777,216
> 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 4,294,967,295
> max sort length 1,024
> max sp recursion depth 0
> max tmp tables 32
> max user connections 0
> max write lock count 4,294,967,295
> multi range count 256
> myisam data pointer size 6
> myisam max sort file size 2,147,483,647
> 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 30
> net retry count 10
> net write timeout 60
> new OFF
> old passwords OFF
> open files limit 25,010
> optimizer prune level 1
> optimizer search depth 62
> pid file /var/lib/mysql/dbs.live.pid
> port 3,306
> preload buffer size 32,768
> prepared stmt count 0
> 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 0
> 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
> server id 1
> 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 EDT
> table cache 64
> table lock wait timeout 50
> table type MyISAM
> thread cache size 0
> thread stack 196,608
> 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.27-standard
> version comment MySQL Community Edition - Standard (GPL)
> version compile machine i686
> version compile os pc-linux-gnu
> wait timeout 60
> Open new phpMyAdmin window
>
>
>
>
>
>
>