Re: BUG #4919: CREATE USER command slows down system performance

Hello again!

I did test on my local test server

I created up 500 000 users in function loop very quickly - within 48
seconds. I did again this script reaching up to 1 billion users - results
was the same - 48 seconds. It is very quickly.

But problem seems is with transaction preparation because if in database is
1 billion users and I want to create 1 new - it will take 4 seconds!

After that I generated up to 2 billion users in this server (generation
process took just 1.44 minutes of times - again quickly).

And did 1 user creation again - now it took 9 seconds of time!

What is a reason of this slowness? Is there a workaround or solution how to
avoid it?

-----Original Message-----
From: Heikki Linnakangas [mailto:heikki(dot)linnakangas(at)enterprisedb(dot)com]
Sent: Tuesday, July 14, 2009 6:09 PM
To: Lauris Ulmanis
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4919: CREATE USER command slows down system
performance

Lauris Ulmanis wrote:
> The following bug has been logged online:
>
> Bug reference: 4919
> Logged by: Lauris Ulmanis
> Email address: lauris(dot)ulmanis(at)mykoob(dot)com
> PostgreSQL version: 8.3.7, 8.4.0
> Operating system: Any
> Description: CREATE USER command slows down system performance
> Details:
>
> When user count in Postgres database reaches up to 500 000 - database
> command of creating users 'CREATE USER' slows down to 5-10 seconds per
user.

I don't see such slowdown here, by just issuing 500000 CREATE USER
commands in a loop.

> For each of user can be associated up to 10 roles with grants to system
> objects.

That may be related..

Can you produce a repeatable test case?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com