Re: [PERFORM] [ADMIN] Monitoring tool for Postgres Database
On Thu, May 25, 2017 at 9:20 PM, Steven Chang <stevenchang1...@gmail.com> wrote: > Hello Scott, > > Nagios is an alert system, I guess the check_postgres module is in > use. > EM Grid is a management console whose functions are not just alert , > but also remote control, etc. > > Hello Ravi, > > From a open source standing, I propose pgcluu. But it doesn't include > remote control . > > Best Regards, > Steven > > 2017-05-26 4:00 GMT+08:00 Scott Whitney <sc...@journyx.com>: > >> Personally I push those off to my network monitor. Nagios in my case. >> >> I find a single integrated alert and escalation framework is better than >> individual tools, but that's just me. >> >> If you're using Nagios, let me know, and I can pop you several stub >> scripts to help. >> > Personally, I'm a huge fan of grafana and collectd It's definitely not a pre-packaged solution, but it's simple, easy to use and very, VERY fast. Alerts with nagios work, but, nagios is awful with history and trending, but, it beats most others for just alerts > >> On May 25, 2017 2:50 PM, Ravi Tammineni <rtammin...@partner.aligntech.com> >> wrote: >> >> Hi, >> >> >> >> What is the best monitoring tool for Postgres database? Something like >> Oracle Enterprise Manager. >> >> >> >> Specifically I am interested in tools to help: >> >> >> >> Alert DBAs to problems with both configuration and performance issues >> >> Deadlocks, Long running queries etc., >> >> Monitoring of overall system performance >> >> General performance tuning >> >> Storage/Disk latencies >> >> >> >> >> >> Thanks >> >> ravi >> >> >> >> >> Journyx, Inc. >> 7600 Burnet Road #300 >> Austin, TX 78757 >> www.journyx.com >> >> p 512.834. <(512)%20834-> >> f 512-834-8858 <(512)%20834-8858> >> >> Do you receive our promotional emails? You can subscribe or unsubscribe >> to those emails at http://go.journyx.com/emailPreference/e/4932/714/ >> > > -- -- Scott Mead Sr. Architect *OpenSCG <http://openscg.com>* http://openscg.com
Re: [PERFORM] PostgreSQL with Zabbix - problem of newbe
The OP is using: autovacuum_vacuum_threshold | 10 That means that vacuum won't consider a table to be 'vacuum-able' until after 100k changes that's nowhere near aggressive enough. Probably what's happening is that when autovacuum finally DOES start on a table, it just takes forever. --Scott 2010/4/9 Merlin Moncure mmonc...@gmail.com 2010/4/9 Greg Smith g...@2ndquadrant.com: Merlin Moncure wrote: postgresql 8.2: autovacuum enabled by default postgresql 8.3: HOT (reduces update penalty -- zabbix does a lot of updates) autovacuum wasn't enabled by default until 8.3. It didn't really work all that well out of the box until the support for multiple workers was added in that version, along with some tweaking to its default parameters. There's also a lot more logging information available, both the server logs and the statistics tables, to watch what it's doing that were added in 8.3. you're right! iirc it was changed at the last minute... merlin -- 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] Got that new server, now it's time for config!
On Tue, Mar 23, 2010 at 12:12 AM, Greg Smith g...@2ndquadrant.com wrote: Carlo Stonebanks wrote: So, we have the hardware, we have the O/S - but I think our config leaves much to be desired. Typically, our planner makes nad decisions, picking seq scan over index scan, where index scan has a better result. You're not setting effective_cache_size, so I wouldn't expect it to ever choose an index scan given the size of your data set. The planner thinks that anything bigger than 128MB isn't likely to fit in RAM by default, which favors sequential scans. That parameter should probably be 24GB on your server, so it's off by more than two orders of magnitude. +1 I'm curious why you've set: log_min_error_statement = debug1 log_min_messages = debug1 client_min_messages = debug1 Although not directly addressing the problem of using index scans, this is going to be causing lots of message verbosity, possibly (based on your rate) enough to clobber the disks more than you need to. -Scott M wal_sync_method = open_sync This is a scary setting to be playing with on Linux when using ext3 filesystems due to general kernel bugginess in this area. See http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php for an example. I wouldn't change this from the default in your position if using that filesystem. I'd drastically increase effective_cache_size, put wal_sync_method back to the default, and then see how things go for a bit before tweaking anything else. Nothing else jumped out as bad in your configuration besides the extremely high logging levels, haven't looked at it that carefully yet though. -- 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] 8.4.1 ubuntu karmic slow createdb
On Fri, Dec 11, 2009 at 4:39 PM, Nikolas Everett nik9...@gmail.com wrote: Fair enough. I'm of the opinion that developers need to have their unit tests run fast. If they aren't fast then your just not going to test as much as you should. If your unit tests *have* to createdb then you have to do whatever you have to do to get it fast. It'd probably be better if unit tests don't create databases or alter tables at all though. Regardless of what is going on on your dev box you really should leave fsync on on your continuous integration, integration test, and QA machines. They're what your really modeling your production on anyway. The other common issue is that developers running with something like 'fsync=off' means that they have completely unrealistic expectations of the performance surrounding something. If your developers see that when fsync is on, createdb takes x seconds vs. when it's off, then they'll know that basing their entire process on that probably isn't a good idea. When developers think something is lightning, they tend to base lots of stuff on it, whether it's production ready or not. --Scott
Re: [PERFORM] Load experimentation
On Mon, Dec 7, 2009 at 1:12 PM, Ben Brehmer benbreh...@gmail.com wrote: Hello All, I'm in the process of loading a massive amount of data (500 GB). After some initial timings, I'm looking at 260 hours to load the entire 500GB. 10 days seems like an awfully long time so I'm searching for ways to speed this up. The load is happening in the Amazon cloud (EC2), on a m1.large instance: -7.5 GB memory -4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each) -64-bit platform So far I have modified my postgresql.conf file (PostgreSQL 8.1.3). The modifications I have made are as follows: Can you go with PG 8.4? That's a start :-) shared_buffers = 786432 work_mem = 10240 maintenance_work_mem = 6291456 max_fsm_pages = 300 wal_buffers = 2048 checkpoint_segments = 200 checkpoint_timeout = 300 checkpoint_warning = 30 autovacuum = off I'd set fsync=off for the load, I'd also make sure that you're using the COPY command (on the server side) to do the load.
Re: [PERFORM] query planning different in plpgsql?
On Fri, Oct 23, 2009 at 11:38 AM, Michal J. Kubski michal.kub...@cdt.plwrote: Hi, Is there any way to get the query plan of the query run in the stored procedure? I am running the following one and it takes 10 minutes in the procedure when it is pretty fast standalone. Any ideas would be welcome! # EXPLAIN ANALYZE SELECT m.domain_id, nsr_id FROM nsr_meta m, last_snapshot l WHERE m.domain_id = l.domain_id; QUERY PLAN Nested Loop (cost=0.00..562432.32 rows=12227848 width=16) (actual time=1430.034..7476.081 rows=294418 loops=1) - Seq Scan on last_snapshot l (cost=0.00..3983.68 rows=60768 width=8) (actual time=0.010..57.304 rows=60641 loops=1) - Index Scan using idx_nsr_meta_domain_id on nsr_meta m (cost=0.00..6.68 rows=201 width=16) (actual time=0.111..0.115 rows=5 loops=60641) Index Cond: (m.domain_id = l.domain_id) Total runtime: 7635.625 ms (5 rows) Time: 7646.243 ms Do you not have an index on last_snapshot.domain_id? --Scott
Re: [PERFORM] cluster index on a table
On Wed, Jul 15, 2009 at 10:36 PM, Scott Marlowe scott.marl...@gmail.comwrote: I'd love to see it. +1 for index organized tables --Scott
Re: [PERFORM] Performance comparison between Postgres and Greenplum
On Wed, Jul 15, 2009 at 9:18 AM, Alex Goncharov alex-goncha...@comcast.netwrote: ,--- You/Suvankar (Wed, 15 Jul 2009 18:32:12 +0530) * | Yes, I have got 2 segments and a master host. So, in a way processing | should be faster in Greenplum. No, it should not: it all depends on your data, SQL statements and setup. In my own experiments, with small amounts of stored data, PostgreSQL beats Greenplum, which doesn't surprise me a bit. Agreed. You're only operating on 99,000 rows. That isn't really enough rows to exercise the architecture of shared-nothing clusters. Now, I don't know greenplum very well, but I am familiar with another warehousing product with approximately the same architecture behind it. From all the testing I've done, you need to get into the 50 million plus row range before the architecture starts to be really effective. 99,000 rows probably fits completely into memory on the machine that you're testing PG with, so your test really isn't fair. On one PG box, you're just doing memory reads, and maybe some high-speed disk access, on the Greenplum setup, you've got network overhead on top of all that. Bottom line: You need to do a test with a number of rows that won't fit into memory, and won't be very quickly scanned from disk into memory. You need a LOT of data. --Scott
Re: [PERFORM] Terrible Write Performance of a Stored Procedure
You're right that it should be removed, but this explanation is wrong. The behavior as configured is actually if there are =100 other transactions in progress, wait 0.1 second before committing after the first one gets committed, in hopes that one of the other 100 might also join along in the disk write. Thanks for the correction. My question is how you're getting .1 seconds from his commit_delay? if (CommitDelay 0 enableFsync CountActiveBackends() = CommitSiblings) pg_usleep(CommitDelay); Wouldn't this actually be 1 second based on a commit_delay of 10? Since in this case max_connections it set to 100, it's actually impossible for the commit_delay/commit_siblings behavior to trigger give this configuration. That's one reason it should be removed. The other is that i general, if you don't exactly what you're doing, you shouldn't be touching either parameters; they don't do what people expect them to and it's extremely unlikely you'll encounter any of the rare use cases where they might help. After looking, I agree, thanks again for the correction Greg. --Scott
Re: [PERFORM] Terrible Write Performance of a Stored Procedure
sales_rank = isales_rank WHERE asin = iasin; END IF; IF ioffers IS NOT NULL THEN UPDATE amazon_items SET offers = crunch(ioffers), offers_last_updated = CURRENT_TIMESTAMP, offer_pages = ioffer_pages WHERE asin = iasin; END IF; END; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION crunch(text) RETURNS text AS $$ BEGIN RETURN encode(text2bytea($1), 'base64'); END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION text2bytea(text) RETURNS bytea AS $$ BEGIN RETURN $1; END; $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; Thanks, Brian -- Sent via pgsql-performance mailing list ( pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent from my mobile device -- Scott Mead Sr. Systems Engineer EnterpriseDB scott.m...@enterprisedb.com C: 607 765 1395 www.enterprisedb.com
Re: [PERFORM] Censorship
On Wed, Jun 10, 2009 at 9:39 AM, Matthew Wakeling matt...@flymine.orgwrote: On Wed, 10 Jun 2009, Gurjeet Singh wrote: There is a limit on the size of the mail that you can send to different mailing lists. Please try to remove/link your attachments if you are trying to send any. No, size is not an issue - it's only 3kB. Are you getting a bounce message? They usually have the reason in there. --Scott Matthew -- Q: What's the difference between ignorance and apathy? A: I don't know, and I don't care. -- 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] Scalability in postgres
2009/5/29 Greg Smith gsm...@gregsmith.com On Fri, 29 May 2009, Grzegorz Ja?kiewicz wrote: if it is implemented somewhere else better, shouldn't that make it obvious that postgresql should solve it internally ? Opening a database connection has some overhead to it that can't go away without losing *something* in the process that you want the database to handle. That something usually impacts either security or crash-safety. This is why every serious database product in the world suggests using connection pooling; examples: http://blogs.oracle.com/opal/2006/10/oracle_announces_new_connectio.html http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/conn/c0006170.htm http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html Exactly, here's the thing, if you have an open transaction somewhere to the system, there may be a REALLY good reason for it. If you're app or dev team is keeping those open, it's very possible that 'reaping' them is going to cause some kind of data integrity issue in your database. I would investigate the application and make sure that everything is actually rolling back or commiting. If you're using an ORM, make sure that it's using autocommit, this usually makes the issue go away. As to the context switching point -- A connection pooler is what you need. Why make your database server dedicate cycles to having to figure out who gets on the CPU next? Why not lower the number of connections, and let a connection pool decide what to use. That usually helps with your open transactions too (if they indeed are just abandoned by the application). The only difference here is that some of the commercial products bundle the connection pooler into the main program. In most cases, you're still stuck with configuring a second piece of software, the only difference is that said software might already be installed for you by the big DB installer. Since this project isn't in the business of bundling every piece of additional software that might be useful with the database, it's never going to make it into the core code when it works quite happily outside of it. The best you could hope for is that people who bundle large chunks of other stuff along with their PostgreSQL installer, like Enterprise DB does, might include one of the popular poolers one day. This sounds like a dirty plug (sorry sorry sorry, it's for informative purposes only)... Open Source: One-Click installers :No connection pool bundled (will be included in 8.4 one-click installers) PostgresPlus Standard Edition : pgBouncer is bundled Proprietary: PostgresPlus Advanced Server: pgBouncer is bundled That being said, the well known connection pools for postgres are pretty small and easy to download / build / configure and get up and running. https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer http://pgfoundry.org/projects/pgpool/ --Scott
Re: [PERFORM] Unexpected query plan results
On Fri, May 29, 2009 at 1:30 PM, Dave Dutcher d...@tridecap.com wrote: From: Anne Rosset Subject: Re: [PERFORM] Unexpected query plan results Thank Dave. We are using postgresql-server-8.2.4-1PGDG and have work-mem set to 20MB. What value would you advise? thanks, Anne Work-mem is kind of tricky because the right setting depends on how much ram your machine has, is the machine dedicated to postgres, and how many simultaneous connections you have. If this is a test server, and not used in production, you could just play around with the setting and see if your query gets any faster. Right, the trick to remember is that you could possibly end up in a scenario where you have max_connections * work_mem being used just for sorting / joins and the rest of your memory will be swapped, so be careful not to push too high. Also, work_mem is not going to be fully allocated at fork time, it'll only use up to that much as needed. --Scott Here are the docs on work mem http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html# RUNTIME-CONFIG-RESOURCE-MEMORY -- 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] Scalability in postgres
On Thu, May 28, 2009 at 4:53 PM, Fabrix fabrix...@gmail.com wrote: Wow, that's some serious context-switching right there - 300k context switches a second mean that the processors are spending a lot of their time fighting for CPU time instead of doing any real work. There is a bug in the quad core chips during a massive amount of connections that will cause all cores to go to 100% utilization and no work be done. I'm digging to find links, but if I remember correctly, the only way to fix it was to disable the 4th core in linux (involved some black magic in /proc). You really need to lower the number of processes you're forcing each processor bus to switch through (or switch to AMD's hyper-transport bus). It appears that you have the server configured with a very high number of connections as well? My first suggestion would be to look at a way to limit the number of active connections to the server at a time (pgPool or similar). yes, i have max_connections = 5000 can lower, but at least i need 3500 connections Typically, it's a bad idea to run run with anything over 1000 connections (many will suggest lower than that). If you need that many connections, you'll want to look at a connection pool like pgBouncer or pgPool. --Scott
Re: [PERFORM] Hosted servers with good DB disk performance?
On Tue, May 26, 2009 at 7:58 PM, Dave Page dp...@pgadmin.org wrote: On 5/26/09, Greg Smith gsm...@gregsmith.com wrote: I keep falling into situations where it would be nice to host a server somewhere else. Virtual host solutions and the mysterious cloud are no good for the ones I run into though, as disk performance is important for all the applications I have to deal with. What I'd love to have is a way to rent a fairly serious piece of dedicated hardware, ideally with multiple (at least 4) hard drives in a RAID configuration and a battery-backed write cache. The cache is negotiable. Linux would be preferred, FreeBSD or Solaris would also work; not Windows though (see good DB performance). Is anyone aware of a company that offers such a thing? www.contegix.com offer just about the best support I've come across and are familiar with Postgres. They offer RHEL (and windows) managed servers on a variety of boxes. They're not a budget outfit though, but that's reflected in the service. +1 These guys have the servers AND they have the knowledge to really back it up. If you're looking for co-lo, or complete hands-off management, they're your guys (at a price). --Scott
Re: [PERFORM] Postgres Clustering
On Wed, May 27, 2009 at 1:57 PM, Alan McKay alan.mc...@gmail.com wrote: Hey folks, I have done some googling and found a few things on the matter. But am looking for some suggestions from the experts out there. Got any good pointers for reading material to help me get up to speed on PostgreSQL clustering? What options are available? What are the issues? Terminology. I'm pretty new to the whole data-warehouse thing. And once I do all the reading, I'll even be open to product recommendations :-) And in particular since I already have heard of this particular product - are there any opinions on Continuent? What's your specific use case? Different types of clustering behave differently depending on what you're trying to do. If you're looking to parallelize large BI type queries something like GridSQL or PGPool may make sense. If you're looking for more of an OLTP solution, or multi-master replication, pgCluster will make more sense. --Scott
Re: [PERFORM] Slow select performance despite seemingly reasonable query plan
On Thu, May 7, 2009 at 10:14 AM, David Brain dbr...@bandwidth.com wrote: Hi, Some context, we have a _lot_ of data, 1TB, mostly in 1 'table' - the 'datatable' in the example below although in order to improve performance this table is partitioned (by date range) into a number of partition tables. Each partition contains up to 20GB of data (tens of millons of rows), with an additional ~3GB of indexes, all this is served off a fairly high performance server (8 core 32Gb, with FC attached SAN storage). PostgreSQL version is 8.3.5 (running on 64bit RHEL 5.2) This has been working reasonably well, however in the last few days I've been seeing extremely slow performance on what are essentially fairly simple 'index hitting' selects on this data. Have you re-indexed any of your partitioned tables? If you're index is fragmented, you'll be incurring extra I/O's per index access. Take a look at the pgstattuple contrib for some functions to determine index fragmentation. You can also take a look at the pg_stat_all_indexes tables. If your number of tup's fetched is 100 x more than your idx_scans, you *may* consider reindexing. --Scott
