Re: [PERFORM] now() gives same time within the session
On 12/07/10 14:15, A. Kretschmer wrote: Use timeofday() instead, now() returns the transaction starting time. timeofday() is a legacy function kept only for backwards-compatibility. It returns a string, which is quite awkward. Use clock_timestamp() instead. -- 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] Pooling in Core WAS: Need help in performance tuning.
Hi, Sorry, if posting here was not proper instead of starting new thread (I am really not sure if its bad thing to do) I would like to share my recent experience on implementation of client side pooling using pgbouncer. By client side i mean that the the pgbouncer process in not on same machine as postgresql server. In first trial pgbouncer and postgresql were in same machine phbouncer was connecting to postgresql using unix domain sockets. But i shifted it laters owing to high CPU usage 50%. ( using top) Now i have shifted pgbouncer into a virtual machine (openvz container) in the application server hardware and all my applications on other virtual machines (web applications) connect to pgbouncer on this virtual machine. I tested the setup with pgbench in two scenarios 1. connecting to DB server directly 2. connecting to DB via pgbouncer the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. the result was that 1. direct ~ 2000 tps 2. via pgbouncer ~ 1200 tps -- Experience on deploying to production environment with real world load/usage pattern -- Pgbouncer was put in same machine as postgresql connecting via unix domain to server and tcp sockets with clients. 1. There was drastic reduction in CPU loads from 30 to 10 ldavg 2. There were no clients waiting, pool size was 150 and number of active connections was 100-120. 3. Application performance was worse (inspite of 0 clients waiting ) I am still waiting to see what is the effect of shifting out pgbounce from dbserver to appserver, but with pgbench results i am not very hopeful. I am curious why inspite of 0 clients waiting pgbounce introduces a drop in tps. Warm Regds Rajesh Kumar Mallah. CTO - tradeindia.com. Keywords: pgbouncer performance On Mon, Jul 12, 2010 at 6:11 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Craig Ringer cr...@postnewspapers.com.au wrote: So rather than asking should core have a connection pool perhaps what's needed is to ask what can an in-core pool do that an external pool cannot do? (1) It can prevent the most pessimal performance problems resulting from lack of an external connection pool (or a badly configured one) by setting a single GUC. Configuration tools could suggest a good value during initial setup. (2) It can be used without installing and configuring a more sophisticated and complex product. (3) It might reduce latency because it avoids having to receive, parse, and resend data in both directions -- eliminating one hop. I know the performance benefit would usually accrue to the external connection pooler, but there might be some circumstances where a built-in pool could win. (4) It's one more checkbox which can be ticked off on some RFPs. That said, I fully agree that if we can include good documentation on the external poolers and we can get packagers to include poolers in their distribution, that gets us a much bigger benefit. A built-in solution would only be worthwhile if it was simple enough and lightweight enough not to be a burden on execution time or maintenance. Maybe that's too big an if. -Kevin -- 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] Pooling in Core WAS: Need help in performance tuning.
note: my postgresql server pgbouncer were not in virtualised environment in the first setup. Only application server has many openvz containers.
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Nice suggestion to try , I will put pgbouncer on raw hardware and run pgbench from same hardware. regds rajesh kumar mallah. Why in VM (openvz container) ? Did you also try it in the same OS as your appserver ? Perhaps even connecting from appserver via unix seckets ? and all my applications on other virtual machines
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Pooling in Core WAS: Need help in performance tuning.
ok , now the question is , is it possible to dig out from from postgresql database server if connection pooling is needed ? In our case eg i have kept max_connections = 300 if i reduce below 250 i get error max connection reached. on connecting to db directly, if i put pgbouncer i get less performance (even if no clients waiting) without pooling the dbserver CPU usage increases but performance of apps is also become good. Regds Rajesh Kumar Mallah. On Sun, Jul 18, 2010 at 10:55 PM, Greg Smith g...@2ndquadrant.com wrote: Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
On Sun, Jul 18, 2010 at 10:55 PM, Greg Smith g...@2ndquadrant.com wrote: Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. Dear Greg, my max_client is 300 shall i test with client count 250 ? if so what should be the scaling factor while initializing the pgbench db? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
On Jul 9, 2010, at 8:33 PM, Craig Ringer wrote: On 10/07/2010 9:25 AM, Josh Berkus wrote: It *is* the last place you want to put it, but putting it there can be much better than not putting it *anywhere*, which is what we've often seen. Well, what you proposed is an admission control mechanism, which is *different* from a connection pool, although the two overlap. A connection pool solves 4 problems when it's working: a) limiting the number of database server processes b) limiting the number of active concurrent queries c) reducing response times for allocating a new connection d) allowing management of connection routes to the database (redirection, failover, etc.) I agree with you: for most Pg users (a) is really, really important. As you know, in PostgreSQL each connection maintains not only general connection state (GUC settings, etc) and if in a transaction, transaction state, but also a query executor (full backend). That gets nasty not only in memory use, but in impact on active query performance, as all those query executors have to participate in global signalling for lock management etc. So an in-server pool that solved (b) but not (a) would IMO not be particularly useful for the majority of users. That said, I don't think it follows that (a) cannot be solved in-core. How much architectural change would be required to do it efficiently enough, though... a, b, and c can all be handled in core. But that would be a radical re-architecture to do it right. Postgres assumes that the client connection, authentication, and query processing all happen in one place in one process on one thread. Most server software built and designed today avoids that model in order to decouple its critical resources from the # of client connections. Most server software designed today tries to control its resources and not let the behavior of clients dictate resource usage. Even Apache HTTPD is undergoing a radical re-design so that it can handle more connections and more easily decouple connections from concurrent processing to keep up with competitors. I'm not saying that Postgres core should change -- again thats a radical re-architecture. But it should be recognized that it is not like most other server applications -- it can't control its resources very well and needs help to do so. From using a connection pool to manually setting work_mem differently for different clients or workloads, resource management is not what it does well. It does a LOT of things very very well, just not that. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Pooling in Core WAS: Need help in performance tuning.
Looks like , pgbench cannot be used for testing with pgbouncer if number of pgbench clients exceeds pool_size + reserve_pool_size of pgbouncer. pgbench keeps waiting doing nothing. I am using pgbench of postgresql 8.1. Are there changes to pgbench in this aspect ? regds Rajesh Kumar Mallah. On Sun, Jul 18, 2010 at 11:38 PM, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote: On Sun, Jul 18, 2010 at 10:55 PM, Greg Smith g...@2ndquadrant.com wrote: Rajesh Kumar Mallah wrote: the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. Of course pgbouncer is going decrease performance in this situation. You've added a whole layer to things that all traffic has to pass through, without a setup that gains any benefit from the connection pooling. Try making the client count 1000 instead if you want a useful test. Dear Greg, my max_client is 300 shall i test with client count 250 ? if so what should be the scaling factor while initializing the pgbench db? -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
On Sun, 2010-07-18 at 21:48 +0530, Rajesh Kumar Mallah wrote: Hi, Sorry, if posting here was not proper instead of starting new thread (I am really not sure if its bad thing to do) I would like to share my recent experience on implementation of client side pooling using pgbouncer. By client side i mean that the the pgbouncer process in not on same machine as postgresql server. In first trial pgbouncer and postgresql were in same machine phbouncer was connecting to postgresql using unix domain sockets. But i shifted it laters owing to high CPU usage 50%. ( using top) Now i have shifted pgbouncer into a virtual machine (openvz container) in the application server hardware Why in VM (openvz container) ? Did you also try it in the same OS as your appserver ? Perhaps even connecting from appserver via unix seckets ? and all my applications on other virtual machines (web applications) connect to pgbouncer on this virtual machine. I tested the setup with pgbench in two scenarios 1. connecting to DB server directly 2. connecting to DB via pgbouncer the no of clients was 10 ( -c 10) carrying out 1 transactions each (-t 1) . pgbench db was initilised with scaling factor -s 100. since client count was less there was no queuing of requests in pgbouncer i would prefer to say it was in 'passthrough' mode. the result was that 1. direct ~ 2000 tps 2. via pgbouncer ~ 1200 tps Are you sure you are not measuring how much sunning pgbouncer slows down pgbench directly, by competing for CPU resources and not by adding latency to requests ? -- Experience on deploying to production environment with real world load/usage pattern -- Pgbouncer was put in same machine as postgresql connecting via unix domain to server and tcp sockets with clients. 1. There was drastic reduction in CPU loads from 30 to 10 ldavg 2. There were no clients waiting, pool size was 150 and number of active connections was 100-120. 3. Application performance was worse (inspite of 0 clients waiting ) I am still waiting to see what is the effect of shifting out pgbounce from dbserver to appserver, but with pgbench results i am not very hopeful. I am curious why inspite of 0 clients waiting pgbounce introduces a drop in tps. If you have less clients than pgbouncer connections, you can't have any clients waiting in pgbouncer, as each of them is allocated it's own connection right away. What you were measuring was 1. pgbench and pgbouncer competeing for the same CPU 2. overhead from 2 hops to db (app-proxy-db) instead of 1 (app-db) Warm Regds Rajesh Kumar Mallah. CTO - tradeindia.com. Keywords: pgbouncer performance On Mon, Jul 12, 2010 at 6:11 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Craig Ringer cr...@postnewspapers.com.au wrote: So rather than asking should core have a connection pool perhaps what's needed is to ask what can an in-core pool do that an external pool cannot do? (1) It can prevent the most pessimal performance problems resulting from lack of an external connection pool (or a badly configured one) by setting a single GUC. Configuration tools could suggest a good value during initial setup. (2) It can be used without installing and configuring a more sophisticated and complex product. (3) It might reduce latency because it avoids having to receive, parse, and resend data in both directions -- eliminating one hop. I know the performance benefit would usually accrue to the external connection pooler, but there might be some circumstances where a built-in pool could win. (4) It's one more checkbox which can be ticked off on some RFPs. That said, I fully agree that if we can include good documentation on the external poolers and we can get packagers to include poolers in their distribution, that gets us a much bigger benefit. A built-in solution would only be worthwhile if it was simple enough and lightweight enough not to be a burden on execution time or maintenance. Maybe that's too big an if. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
Thanks for the thought but it (-C) does not work . BTW, I think you should use -C option with pgbench for this kind of testing. -C establishes connection for each transaction, which is pretty much similar to the real world application which do not use connection pooling. You will be supprised how PostgreSQL connection overhead is large. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning.
From: Rajesh Kumar Mallah mallah.raj...@gmail.com Subject: Re: [PERFORM] Pooling in Core WAS: Need help in performance tuning. Date: Mon, 19 Jul 2010 08:06:09 +0530 Message-ID: aanlktilggkbmc9h7wlhlcdqfm5rjth1-9dpf8golv...@mail.gmail.com Thanks for the thought but it (-C) does not work . Still you need: pgbench's -c = (pool_size + reserve_pool_size) -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
