Cost aside, are there any technical factors to consider before increasing RAM
(assuming the system can support it) ? Is there a sweet spot beyond which more
RAM does not help Postgres or actually harms ?
If my database is only couple of hundred Gigs, can I not just have RAM that big
and have re
time echo '\timing \\select * from table1 where id = 123;' | psql
I am trying to time a simple select statement from different clients located at
different places. The database is on US east-coast.
In the above query. the 'timing' will time the database time and the 'time'
command at the very s
.
From: Kevin Grittner
To: pgsql-admin@postgresql.org; A J
Sent: Tue, August 31, 2010 2:14:27 PM
Subject: Re: [ADMIN] Confused by 'timing' results
A J wrote:
> time echo '\timing \\select * from table1 where id = 123;' | psql
&g
og files are written,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'# log file name pattern,
____
From: Scott Marlowe
To: A J
Cc: Kevin Grittner ; pgsql-admin@postgresql.org
Sent: Tue, August 31, 2010 4:02:33 PM
Subject: Re: [ADMIN] Confused by 'timing&
efully to not skew the measurement being tried.
Looking for suggestions to solve this.
Thank you, AJ
From: Kevin Grittner
To: Scott Marlowe ; A J
Cc: pgsql-admin@postgresql.org
Sent: Thu, September 2, 2010 12:48:58 PM
Subject: Re: [ADMIN] Confused by &#x
files equal or larger
#log_timezone = unknown # actually, defaults to TZ environment
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
___
ation to syslog may make a difference (Kevin
mentioned even this timing is not totally immune from network effects but if
possible to measure should be very close to the query time) ?
From: Kevin Grittner
To: Scott Marlowe ; A J
Cc: pgsql-admin@postgresq
.
From: Tom Lane
To: A J
Cc: Kevin Grittner ; Scott Marlowe
; pgsql-admin@postgresql.org
Sent: Thu, September 2, 2010 3:03:33 PM
Subject: Re: [ADMIN] Confused by 'timing' results
A J writes:
> The performance as seen from the clients dropped substantially after
PostgreSQL 8.4
CentoOS 5.5
I have got WCE=0, on the drive that mounts the data directory with all its
subdirectory (including pg_log)
Maybe I should try to mount pg_log to a different drive and have write cache
enabled on that one.
From: Tom Lane
To: A J
time reasonably by
discarding
most of the network variance.
From: A J
To: Tom Lane
Cc: Kevin Grittner ; Scott Marlowe
; pgsql-admin@postgresql.org
Sent: Thu, September 2, 2010 3:21:24 PM
Subject: Re: [ADMIN] Confused by 'timing' results
Post
I am seeing some funny behavior on using both:
EXEC SQL SET AUTOCOMMIT TO ON;
and
CURSORS (EXEC SQL DECLARE ., EXEC SQL FETCH NEXT FROM .)
On autocommit on (either through above method or precompiling with -t option),
the cursor does not return any rows. On disabling autoc
That would explain it. I was neither in a transaction nor did any explicit
HOLD.
Thanks Kevin.
From: Kevin Grittner
To: pgsql-admin@postgresql.org; A J
Sent: Mon, October 4, 2010 5:20:14 PM
Subject: Re: [ADMIN] ECPG: AUTOCOMMIT and CURSORs
A J wrote:
>
During restore of a database (from a full backup), the general practice is to
rebuild the indexes. I think with pg_dump and pg_restore (or psql restore), the
only option is to rebuild the indexes. Am I right or is it possible to backup
indexes as well and restore them ?
Just exploring, to figure
Hello,
I am planning to have a postgres database for large object storage. I believe
that with version 9.0, the 'world readable' issue with pg_largeobject is
resolved and we can now control at object level- which users can read/write
what
objects.
Streaming performance is an issue and hence wi
Does anyone know of a module for postgres that can do metadata management in
postgres but actual storage of binary objects in filesystem ?
Not really using Postgres large object but want to just do the metadata
management with postgres. Example, need to store file
name/size/directory/drive/node
Hello,
I am trying to switch the master and slave roles in a test I am doing with
streaming replication in 9.1 beta.
To start with, I have one master (N1 node) and one slave (N2 node).
I stop N1 and promote N2 as primary (by touching the trigger file).
Now I wish for N1 to come back up as slave
and get the incremental
from the new primary ? Do I have to remove all the data files from the former
primary and get all the datafiles through rsync (or other similar manner) from
the new primary ?
____
From: Ray Stell
To: A J
Cc: pgsql-admin@postgresql.org
ave.
Right ?
From: Kevin Grittner
To: Ray Stell ; A J
Cc: pgsql-admin@postgresql.org
Sent: Tue, June 7, 2011 2:44:41 PM
Subject: Re: [ADMIN] Switchover of Master and Slave roles
A J wrote:
> What does it exactly mean to 'recreate a standby server'
Hello
Having a master and 2 slaves in synchronous replication mode in 9.1
The master seems to wait for only one slave to respond before considering the
transaction to be complete. I have done the setting for master to wait for all
the slaves to finish:
synchronous_standby_names = '*'
I even tri
I understand that 'cluster' performs the role of defrag (along with rewriting
in index order) in Postgres.
How frequently does one have to run cluster ? Any thumb-rules or experience ?
How do I find if my table is fragmented enough to need a cluster ?
We are still to use Postgres in production,
Couple of months back, Josh Berkus posted an issue with promotion of the
standby in Streaming replication. Subject 'Standby promotion does not work' in
the pgsql-hackers forum.
It seemed that during failover, it was not possible to repoint the rest of the
slaves to the new master. Is this resolv
On 9.1, Beta3 I set the following on master
replication_timeout = 10s # in milliseconds; 0 disables
With no slaves running, I expect a failure in about 10s. But any Insert just
hangs. Any idea ?
Thanks.
be successful, after trying for
'n' seconds. How can that be accomplished ?
Thanks.
From: Fujii Masao
To: A J
Cc: PG Admin
Sent: Friday, July 22, 2011 9:19 AM
Subject: Re: [ADMIN] replication_timeout does not seem to be working
On Fri, Jul 22, 20
Hello,
Trying to understand the mechanics of streaming replication.
I understand that the WAL SENDER on primary streams to WAL RECEIVER on standby.
Also that depending on the settings, completed WAL files on the primary are
sent to the archive directory. If needed, the standby will move the files
Does a synchronous_commit force WAL to be committed to disk or does it just
WAIT for WAL to be committed according to WAL's set frequency to write ?
i.e. if I set wal_writer_delay=200ms and set synchronous_commit=on, will each
commit wait for upto 200ms before committing ?
I am trying to see the impact of commit_delay.
Set it to 10 (microseconds), i.e. to 100ms
synchronous_commit is kept to default (i.e. ON)
Now I expect my DML statements to take atleast 100ms. But when I try to execute
a few and measure using the timing command of postgres or the time command
What are the tentative release dates of 9.1 and 9.2 versions ?
Thanks.
What is the suggested technique for cleaning up an archive location shared by
multiple standbys. Looks like pg_archivecleanup cannot be used as %r refers to
last restartpoint of only a single standby server.
Do I need to create a custom script that somehow determines the latest common
restartpoi
.
From: A J
To: PG Admin
Sent: Wednesday, August 10, 2011 12:41 PM
Subject: Cleanup of same archive used by multiple standbys
What is the suggested technique for cleaning up an archive location shared by
multiple standbys. Looks like pg_archivecleanup cannot be used as %r refers to
Couple of questions:
1. At what frequency is the archive_cleanup_command executed from the
recovery.conf file on the standby ?
2. If for some reason lets say a WAL file is missing from the archive directory
but the files after that are present. When the standby tries to restore the
missing WAL f
Hello
I am running a test to find the impact of corrupt indexes on queries. I
corrupted the index file but observed that the queries continued to perform
without issues. It was only on restart of the database that postgres realized
that the indexes are corrupt and my queries started failing (wit
What are the various ways in which I can validate that a standby (new or
existing) is caught-up with the master ? For streaming replication setup in v9.x
How does Postgres detect corruption of index data files ? What is the exact
mechanism by which it knows that the index is corrupt ? Can it happen that part
of the index is corrupt but Postgres does not realize because those specific
rows are not accessed (but other rows from that table are acces
So I assume there is no redundant information stored such as checksum to
validate the files against the stored checksums.
I don't have an active issue. Just trying to understand in detail how Postgres
behaves under index corruption.
From: Craig Ringer
To
In FTS, how do I search for partial substrings that don't form a English word.
Example, in the text: 'one hundred thirty four' I want to find the records
based on 'hun'
SELECT to_tsvector('one hundred thirty four') @@ to_tsquery('hun');
does not return anything.
Thanks.
What are the key benefits of using hstore over simple K:V storage in Postgres
where you split the key in its own column and value in its own column ?
Thank you for any inputs.
Hi,
I have a read heavy application. I would want it to read from memory as
database latency has to be in low milliseconds.
The database is not too big in size and can be fully contained in memory.
With Postgres, if I cache all the tables (by pre-emptive querying such as
select * from tables); i
37 matches
Mail list logo
