[PERFORM] Re: [BUGS] BUG #4919: CREATE USER command slows down system performance
On Wed, 15 Jul 2009 16:02:09 +0200, Alvaro Herrera alvhe...@commandprompt.com wrote: My bet is on the pg_auth flat file. I doubt we have ever tested the behavior of that code with 1 billion users ... I've noticed this behaviour some time ago, on a cluster with 50k+ roles (not sure about the number now). Restoring the backup took a lot of time, especially when the users file grew significantly and each additional user caused PG to rewrite the whole file. I never bothered to report this, as it's not like the users are (re)created every day, it was just a one-time run (== some extra time for another coffee during restore ;-)). I was always wondering, though, why PostgreSQL uses this approach and not its catalogs. Regards, -- ru -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: [BUGS] BUG #4919: CREATE USER command slows down system performance
Lauris Ulmanis wrote: 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? My bet is on the pg_auth flat file. I doubt we have ever tested the behavior of that code with 1 billion users ... Do you really need 1 billion users? Are you planning on giving accounts to every human being in the planet or what? I mean, what's the point of this test? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Re: [BUGS] BUG #4919: CREATE USER command slows down system performance
toruvinn wrote: On Wed, 15 Jul 2009 16:02:09 +0200, Alvaro Herrera alvhe...@commandprompt.com wrote: My bet is on the pg_auth flat file. I doubt we have ever tested the behavior of that code with 1 billion users ... I was always wondering, though, why PostgreSQL uses this approach and not its catalogs. It does use the catalog for most things. THe flatfile is used for the situations where the catalogs are not yet ready to be read. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
