[GENERAL] Index Advice in PG
Hi list, I am happy to announce the new release of pg_idx_advisor version 0.1.2. This is a PostgreSQL extension which allows a user to get index advice from the DB optimizer. It's the first "stable" release so please don't go running this on your production environment :) But, I'd greatly appreciate any comments/thoughts/issues/pull requests... It's fairly simple to use: 1. # create extension pg_idx_advisor; 2. # load '$libdir/plugins/pg_idx_advisor.so'; and then run explain on the queries you'd like to get advice on: 3. # explain select * from t where a = 100;INFO: ** Plan with original indexes ** QUERY PLAN Seq Scan on t (cost=0.00..36.75 rows=11 width=8) Filter: (a = 100) ** Plan with hypothetical indexes ** read only, advice, index: create index on t(a) Bitmap Heap Scan on t (cost=4.12..14.79 rows=11 width=8) Recheck Cond: (a = 100) -> Bitmap Index Scan on :114699 (cost=0.00..4.11 rows=11 width=0) Index Cond: (a = 100) (9 rows) You can get it from the postgres extension network: http://pgxn.org/dist/pg_idx_advisor Or directly from GitHub: https://github.com/cohenjo/pg_idx_advisor Regards, - Jony
Re: [GENERAL] scaling postgres
Hi, I'd recommend looking into 2 solutions here, depending if you want to keep the data or not and how "fault tolerant" is the app (could you loose data from the last hour?) If you could loose some data and you don't intend keeping the whole data set - I'd look at using RAM as your storage - it's the fastest place to store data and you can easily get servers with lot's of RAM these days. If you can't loose data then you'll need to use disks - depending on how big each row is, compute your expected read/write throughput and go shopping :) for 1kb rows you get 60GB per hour = 16MB per sec - simple disks can handle this. for 10kb rows you get 160MB/s - now you need better disks :) SSD disks are cheep these days but they don't like repeated writes/deletes so it might cause problems down the line (hence my first RAM recommendation) as for keeping the raw data - you could easily do it if you use partitions, if you have daily partitions inheriting from a master table you can quickly access the last day (or even several days) but do take note that a full table scan takes time and you'll need to manage writing to the right partition yourself (not that hard - simply insert to _) if you can write the data daily, keeping it will not add any real load (a little on parse times for queries that access the master table) Just my 2 cents, Regards, - Jony On Mon, Aug 3, 2015 at 9:53 AM, Chris Withers wrote: > Hi All, > > I'm curious if there are recommendations for scaling postgres to what, for > me, seems like "a lot" of data... > > The app in question currently writes around 1.5 billion rows into a table > before rolling them up into tables that have a few million roll up rows > each. That 1.5 billion row table is emptied and refilled each day, so we're > talking about quite high write as well as quite high read. Where can I find > could examples/docs of how to scale postgres for this kind of data load? > What sort of hardware would I be looking to spec? > > Okay, now this app may well eventually want to progress to storing those > 1.5 billion rows per day. Is that feasible with postgres? If not, what > storage and processing solutions would people recommend for that kind of > data load? > > cheers, > > Chris > > > -- > 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] Postgresql-9.1 CentOS7 effective_cache_size issue
Hi, effective_cache_size is used only for query planing - you will not see it in vmstat etc. the default is 128mb, meaning you'd expect to see major differences when running with 128mb vs 80GB of effective cache. I'd take a look at your execution plans - I think you would find them very different between the 2 settings. could you share your pgbench configuration? BTW, have you considered upgrading to a newer PG version - you are missing out on quite a few performance improvements (for large memory clusters as well) Regards, - Jony On Tue, Aug 11, 2015 at 11:52 AM, Michael H wrote: > Hi All, > > I've been performance tuning a new database server for the past couple of > weeks with very mixed results, I've read every guide to tuning I can locate > on Google aswell as Gregory Smiths - Postgresql 9.0 High Performance book. > > The server is a HP DL385P gen8, dual processor AMD Opteron 6386SE, 16core > 2.8Ghz (32 cores total). 128GB DDR3 1600mhz, 8 x 16GB sticks. 4 x 300GB 6G > SAS 10K in a RAID1+0 configuration. > > We are using CentOS7.1 minimal with Postgresql-9.2.13. > > I'm seeing good IOPS, memory throughput is good, the server is > benchmarking very well in comparison to it's predecessor. > > I have left most of the configuration as defaults and tuned the following > parameters; > > > shared_buffers = 8GB > max_prepared_transactions = 5 > work_mem = 32MB > max_stack_depth = 7MB > max_files_per_process = 100 > wal_level = hot_standby > max_wal_senders = 3 > wal_keep_segments = 128 > wal_buffers=64MB > checkpoint_segments = 64 > maintenance_work_mem=2GB > > ## note this is commented out > #effective_cache_size = 40GB > > # increased logging levels for PGBADGER > track_activities = on > track_counts = on > track_functions = all > log_parser_stats = off > log_planner_stats = off > log_executor_stats = off > log_statement_stats = off > log_min_duration_statement = 0 > log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d ' > log_checkpoints = on > log_connections = on > log_disconnections = on > log_lock_waits = on > log_temp_files = 0 > log_destination = 'stderr' > logging_collector = on > log_directory = 'pg_log' > log_filename = 'postgresql-%Y-%m-%d.log' > log_rotation_age = 0 > client_min_messages = notice > log_min_messages = warning > log_min_error_statement = error > log_min_duration_statement = 0 > log_checkpoints = on > log_connections = on > log_disconnections = on > log_duration = off > log_error_verbosity = verbose > log_hostname = on > log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u ' > log_lock_waits = on > log_statement = 'none' > log_temp_files = 0 > > From my readings online I have configured shmmax and shmall in > /etc/sysctl.conf, the suggested settings were 50% of RAM dedicated to > shared_buffers. > > kernel.shmmax = 67462680576 # roughly 62GB allowing 60GB for PGSQL. > kernel.shmall = 16470381# shmmax/16 the same ratio as default > values and my previous server. > > the shmmax and shamall can be reduced, this was my starting point. > > Now, when I make changes with work_mem and shared_buffers I am seeing > performance increases / decreases as I would expect. > > When I set effective_cache_size to anything other than the default > (comment out my setting) my TPS takes a huge nose dive, from 37TPS down to > 5TPS. > > > wal_buf wal_seg effective_cache_sizeshared_buffers work_mem > 64MB64 defaults8GB 64MB > > pgbench - my data on my database > TPS total transactions > 37.324716 11224 > 34.353093 10337 > 19.832292 6003 > 10.010148 3120 > 5.8597982073 > > > changing effective_cache_size (tested from 1GB to 80GB) causes these > benchmark results > wal_buf wal_seg effective_cache_sizeshared_buffers work_mem > 64MB64 * 8GB 64MB > > pgbench - my data on my database > TPS total transactions > 5.861,770 > 3.781,168 > 1.34430 > 0.66258 > 0.37512 > > > looking at vmstat, free, top and ipcs I'm not seeing anything unusual, > nothing is being swapped to disk, cache is not flooding and I am only > consuming about 8GB of RAM no matter what configuration changes I make. > > Are there known issues with Postgresql-9.2.13 and Centos7? I found one > article where a guy had the same kind of issues with memory consumption. > > > http://postgresql.nabble.com/PostgreSQL-9-3-abd-CentOS-7-memory-usage-td5822755.html > > Can anybody point me in the right direction?! am I making some fundamental > mistakes with my configuration? > > Any assistance would be great, I'm pushing to get this box into production > later this week! > > Thank you in advance, > > Michael > > - > I just sent this message to psql-admin and realised this may be the more > appropriate location to ask. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsq
Re: [GENERAL] repmgr won't update witness after failover
Hi Aviel, you can use the 'show cluster' command to see the repmgr state before you do the 2nd failover - make sure the node1 is indeed marked as replica. After a failover the Master doesn't automatically attach to the new master - you need to point him as a slave (standby follow - if possible...) did you start the repmgrd on node1 after making it a replica of the new master? (it needs 2 daemons to decide what to promote) Regards, - Jony On Thu, Aug 13, 2015 at 1:29 PM, Aviel Buskila wrote: > Hey, > I have set up three nodes of postgresql 9.4 with repmgr in this way: > 1. master - node1 > 2. standby - node2 > 3. witness - node3 > > Now I have set up the replication and the witness as it says here: > https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.rst > > Now when I do 'kill -9 $(pidof postmaster)' The witness detects that > something went wrong and fails over from node1 to node2 > But when I setup the replication now to work from node2 to node1 and I > kill the postgresql process it doesn't failover and the repmgrd log shows > the following message: > unable to determine a valid master server; waiting 10 seconds to retry... > > it seems that the witness doesn't know about the new standby server.. > > Has anyone got any idea about what am I doing wrong here? > > > Best regards, > Aviel Buskila >
Re: [GENERAL] repmgr won't update witness after failover
Hi, The clone command just clones the data from node2 to node1, you need to also register it with the `force` option to override the old record. (as if you're building a new replica node...) see: https://github.com/2ndQuadrant/repmgr#converting-a-failed-master-to-a-standby Regards, - Jony On Sun, Aug 16, 2015 at 3:19 PM, Aviel Buskila wrote: > Hey, > I think I know what the problem is, > after the first failover when I clone the old master to be standby with > the 'repmgr standby clone' command it seems that nothing updates the > repl_nodes table with the new standby in my cluster so on the next failover > the repmgrd is failed to find a new upcoming standby to failover.. > > this issue is confirmed after that I manually updated the repl_nodes table > after the clone so that the old master is now a standby database. > > now my question is: > Where does is suppose to happen that after I issue the 'repmgr standby > clone' the repl_nodes should be updated too about the new standby server? > > Best regards, > Aviel Buskila > > > > 2015-08-16 12:11 GMT+03:00 Aviel Buskila : > >> hey, >> >> I have tried to set the configuration all over again, now the status of >> 'repl_nodes' before the failover is: >> >> id | type | upstream_node_id | cluster | name | conninfo | priority | >> active >> >> +-+---++--+- >> 1 | master | | cluster_name |node1| host=node1 >> dbname=repmgr port=5432 user=repmgr | 100 | t >> 2 | standby|1| cluster_name |node2| host=node2 >> dbname=repmgr port=5432 user=repmgr | 100 | t >> >> 3 | witness|| cluster_name |node3| host=node3 >> dbname=repmgr port=5499 user=repmgr | 100 | t >> >> >> repmgr is started on node2 and node3 (standby and witness) now when I >> kill postgresmaster process I can see in the >> >> repmgrd log the following messages: >> >> [WARNING] connection to master has been lost, trying to recover... 60 >> seconds before failover decision >> >> [WARNING] connection to master has been lost, trying to recover... 50 >> seconds before failover decision >> >> [WARNING] connection to master has been lost, trying to recover... 40 >> seconds before failover decision >> >> [WARNING] connection to master has been lost, trying to recover... 30 >> seconds before failover decision >> >> [WARNING] connection to master has been lost, trying to recover... 20 >> seconds before failover decision >> >> [WARNING] connection to master has been lost, trying to recover... 10 >> seconds before failover decision >> >> >> and than when it tried to elect node2 to be promoted it shows the >> following messages: >> >> [DEBUG] connecting to: 'host=node2 user=repmgr dbname=repmgr >> fallback_application_name='repmgr'' >> >> [WARNING] unable to defermmine a valid master server; waiting 10 seconds >> to retry... >> >> [ERROR] unable to determine a valid master node, terminating... >> >> [INFO] repmgrd terminating.. >> >> >> >> what am I doing wrong? >> >> >> El 14/08/15 a las 04:14, Aviel Buskila escribió: >> > Hey, >> > yes I did .. and still it wont fail back.. >> >> Can you send over the output of "repmgr cluster show" before and after >> the failover process? >> >> The output of SELECT * FROM repmgr_schema.repl_nodes; after the failover >> (you need to change repmgr_schema with what you have configured). >> >> Also, which version of repmgr are you running? >> >> > 2015-08-13 16:23 GMT+03:00 Jony Vesterman Cohen > >: >> > >> >> Hi, did you make the old master follow the new one using repmgr? >> >> >> >> It doesn't update itself automatically... >> >> From the looks of it repmgr thinks you have 2 masters - the old one >> >> offline and the new one online. >> >> Regards, >> >> -- >> Martín Marquéshttp://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services >> >
Re: [GENERAL] First-class Polymorphic joins?
Hi, You can do this today using inheritance. define a table "tagable" with person & blog as child tables. than you could run queries like: select * from tags JOIN tagable on (tag_id = tag_fk); tag_id | tag_desc | id | tag_fk | data +--+++ 3 | blog tag 1 | 1 | 3 | blog tagged by tag 1 2 | person tag 2 | 2 | 2 | person tagged by tag 2 simple :) Regards, - Jony On Fri, Aug 14, 2015 at 4:04 AM, Adrian Klaver wrote: > On 08/13/2015 05:59 PM, Guyren Howe wrote: > > Ccing list > > On Aug 13, 2015, at 17:49 , Adrian Klaver >> wrote: >> >>> >>> A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. >>> >>> I am pretty sure it already does that: >>> >>> http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html >>> >>> REFERENCES reftable [ ( refcolumn ) ] >>> >> >> I apologize for not being clearer. >> >> The point is that the fk in different rows can reference different >> tables. I might want to be able to attach a tag to a person or a blog post, >> say. And then I want to find all the persons and blog posts with a >> particular tag, in a single query. >> > > Could you just not turn that around?: > > tag > tag_id > tag_desc > > person > person_id > tag_fk references tag > > blog > blog_id > tag_fk references tag > > >> The simplest implementation is to have a table reference as a first-class >> value I can store in a field. >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > 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] efficient math vector operations on arrays
Hi, Don't know if it's exactly what you're looking for but the MADLib package has utility function for matrix and vector operations. see: http://doc.madlib.net/latest/group__grp__array.html Regards, - Jony On Fri, Dec 25, 2015 at 9:58 PM, Jim Nasby wrote: > On 12/24/15 1:56 AM, Pavel Stehule wrote: > >> I don't know any extension that calculate euclid distance, but it should >> be trivial in C - if you don't need to use generic types and generic >> operations. >> > > Before messing around with that, I'd recommend trying either pl/r or > pl/pythonu. > -- > Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX > Experts in Analytics, Data Architecture and PostgreSQL > Data in Trouble? Get it in Treble! http://BlueTreble.com > > > -- > 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] understanding postgres backend process memory usage
Hi David, How many different table/objects do you read from and do you use bind variables? Each postmaster process keeps internal dictionary on objects and queries ran - If you have too many objects or queries planned you might take up too much ram. If this is indeed your situation, you might benefit from shorter lived connections. Regards, - Jony On Fri, Apr 15, 2016 at 12:14 AM, Day, David wrote: > Hello, > > > Understanding postgresql memory usage ? > > While traffic loading our application that uses a postgres 9.3.11 database > instance, we observe higher RAM usage then expected. The system bogs down > as free memory decreases to minimal values. The most RAM usage seems to be > with postgres backends. I am looking for clarity on how postgress backend > connections use RAM. I suspect our tuning is bad for 2 GB RAM pool. We > likely need to add memory. > > Please look at the "top" output below and offer an opinion that the "top > RES" values could be that high for the configuration parameters postgres is > running on. > > There are no error messages in postgres log of relevance. > > Regards and thank you for consideration on the matter. > > > Dave Day > > -- > > > Details: > > > Postgresql.conf ( relevant subset ) > > max_connections = 100 > shared_buffers = 64MB > #temp_buffers = 8MB > work_mem = 1MB > maintenance_work_mem=16MB > max_stack_depth=2MB > #wal_level=minimal > #fsync=on > #synchronous_commit=on > #full_page_writes = on > #wal_buffers = -1 > #autovacuum = on > log_destination='syslog' > update_process_title=off > > -- > > Here are all the backend connections via "top" taken after offered load > has been running a while. > > I annotated some of the "top" data as to connection ownership sorted by > RES and user. > Within the DBM connections I correlate the pid to the client session. > Connections > 0-3 are somewhat specialized towards read operations ( selects ) whiles > connections 4-9 are > More write oriented. ( Insert/updates ). > > > top capture: > > 97 processes: 1 running, 91 sleeping, 5 zombie > CPU: 1.6% user, 0.0% nice, 9.4% system, 0.4% interrupt, 88.6% idle > Mem: 1474M Active, 79M Inact, 387M Wired, 19M Cache, 10M Free > ARC: 156M Total, 44M MFU, 38M MRU, 1885K Anon, 2619K Header, 70M Other > Swap: 2048M Total, 761M Used, 1287M Free, 37% Inuse, 144K In > > PID USERNAME THR PRI NICE SIZERES STATE C TIMEWCPU > COMMAND DBM connection > DBM > 9099 pgsql1 200 373M 205M sbwait 1 4:02 0.00% > postgres 6 > 9094 pgsql1 200 365M 204M sbwait 1 3:53 0.00% > postgres 4 > 9089 pgsql1 200 325M 162M sbwait 1 8:14 0.00% > postgres 9 > 9097 pgsql1 200 309M 145M sbwait 1 3:40 0.00% > postgres 5 > 9096 pgsql1 200 301M 138M sbwait 0 3:24 0.00% > postgres 8 > 9095 pgsql1 200 233M 72648K sbwait 1 1:03 0.00% > postgres 0 > 9092 pgsql1 200 217M 53852K sbwait 1 0:44 0.00% > postgres 2 > 9100 pgsql1 200 217M 53708K sbwait 0 0:44 0.00% > postgres 3 > 9098 pgsql1 200 217M 53552K sbwait 0 0:43 0.00% > postgres 1 > 9093 pgsql1 200 373M 32328K sbwait 1 4:02 0.00% > postgres 7 > > 9084 pgsql1 200 112M 21284K select 0 0:37 0.00% > postgres > > glassfish > 72033 pgsql1 200 112M 13624K sbwait 0 0:00 0.00% > postgres > 72034 pgsql1 200 112M 13456K sbwait 1 0:00 0.00% > postgres > 70428 pgsql1 200 112M 13244K sbwait 0 0:00 0.00% > postgres > 70430 pgsql1 200 112M 13244K sbwait 1 0:00 0.00% > postgres > 70429 pgsql1 200 112M 13244K sbwait 1 0:00 0.00% > postgres > 70431 pgsql1 200 112M 13244K sbwait 0 0:00 0.00% > postgres > 70432 pgsql1 200 112M 13240K sbwait 0 0:00 0.00% > postgres > 70427 pgsql1 200 112M 13224K sbwait 1 0:00 0.00% > postgres > > 9087 pgsql1 200 112M 10772K select 1 0:10 0.00% > postgres > 9085 pgsql1 200 112M 10736K select 0 0:04 0.00% > postgres > 9082 pgsql1 200 112M 10648K select 0 0:04 0.00% > postgres > 9086 pgsql1 200 112M 10496K select 0 0:24 0.00% > postgres > 9088 pgsql1 200 39808K 5384K select 1 0:24 0.00% > postgres > > collectd > 9140 pgsql1 200 112M 4408K sbwait 0 0:13 0.00% > postgres > > > Machine details: Oracle Virtual Box VM - guest on Windows. > > Copyright (c) 1992-2014 The FreeBSD Project. > Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 > The Regents of the University of California. All rights reserved. > FreeBSD is a registered trademark of The FreeBSD Foundation
