List:General Discussion« Previous MessageNext Message »
From:Rolando Edwards Date:August 27 2007 6:41pm
Subject:Re: servers full potential / FT searches locking tables
View as plain text  
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@stripped>
To: "Justin" <mysql@stripped>
Cc: mysql@stripped
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=1



-- 
Rolando A. Edwards
MySQL DBA

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

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