Re: [GENERAL] Debugging a backend stuck consuming CPU
On Thu, May 19, 2016 at 05:52:26PM -0400, Tom Lane wrote: > "k...@rice.edu" writes: > > The stack trace just appeared to be what I would expect while a 'DISCARD > > ALL' > > command was being run: > > > #0 0x0073bc7c in MemoryContextSetParent () > > #1 0x0073bde3 in MemoryContextDelete () > > #2 0x0054e3a9 in DropAllPreparedStatements () > > #3 0x005365f3 in DiscardCommand () > > Hmm, what it seems from these traces is that you've got a whole heck of > a lot of prepared statements. > > > The backend does have a very large memory footprint (12GB). > > Um. > > The most likely explanation is that you are hitting O(N^2) behavior as > a consequence of MemoryContextSetParent being O(N) in the number of > sibling contexts of the context to be deleted. We fixed that for 9.6 > (commit 25c539233044c235e97fd7c9dc600fb5f08fe065) but there's no easy > solution in older branches, short of not using so many prepared > statements. I'm a bit surprised that you could have gotten up to 12GB > worth of prepared statements in an application that sends DISCARD ALL > periodically. > > regards, tom lane > Hi, The DISCARD ALL is only sent by pgbouncer at the end of the processing. The actual process builds up a cache to be used later whose size is proportional to the number of items. The initial run is large, but the regular runs are much smaller and cleanup quickly. I was more concerned with incorrect behavior leading to DB corruption. Thank you for your suggestions and assistance. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debugging a backend stuck consuming CPU
On Thu, May 19, 2016 at 09:58:45AM -0400, Tom Lane wrote: > "k...@rice.edu" writes: > > I am investigating a problem with a backend that appears to be stuck > > and spinning while performing a "DISCARD ALL" command. The system is > > running an older release 9.2.2. > > You do realize that the current release in that series is 9.2.17. > > > Are there any bugs that could be causing this behavior? > > Known bugs are summarized here: > http://www.postgresql.org/docs/9.2/static/release.html > > > How can I tell what the process is actually doing? > > Getting a stack trace with gdb might be informative: > https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend > > regards, tom lane > Hi, The stack trace just appeared to be what I would expect while a 'DISCARD ALL' command was being run: Continuing. Program received signal SIGINT, Interrupt. 0x0073bc7c in MemoryContextSetParent () #0 0x0073bc7c in MemoryContextSetParent () #1 0x0073bde3 in MemoryContextDelete () #2 0x0054e3a9 in DropAllPreparedStatements () #3 0x005365f3 in DiscardCommand () #4 0x006582c7 in ?? () #5 0x006592bd in ?? () #6 0x00659a42 in PortalRun () #7 0x0065603d in ?? () #8 0x00656ed0 in PostgresMain () #9 0x00613b91 in ?? () #10 0x006167fc in PostmasterMain () #11 0x005b5290 in main () Continuing. Program received signal SIGINT, Interrupt. 0x0073bc7c in MemoryContextSetParent () #0 0x0073bc7c in MemoryContextSetParent () #1 0x0073bde3 in MemoryContextDelete () #2 0x0054e3a9 in DropAllPreparedStatements () #3 0x005365f3 in DiscardCommand () #4 0x006582c7 in ?? () #5 0x006592bd in ?? () #6 0x00659a42 in PortalRun () #7 0x0065603d in ?? () #8 0x00656ed0 in PostgresMain () #9 0x00613b91 in ?? () #10 0x006167fc in PostmasterMain () #11 0x005b5290 in main () Continuing. Program received signal SIGINT, Interrupt. 0x0073bc7c in MemoryContextSetParent () #0 0x0073bc7c in MemoryContextSetParent () #1 0x0073bde3 in MemoryContextDelete () #2 0x0070e7df in DropCachedPlan () #3 0x0054e3a9 in DropAllPreparedStatements () #4 0x005365f3 in DiscardCommand () #5 0x006582c7 in ?? () #6 0x006592bd in ?? () #7 0x00659a42 in PortalRun () #8 0x0065603d in ?? () #9 0x00656ed0 in PostgresMain () #10 0x00613b91 in ?? () #11 0x006167fc in PostmasterMain () #12 0x005b5290 in main () Continuing. Program received signal SIGINT, Interrupt. 0x0073bc7c in MemoryContextSetParent () #0 0x0073bc7c in MemoryContextSetParent () #1 0x0073bde3 in MemoryContextDelete () #2 0x0054e3a9 in DropAllPreparedStatements () #3 0x005365f3 in DiscardCommand () #4 0x006582c7 in ?? () #5 0x006592bd in ?? () #6 0x00659a42 in PortalRun () #7 0x0065603d in ?? () #8 0x00656ed0 in PostgresMain () #9 0x00613b91 in ?? () #10 0x006167fc in PostmasterMain () #11 0x005b5290 in main () Continuing. Program received signal SIGINT, Interrupt. 0x0070e7ff in DropCachedPlan () #0 0x0070e7ff in DropCachedPlan () #1 0x0054e3a9 in DropAllPreparedStatements () #2 0x005365f3 in DiscardCommand () #3 0x006582c7 in ?? () #4 0x006592bd in ?? () #5 0x00659a42 in PortalRun () #6 0x0065603d in ?? () #7 0x00656ed0 in PostgresMain () #8 0x00613b91 in ?? () #9 0x006167fc in PostmasterMain () #10 0x005b5290 in main () Detaching from program: /usr/pgsql-9.2/bin/postgres, process 38604 Undefined command: "exit". Try "help". Continuing. Program received signal SIGINT, Interrupt. 0x0070e7ff in DropCachedPlan () #0 0x0070e7ff in DropCachedPlan () #1 0x0054e3a9 in DropAllPreparedStatements () #2 0x005365f3 in DiscardCommand () #3 0x006582c7 in ?? () #4 0x006592bd in ?? () #5 0x00659a42 in PortalRun () #6 0x0065603d in ?? () #7 0x00656ed0 in PostgresMain () #8 0x00613b91 in ?? () #9 0x006167fc in PostmasterMain () #10 0x005b5290 in main () Continuing. Program received signal SIGINT, Interrupt. 0x0070e7ff in DropCachedPlan () #0 0x0070e7ff in DropCachedPlan () #1 0x0054e3a9 in DropAllPreparedStatements () #2 0x005365f3 in DiscardCommand () #3 0x006582c7 in ?? () #4 0x006592bd in ?? () #5 0x00659a42 in PortalRun () #6 0x0065603d in ?? () #7 0x00656ed0 in PostgresMain () #8 0x00613b91 in ?? () #9 0x
Re: [GENERAL] Debugging a backend stuck consuming CPU
On Thu, May 19, 2016 at 09:58:45AM -0400, Tom Lane wrote: > "k...@rice.edu" writes: > > I am investigating a problem with a backend that appears to be stuck > > and spinning while performing a "DISCARD ALL" command. The system is > > running an older release 9.2.2. > > You do realize that the current release in that series is 9.2.17. > > > Are there any bugs that could be causing this behavior? > > Known bugs are summarized here: > http://www.postgresql.org/docs/9.2/static/release.html > > > How can I tell what the process is actually doing? > > Getting a stack trace with gdb might be informative: > https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend > > regards, tom lane > Hi, We do have an upgrade scheduled. I will get a stack trace and see what information that provides. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Debugging a backend stuck consuming CPU
Hi PostgreSQL community, I am investigating a problem with a backend that appears to be stuck and spinning while performing a "DISCARD ALL" command. The system is running an older release 9.2.2. Are there any bugs that could be causing this behavior? How can I tell what the process is actually doing? It does not respond to either pg_cancel_backend() or to pg_terminate_backend() so it is acting like it is not receiving the signals. Any ideas on how to debug this would be appriciated. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
On Thu, Jun 18, 2015 at 07:19:13PM +, Sheena, Prabhjot wrote: > Hi Ken/ Will > > I have checked the ulimit value and we are nowhere hitting the max 4096 > that we have currently set. Is there any other explanation why we should be > thinking of bumping it to like ulimit -n 5 ( Add ulimit -n 5 to the > start of whatever you use to start pgbouncer (init script, etc..)) even > though we are not reaching 4096 max value > > Regards > Prabhjot Singh > Hi, Try attaching to the pgbouncer with strace and see if you are getting any particular errors. Do you have a /etc/security/limits.d directory? And if so, what is in it? We found a nice default ulimit of 1024 for all non-root users. :( Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
On Thu, Jun 18, 2015 at 05:41:01PM +, Sheena, Prabhjot wrote: > Here is the output of OS limits > > postgres@symds-pg:~ $ ulimit -a > > core file size (blocks, -c) 0 > data seg size (kbytes, -d) unlimited > scheduling priority (-e) 0 > file size (blocks, -f) unlimited > pending signals (-i) 790527 > max locked memory (kbytes, -l) 32 > max memory size (kbytes, -m) unlimited > open files (-n) 4096 > pipe size(512 bytes, -p) 8 > POSIX message queues (bytes, -q) 819200 > real-time priority (-r) 0 > stack size (kbytes, -s) 10240 > cpu time (seconds, -t) unlimited > max user processes (-u) 16384 > virtual memory (kbytes, -v) unlimited > file locks (-x) unlimited > > > Thanks > Prabhjot > I would bump your open files as was suggested in your pgbouncer start script. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] PGBOUNCER ISSUE PLEASE HELP(Slowing down the site)
On Thu, Jun 18, 2015 at 05:09:10PM +, Sheena, Prabhjot wrote: > Guys > I have an issue going on with PGBOUNCER which is slowing down the > site > > PGBOUNCER VERSION: pgbouncer-1.5.4-2.el6 (Hosted on separate machine) (16 > cpu) 98GB RAM > DATABASE VERION: postgresql 9.3 > > When the total client connections to pgbouncer are close to 1000, site > application works fine but when the total client connections crosses 1150 > site application starts showing slowness. > > Here is an example of output > > postgres@symds-pg:~ $ netstat -atnp | grep 5432 | wc > (Not all processes could be identified, non-owned process info > will not be shown, you would have to be root to see it all.) > 9606720 104640 > > > As you can see total connections are like 960 right now my site application > is working fine. When connections crosses 1150 and even though I see lot of > available connections coz my default_pool_size is set high to 250 but still > the application gets slow. Database performance on the other end is great > with no slow running queries or anything. So the only place I can think the > issue is at PGBOUNCER end. > Hi Prabhjot, This is classic behavior when you have a 1024 file limit. When you are below that number, it work fine. Above that number, you must wait for a connection to close and exit before you can connect which will cause a delay. See what ulimit has to say? Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] pg bouncer issue what does sv_used column means
On Fri, Jun 12, 2015 at 09:37:36PM +, Sheena, Prabhjot wrote: > Here is some more information > > pool_mode | transaction > > We have transactional pooling and our application is set up in such a way > that we have one query per transaction. We have set default pool size to 100. > > This is output . As you guys can see active connection are 100 and 224 are > waiting. We are planning to move default pool size to 250. Please suggest if > you guys think otherwise > > pgbouncer=# show pools; > database | user| cl_active | cl_waiting | sv_active | sv_idle | > sv_used | sv_tested | sv_login | maxwait > ---+---+---++---+-+-+---+--+- > pgbouncer | pgbouncer | 2 | 0 | 0 | 0 | > 0 | 0 |0 | 0 > site | feature | 418 | 0 |20 | 17 | > 0 | 0 |0 | 0 > site | service | 621 |224 | 100 | 0 | > 0 | 0 |0 | 0 > site | zabbix| 0 | 0 | 0 | 0 | > 0 | 0 |0 | 0 > > Prabhjot Singh > Database Administrator > > CLASSMATES > 1501 4th Ave., Suite 400 > Seattle, WA 98101 > 206.301.4937 o > 206.301.5701 f > > From: Sheena, Prabhjot > Sent: Friday, June 12, 2015 10:57 AM > To: 'pgsql-general@postgresql.org'; 'pgsql-performa...@postgresql.org' > Subject: pg bouncer issue what does sv_used column means > > Guys we see spike in pg bouncer during the peak hours and that was slowing > down the application. We did bump up the connection limit and it is helpful > but now we again notice little spike in connection. And one thing that I > notice that is different is jump in sv_used value when I run command show > pools during problem times > > > Can anyone please explain what value of sv_used means when i run show pools; > > > > Regards > Prabhjot > Hi Parbhjot, The spike in pgbouncer during peak hours just indicates that you are busier then. How many sv_active do you have in non-peak hours? What kind of system is this on? I suspect that your hardware cannot actually handle 100 simultaneous processes at once and if you increase that to 250 processes there is a good likelyhood that your system response will get even worse. Number of CPU to 2x number of CPU is typical for peak performance throughput. Are you using a 50-core system? What do the I/O stats look like? You may be I/O limited. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [SQL] commit inside a function failing
On Fri, Jan 23, 2015 at 01:56:53PM -0600, Suresh Raja wrote: > Hi All: > > We are running a function with a loop in it. We tried > commit; > We are getting error with above command. How can i easily commit withing > a function. > > > Thanks, > -SR PostgreSQL functions run within a transaction. You would need to use something like db_link. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ADMIN] [GENERAL] Use, Set Catalog and JDBC questions
On Mon, Feb 25, 2013 at 01:57:11PM -0500, Frank Cavaliero wrote: > Hi Adrian, > > Thanks for the response. The situation is more like the following: > > Using the JDBC driver, I connect to database TEST1 and immediately, > without having to pass username credentials again, I want to use database > TEST2. In MySQL, you can simply run: use TEST2. Wondering if > PostgreSQL has something similar. > > Thanks, > Frank > Hi Frank, The "USE xxx;' is a non-standard MySQL extension to the SQL language. This functionality is not available at the SQL layer and must be supported by the connection application itself, I think. For example, you can use "\c xxx" in psql to perform that function. If JDBC does not support it natively, you would need to open a new connection to the new database. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] number of rows in analyze
On Mon, Aug 01, 2011 at 04:42:23PM +0200, Willy-Bas Loos wrote: > Hi, > > I'ver been wondering how to set the number of rows that are scanned by > analyze. (I want to increase it) > I couln't find it at first but when i read closer i found that is does: > "The largest statistics target among the columns being analyzed > determines the number of table rows sampled to prepare the > statistics." > (http://www.postgresql.org/docs/8.4/interactive/sql-analyze.html) > > > My question is *HOW* does it "detirmine the number of table rows > sampled" with the statistics target? > What is the formula behind it? > > I am not familiar enough with the source code to figure that out > myself. sry about that. > Would be good to include in the docs if we find out. > > Cheers, > > WBL > Peruse the above reference a bit more carefully. It is actually explained pretty well. Regards, Ken -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
