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

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