Josh Berkus <j...@agliodbs.com> wrote: > We can go back to Kevin's originally proposed simple feature: > just allowing the DBA to limit the number of concurrently > executing queries by role and overall. Well, that's more sophisticated than what I proposed, but it's an interesting twist on it. > This would consist of two parameters, > max_concurrent_statements and max_statement_wait; the second would > say how long the connection would wait for a query slot before > giving up and returning an error. The timeout is also an embellishment to what I proposed, but another interesting idea. > even this solution has two thorny issues to be solved: > > a) the "waiting query storm" issue mentioned above I fleshed out the idea a bit more on the thread titled "Built-in connection pool", since this would effectively function in a very similar way to a connection pool. If you look at that proposal, at most one transaction would be released to execution when one transaction completed. I'm not seeing anything resembling a "storm" in that, so you must be envisioning something rather different. Care to clarify? > b) pending queries are sitting on idle connections, which could > easily block higher-priority queries, so managing max_connections > per role would become much more complex. That is a good point. The biggest difference between the functionality of the proposal on the other thread and the connection pool built in to our application framework is that the latter has a prioritized FIFO queue, with ten levels of priority. A small query which is run as you tab between controls in a GUI window runs at a much higher priority than a query which fills a list with a large number of rows. This involves both connections reserved for higher priorities and having higher priority transactions "jump ahead" of lower priority transactions. This helps the user perception of performance in the fat-client GUI applications. I suppose if we had limits by role, we could approach this level of functionality within PostgreSQL. On the other hand, our web apps run everything at the same priority, so there would effectively be *no* performance difference between what I proposed we build in to PostgreSQL and what our shop currently puts in front of PostgreSQL for a connection pool. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
