Обсуждение: Postgres 9.3 beta log
Hello and namaste,
I have installed the latest version an running tests on it.
Since i analyze logs , i have found three new entries in log for each insert statement
which isn't present in postgres 9.0 log
---
2013-08-10 11:59:27.031 IST,"postgres","aeppos",2780,"192.168.10.19:50935",5205dd5b.adc,352787,"idle",2013-08-10 11:57:39 IST,5/129358,0,LOG,00000,"PARSER STATISTICS","! system usage stats:
! 0.000000 elapsed 0.000000 user 0.000000 system sec
! [43.227877 user 19.250523 sys total]",,,,,"insert into loginmaster (loginid,fullname,empid,passwd) values ( 'login88193', 'fullname88193', 'empid88193', 'passwd88193')",,,""
2013-08-10 11:59:27.032 IST,"postgres","aeppos",2780,"192.168.10.19:50935",5205dd5b.adc,352788,"INSERT",2013-08-10 11:57:39 IST,5/129358,0,LOG,00000,"PARSE ANALYSIS STATISTICS","! system usage stats:
! 0.000000 elapsed 0.000000 user 0.000000 system sec
! [43.227877 user 19.250523 sys total]",,,,,"insert into loginmaster (loginid,fullname,empid,passwd) values ( 'login88193', 'fullname88193', 'empid88193', 'passwd88193')",,,""
2013-08-10 11:59:27.032 IST,"postgres","aeppos",2780,"192.168.10.19:50935",5205dd5b.adc,352789,"INSERT",2013-08-10 11:57:39 IST,5/129358,0,LOG,00000,"REWRITER STATISTICS","! system usage stats:
! 0.000000 elapsed 0.000000 user 0.000000 system sec
! [43.227877 user 19.250523 sys total]",,,,,"insert into loginmaster (loginid,fullname,empid,passwd) values ( 'login88193', 'fullname88193', 'empid88193', 'passwd88193')",,,""
---
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
thanking you
greetings from india
arvind
I have installed the latest version an running tests on it.
Since i analyze logs , i have found three new entries in log for each insert statement
which isn't present in postgres 9.0 log
---
2013-08-10 11:59:27.031 IST,"postgres","aeppos",2780,"192.168.10.19:50935",5205dd5b.adc,352787,"idle",2013-08-10 11:57:39 IST,5/129358,0,LOG,00000,"PARSER STATISTICS","! system usage stats:
! 0.000000 elapsed 0.000000 user 0.000000 system sec
! [43.227877 user 19.250523 sys total]",,,,,"insert into loginmaster (loginid,fullname,empid,passwd) values ( 'login88193', 'fullname88193', 'empid88193', 'passwd88193')",,,""
2013-08-10 11:59:27.032 IST,"postgres","aeppos",2780,"192.168.10.19:50935",5205dd5b.adc,352788,"INSERT",2013-08-10 11:57:39 IST,5/129358,0,LOG,00000,"PARSE ANALYSIS STATISTICS","! system usage stats:
! 0.000000 elapsed 0.000000 user 0.000000 system sec
! [43.227877 user 19.250523 sys total]",,,,,"insert into loginmaster (loginid,fullname,empid,passwd) values ( 'login88193', 'fullname88193', 'empid88193', 'passwd88193')",,,""
2013-08-10 11:59:27.032 IST,"postgres","aeppos",2780,"192.168.10.19:50935",5205dd5b.adc,352789,"INSERT",2013-08-10 11:57:39 IST,5/129358,0,LOG,00000,"REWRITER STATISTICS","! system usage stats:
! 0.000000 elapsed 0.000000 user 0.000000 system sec
! [43.227877 user 19.250523 sys total]",,,,,"insert into loginmaster (loginid,fullname,empid,passwd) values ( 'login88193', 'fullname88193', 'empid88193', 'passwd88193')",,,""
---
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
thanking you
greetings from india
arvind
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. +
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
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 areby the replica. From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replicationlag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-onlyqueries, can you please suggest a way to monitor and failover the read-only query when the replication lag in areplica is more than 5 minutes? regards
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. +
replication server: LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376
От
"ascot.moss@gmail.com"
Дата:
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
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
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
2013/8/11 ascot.moss@gmail.com <ascot.moss@gmail.com>
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
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
2013/8/11 ascot.moss@gmail.com <ascot.moss@gmail.com>
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
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 areby the replica. > > From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replicationlag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-onlyqueries, can you please suggest a way to monitor and failover the read-only query when the replication lag in areplica 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
Re: replication server: LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376
От
"ascot.moss@gmail.com"
Дата:
Thanks so much.
On 11 Aug 2013, at 7:36 PM, Jov wrote:
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.Jovblog: http:amutu.com/blog2013/8/11 ascot.moss@gmail.com <ascot.moss@gmail.com>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
> 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 onesare by the replica. >> >> From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replicationlag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-onlyqueries, can you please suggest a way to monitor and failover the read-only query when the replication lag in areplica 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
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 onesare by the replica. >> >> From time to time the replica itself is too busy, all read-only queries will get inconsistent results because of replicationlag, sometimes it can be longer than 6 minutes. I am thinking to add multiple replicas to off-load read-onlyqueries, can you please suggest a way to monitor and failover the read-only query when the replication lag in areplica 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