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

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