Re: [PERFORM] Connection pooling - Number of connections
Reaching the maxPoolSize from the minPoolSize means creating the connections at the crucial moment where the client application is in the desperate need of completing an important query/transaction which the primary responsibility since it cannot hold the data collected. So here the connection creation action is the costliest among all the other management tasks. so keeping the connections ready is the best option. poolSize parameter is very good in the sense when the application owner know what is the optimal number to put, after having application performance analysed with the history of previous settings and the improvements made on it. server sizing always shows up in this sort of analysis. On Fri, Mar 21, 2014 at 9:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Guillaume Smet guillaume.s...@gmail.com writes: On Fri, Mar 21, 2014 at 4:49 PM, David Johnston pol...@yahoo.com wrote: Consider this train-of-thought: no matter how large the pool size if you are constantly keeping, say, 90% of the connections actively working then having, on average, 10% of the connections sitting idle is probably not going to be noticeable on the server and the reduction in overhead of managing a pool is typically a net positive. Now, I had no clue what percentage is actually true, or under what conditions and pool sizes it may vary, but that is a calculation that someone deciding on between managed and un-managed pools would need to make. Sure. The big question is if it is suited for general purpose or if having 100 connections open when 10 only are necessary at the time is causing any unnecessary contention/spinlock issues/performance overhead/whatever... It will cost you, in ProcArray scans for example. But lots-of-idle- connections is exactly what a pooler is supposed to prevent. If you have a server that can handle say 10 active queries, you should have a pool size of 10, not 100. (If you have a server that can actually handle 100 active queries, I'd like to have your IT budget.) The proposed design sounds fairly reasonable to me, as long as users are clear on how to set the pool size --- and in particular that bigger is not better. Clueless users could definitely shoot themselves in the foot, though. regards, tom lane -- 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] slave wal is ahead of master
fsync ... taking more knowledge around this will shed some light to understand this problem slave ahead of master there was silence, because master hang. ... replication halted here, master holds the latest copy which is missing at both the slaves I decided to promote one of the slaves ... only 2 slaves are left, and one among them is going to be the master now, in master/slave nomenclature the data from master is considered as the valid one from this point onward master is behind his slave ... you mentioned that the original master comes up in the mean time one of the slave was already a master On Wed, Mar 19, 2014 at 10:40 PM, Evgeniy Shishkin itparan...@gmail.comwrote: Hello, we have 3 servers with postgresql 9.3.3. One is master and two slaves. We run synchronous_replication and fsync, synchronous_commit and full_page_writes are on. Suddenly master hang up with hardware failure, it is a strange bug in iLo which we investigate with HP. Before master was rebooted, i ran ps aux on slave postgres: wal receiver process streaming 12/F1031DF8 Last messages in slaves logs was 2014-03-19 02:41:29.005 GMT,,,7389,,53108c69.1cdd,16029,,2014-02-28 13:17:29 GMT,,0,LOG,0,recovery restart point at 12/DFFBB3E8,last completed transaction was at log time 2014-03-19 02:41:28.886869+00 and then there was silence, because master hang. Then master was rebooted and slave wrote in log 2014-03-19 15:36:39.176 GMT,,,7392,,53108c69.1ce0,2,,2014-02-28 13:17:29 GMT,,0,FATAL,XX000,terminating walreceiver due to timeout, 2014-03-19 15:36:39.177 GMT,,,7388,,53108c69.1cdc,6,,2014-02-28 13:17:29 GMT,1/0,0,LOG,0,record with zero length at 12/F1031DF8, 2014-03-19 15:36:57.181 GMT,,,12100,,5329b996.2f44,1,,2014-03-19 15:36:54 GMT,,0,FATAL,XX000,could not connect to the primary server: could not connect to server: No route to host Is the server running on host 10.162.2.50 and accepting TCP/IP connections on port 5432? , Then master finally came back, slave wrote 2014-03-19 15:40:09.389 GMT,,,13121,,5329ba59.3341,1,,2014-03-19 15:40:09 GMT,,0,FATAL,XX000,could not connect to the primary server: FATAL: the database system is starting up , 2014-03-19 15:40:16.468 GMT,,,13136,,5329ba5e.3350,1,,2014-03-19 15:40:14 GMT,,0,LOG,0,started streaming WAL from primary at 12/F100 on timeline 1, 2014-03-19 15:40:16.468 GMT,,,13136,,5329ba5e.3350,2,,2014-03-19 15:40:14 GMT,,0,FATAL,XX000,could not receive data from WAL stream: ERROR: requested starting point 12/F100 is ahead of the WAL flush position of this server 12/F0FFFCE8 , last message was repeated several times and then this happened 2014-03-19 15:42:04.623 GMT,,,13722,,5329bacc.359a,1,,2014-03-19 15:42:04 GMT,,0,LOG,0,started streaming WAL from primary at 12/F100 on timeline 1, 2014-03-19 15:42:04.628 GMT,,,7388,,53108c69.1cdc,7,,2014-02-28 13:17:29 GMT,1/0,0,LOG,0,invalid record length at 12/F1031DF8, 2014-03-19 15:42:04.628 GMT,,,13722,,5329bacc.359a,2,,2014-03-19 15:42:04 GMT,,0,FATAL,57P01,terminating walreceiver process due to administrator command, 2014-03-19 15:42:09.628 GMT,,,7388,,53108c69.1cdc,8,,2014-02-28 13:17:29 GMT,1/0,0,LOG,0,invalid record length at 12/F1031DF8, 2014-03-19 15:42:14.628 GMT,,,7388,,53108c69.1cdc,9,,2014-02-28 13:17:29 GMT,1/0,0,LOG,0,invalid record length at 12/F1031DF8, 2014-03-19 15:42:19.628 GMT,,,7388,,53108c69.1cdc,10,,2014-02-28 13:17:29 GMT,1/0,0,LOG,0,invalid record length at 12/F1031DF8, and it just repeats forever. Meanwhile on master 2014-03-19 15:39:30.957 GMT,,,7115,,5329ba32.1bcb,2,,2014-03-19 15:39:30 GMT,,0,LOG,0,database system was not properly shut down; automatic recovery in progress, 2014-03-19 15:39:30.989 GMT,,,7115,,5329ba32.1bcb,3,,2014-03-19 15:39:30 GMT,,0,LOG,0,redo starts at 12/DFFBB3E8, 2014-03-19 15:39:47.114 GMT,,,7115,,5329ba32.1bcb,4,,2014-03-19 15:39:30 GMT,,0,LOG,0,redo done at 12/F0FFFC38, 2014-03-19 15:39:47.114 GMT,,,7115,,5329ba32.1bcb,5,,2014-03-19 15:39:30 GMT,,0,LOG,0,last completed transaction was at log time 2014-03-19 05:02:29.273138+00, 2014-03-19 15:39:47.115 GMT,,,7115,,5329ba32.1bcb,6,,2014-03-19 15:39:30 GMT,,0,LOG,0,checkpoint starting: end-of-recovery immediate, 2014-03-19 15:40:16.466 GMT,replicator,,7986,10.162.2.52:44336,5329ba5e.1f32,1,idle,2014-03-19 15:40:14 GMT,2/0,0,ERROR,XX000,requested starting point 12/F100 is ahead of the WAL flush position of this server 12/F0FFFCE8,walreceiver So, all two slaves are disconnected from master, which somehow is past his slaves. I decided to promote one of the slaves, so we can have some snapshot of the data. relevant logs from this are 2014-03-19 16:50:43.118 GMT,,,,,5329cae3.115c,3,,2014-03-19 16:50:43 GMT,,0,LOG,0,redo starts
Re: [PERFORM] Hot Standby performance issue
http://www.postgresql.org/docs/current/static/hot-standby.html#HOT-STANDBY-CAVEATS On Fri, Oct 18, 2013 at 11:49 PM, sparikh spar...@ecotality.com wrote: Anybody has any idea, or pointer ? This is a high priority issue I have resolve at work. Any help would be of great help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Hot-Standby-performance-issue-tp5774673p5775103.html Sent from the PostgreSQL - performance mailing list archive at Nabble.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] DBT-5 Postgres 9.0.3
http://sourceforge.net/mailarchive/forum.php?forum_name=osdldbt-generalmax_rows=25style=nestedviewmonth=201104 - Sethu On Thu, May 12, 2011 at 5:22 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Apr 12, 2011 at 3:51 AM, Sethu Prasad sethuprasad...@gmail.com wrote: Anyone lucky to have dbt5 run for PostgreSQL 9.0.3?! I am trying on Novell SuSE Linux Enterprise Server 11 SP1 x86_64 with a virtual machine and bit hard with no success run yet. If you can help me with any docs will be more of a support. What's going wrong for you? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [PERFORM] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance = Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226
Yes it has something to do with Hot Standby, if you omit some parts on the archive then the standby instance will not have the necessary stuff and complain like this.. I kept the FusionIO drive in my checklist while attending to this issue, as we tried it looking for performance combined with read-only hot standby and in doubt I thought that the recovery is not successful on this drive safely. so I pointed that Fio Drive here. Straight to say, I missed the pg_clog directory on archive. seq_page_cost = 1.0 random_page_cost = 1.0 Is the above settings are fine when we deal with Fio and Performance, as I have the advice earlier stating that read and write are treated same with Fio drives. Any suggestions on configuration changes to have read-only hot standby faster on READs. - Sethu On Sun, May 8, 2011 at 11:08 AM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, May 3, 2011 at 10:02 AM, Sethu Prasad sethuprasad...@gmail.com wrote: I tried with the PostgreSQL 9.0.4 + Hot Standby and running the database from Fusion IO Drive to understand the PG Performance. While doing so I got the Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226. Any idea on this error? Is that combination PG + HotSB + Fusion IO Drive is not advisable?! Why I wonder do you think this might have anything to do with Hot Standby and/or FusionIO drives? This indicates either catalog or catalog index corruption of some kind. Did you only get this error once? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services
Re: [PERFORM] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance = Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226
I did the hot standby configured earlier and at that time I started using(querying) the standby database. May be something missed on the archive command. On Wed, May 4, 2011 at 8:44 PM, Josh Berkus j...@agliodbs.com wrote: While doing so I got the *Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226*. Any idea on this error? Is that combination PG + HotSB + Fusion IO Drive is not advisable?! What were you doing when you got this error? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance = Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226
Hi, I tried with the PostgreSQL 9.0.4 + Hot Standby and running the database from Fusion IO Drive to understand the PG Performance. While doing so I got the *Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226*. Any idea on this error? Is that combination PG + HotSB + Fusion IO Drive is not advisable?! Regards, Sethu Prasad. G.
Re: [PERFORM] Performance
Just want to share the DBT(25) thing http://archives.postgresql.org/pgsql-performance/2011-04/msg00145.php http://sourceforge.net/mailarchive/forum.php?forum_name=osdldbt-generalmax_rows=25style=nestedviewmonth=201104 On Wed, Apr 27, 2011 at 11:55 PM, Greg Smith g...@2ndquadrant.com wrote: Tomas Vondra wrote: Hmmm, just wondering - what would be needed to build such 'workload library'? Building it from scratch is not feasible IMHO, but I guess people could provide their own scripts (as simple as 'set up a a bunch of tables, fill it with data, run some queries') and there's a pile of such examples in the pgsql-performance list. The easiest place to start is by re-using the work already done by the TPC for benchmarking commercial databases. There are ports of the TPC workloads to PostgreSQL available in the DBT-2, DBT-3, and DBT-5 tests; see http://wiki.postgresql.org/wiki/Category:Benchmarking for initial information on those (the page on TPC-H is quite relevant too). I'd like to see all three of those DBT tests running regularly, as well as two tests it's possible to simulate with pgbench or sysbench: an in-cache read-only test, and a write as fast as possible test. The main problem with re-using posts from this list for workload testing is getting an appropriately sized data set for them that stays relevant. The nature of this sort of benchmark always includes some notion of the size of the database, and you get different results based on how large things are relative to RAM and the database parameters. That said, some sort of systematic collection of hard queries would also be a very useful project for someone to take on. People show up regularly who want to play with the optimizer in some way. It's still possible to do that by targeting specific queries you want to accelerate, where it's obvious (or, more likely, hard but still straightforward) how to do better. But I don't think any of these proposed exercises adjusting the caching model or default optimizer parameters in the database is going anywhere without some sort of benchmarking framework for evaluating the results. And the TPC tests are a reasonable place to start. They're a good mixed set of queries, and improving results on those does turn into a real commercial benefit to PostgreSQL in the future too. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- 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] REINDEX takes half a day (and still not complete!)
You mean the maintenance instead of mentioning the recovery? If yes The following types of administration commands are not accepted during recovery mode: - * Data Definition Language (DDL) - e.g. CREATE INDEX* - * Privilege and Ownership - GRANT, REVOKE, REASSIGN* - * Maintenance commands - ANALYZE, VACUUM, CLUSTER, REINDEX* Thanks. On Sun, Apr 17, 2011 at 5:30 PM, Phoenix Kiula phoenix.ki...@gmail.comwrote: Sorry, rejuvenating a thread that was basically unanswered. I closed the database for any kinds of access to focus on maintenance operations, killed all earlier processes so that my maintenance is the only stuff going on. REINDEX is still taking 3 hours -- and it is still not finished! Similarly, if I cancel the REINDEX and issue a VACUUM ANALYZE VERBOSE, this too seems to just hang there on my big table. I changed the maintenance_work_men to 2GB for this operation. It's highly worrisome -- the above slow times are with 2GB of my server dedicated to Postgresql Surely this is not tenable for enterprise environments? I am on a 64bit RedHat server with dual CPU Intel Woodcrest or whatever that was called. Postgres is 8.2.9. How do DB folks do this with small maintenance windows? This is for a very high traffic website so it's beginning to get embarrassing. Would appreciate any thoughts or pointers. Thanks! On Mon, Mar 21, 2011 at 9:28 PM, Merlin Moncure mmonc...@gmail.com wrote: On Fri, Mar 18, 2011 at 10:07 PM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I have a large table but not as large as the kind of numbers that get discussed on this list. It has 125 million rows. REINDEXing the table takes half a day, and it's still not finished. To write this post I did SELECT COUNT(*), and here's the output -- so long! select count(*) from links; count --- 125418191 (1 row) Time: 1270405.373 ms That's 1270 seconds! I suppose the vaccuum analyze is not doing its job? As you can see from settings below, I have autovacuum set to ON, and there's also a cronjob every 10 hours to do a manual vacuum analyze on this table, which is largest. PG is version 8.2.9. Any thoughts on what I can do to improve performance!? Below are my settings. max_connections = 300 shared_buffers = 500MB effective_cache_size = 1GB max_fsm_relations= 1500 max_fsm_pages= 95 work_mem = 100MB temp_buffers = 4096 authentication_timeout = 10s ssl = off checkpoint_warning = 3600 random_page_cost = 1 autovacuum = on autovacuum_vacuum_cost_delay = 20 vacuum_cost_delay= 20 vacuum_cost_limit= 600 autovacuum_naptime = 10 stats_start_collector= on stats_row_level = on autovacuum_vacuum_threshold = 75 autovacuum_analyze_threshold = 25 autovacuum_analyze_scale_factor = 0.02 autovacuum_vacuum_scale_factor = 0.01 wal_buffers = 64 checkpoint_segments = 128 checkpoint_timeout = 900 fsync= on maintenance_work_mem = 512MB how much memory do you have? you might want to consider raising maintenance_work_mem to 1GB. Are other things going on in the database while you are rebuilding your indexes? Is it possible you are blocked waiting on a lock for a while? How much index data is there? Can we see the table definition along with create index statements? merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Is there a way to selective dump of records in Postgres 9.0.3?
Hi List, I am using PostgreSQL 9.0.3 and I have a need to dump only the selective data from partial list of tables of a database. Is there a straight way to do it with pg_dump or any alternative work around to suggest here?! Sethu Prasad. G.
[PERFORM] DBT-5 Postgres 9.0.3
Hi, Anyone lucky to have dbt5 run for PostgreSQL 9.0.3?! I am trying on Novell SuSE Linux Enterprise Server 11 SP1 x86_64 with a virtual machine and bit hard with no success run yet. If you can help me with any docs will be more of a support. Regards, Sethu Prasad
Re: [PERFORM] Postgres Performance Tuning
Also you can try to take the help of pgtune before hand. pgfoundry.org/projects/*pgtune*/ On Mon, Apr 4, 2011 at 12:43 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma adarsh.sha...@orkash.com wrote: Dear all, I have a Postgres database server with 16GB RAM. Our application runs by making connections to Postgres Server from different servers and selecting data from one table insert into remaining tables in a database. Below is the no. of connections output :- postgres=# select datname,numbackends from pg_stat_database; datname | numbackends ---+- template1 | 0 template0 | 0 postgres | 3 template_postgis | 0 pdc_uima_dummy| 107 pdc_uima_version3 | 1 pdc_uima_olap | 0 pdc_uima_s9 | 3 pdc_uima | 1 (9 rows) I am totally confused for setting configuration parameters in Postgres Parameters :- First of all, I research on some tuning parameters and set mu postgresql.conf as:- max_connections = 1000 That's a little high. shared_buffers = 4096MB work_mem = 64MB That's way high. Work mem is PER SORT as well as PER CONNECTION. 1000 connections with 2 sorts each = 128,000MB. [root@s8-mysd-2 ~]# free total used free shared buffers cached Mem: 16299476 16202264 97212 0 58924 15231852 -/+ buffers/cache: 911488 15387988 Swap: 16787884 153136 16634748 There is nothing wrong here. You're using 153M out of 16G swap. 15.x Gig is shared buffers. If your system is slow, it's not because it's running out of memory or using too much swap. I think there may be some problem in my Configuration parameters and change it as : Don't just guess and hope for the best. Examine your system to determine where it's having issues. Use vmstat 10 iostat -xd 10 top htop and so on to see where your bottleneck is. CPU? Kernel wait? IO wait? etc. log long running queries. Use pgfouine to examine your queries. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
