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 and an Oracle 11gR2
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
really
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 ago
On Wed, Jul 12, 2017 at 9:38 AM, Charles Nadeau
wrote:
> Rick,
>
> Should the number of page should always be correlated to the VmPeak of the
> postmaster or could it be set to reflect shared_buffer or another setting?
> Thanks!
>
>
The documentation implies that you may ne
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
wrote:
> I’m run
>
>
> 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;
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 concurrent update, it essentially perfor
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.
look at WAL and checkpoint tuning.
On Tue, May 30, 2017 at 3:34 AM, kevin.hug...@uk.fujitsu.com <
kevin.hug...@uk.fujitsu.com> wrote:
> Hi Rick thanks for the reply.
>
>
>
> Our aim is to minimise latency hence we have a dedicated 1:1 relationship
> between the cl
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 genera
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 bo
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
>
> #
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:
> I'm spoiled by using pg
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
doze
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 recor
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 lot faster than using
> the index on these large t
COPY
> 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
> Cc: Rick Otten ; Dinesh Chandra 12108 <
> dinesh.chan...@cyient.c
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 -l
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
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:
> Hello,
>
> I'm inves
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
> 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
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
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 manage
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
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 sp
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 Dat
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 d
should go where.
Sometimes it is really nice to have that option.
On Wed, Feb 24, 2016 at 9:25 AM, Dave Stibrany wrote:
> Thanks for the advice, Rick.
>
> I have an 8 disk chassis, so possible extension paths down the line are
> adding raid1 for WALs, adding another RAID10, or cr
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 y
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.
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).
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 wrote:
>
> > Rick Otten hat am 11. Dezember 2015 um 23:09
> > geschrieben:
>
> >
> > The query performance hit for sequence scanning isn&#x
ought to be able 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 wrote:
>
>
&g
e couple
with only a few rows), and doesn't sequence scan for the mypk column at all.
On Fri, Dec 11, 2015 at 2:44 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Rick Otten wrote:
>
> > I'm using PostgreSQL 9.5 Beta 2.
> >
> > I am working
milar question to this list back in January,
however I didn't see the answer.
What should I look at to try to figure out why a join doesn't use the
indexes while a straight query on the same column for the table does?
FWIW, the column in question is a UUID column and is the primary key for
each of the child tables.
--
Rick.
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 degradati
>> 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 po
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: [PE
5:08 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'.
>
> W
still have to parse the logs to get the
data.)
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.
Tha
On Apr 22, 2:55 pm, robertmh...@gmail.com (Robert Haas) wrote:
> On Wed, Apr 21, 2010 at 11:06 AM, Rick wrote:
> > I have a DB with small and large tables that can go up to 15G.
> > For performance benefits, it appears that analyze has much less cost
> > than vacuum,
I have a DB with small and large tables that can go up to 15G.
For performance benefits, it appears that analyze has much less cost
than vacuum, but the same benefits?
I can’t find any clear recommendations for frequencies and am
considering these parameters:
Autovacuum_vacuum_threshold = 5
Au
On a linux box (Linux db1 2.6.18.8-md #1 SMP Wed May 23 17:21:37 EDT
2007 i686 GNU/Linux)
I edited postgresql.conf and changed:
shared_buffers = 5000 work_mem = 16384
max_stack_depth = 4096
and then restarted postgres. The puzzling part is that postgres
actually started. When I hav
Based on a suggestion on the postgis list, I partitioned my 80 million (for
now) record table into
subtables of about 230k records (the amount of data collected in five
minutes). At the moment
I have 350 subtables.
Everything seems to be great…COPY time is ok, building a
geometric in
I installed another drive in my linux pc in an attempt to
improve performance
on a large COPY to a table with a geometry index.
Based on previous discussion, it seems there are three
things competing for the hard
drive:
1)
the input data file
2)
the pg table
3)
once the table has,
say, 50 million rows.
> -Original Message-
> From: Luke Lonergan [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 01, 2005 9:27 PM
> To: Rick Schumeyer; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] COPY into table too slow with index: n
keep the input data on a separate drive
from my pg tables? If so, some pointers on the best way to set that up
would be appreciated.
Please let me know if anyone has
additional ideas.
-Original Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Rick
I’m running postgresql 8.1.0 with postgis 1.0.4 on a
FC3 system, 3Ghz, 1 GB memory.
I am using COPY to fill a table that contains one postgis
geometry column.
With no geometry index, it takes about 45 seconds to COPY
one file.
If I add a geometry index, this time degrades. It
k
rows. Did you run 'vacuum analyze' ?
I see a big discrepancy between estimated rows (8041) and actual rows.
Yes, I did a vacuum analyze right before executing these queries.
I'm going to recreate the gist index now, and do a vacuum full analyze
after that.. see if that makes
\''::tsquery)
Total runtime: 48863.874 ms
(3 rows)
I dont know what happened, these queries were a lot faster 2 days
ago..what the feck is going on?!
Rick
--
Systems Administrator for Rockingstone IT
http://www.rockingstone.com
http://www.megabooksearch.com - Search many b
Oleg Bartunov wrote:
On Tue, 22 Mar 2005, Rick Jansen wrote:
Hmm, default configuration is too eager, you index every lexem using
simple dictionary) ! Probably, it's too much. Here is what I have for my
russian configuration in dictionary database:
default_russian | lword| {en_i
Oleg Bartunov wrote:
Mike,
no comments before Rick post tsearch configs and increased buffers !
Union shouldn't be faster than (term1|term2).
tsearch2 internals description might help you understanding tsearch2
limitations.
See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_inte
t sounds very promising, I'd love to get those results.. could you
tell me what your settings are, howmuch memory you have and such? Thanks.
Rick
--
Systems Administrator for Rockingstone IT
http://www.rockingstone.com
http://www.megabooksearch.com - Search many book listing sites at once
-
7; on our MySQL search
takes 5.8 seconds and returns 375 results. The same query on postgresql
using the tsearch2 index takes 30802.105 ms and returns 298 results.
How do I speed this up? Should I change settings, add or change indexes
or.. what?
Rick Jansen
--
Systems Administrator for
That makes a lot of sense. Sure enough, if I change the query from
WHERE x > 0 (which return a lot of rows) to
WHERE x > 0 AND x < 1
I now get an index scan.
> As for why you see index usage in your first example query and not your
> second: compare the number of rows in question. An index is e
I have two index questions. The first is about an
issue that has been recently discussed,
and I just wanted to be sure of my understanding.
Functions like count(), max(), etc. will
use sequential scans instead of index scans because the
index doesn’t know which rows
are actually visibl
57 matches
Mail list logo
