Postgres 9.3 beta log

On Sat, Aug 10, 2013 at 06:38:57AM +0000, Arvind Singh wrote:

as you can see it is for a single record "login88193" it places

PARSER STATISTICS
PARSE ANALYSIS STATISTICS
REWRITER STATISTICS

I know it appers because i have enabled log_parser_* options in log

But what i request to know is the meaning of these statistics and how usefull
is can be for a server load evaluation

You should look get the getrusage manual pages for the meaning of the
values, and perhaps this for the meaning of these stages of a query:

http://www.postgresql.org/developer/backend/

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/10/2013 04:21 PM, Bruce Momjian wrote:

On Sat, Aug 10, 2013 at 06:38:57AM +0000, Arvind Singh wrote:

as you can see it is for a single record "login88193" it places

PARSER STATISTICS
PARSE ANALYSIS STATISTICS
REWRITER STATISTICS

I know it appers because i have enabled log_parser_* options in log

But what i request to know is the meaning of these statistics and how usefull
is can be for a server load evaluation

You should look get the getrusage manual pages for the meaning of the
values, and perhaps this for the meaning of these stages of a query:

http://www.postgresql.org/developer/backend/

true - but it seems a bit dubious that all of them show basically zero
(or a constant time)

Stefan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica.

From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replication lag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-only queries, can you please suggest a way to monitor and failover the read-only query when the replication lag in a replica is more than 5 minutes?

regards

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Aug 10, 2013 at 09:52:48PM +0200, Stefan Kaltenbrunner wrote:

On 08/10/2013 04:21 PM, Bruce Momjian wrote:

On Sat, Aug 10, 2013 at 06:38:57AM +0000, Arvind Singh wrote:

as you can see it is for a single record "login88193" it places

PARSER STATISTICS
PARSE ANALYSIS STATISTICS
REWRITER STATISTICS

I know it appers because i have enabled log_parser_* options in log

But what i request to know is the meaning of these statistics and how usefull
is can be for a server load evaluation

You should look get the getrusage manual pages for the meaning of the
values, and perhaps this for the meaning of these stages of a query:

http://www.postgresql.org/developer/backend/

true - but it seems a bit dubious that all of them show basically zero
(or a constant time)

Yes, in an ideal world, the majority of time should be spent in the
executor. Those earlier stages are designed to set thing up for the
executor.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

I found 'LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376" from the replica's log:

LOG: entering standby mode
LOG: redo starts at A8/BE81B200
LOG: consistent recovery state reached at A9/4CFFFFF8
LOG: database system is ready to accept read only connections
LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376
LOG: streaming replication successfully connected to primary

Can you please advise what it means? can it be ignored?

regards

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

I want to archive WAL log files in the replication server as well, the postgresql.conf is added with following new lines, after restarting PG, the /var/pgsql/data/archive/ is still empty after 1 hour:

### new lines added today
#
# to enable the replica as Hot Standby
hot_standby = on
#
# to enable WAL archive (coped from master's conf)
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 500
wal_sync_method = fsync
fsync = on
archive_mode = on
archive_command = 'test ! -f /var/pgsql/data/archive/%f && cp %p /var/pgsql/data/archive/%f'
###

ls -la /var/pgsql/data/archive/
total 8
drwx------ 2 postgres root 4096 Aug 11 16:12 ./
drwx------ 3 postgres root 4096 Aug 10 10:59 ../

PG: 9.2.4
OS: Ubuntu 12.04

I am new to Streaming Replication, can you please advise why there is no WAL archive files found in the archive folder?

regards

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

now you can only enable WAL archive on the Master server,it will not take
effect on replication server.

I can't find the doc which explicit prohibit WAL archive on the replication
server,and there are WAL generated in the slave pg_xlog dir with
archive_status subdir,so I think this may be a bug.

Jov
blog: http:amutu.com/blog <http://amutu.com/blog&gt;

2013/8/11 ascot.moss@gmail.com <ascot.moss@gmail.com>

This means the slave meet the end of the WAL when it replay the WAL
files/records.The message level is "LOG",so it can be safely ignored.

Jov
blog: http:amutu.com/blog <http://amutu.com/blog&gt;

2013/8/11 ascot.moss@gmail.com <ascot.moss@gmail.com>

On Sun, Aug 11, 2013 at 5:51 AM, ascot.moss@gmail.com
<ascot.moss@gmail.com> wrote:

Hi,

I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica.

From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replication lag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-only queries, can you please suggest a way to monitor and failover the read-only query when the replication lag in a replica is more than 5 minutes?

I assume that you could use pgpool for that. It has some monitoring
features for replication delay and it can do read-only load balancing
among several servers. You also shouldn't need to change your
application.
Have a look at its documentation to make an opinion:
http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html

Regards,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks so much.

On 11 Aug 2013, at 7:36 PM, Jov wrote:

On Sun, Aug 11, 2013 at 5:51 AM, ascot.moss@gmail.com
<ascot.moss@gmail.com> wrote:

Hi,

I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica.

From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replication lag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-only queries, can you please suggest a way to monitor and failover the read-only query when the replication lag in a replica is more than 5 minutes?

I assume that you could use pgpool for that. It has some monitoring
features for replication delay and it can do read-only load balancing
among several servers. You also shouldn't need to change your
application.
Have a look at its documentation to make an opinion:
http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html

The url above is obsoleted. Please visit:

http://www.pgpool.net/docs/latest/pgpool-en.html
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks so much.

On 11 Aug 2013, at 9:50 PM, Michael Paquier wrote:

On Sun, Aug 11, 2013 at 5:51 AM, ascot.moss@gmail.com
<ascot.moss@gmail.com> wrote:

Hi,

I have a pair of PG servers, a master and a replica, all read-write queries are handled by the master, read-only ones are by the replica.

From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replication lag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-only queries, can you please suggest a way to monitor and failover the read-only query when the replication lag in a replica is more than 5 minutes?

I assume that you could use pgpool for that. It has some monitoring
features for replication delay and it can do read-only load balancing
among several servers. You also shouldn't need to change your
application.
Have a look at its documentation to make an opinion:
http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html

Regards,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general