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

SELECTs don't lock the table. Are you having frequent UPDATEs while
selecting? That would be the reason for locks.

-jay

Justin wrote:
> 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
>
>
>
>
>
>
>

SELECTs do lock the tables implicitly.

According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading "A lock on data can be acquired implicitly or explicitly":

For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement.

----- Original Message -----
From: "Jay Pipes" <jay@mysql.com>
To: "Justin" <mysql@b0rker.com>
Cc: mysql@lists.mysql.com
Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York
Subject: Re: servers full potential / FT searches locking tables

SELECTs don't lock the table. Are you having frequent UPDATEs while
selecting? That would be the reason for locks.

-jay

Justin wrote:
> 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
>
>
>
>
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com



--
Rolando A. Edwards
MySQL DBA

SWMX, Inc.
1 Bridge Street
Irvington, NY 10533
(914) 406-8406 (Main)
(201) 660-3221 (Mobile)

sorry.. you're right.. that came out wrong.. actually shouldn't of even of
put that in there like that..

The locking is some other issue that I can't for the life of me remember
what the query was.. I know it wasn't an Update but it was copying to a tmp
table then sorting the result which was locking the table from the process I
was looking at. I'll keep an eye on it to see if I Can replicate it
again.. I just want to make sure I've got the server config'd right to
utilize everything I have.

thanks for the response




----- Original Message -----
From: "Jay Pipes" <jay@mysql.com>
To: "Justin" <mysql@b0rker.com>
Cc: <mysql@lists.mysql.com>
Sent: Monday, August 27, 2007 2:26 PM
Subject: Re: servers full potential / FT searches locking tables


> SELECTs don't lock the table. Are you having frequent UPDATEs while
> selecting? That would be the reason for locks.
>
> -jay
>
> Justin wrote:
>> 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
>>
>>
>>
>>
>>
>>
>>
>
>

A read lock does not prevent other reads.

Rolando Edwards wrote:
> SELECTs do lock the tables implicitly.
>
> According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading "A lock on data can be acquired implicitly or explicitly":
>
> For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement.
>
> ----- Original Message -----
> From: "Jay Pipes" <jay@mysql.com>
> To: "Justin" <mysql@b0rker.com>
> Cc: mysql@lists.mysql.com
> Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York
> Subject: Re: servers full potential / FT searches locking tables
>
> SELECTs don't lock the table. Are you having frequent UPDATEs while
> selecting? That would be the reason for locks.
>
> -jay
>
> Justin wrote:
>> 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
>>
>>
>>
>>
>>
>>
>>
>
>

Sometimes I get about 300 connections to the server, all are selects and all
select and get the data returned but the connection doesn't go away and the
website doesn't load up.. usually if there is a lock, the selects wait 2-3
secs and build up, but once unlocked the queries all are performed and then
go away.. and the sites load fine.. 3 times today this has happened and I'm
trying to figure out what could be the cause of it. if I restart MySQL
everything is good.

Anyone have any ideas or any ideas on how I can trace where the culprit
would be..

it's a LAMP backend..




----- Original Message -----
From: "Jay Pipes" <jay@mysql.com>
To: "Rolando Edwards" <rolando@swmx.com>
Cc: <mysql@lists.mysql.com>; "Justin" <mysql@b0rker.com>
Sent: Monday, August 27, 2007 3:03 PM
Subject: Re: servers full potential / FT searches locking tables


>A read lock does not prevent other reads.
>
> Rolando Edwards wrote:
>> SELECTs do lock the tables implicitly.
>>
>> According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0
>> Certification Study Guide (ISBN 0-672-32812-7), here is what the first
>> bulletpoint says under the heading "A lock on data can be acquired
>> implicitly or explicitly":
>>
>> For a client that does nothing special to acquires locks, the MySQL
>> server implicitly acquires locks as necessary to process the client's
>> statments sdafely. For example, the server acquires a read lock when the
>> client issues a SELECT statement and a write lock when the client issues
>> an INSERT statement. Implicit locks are acquired only for the duration of
>> a single statement.
>>
>> ----- Original Message -----
>> From: "Jay Pipes" <jay@mysql.com>
>> To: "Justin" <mysql@b0rker.com>
>> Cc: mysql@lists.mysql.com
>> Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York
>> Subject: Re: servers full potential / FT searches locking tables
>>
>> SELECTs don't lock the table. Are you having frequent UPDATEs while
>> selecting? That would be the reason for locks.
>>
>> -jay
>>
>> Justin wrote:
>>> 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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@b0rker.com
>
>

Justin wrote:
> Sometimes I get about 300 connections to the server, all are selects
> and all select and get the data returned but the connection doesn't go
> away and the website doesn't load up.. usually if there is a lock, the
> selects wait 2-3 secs and build up, but once unlocked the queries all
> are performed and then go away.. and the sites load fine.. 3 times
> today this has happened and I'm trying to figure out what could be the
> cause of it. if I restart MySQL everything is good.
>
> Anyone have any ideas or any ideas on how I can trace where the
> culprit would be..
>
> it's a LAMP backend..
>
>
Are you classing insert...select or create table....select as selects?
They have very different locking effects.
Is the database in replication? if so is it a master or slave?
Do you have NFS mounts anywhere on the database server?
Are you using exclusively innodb tables? if not the source is probably
table contention.
Get a couple of 'show full processlist', 'show innodb status' query
outputs during the lockups and run vmstat 1 -S M in another terminal.
With the outputs from both you've something to work with.

Nigel Wood

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
>
>
>
>
>
>
>

32bit, but I have all available memory..

MemTotal: 8179612 kB
MemFree: 43684 kB

on the box. I think the 4gb is only windows.

All my tables are in myisam

so if I was to set
key_buffer_size=5500M

That'd be acceptable?

----- Original Message -----
From: "Mathieu Bruneau" <mathieu.bruneau@argontechnologies.ca>
To: "Justin" <mysql@b0rker.com>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, August 28, 2007 12:51 AM
Subject: Re: servers full potential / FT searches locking tables


> 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