Re: User concurrency thresholding: where do I look?

От: Alvaro Herrera
Тема: Re: User concurrency thresholding: where do I look?
Дата: ,
Msg-id: 20070719154912.GG6538@alvh.no-ip.org
(см: обсуждение, исходный текст)
Ответ на: User concurrency thresholding: where do I look?  (Josh Berkus)
Ответы: Re: User concurrency thresholding: where do I look?  (Josh Berkus)
Список: pgsql-performance

Скрыть дерево обсуждения

User concurrency thresholding: where do I look?  (Josh Berkus, )
 Re: User concurrency thresholding: where do I look?  ("Joshua D. Drake", )
 Re: User concurrency thresholding: where do I look?  (Alvaro Herrera, )
  Re: User concurrency thresholding: where do I look?  (Josh Berkus, )
   Re: User concurrency thresholding: where do I look?  (Tom Lane, )
    Re: User concurrency thresholding: where do I look?  (Alvaro Herrera, )
     Re: User concurrency thresholding: where do I look?  (Tom Lane, )
      Re: User concurrency thresholding: where do I look?  (Josh Berkus, )
       Re: User concurrency thresholding: where do I look?  (Tom Lane, )
        Re: User concurrency thresholding: where do I look?  (Josh Berkus, )
         Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
          Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
          Re: User concurrency thresholding: where do I look?  (Tom Lane, )
           Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
            Re: User concurrency thresholding: where do I look?  (Tom Lane, )
             Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
              Re: User concurrency thresholding: where do I look?  (Tom Lane, )
               Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                 Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                 Re: User concurrency thresholding: where do I look?  (David Boreham, )
                  Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                 Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                  Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                   Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                    Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                     Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                      Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                       Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                      Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                       Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                        Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                        Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                         Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                          Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                           Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                            Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                             Re: User concurrency thresholding: where do I look?  (Tom Lane, )
                              Re: User concurrency thresholding: where do I look?  (Robert Lor, )
                             Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                              Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                               Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                                Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                                 Re: User concurrency thresholding: where do I look?  (Alvaro Herrera, )
                                  Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                                 Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
                                  Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                                  Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                                   Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                                    Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                                  Re: User concurrency thresholding: where do I look?  (Josh Berkus, )
                                   Re: User concurrency thresholding: where do I look?  ("Jignesh K. Shah", )
                              CLOG Patch  ("Jignesh K. Shah", )
                               Re: CLOG Patch  ("Simon Riggs", )
                                Re: CLOG Patch  ("Jignesh K. Shah", )
                                 Re: CLOG Patch  ("Simon Riggs", )
                           Re: User concurrency thresholding: where do I look?  ("Simon Riggs", )
       Re: User concurrency thresholding: where do I look?  (Greg Smith, )
        Re: User concurrency thresholding: where do I look?  (Josh Berkus, )
     Re: User concurrency thresholding: where do I look?  ("Joshua D. Drake", )
    Re: User concurrency thresholding: where do I look?  (Gregory Stark, )
 Re: User concurrency thresholding: where do I look?  (Greg Smith, )

Josh Berkus wrote:
> Folks,
>
> I've run into this a number of times with various PostgreSQL users, so we
> tested it at Sun.  What seems to be happening is that at some specific number
> of connections average throughput drops 30% and response time quadruples or
> worse.  The amount seems to vary per machine; I've seen it as variously 95,
> 1050, 1700 or 2800 connections.  Tinkering with postgresql.conf parameters
> doesn't seem to affect this threshold.
>
> As an example of this behavior:
>
> Users    Txn/User  Resp. Time
> 50    105.38    0.01
> 100    113.05    0.01
> 150    114.05    0.01
> 200    113.51    0.01
> 250    113.38    0.01
> 300    112.14    0.01
> 350    112.26    0.01
> 400    111.43    0.01
> 450    110.72    0.01
> 500    110.44    0.01
> 550    109.36    0.01
> 600    107.01    0.02
> 650    105.71    0.02
> 700    106.95    0.02
> 750    107.69    0.02
> 800    106.78    0.02
> 850    108.59    0.02
> 900    106.03    0.02
> 950    106.13    0.02
> 1000    64.58    0.15
> 1050    52.32    0.23
> 1100    49.79    0.25
>
> Tinkering with shared_buffers has had no effect on this threholding (the above
> was with 3gb to 6gb of shared_buffers).   Any ideas on where we should look
> for the source of the bottleneck?

Have you messed with max_connections and/or max_locks_per_transaction
while testing this?  The lock table is sized to max_locks_per_xact times
max_connections, and shared memory hash tables get slower when they are
full.  Of course, the saturation point would depend on the avg number of
locks acquired per user, which would explain why you are seeing a lower
number for some users and higher for others (simpler/more complex
queries).

This is just a guess though.  No profiling or measuring at all, really.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"How amazing is that? I call it a night and come back to find that a bug has
been identified and patched while I sleep."                (Robert Davidson)
               http://archives.postgresql.org/pgsql-sql/2006-03/msg00378.php


В списке pgsql-performance по дате сообщения:

От: Greg Smith
Дата:
Сообщение: Re: User concurrency thresholding: where do I look?
От: "Carlos H. Reimer"
Дата:
Сообщение: Improving select peformance