Re: Lock wait timeout exceeded

On 8/16/07, Stewart Smith <stewart@mysql.com> wrote:
>
> On Thu, 2007-07-26 at 10:57 -0400, Gerard C wrote:
> > It seems daily the cluster becomes non responsive. The error I get from
> the
> > php application is "Query failed: Lock wait timeout exceeded; try
> restarting
> > transaction". My setup is 2 servers running the API and the storage node
> on
> > each, and then another server running only the management server. There
> is a
> > Netscaler load balancer in front of the API servers balancing the
> > connections.
> >
> > When the issue occurs it seem there are more connections then queries. I
> > would say there are probably about 3 times more connections then
> queries. As
> > far as the traffic goes each server currently does about 100 queries per
> > second, but this will go up as we add more databases to the cluster.
> >
> > Thanks in advance for your help.
>
> provide cluster config, table schema, queries, explain. then can start
> helping.
>
> --
> Stewart Smith, Senior Software Engineer
> MySQL AB, www.mysql.com
> Office: +14082136540 Ext: 6616
> VoIP: 6616@sip.us.mysql.com
> Mobile: +61 4 3 8844 332
>
> Jumpstart your cluster:
> http://www.mysql.com/consulting/packaged/cluster.html
>
>

Below are the 2 tables that are used the most. Once this stabilizes we need to add more but those are the current. Also, after skip-locking was added the issue happens less frequently but still happens which makes it harder to troubleshoot. These databases are doing about 95% select statements. The inserts and updates are rare. Let me know what other information you would need. Thanks.

+------------+-------------+------+-----+--------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+--------------+-------+

| field1       | varchar(16) | NO   | PRI |              |       |
| field2       | varchar(64) | NO   | MUL |              |       |
| field3 | varchar(16) | NO   |     |  |       |
| field4       | varchar(8)  | NO   |     | 143          |       |
| field5   | varchar(64) | NO   |     |              |       |
| field6     | varchar(32) | NO   |     |  |       |
| field7     | char(1)     | NO   |     | 0            |       |

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| field1       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| field2 | varchar(254)     | NO   | UNI |         |                |
| field3  | varchar(254)     | NO   |     |         |                |
| field4     | varchar(15)      | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

config.ini

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=2500M
IndexMemory=500M

[NDB_MGMD]
hostname=x.x.x.1
[NDBD]
HostName=x.x.x.2
DataDir= /var/lib/mysql-cluster
[NDBD]
HostName=x.x.x.3
DataDir=/var/lib/mysql-cluster
[MYSQLD]
hostname=x.x.x.1
[MYSQLD]
HostName=x.x.x.2
[MYSQLD]
HostName=x.x.x.3

my.cnf
[mysqld]
ndbcluster
ndb-connectstring=x.x.x.1
max_connections = 900
skip-name-resolve
skip-locking
#log = /var/log/mysql.log
log-slow-queries = /var/log/mysql-slow-queries.log log-error = /var/log/mysql-error.log

# provide connectstring for management server host (default port: 1186) [ndbd]
connect-string=x.x.x.1
TransactionDeadlockDetectionTimeout = 12000

# provide connectstring for management server host (default port: 1186) [ndb_mgm]
connect-string=x.x.x.1

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

# provide location of cluster configuration file [ndb_mgmd]
config-file=/etc/config.ini Received on Tue Sep 4 16:27:43 2007