Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: toruvinn wrote: 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. Now that we have SQL-level CONNECT privilege, I wonder just how much functionality would be lost if we got rid of the flat files and told people they had to use CONNECT to do any per-user or per-database access control. The main point I can see offhand is that password checking would have to be done a lot later in the startup sequence, with correspondingly more cycles wasted to reject bad passwords. Is this a TODO? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Now that we have SQL-level CONNECT privilege, I wonder just how much functionality would be lost if we got rid of the flat files and told people they had to use CONNECT to do any per-user or per-database access control. The main point I can see offhand is that password checking would have to be done a lot later in the startup sequence, with correspondingly more cycles wasted to reject bad passwords. Is this a TODO? Well, it's a TO-THINK-ABOUT anyway. I think the appropriate next step would not be to write code, but to do a detailed investigation of what would be gained or lost. I don't remember exactly what we do with the flat-file contents. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Tom Lane wrote: Well, it's a TO-THINK-ABOUT anyway. I think the appropriate next step would not be to write code, but to do a detailed investigation of what would be gained or lost. I don't remember exactly what we do with the flat-file contents. Maybe what we need is not to get rid of the flat files, but to speed them up. If we're worried about speed in the pg_authid flatfile, and come up with a solution to that problem, what will we do with the pg_database flatfile when it grows too large? We can't just get rid of it, because autovacuum needs to access it. -- 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
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: ... I don't remember exactly what we do with the flat-file contents. Maybe what we need is not to get rid of the flat files, but to speed them up. If we're worried about speed in the pg_authid flatfile, and come up with a solution to that problem, what will we do with the pg_database flatfile when it grows too large? We can't just get rid of it, because autovacuum needs to access it. Well, one of the components of the TODO would have to be to figure out a way to fix autovacuum to avoid that. Or we could consider getting rid of the pg_auth flatfile while keeping the pg_database one, which would fix the issue for role names anyway... but it's certainly an ugly compromise. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
I wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: ... I don't remember exactly what we do with the flat-file contents. Maybe what we need is not to get rid of the flat files, but to speed them up. If we're worried about speed in the pg_authid flatfile, and come up with a solution to that problem, what will we do with the pg_database flatfile when it grows too large? We can't just get rid of it, because autovacuum needs to access it. Well, one of the components of the TODO would have to be to figure out a way to fix autovacuum to avoid that. Actually, I had forgotten that we were using the pg_database flatfile for purposes other than authentication checks. In particular, we need it during backend startup to map from database name to database OID, without which it's impossible to locate the system catalogs for the target database. It's pretty hard to see a way around that one. We could grovel through pg_database itself, as indeed is done to rebuild the flatfile during system start. But that's certainly not going to be fast in cases where there are enough DBs to make the flatfile slow. So on third thought, Alvaro's right: the only real solution here is to adopt a more efficient representation of the flat files. Maybe some sort of simple hashtable arrangement would work. (Rendering them not so flat anymore...) regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Tom Lane wrote: Actually, I had forgotten that we were using the pg_database flatfile for purposes other than authentication checks. In particular, we need it during backend startup to map from database name to database OID, without which it's impossible to locate the system catalogs for the target database. It's pretty hard to see a way around that one. We could grovel through pg_database itself, as indeed is done to rebuild the flatfile during system start. But that's certainly not going to be fast in cases where there are enough DBs to make the flatfile slow. Also, IIRC flatfiles were introduced precisely to avoid having to read the catalogs manually. So on third thought, Alvaro's right: the only real solution here is to adopt a more efficient representation of the flat files. Maybe some sort of simple hashtable arrangement would work. (Rendering them not so flat anymore...) As long as there's a simple API, there should be no problem. (Except that it would be nice to be able to build the file incrementally ... If we have to write out a million lines each time a millionth user is created, there will still be a bottleneck at CREATE USER time.) -- 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
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: So on third thought, Alvaro's right: the only real solution here is to adopt a more efficient representation of the flat files. Maybe some sort of simple hashtable arrangement would work. (Rendering them not so flat anymore...) As long as there's a simple API, there should be no problem. (Except that it would be nice to be able to build the file incrementally ... If we have to write out a million lines each time a millionth user is created, there will still be a bottleneck at CREATE USER time.) If we allow the requirements to creep on this, we'll soon find ourselves either using or reinventing BDB for the flatfiles. Ick. [ thinks for awhile... ] In some sense this is a bootstrap problem: what does it take to get to the point of being able to read pg_database and its indexes? That is necessarily not dependent on the particular database we want to join. Maybe we could solve it by having the relcache write a global cache file containing only entries for the global tables, and load that before we have identified the database we want to join (after which, we'll load another cache file for the local entries). It would doubtless take some rearrangement of the backend startup sequence, but it doesn't seem obviously impossible. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Tom Lane wrote: In some sense this is a bootstrap problem: what does it take to get to the point of being able to read pg_database and its indexes? That is necessarily not dependent on the particular database we want to join. Maybe we could solve it by having the relcache write a global cache file containing only entries for the global tables, and load that before we have identified the database we want to join (after which, we'll load another cache file for the local entries). This sounds good, because autovacuum could probably use this too. -- 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
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: In some sense this is a bootstrap problem: what does it take to get to the point of being able to read pg_database and its indexes? That is necessarily not dependent on the particular database we want to join. Maybe we could solve it by having the relcache write a global cache file containing only entries for the global tables, and load that before we have identified the database we want to join (after which, we'll load another cache file for the local entries). This sounds good, because autovacuum could probably use this too. Maybe I'll look at this after commitfest is over. I haven't messed with the bootstrap sequence in awhile, but I used to remember how it worked ... As far as AV is concerned, taking this approach would likely mean turning the launcher into a full-fledged backend just like the workers. Do you see any problem with that? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Now that we have SQL-level CONNECT privilege, I wonder just how much functionality would be lost if we got rid of the flat files and told people they had to use CONNECT to do any per-user or per-database access control. The main point I can see offhand is that password checking would have to be done a lot later in the startup sequence, with correspondingly more cycles wasted to reject bad passwords. Is this a TODO? Well, it's a TO-THINK-ABOUT anyway. I think the appropriate next step would not be to write code, but to do a detailed investigation of what would be gained or lost. I don't remember exactly what we do with the flat-file contents. The flat file is the username/password sorted list. We load that info into the postmaster in an array that we can binary sort. I wonder how big the postmaster process address space was when handling 2 billion users: http://archives.postgresql.org/pgsql-bugs/2009-07/msg00176.php It seems just storing many users in the postmaster could be burdensome, but fixing that would be creating something like a database, which we already have. ;-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] 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.linnakan...@enterprisedb.com] Sent: Tuesday, July 14, 2009 6:09 PM To: Lauris Ulmanis Cc: pgsql-b...@postgresql.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.ulma...@mykoob.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 50 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
Alvaro Herrera alvhe...@commandprompt.com writes: toruvinn wrote: 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. Now that we have SQL-level CONNECT privilege, I wonder just how much functionality would be lost if we got rid of the flat files and told people they had to use CONNECT to do any per-user or per-database access control. The main point I can see offhand is that password checking would have to be done a lot later in the startup sequence, with correspondingly more cycles wasted to reject bad passwords. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
On 7/15/09, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: toruvinn wrote: 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. Now that we have SQL-level CONNECT privilege, I wonder just how much functionality would be lost if we got rid of the flat files and told people they had to use CONNECT to do any per-user or per-database access control. The main point I can see offhand is that password checking would have to be done a lot later in the startup sequence, with correspondingly more cycles wasted to reject bad passwords. From security standpoint, wasting more cycles on bad passwords is good, as it decreases the rate bruteforce password scanning can happen. And I cannot imagine a scenario where performance on invalid logins can be relevant.. -- marko -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
On Wed, Jul 15, 2009 at 11:10 AM, Marko Kreenmark...@gmail.com wrote: From security standpoint, wasting more cycles on bad passwords is good, as it decreases the rate bruteforce password scanning can happen. And I cannot imagine a scenario where performance on invalid logins can be relevant.. DoS attacks. The longer it takes to reject an invalid login, the fewer invalid login attempts it takes to DoS the server. -- - David T. Wilson david.t.wil...@gmail.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
David Wilson david.t.wil...@gmail.com writes: On Wed, Jul 15, 2009 at 11:10 AM, Marko Kreenmark...@gmail.com wrote: From security standpoint, wasting more cycles on bad passwords is good, as it decreases the rate bruteforce password scanning can happen. And I cannot imagine a scenario where performance on invalid logins can be relevant.. DoS attacks. The longer it takes to reject an invalid login, the fewer invalid login attempts it takes to DoS the server. Yeah, but even with the current setup, an attacker who can fire connection request packets at your postmaster port is not going to have any trouble DoS'ing the service. We expend quite a lot of cycles before getting to the password challenge already. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] [BUGS] BUG #4919: CREATE USER command slows down system performance
On 7/15/09, David Wilson david.t.wil...@gmail.com wrote: On Wed, Jul 15, 2009 at 11:10 AM, Marko Kreenmark...@gmail.com wrote: From security standpoint, wasting more cycles on bad passwords is good, as it decreases the rate bruteforce password scanning can happen. And I cannot imagine a scenario where performance on invalid logins can be relevant.. DoS attacks. The longer it takes to reject an invalid login, the fewer invalid login attempts it takes to DoS the server. No, this is not a good argument against it. Especially if you consider that DoS via hanging-connect or SSL is still there. Compared to minor DoS, the password-leakage is much worse danger. -- marko -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
