Yes, it seems problem in pg_auth flat file. We are using db users to manage access rights to db tables and data, that way we have two layer security - application and DB. Each system user has it's own group role and groups have different access levels. So we cannot use one login role for all users. Because of using group roles we need to use pgbouncer connection pooling for seperate users sessions to pool server and use this connection till user closes session. We cannot user pgbouncer pooling and role managment if in postgres is one login role for all users. I hope there is some solution how to use login roles for each users and role groups with grants to system objects up to ~500 000 users. For example, Oracle database allows to create users more then 500 000 without performance problems in creation process. I suppose it is because oracle don't use flat file to store all users. -----Original Message----- From: Alvaro Herrera [mailto:alvherre@xxxxxxxxxxxxxxxxx] Sent: Wednesday, July 15, 2009 5:02 PM To: Lauris Ulmanis Cc: 'Heikki Linnakangas'; pgsql-bugs@xxxxxxxxxxxxxx; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: [BUGS] BUG #4919: CREATE USER command slows down systemperformance 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 Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
