Forum .LRN Q&A: Degraded performance adding users to dot

1: Degraded performance adding users to dot

Posted by Jose Agustin Lopez Bueno on 01/14/04 12:09 PM

Hello, all!

I am adding users to OpenACS + Dot
with one web script (like bulk-users)
and I getting a strange problem.

The initial time to create one user is
about 200 ms. But every 500 insertions,
the creation time is degraded to 1 or more
seconds. This implies that for load the
full DB I need several days!

I am tracing the procs: and the problem
is in dorlrn::user_add function.
The proc auth::create_local_account for OpenACS
works Ok.

Any way to do a fast load of the users?

Any pointer will be welcome.

(The Database is Postgres 7.3.4
and the machine is a P4 con 1 GB de RAM.)

Regards,
Agustin

2: Re: Degraded performance adding users to dot (response to 1)

Posted by Dirk Gomez on 01/14/04 12:32 PM

Is only the database involved or do you touch external systems?

Can you be sure that PostgreSQL queries are the culprit? If yes, find out which queries have gotten slow. Developer-Support may be of immense help.

Then run explain plan on the slow query and post the results. It's quite likely that someone will be able to diagnose the problem then.

3: Re: Degraded performance adding users to dot (response to 2)

Posted by Tilmann Singer on 01/14/04 01:14 PM

The problem is that dotlrn::user_add triggers a huge number of other operations - depending on the number of other dotlrn packages installed, which are difficult to unwind. This is even more true when adding users to communities I believe, because then lots of personal data will be created for them for each dotlrn package installed.

Anyway, Dirks advice to install acs-developer-support is an excellent one. You might find one or two queries that account for the main part of the time the operation takes and find a way to optimize or postpone them.

4: Re: Degraded performance adding users to dot (response to 1)

Posted by Malte Sussdorff on 01/14/04 02:35 PM

Though it is not going to help you, the performance degrade is what is to be expected. We improved the adding of users into the OpenACS system lately (if I'm not mistaken), but due to the many issues with .LRN dependencies you are most likely going to wait for a couple of days before you finish with your 80000 users.

When upgrading from ACES to .LRN 1.0 it took us two days to import 80.000 users, but Tilmann is an expert in this as he wrote the import scripts.

All in all, install developer support and trace which queries take up the most time. If you are not sure what to do about them, post them here, so we might take a stab at this in the long run (as you will not be the only organization trying to mass import users).

5: Re: Degraded performance adding users to dot (response to 1)

Posted by Ola Hansson on 01/14/04 04:28 PM

Make sure you "vacuum --full --analyze yourdb" before you start the migration of users. If you haven't done that on your db, especially after installing a lot of packages and inserting many rows, the DB becomes considerably slower than just after being vacuumed. If it is possible to pause the migration and vacuum every 500 users or so that may help speed the process up somewhat ...

Neither would it surprize me if you'd get a big boost in the migration performance if you were able to locate and temporarily drop some relevant indices just to recreate them when the process is over, of course. (An index always causes a slow-down in insertion performance on a table althogh it in most cases drastically decreases the time it takes to select rows from it.)

The question of course is which index to drop and which to leave all alone :-)

Just my $.02

/Ola

6: Re: Degraded performance adding users to dot (response to 1)

Posted by Denis Roy on 01/14/04 08:23 PM

I was to migrate 100K+ users into OACS/.LRN (Oracle) and I noticed the very same when I migrated users into dotlrn based on MIT's/Tilmann's scripts. I haven't seen such a huge degration in performance, though. When I started the migration process, it would create about 5 users per second which subsequently dropped down to about 1.5 users towards the end.

I have traced the problem a bit but not too much since in the end it was still ok for us. The thing is that a new portal is created for every new dotlrn user and all dotlrn packages add a portlet to the new user's workspace. So the more dotlrn packages you have installed, the longer it takes to create a new user.

The first step for us was to limit the users who would become dotlrn users since only about 20,000 users needed a personal workspace. This helped tremendously since adding a new user to OACS is much faster than adding a user to dotlrn.