"user_" table update performance

[Eugene Rachitskiy, geändert vor 15 Jahren.]

Hi Everybody,

I'm running Liferay 5.5-5.1.1 with MySQL's InnoDB engine and experiencing a slight performance issue. After logging slow queries, it appears that updates of the User_ table sometimes take quite a while to execute - often over 20 seconds. Specifically, the first line of output from mysqldumpslow is the following:

Count: 466  Time=22.87s (10659s)  Lock=0.00s (0s)  Rows=0.0 (0), liferay[liferay]@localhost
 update User_ set uuid_='S', companyId=N, createDate='S', modifiedDate='S', defaultUser=N, contactId=N, password_='S', 
passwordEncrypted=N, passwordReset=N, passwordModifiedDate='S', graceLoginCount=N, screenName='S', emailAddress='S', 
openId='S', portraitId=N, languageId='S', timeZoneId='S', greeting='S', comments='S', loginDate='S', loginIP='S', lastLoginDate='S',
 lastLoginIP='S', lastFailedLoginDate=null, failedLoginAttempts=N, lockout=N, lockoutDate=null, agreedToTermsOfUse=N, active_=N where userId=N

My User_ table is populated with about 1500 users and usually updates fairly quickly. Nevertheless, as soon as I place the slightest of loads on the server (maybe 3 concurrent users attempting to log in) I start seeing this very slow update behavior.


I know this isn't the place to ask MySQL tuning questions, so my question to the Liferay community is this: has anyone experienced similar issues with the user_ table? I saw that the table has quite a few indicies; has anyone attempted to play around with these in an attempt to trade some query for update performance?


Cheers,
Eugene

[Jason E Shao, geändert vor 15 Jahren.]

Following up on Eugene's note - it looks like the above issues was related to some LDAP performance issues (reported in LPS-704)

What appears to be happening is the DB transaction for the User_ update appears to open the transaction, perform some LDAP interactions, and then commit the transaction. This sequence can then cause InnoDB tables in MySQL to lock.

We were seeing situations where LDAP queries & sync would take up to 15-20 seconds, which made this delay noticeable.

Has anyone else seen this issue? Any thoughts on addressing it?

[Fuad Efendi, geändert vor 15 Jahren.]

Using Liferay with InnoDB would be cool if-and-only-if Liferay community rewrites all methods with "count" prefix... SELECT COUNT(*) is extremely slow in InnoDB; although InnoDB provides best performance in heavily concurrent write-mostly environment.

Slow "UPDATE ... WHERE userId=N" statement could be caused by previously executed much slower statement.

InnoDB does not lock tables btw...

[Jason E Shao, geändert vor 14 Jahren.]

Fuad Efendi:

Using Liferay with InnoDB would be cool if-and-only-if Liferay community rewrites all methods with "count" prefix... SELECT COUNT(*) is extremely slow in InnoDB; although InnoDB provides best performance in heavily concurrent write-mostly environment.

Slow "UPDATE ... WHERE userId=N" statement could be caused by previously executed much slower statement.

InnoDB does not lock tables btw...

Well, InnoDB is *designed* not to lock tables, but there are numerous reports across the net that it's still possible to get into situations where tables lock.

One thing we could try doing is switching to the MySQLInnoDBDialect -- though I'm not that familiar with the characteristics of that vs. the generic MySQLDialect for Hibernate -- and of course that doesn't address all concerns.

Out of curiousity -- is anyone running Liferay *not* against InnoDB? Especially against MyISAM or a non-transactional table store?

[Fuad Efendi, geändert vor 14 Jahren.]

Jason E Shao:

Out of curiousity -- is anyone running Liferay *not* against InnoDB? Especially against MyISAM or a non-transactional table store?

My biggest concern is performance of SELECT COUNT(*) -type queries with InnoDB.

[indent]About transactional support: I can't believe Liferay uses more than just single-table updates transactionally, especially with xxxServiceUtil classes designed to be called in a separate transaction. Can anyone confirm that at least xxxServiceUtil are transactional wrappers around possibly multitable updates?[/indent]


InnoDB is the best for concurrent updates. And it is like index-organized structure in Oracle, it also uses structure similar to block-extent-segment in Oracle, and transaction log segment. And it is faster to update few bytes in 8kb block stored in memory cache than to lock whole table in order to rewrite hardware-predefined 8kb (or even 64kb in case of some RAID) block of data.

For concurrent updates only! Popular site slashdot.com was forced to rewrite huge amount of code (SELECT COUNT(*) -related) in order to migrate from MyISAM to InnoDB; they were enforced to do that due to huge multiuser concurrent environment on their forums.

[indent]Where Liferay uses SELECT COUNT(*)? Almost everywhere.[/indent]

[Fuad Efendi, geändert vor 14 Jahren.]

Jason E Shao:

Well, InnoDB is *designed* not to lock tables, but there are numerous reports across the net that it's still possible to get into situations where tables lock.

I am very well familiar with such stupid situations; it is due to bad design of an application.

Just a simplest sample: create two tables, one-to-many, with FOREIGN KEY constraint, then issue UPDATE statement to parent table, and put additionally SET ... perentId=? ... into clause. It will lock whole child table - you are going to update theoretically immutable key! Fortunately Liferay uses surrogate (without business meaning) primary keys, fortunately(???) it doen't use FOREIGN KEY, and hopefully Hibernate does not generate such statements (are you sure?).

But... Why Liferay does not have any FOREIGN KEY in schema definition? Instead, Liferay provides com.liferay.portal.upgrade.*** processes (we even have verify.frequency on properties, just in case if we need repair integrity) - and we are talking about transactions now...


P.S.
Just sample of indexes (it slows down performance of updates):

reate index IX_3C865EE5 on MBMessage (categoryId);
create index IX_138C7F1E on MBMessage (categoryId, threadId);

...
- second index makes first one absolutely unnecessary.

[Jason E Shao, geändert vor 14 Jahren.]

Fuad Efendi:

But... Why Liferay does not have any FOREIGN KEY in schema definition? Instead, Liferay provides com.liferay.portal.upgrade.*** processes (we even have verify.frequency on properties, just in case if we need repair integrity) - and we are talking about transactions now...

This very much surprised me too - I assumed our schema had been created incorrectly. To get around the initial performance issues (and for various other reasons related to ensuring we don't keep NPPI) we disabled password tracking in our environment, which got us over the initial hump.

Anyone know where the right spot to talk about the DB schema/design is?

[Brian Chan, geändert vor 14 Jahren.]

Hey Fuad,

We've known about the issue of duplicate indexes. We're looking at fixing the ServiceBuilder to not generate these additional indexes if they're not needed. Thanks for pointing that out.