Magnus,
Here's the schema -
CREATE TABLE `linkedavailableroute2` (
`routeKey1` bigint(20) NOT NULL,
`routeKey2` bigint(20) NOT NULL,
`routeKey3` bigint(20) NOT NULL,
`pointOfSaleCountryCode` char(3) DEFAULT NULL,
`possibleDupeJourney` char(1) DEFAULT NULL,
`pointOfCommencementCityCode` char(3) DEFAULT NULL,
`destinationCityCode` char(3) DEFAULT NULL,
`airportCodes` varchar(24) DEFAULT NULL,
`lastModifiedDate` int(11) DEFAULT '0',
`lastModifiedTime` int(11) DEFAULT '0',
`availability` char(26) NOT NULL,
`travelDateOffsets` smallint(6) DEFAULT '0',
PRIMARY KEY (`routeKey1`,`routeKey2`,`routeKey3`),
KEY `lastModifiedDateIndex` (`lastModifiedDate`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
and the Java Interface
@PersistenceCapable(table="linkedavailableroute2")
public interface Availability {
@Column(name="routeKey1")
long getRouteKey1();
void setRouteKey1(long routeKey1);
@Column(name="routeKey2")
long getRouteKey2();
void setRouteKey2(long routeKey2);
@Column(name="routeKey3")
long getRouteKey3();
void setRouteKey3(long routeKey3);
@Column(name="pointOfSaleCountryCode")
String getPointOfSaleCountryCode();
void setPointOfSaleCountryCode(String pointOfSaleCountryCode);
@Column(name="possibleDupeJourney")
String getPossibleDupeJourney();
void setPossibleDupeJourney(String possibleDupeJourney);
@Column(name="pointOfCommencementCityCode")
String getPointOfCommencementCityCode();
void setPointOfCommencementCityCode(String pointOfCommencementCityCode);
@Column(name="destinationCityCode")
String getDestinationCityCode();
void setDestinationCityCode(String destinationCityCode);
@Column(name="airportCodes")
String getAirportCodes();
void setAirportCodes(String airportCodes);
@Column(name="lastModifiedDate")
int getLastModifiedDate();
void setLastModifiedDate(int lastModifiedDate);
@Column(name="lastModifiedTime")
int getLastModifiedTime();
void setLastModifiedTime(int lastModifiedTime);
@Column(name="availability")
String getAvailability();
void setAvailability(String availability);
@Column(name="travelDateOffsets")
short getTravelDateOffsets();
void setTravelDateOffsets(short travelDateOffsets);
}
And the run() method from my Runnable implementation - (i have a
ExecutorService with a configurable number of threads that pulls these off
its internal queue)
public void run() {
Session session =
ThreadContext.getInstance().getSession().get();
Availability availability =
ThreadContext.getInstance().getAvailability().get();
Integer persistedCount =
ThreadContext.getInstance().getPersistedCount().get();
if (session == null) {
System.out.println("Thread " +
Thread.currentThread().getName() + " creating session");
session =
ThreadContext.getInstance().getSessionFactory().getSession();
session.currentTransaction().begin();
ThreadContext.getInstance().getSession().set(session);
availability = session.newInstance(Availability.class);
ThreadContext.getInstance().getAvailability().set(availability);
persistedCount = 0;
}
availability.setRouteKey1(AvailabilityHelper.generateRouteKey(_originCityCode,
_destinationCityCode, _carrierCode, _travelDate));
availability.setRouteKey2(_flightNumber);
availability.setRouteKey3(0);
availability.setAirportCodes(_airportCodes);
availability.setAvailability(_availability);
availability.setDestinationCityCode(_destinationCityCode);
availability.setLastModifiedDate(_lastModifiedDate);
availability.setLastModifiedTime(_lastModifiedTime);
availability.setPointOfSaleCountryCode(_pointOfSaleCountryCode);
availability.setPossibleDupeJourney(_possibleDupeJourney);
availability.setPointOfCommencementCityCode(_pointOfCommencementCityCode);
availability.setTravelDateOffsets(_travelDateOffsets);
session.savePersistent(availability);
ThreadContext.getInstance().getPersistedCount().set(++persistedCount);
if (persistedCount % TRANSACTION_LIMIT == 0) {
System.out.println("Thread " +
Thread.currentThread().getName() + " committing " + persistedCount);
session.currentTransaction().commit();
session.currentTransaction().begin();
}
}
The ThreadContext is just a singleton wrapper around some ThreadLocal's to
store Session and Availability objects. SessionFactory is an instance
variable.
On Tue, Nov 27, 2012 at 6:12 AM, Magnus Blåudd
<magnus.blaudd@stripped>wrote:
> Hi Graeme,
>
> Normally MySQL Cluster is able to cope with very high read and write load,
> but unfortunately something is not right. Do you think you could show us
> schema and the code you use?
>
> Quickly looked in SaveTest.java which shows how to use savePersistent()
> and savePersistentAll(), I assume you use the latter? Could serve as a
> starting point for a reproducable public test case.
>
>
> / Magnus
>
>
> On 11/26/2012 06:49 PM, Graeme Wallace wrote:
>
>> Ok tried reducing transaction size down to 4K rows. Works for 2 threads,
>> but its slower than doing one thread with larger transactions.
>>
>> If i up the threads to 4, i get the same error as before
>>
>> Nov 26, 2012 12:44:24 PM com.mysql.clusterj.tie.Utility throwError
>> SEVERE: Error in NdbJTie: returnCode -1, code 266, mysqlCode 146, status
>> 1, classification 10, message Time-out in NDB, probably caused by
>> deadlock .
>>
>>
>> Am I just going about this wrong ? I assumed that as i have a cluster of
>> 10 datanodes i would be able to have lots of threads and therefore lots
>> of writes going on at the same time. (Caveat - i read the marketing
>> material for 1 billion reads and writes a second :) )
>>
>> regards,
>>
>>
>>
>> Graeme
>>
>>
>>
>> On Mon, Nov 26, 2012 at 8:44 AM, Magnus Blåudd
> <magnus.blaudd@stripped
>> <mailto:magnus.blaudd@oracle.**com <magnus.blaudd@stripped>>>
> wrote:
>>
>> On Mon 26 Nov 2012 04:33:57 PM CET, Graeme Wallace wrote:
>>
>> I've tried playing around with transaction size, but the error
>> still
>> remains even if I make the transaction small ie 32K rows AND i
>> make all the
>> primary keys unique - so that each transaction should have a
>> unique set of
>> keys.
>>
>> G.
>>
>>
>> On Mon, Nov 26, 2012 at 8:30 AM, Magnus Blåudd
>> <magnus.blaudd@stripped
> <mailto:magnus.blaudd@oracle.**com<magnus.blaudd@stripped>
>> >>__wrote:
>>
>> On Mon 26 Nov 2012 04:09:42 PM CET, Graeme Wallace wrote:
>>
>> I'm trying to make my app multi-threaded and running into
>> problems.
>>
>> I have a Session local to each thread that is attempting
>> to write to the
>> db. I'm batching up many savePersistent() calls in a
>> transaction - but
>> when
>> the transaction commits I invariably end up with
>>
>> Nov 21, 2012 7:22:29 PM com.mysql.clusterj.tie.Utility
>> throwError
>> SEVERE: Error in NdbJTie: returnCode -1, code 266,
>> mysqlCode 146, status
>> 1,
>> classification 10, message Time-out in NDB, probably
>> caused by deadlock .
>>
>> For reading the docs, it looks like there shouldn't be
>> table locking going
>> on - so i dont understand what resource is being held by
>> one thread that
>> stops the others from being able to write at the same
>> time.
>>
>> Any clues would be most helpful,
>>
>> regards,
>>
>>
>> Graeme
>>
>>
>> Check out Matthew's reply in in this forum thread
>>
> http://forums.mysql.com/read.***__*php?25,505712,505757<http://forums.mysql.com/read.*__*php?25,505712,505757>
>>
> <http://forums.mysql.com/read.****php?25,505712,505757<http://forums.mysql.com/read.**php?25,505712,505757>
>>
> ><http:/**/__forums.mysql.com/read.php?**25,__505712,505757<http://forums.mysql.com/read.php?25,__505712,505757>
>>
> <http://forums.mysql.com/read.**php?25,505712,505757<http://forums.mysql.com/read.php?25,505712,505757>
>> >>
>>
>> "Due to the distributed nature of NDBCLUSTER there is no
>> automatic
>> deadlock detection mechanism within the NDBCLUSTER engine.
>> The only
>> indication that the cluster gives that there is a *possible*
>> deadlock is
>> the "Lock wait timeout exceeded". This error occurs when a
>> given lock
>> operation takes longer than
>> TransactionDeadlockDetectionTi**__**meout
>> (default 1200 ms.). If you have large transactions that lock
>> many rows at
>> once or if you have long running transactions these can
>> prevent this or
>> transactions from completing quickly. Try to avoid large or
>> long running
>> transactions by breaking them into smaller chunks. The
>> TransactionDeadlockDetectionTi**__**meout can also be reached
>> when a node is
>> overloaded but has not yet been ejected from the cluster for
>> missing
>> heartbeats longer than 4xHeartbeatIntervalDbDb (default 1500
>> ms. each). "
>>
>> / Magnus
>>
>>
>>
>>
>>
>> Give it a try with even smaller transactions if possible. Normally
>> better to use more threads with smaller transactions.
>>
>> / Magnus
>>
>>
>>
>>
>> --
>> Graeme Wallace
>> CTO
>> FareCompare.com
>> O: 972 588 1414
>> M: 214 681 9018
>>
>>
>>
>
--
Graeme Wallace
CTO
FareCompare.com
O: 972 588 1414
M: 214 681 9018
