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