List:General Discussion« Previous MessageNext Message »
From:Justin Date:August 27 2007 6:14pm
Subject:servers full potential / FT searches locking tables
View as plain text  
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






Thread
servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
    • Re: servers full potential / FT searches locking tablesRolando Edwards27 Aug
      • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
        • Re: servers full potential / FT searches locking tablesJustin27 Aug
          • Re: servers full potential / FT searches locking tablesnigel wood27 Aug
    • Re: servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesMathieu Bruneau28 Aug
    • Re: servers full potential / FT searches locking tablesJustin28 Aug
      • Re: servers full potential / FT searches locking tablesMichael Dykman28 Aug
        • Re: servers full potential / FT searches locking tablesJustin28 Aug
        • Re: servers full potential / FT searches locking tablesKen Peng29 Aug
          • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
            • Re: servers full potential / FT searches locking tablesKen Peng30 Aug
              • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
        • Re: servers full potential / FT searches locking tablesJustin1 Sep
          • Re: servers full potential / FT searches locking tablesJustin3 Sep
            • Re: servers full potential / FT searches locking tablesBaron Schwartz3 Sep
              • Re: servers full potential / FT searches locking tablesJustin4 Sep
                • Memory Issue would someone confirmJustin4 Sep
                  • Re: Memory Issue would someone confirmJustin4 Sep