Re: MaxNoOfConcurrentTransactions problem or Loc file error 4006 ? - Mikael Ronström - com.mysql.lists.cluster

2 messages in com.mysql.lists.clusterRe: MaxNoOfConcurrentTransactions pro...

From	Sent On	Attachments
IpFrom Tech Dept.	14 Jan 2005 09:37
Mikael Ronström	14 Jan 2005 23:57

Subject:	Re: MaxNoOfConcurrentTransactions problem or Loc file error 4006 ?
From:	Mikael Ronström (mik...@mysql.com)
Date:	01/14/2005 11:57:29 PM
List:	com.mysql.lists.cluster

Hi,

2005-01-14 kl. 18.38 skrev IpFrom Tech Dept.:

Hello, We are experiencing serious problems with this error with version 4.1.9: ERROR 1015 (HY000): Can't lock file (errno: 4006)

In version 4.1.8 it was: ERROR 1297 (HY000): Got temporary error 4006 'Connect failure - out of connection objects (increase MaxNoOfConcurrentTransactions)' from ndbcluster

The error is the same.

We tried to increase the parameter value in the configuration file, but the only change is that the cluster stops working after more queries than before, but it stops. It looks like transactions are not closed once finished. The parameters now are: MaxNoOfConcurrentOperations = 1048576 MaxNoOfConcurrentTransactions = 131072 (see full config file below) To make it work again we must leave the cluster idle for a couple of minutes. We didn't find a better solution yet.

We are testing the system by sending about 100.000 (100 concurrent) http requests to a front-end (apache) which then connects to the cluster backend. We have peaks of 400/500 queries per second on each working node. (we are setting up an adserver which will have to serve more than 7mil bans per day).

When the MySQL Server needs to start a transaction it will look for a transaction object in a pool. Each object in this pool is connected to a certain data node. Also when starting a scan of a table a transaction object is used. Thus in the worst case a connection should be able to allocate Max_no_of_tables_in_query + 1 (transaction) + 1 (short term usage) at the same on a data node. This means if say you use max 20 table joins, then you would maximum get 22 transaction objects per connection. With max 100 concurrent requests I presume it couldn't be more than 100 connections to each MySQL Server and there are 3 of them. So in the absolutely worst case I couldn't figure out that more than 3 * 100 * 22 = 6600 objects are used.

So there are a few things one could do to boil down the problem.

1) Try running the mysqld processes using the command option --skip-ndb-optimized-node-selection If this makes it work, please file a bug report on the previous problem.

2) Try extending the parameter to an even greater number and see if you can find a number big enough. If this is possible then please report this number and a little more of how many tables are involved in queries and number of connections and so forth.

3) If neither of those makes it work then it sounds very much as a bug, in that case report similar things as in 2) in the bug report.

Rgrds Mikael

Thank you all for any suggestion. gian tech at ipfrom dot com

Some info: -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: 10.0.91.118:1186 Cluster Configuration

--------------------- [ndbd(NDB)] 2 node(s) id=2 @10.0.91.119 (Version: 4.1.9, Nodegroup: 0, Master) id=3 @10.0.91.120 (Version: 4.1.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s) id=1 @10.0.91.118 (Version: 4.1.9)

[mysqld(API)] 3 node(s) id=4 @10.0.91.118 (Version: 4.1.9) id=5 @10.0.91.120 (Version: 4.1.9) id=6 @10.0.91.119 (Version: 4.1.9)

This is our config.ini: [NDBD DEFAULT] NoOfReplicas = 2 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] # Managment Server [NDB_MGMD] HostName = 10.0.91.118 # the IP of THIS SERVER # Storage Engines [NDBD] HostName = 10.0.91.119 # the IP of the FIRST SERVER DataDir = /var/lib/mysql MaxNoOfConcurrentOperations = 1048576 MaxNoOfConcurrentTransactions = 131072 [NDBD] HostName = 10.0.91.120 # the IP of the SECOND SERVER DataDir = /var/lib/mysql MaxNoOfConcurrentOperations = 1048576 MaxNoOfConcurrentTransactions = 131072 # 3 MySQL Clients # I personally leave this blank to allow rapid changes of the mysql clients; # you can enter the hostnames of the above two servers here. I suggest you dont. [MYSQLD] [MYSQLD] [MYSQLD]

Mikael Ronström, Senior Software Architect MySQL AB, www.mysql.com

Clustering: http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html

http://www.eweek.com/article2/0,1759,1567546,00.asp