Hi,
I am trying to iterate over a big table about 2.5Gb and 4Mil records,
I am using multithreading too
The problem is that the application starts out very good at about 40Mb/sec and than slowly deteriorates to below 1Mb/sec in a almost linear fashion
I have tried almost everything and I can't fix this, please advise
The run method of a single thread, I am using from 2 - 10 threads same behavior and from 100 to 10000 objects per fetch (this works out diferently it's worse and better but never solves the problem)
Code:
public void run() {
// final SessionFactory sf = getSessionFactory();
final Session session = getSession(true);
final Transaction tx = session.getTransaction();
// sf.getStatistics().setStatisticsEnabled(false);
int e = 0;
int i = 0;
try {
tx.begin();
final Query qry = session.createQuery("from Hit as hit");
qry.setFlushMode(FlushMode.COMMIT);
qry.setCacheMode(CacheMode.IGNORE);
qry.setCacheable(false);
qry.setMaxResults(maxResults);
qry.setFirstResult(offset);
final ScrollableResults sc = qry.scroll(ScrollMode.FORWARD_ONLY);
while (sc.next()) {
i++;
final Hit h = (Hit) sc.get(0);
final Set<Item> itms = h.getItems();
final Set<String> uniqueTH = new TreeSet<String>();
if (itms == null) {
e++;
continue;
}
for (final Item kwid : itms) {
final Set<String> th = itemMap.get(kwid.getWebName());
if (th != null) {
for (final String thid : th) {
uniqueTH.add(thid);
}
} else {
e++;
continue;
}
}
for (final String name : uniqueTH) {
final String itemId = themaIds.get(name);
if (itemId == null) {
logger.warn("No unique " + name);
}
if (itemId != null) {
final String itemIdS = (String) itemId;
h.setItem(itemIdS);
if (!onlyTest)
session.saveOrUpdate(h);
} else {
// logger.warn("Id not found for '" + name + "'");
}
}
session.evict(h);
}
tx.commit();
} catch (final Exception ex) {
if (tx != null) {
tx.rollback();
}
logger.error(LoggerHelper.stackTraceToString(ex));
} finally {
app.decrement();
if (session != null && session.isConnected()) {
// session.disconnect();
releaseSession(session);
}
}
}
Table
Code:
CREATE TABLE `hits` (
`id` bigint(20) NOT NULL,
`source` varchar(255) COLLATE utf8_bin NOT NULL,
`BOOKNUMBER` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`BUSINESSNAME1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`BUSINESSNAME2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`BUSINESSNAME3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`COUPONURL` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`CREATED` datetime DEFAULT NULL,
`DISTRICT` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`ELECTRONICUSAGEALLOWED` bit(1) DEFAULT NULL,
`EMAIL` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`EMAILDISPLAYED` bit(1) DEFAULT NULL,
`EXTRAFIELD1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FAX` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FIRSTNAME1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FIRSTNAME2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FIRSTNAME3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FULLBUSINESSNAMES` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FULLLOCATION` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`FULLNAMES` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`GRAFIC` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HIERARCHYROOTID` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HIERARCHYSTEP` smallint(6) DEFAULT NULL,
`HISTORICNAMEAFFIX1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HISTORICNAMEAFFIX2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HISTORICNAMEAFFIX3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HOMEPAGE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HOUSENUMBER` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`HOUSENUMBERAFFIX` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`INVERSESEARCHALLOWED` bit(1) DEFAULT NULL,
`KEYWORD` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`KGS` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LASTMODIFIED` datetime DEFAULT NULL,
`LASTNAME1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LASTNAME2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LASTNAME3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LATITUDE` double DEFAULT NULL,
`LOGO` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`LONGITUDE` double DEFAULT NULL,
`MISCNAMEAFFIX1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`MISCNAMEAFFIX2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`MISCNAMEAFFIX3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`MOBILE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TEL` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`PHONENUMBERDISPLAYED` bit(1) DEFAULT NULL,
`POSTBOXNUMBER` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`POSTALCODE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`RANKING` int(11) DEFAULT NULL,
`SEQUENCENUMBER` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`SHOPURL` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`STATUS` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`STREET` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`SUBSCRIBERTYPE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`SURENAMEPREFIX1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`SURENAMEPREFIX2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`SURENAMEPREFIX3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TEXT` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TITLE1` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TITLE2` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TITLE3` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`TOWN` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`VANITYPUBLISHALLOWED` bit(1) DEFAULT NULL,
`AREACODE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`searchName` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`,`source`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$
and
Code:
CREATE TABLE `hits_items` (
`hits_id` bigint(20) NOT NULL,
`hits_source` varchar(255) COLLATE utf8_bin NOT NULL,
`items_id` bigint(20) NOT NULL,
PRIMARY KEY (`hits_id`,`hits_source`,`items_id`),
KEY `FK8B76BA01F7A53E26` (`hits_id`,`hits_source`),
KEY `FK8B76BA01FEDAAECE` (`items_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$
and
Code:
CREATE TABLE `items` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`itemId` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`type` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`webName` varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10077 DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$
Login
FAQ
Search