[PERFORM] average query performance measuring

.) It seems like we almost have everything we need to track this in the stats tables, but not quite. I was hoping the folks on this list would have some tips on how to get query performance trends over time out of each node in my cluster. Thanks! -- Rick Otten Data-Systems Engineer rot

Re: [PERFORM] average query performance measuring

PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] average query performance measuring On 21.8.2012 20:35, Rick Otten wrote: I have a PostgreSQL 9.1 cluster. Each node is serving around 1,000 queries per second when we are at a 'steady state'. What I'd like to know

Re: [PERFORM] NEED REPLICATION SOLUTION -POSTGRES 9.1

I recommend SymmetricDS - http://www.symmetricds.org -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of suhas.basavaraj12 Sent: Wednesday, November 28, 2012 1:12 PM To: pgsql-performance@postgresql.org Subject:

Re: [PERFORM] New server setup

I not convinced about the need for BBU with SSD - you *can* use them without one, just need to make sure about suitable longevity and also the presence of (proven) power off protection (as discussed previously). It is worth noting that using unproven or SSD known to be lacking power off

Re: [PERFORM] Performance autovaccum

In our use case, the default autovacuum settings did not work, I guess we are in the 5% group of users. The default settings were too aggressive when it ran against some of our larger tables (example: 100M rows by 250 columns) in our front end OLTP database causing severe performance

Re: [PERFORM] partitioned table set and indexes

to build a reproducible test case to share - at that time I'll see if I can open it up as a real bug. For now I'd rather focus on understanding why my select uses an index and a join won't. On Fri, Dec 11, 2015 at 4:44 PM, Andreas Kretschmer <andr...@a-kretschmer.de > wrote: > > &g

Re: [PERFORM] partitioned table set and indexes

, Dec 11, 2015 at 2:44 PM, Andreas Kretschmer < akretsch...@spamfence.net> wrote: > Rick Otten <rottenwindf...@gmail.com> wrote: > > > I'm using PostgreSQL 9.5 Beta 2. > > > > I am working with a partitioned table set. > > > > The first thing

[PERFORM] partitioned table set and indexes

I'm using PostgreSQL 9.5 Beta 2. I am working with a partitioned table set. The first thing I noticed, when creating indexes on the 20 or so partitions, was that if I create them too fast they don't all succeed. I have to do a few at a time, let them breathe for a few seconds, and then do a few

Re: [PERFORM] partitioned table set and indexes

Why does it index scan when I use where, but not when I do a join? On Fri, Dec 11, 2015 at 7:20 PM, Andreas Kretschmer <andr...@a-kretschmer.de > wrote: > > > Rick Otten <rottenwindf...@gmail.com> hat am 11. Dezember 2015 um 23:09 > > geschrieben: > > > > &

Re: [PERFORM] Connections "Startup"

You can definitely overload most systems by trying to start too many connections at once. (This is actually true for most relational databases.) We used to see this scenario when we'd start a bunch web servers that used preforked apache at the same time (where each fork had its own connection).

Fwd: [PERFORM] Cloud versus buying my own iron

Having gotten used to using cloud servers over the past few years, but been a server hugger for more than 20 before that, I have to say the cloud offers a number of huge advantages that would make me seriously question whether there are very many good reasons to go back to using local iron at all.

Fwd: [PERFORM] Filesystem and Disk Partitioning for New Server Setup

1) I'd go with xfs. zfs might be a good alternative, but the last time I tried it, it was really unstable (on Linux). I may have gotten a lot better, but xfs is a safe bet and well understood. 2) An LVM is just an extra couple of commands. These days that is not a lot of complexity given what

Re: [PERFORM] Filesystem and Disk Partitioning for New Server Setup

ing another RAID10, or creating a 8 disk RAID10. > Would LVM make this type of addition easier? > > > On Wed, Feb 24, 2016 at 6:08 AM, Rick Otten <rottenwindf...@gmail.com> > wrote: > >> >> 1) I'd go with xfs. zfs might be a good alternative, but the last time I >&

Re: [PERFORM] Architectural question

I have another suggestion. How about putting the images in RethinkDB? RethinkDB is easy to set up and manage, and is scalable and easy (almost trivial) to cluster. Many of the filesystem disadvantages you mention would be much more easily managed by RethinkDB. A while back I wrote a Foreign

Re: [PERFORM] using shared_buffers during seq_scan

There is parallel sequence scanning coming in 9.6 -- http://rhaas.blogspot.com/2015/11/parallel-sequential-scan-is-committed.html And there is the GPU extension - https://wiki.postgresql.org/wiki/PGStrom If those aren't options, you'll want your table as much in memory as possible so your scan

Re: [PERFORM] Performant queries on table with many boolean columns

Would a bit string column work? -- http://www.postgresql.org/docs/9.5/static/datatype-bit.html You might need to use a lot of bitwise OR statements in the query though if you are looking at very sparse sets of specific values... Something like the get_bit() function might allow you to select a

Re: [PERFORM] Backup taking long time !!!

Actually, I think this may be the way Oracle Hot Backups work. It was my impression that feature temporarily suspends writes into a specific tablespace so you can take a snapshot of it. It has been a few years since I've had to do Oracle work though and I could be mis-remembering. People may be

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

database. > What exactly it doing ?? > > Regards, > Dinesh Chandra > > -Original Message- > From: vinny [mailto:vi...@xs4all.nl] > Sent: 27 February, 2017 7:31 PM > To: John Gorman <jgor...@eldocomp.com> > Cc: Rick Otten <rottenwindf...@gmail.com&

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

Although it doesn't really tell if the pg_dump was successful (you'll need to do a full restore to be sure), I generate an archive list. If that fails, the backup clearly wasn't successful, and if it succeeds, odds are pretty good that it worked: -- bash code snippet -- archiveList=`pg_restore

Re: [PERFORM] Disk filled-up issue after a lot of inserts and drop schema

In Unix/Linux with many of the common file system types, if you delete a file, but a process still has it open, it will continue to "own" the disk space until that process closes the file descriptor or dies. If you try "ls" or other file system commands, you won't actually see the file there, yet

Re: [PERFORM] Millions of tables

Are the tables constantly being written to, or is this a mostly read scenario? One architecture possibility, if the writes are not so frequent, is to create just a handful of very big tables for writing, and then make smaller tables as materialized views for reading. The vacuum and bloat

[PERFORM] materialized view order by and clustering

If I construct the materialized view with an 'order by', I can use a BRIN index to a sometimes significant performance advantage, at least for the primary sort field. I have observed that even though the first pass is a little lossy and I get index rechecks, it is still much faster than a regular

Re: [PERFORM] Perf decreased although server is better

> Rick, what did you mean by kernel configuration? The OS is a standard Ubuntu 16.04: > > - Linux 4.4.0-45-generic #66-Ubuntu SMP Wed Oct 19 14:12:37 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux > > Do you think losing half the number of cores can explain my performance issue ? (AMD 8 cores down to

Re: [PERFORM] Perf decreased although server is better

How did you migrate from one system to the other? [ I recently moved a large time series table from 9.5.4 to 9.6.1 using dump and restore. Although it put the BRIN index on the time column back on, it was borked. Reindexing didn't help. I had to switch it to a regular btree index. I think the

Re: [PERFORM] Sort-of replication for reporting purposes

I suggest SymmetricDS. ( http://symmetricds.org ) I've had good luck using them to aggregate data from a heterogeneous suite of database systems and versions back to a single back-end data mart for exactly this purpose. On Fri, Jan 6, 2017 at 2:24 PM, Ivan Voras wrote: >

Re: [PERFORM] Impact of track_activity_query_size on high traffic OLTP system

I always bump it up, but usually just to 4096, because I often have queries that are longer than 1024 and I'd like to be able to see the full query. I've never seen any significant memory impact. I suppose if you had thousands of concurrent queries it would add up, but if you only have a few

Re: [PERFORM] Delete, foreign key, index usage

On Wed, Apr 5, 2017 at 6:40 AM, Johann Spies wrote: > On 4 April 2017 at 14:07, Johann Spies wrote: > > > Why would that be? > > To answer my own question. After experimenting a lot we found that > 9.6 uses a parallel seqscan that is actually a

Re: [PERFORM] Filter certain range of IP address.

On Fri, Apr 7, 2017 at 11:29 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108 < > dinesh.chan...@cyient.com> wrote: > >> Dear Vinny, >> >> Thanks for your valuable replay. >> >> but I need a select query, which select only that

Re: [PERFORM] performance problem on big tables

Moving that many gigs of data across your network could also take a long time simply depending on your network configuration. Before spending a huge amount of energy tuning postgresql, I'd probably look at how long it takes to simply copy 20 or 30 G of data between the two machines. > El 14

Re: [PERFORM] Very poor read performance, query independent

Although probably not the root cause, at the least I would set up hugepages ( https://www.postgresql.org/docs/9.6/static/kernel-resources.html#LINUX-HUGE-PAGES ), and bump effective_io_concurrency up quite a bit as well (256 ?). On Mon, Jul 10, 2017 at 10:03 AM, Charles Nadeau

Re: [PERFORM] Very poor read performance, query independent

o_concurrency may be much higher than the actual number of spindles. It is worth experimenting with. If you can, try several values. You can use pg_bench to put consistent workloads on your database for measurement purposes. Charles > > On Mon, Jul 10, 2017 at 5:25 PM, Rick Otten <ro

[PERFORM] partitioning materialized views

I'm pondering approaches to partitioning large materialized views and was hoping for some feedback and thoughts on it from the [perform] minds. PostgreSQL 9.6.3 on Ubuntu 16.04 in the Google Cloud. I have a foreign table with 250M or so rows and 50 or so columns, with a UUID as the primary key.

Re: [PERFORM] partitioning materialized views

On Thu, Jul 6, 2017 at 11:25 AM, Shaun Thomas wrote: > > I'm curious if I'm overlooking other possible architectures or tools > that might make this simpler to manage. > > One of the issues with materialized views is that they are based on > views... For a

Re: [PERFORM] partitioning materialized views

> > > If you _can't_ do >> that due to cloud restrictions, you'd actually be better off doing an >> atomic swap. >> >> CREATE MATERIALIZED VIEW y AS ...; >> >> BEGIN; >> ALTER MATERIALIZED VIEW x RENAME TO x_old; >> ALTER MATERIALIZED VIEW y RENAME TO x; >> DROP MATERIALIZED VIEW x_old; >> COMMIT;

Re: [PERFORM] Log update query along with username who has executed the same.

You need to include "%u" in the log_line_prefix setting in your postgresql.conf. Like this: log_line_prefix = '%m %d %h %u ' > > #log_line_prefix = '' # special values: > > # %a = application name > > # %u = user name > >

Re: [PERFORM] pg_stat_statements with fetch

Would turning on logging of temp files help? That often reports the query that is using the temp files: log_temp_files = 0 It probably wouldn't help if the cursor query never pulls from a temp file, but if it does ... On Fri, May 19, 2017 at 7:04 PM, Jeff Janes wrote: >

Re: [PERFORM] Client Server performance & UDS

You should have a layer such as pgbouncer between your pg instance and your application. It is designed to mitigate the access latency issues you describe. On May 26, 2017 10:03 AM, "kevin.hug...@uk.fujitsu.com" < kevin.hug...@uk.fujitsu.com> wrote: > Hi, > > > > This is a

Re: [PERFORM] Monitoring tool for Postgres Database

On Thu, May 25, 2017 at 3:48 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

Re: [PERFORM] Client Server performance & UDS

and the server. If I use connection pooling surely this > introduced latency – getting a server from the pool establishing the > connection? > > > > Am I missing something? > > > > > > *From:* Rick Otten [mailto:rottenwindf...@gmail.com] > *Sent:* 27 May 2017 13:27 &g

[PERFORM] max partitions behind a view?

I use materialized views to cache results from a foreign data wrapper to a high latency, fairly large (cloud) Hadoop instance. In order to boost refresh times I split the FDW and materialized views up into partitions. Note: I can't use pg_partman or native partitioning because those don't

Re: [PERFORM] synchronization between PostgreSQL and Oracle

On Thu, Oct 12, 2017 at 5:13 AM, ROS Didier wrote: > Hi > >I would like your advice and recommendation about the > following infrastructure problem : > > What is the best way to optimize synchronization between an instance > PostgreSQL on Windows 7 workstation