Hi, On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote: > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of some > basic SET statements. > > I created about 1600 roles and use that setup for a multi tenancy > application: Hm. How often do you drop/create these roles? How many other roles/groups is one role a member of? > My application solely uses the role 'admin' to connect to the database. > When performing sql statements for a specific tenant (e.g. tenant1337), a > connection with user 'admin' is established and the following commands are > executed: > > SET ROLE 1337; > SET search_path = tenant1337; > > Then the application uses that connection to perform various statements in > the database. Just to be sure: You realize bad application code could escape from that, right? > My application is a web service that approximately executes some hundred > statements per second. > > I set "log_min_duration_statement = 200ms" and I get about 2k to 4k lines > per day with statements like "SET ROLE"", "SET search_path ..." and "RESET > ROLE": > > --snip-- > 2017-11-07 09:44:30 CET [27760]: [3-1] user=admin,db=mydb LOG: duration: > 901.591 ms execute <unnamed>: SET ROLE "tenant762" > 2017-11-07 09:44:30 CET [27659]: [4-1] user=admin,db=mydb LOG: duration: > 1803.971 ms execute <unnamed>: SET ROLE "tenant392" That is weird. > Besides those peaks in statement duration, my application performs (i.e. > has acceptable response times) most of the time. > > Is there anything I can do to improve performance here? > Any help is greatly appreciated! Can you manually reproduce the problem? What times do you get if you manually run the statement? Greetings, Andres Freund -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
