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" <[EMAIL PROTECTED]> To: "Justin" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
