Re: [HACKERS] WARNING : pgstat wait timeout - Postgres 9.1
About the stats_temp_directory, I didn't run as root... Now I'm sure the configurations are correct. I think, I have too much IO to use stats. I will ever have this message... Maybe I can disable this option. Do you know what it really impact ? Thanks. Math 2013/5/24 Mathieu Guerin > Hello, > > Thanks a lot for your answers. > > > > You should get it... > > stats_temp_directory | > pg_stat_tmp | Writes temporary > statistics files to the specified directory. > > I don't know why i don't get it. I am in 9.1 version... > > Moreover, when I mount pg_stat_tmp in a tmpfs, the warning messages > decrease the warning messages decrease from 1 each minutes to 1 each five > secondes. I don't have any others logs warning but the file pg_stat.stat in > the mounting point is not created... I tryed before on a test environment > and it works... > > If you have any ideas... > Thanks a lot. > Regards, > Math > > > > 2013/5/24 Michael Paquier > >> >> >> >> On Thu, May 23, 2013 at 9:31 PM, Mathieu Guerin < >> mathieu.gueri...@gmail.com> wrote: >> >>> What are the consequences ? Because this file will be remove if the >>> server reboot. >>> >> Those temporary statistics are stored in global directory when server >> shuts down, so the risk here would be to lose a portion of this data in the >> case of a crash, either at PG or at OS level. >> >> >>> If we change the parameter stats_temp_directory is it necessary to >>> reboot the server ? >>> >> No, sending SIGHUP to the server is enough. >> >> >>> When I lauch a SHOW ALL; command, the parameter stats_temp_director is >>> not here. >>> >> You should get it... >> stats_temp_directory| >> pg_stat_tmp | Writes temporary >> statistics files to the specified directory. >> -- >> Michael >> > >
Re: [HACKERS] WARNING : pgstat wait timeout - Postgres 9.1
Hello, Thanks a lot for your answers. > You should get it... > stats_temp_directory | pg_stat_tmp | Writes temporary statistics files to the specified directory. I don't know why i don't get it. I am in 9.1 version... Moreover, when I mount pg_stat_tmp in a tmpfs, the warning messages decrease the warning messages decrease from 1 each minutes to 1 each five secondes. I don't have any others logs warning but the file pg_stat.stat in the mounting point is not created... I tryed before on a test environment and it works... If you have any ideas... Thanks a lot. Regards, Math 2013/5/24 Michael Paquier > > > > On Thu, May 23, 2013 at 9:31 PM, Mathieu Guerin < > mathieu.gueri...@gmail.com> wrote: > >> What are the consequences ? Because this file will be remove if the >> server reboot. >> > Those temporary statistics are stored in global directory when server > shuts down, so the risk here would be to lose a portion of this data in the > case of a crash, either at PG or at OS level. > > >> If we change the parameter stats_temp_directory is it necessary to >> reboot the server ? >> > No, sending SIGHUP to the server is enough. > > >> When I lauch a SHOW ALL; command, the parameter stats_temp_director is >> not here. >> > You should get it... > stats_temp_directory| > pg_stat_tmp | Writes temporary > statistics files to the specified directory. > -- > Michael >
Re: [HACKERS] WARNING : pgstat wait timeout - Postgres 9.1
On Thu, May 23, 2013 at 9:31 PM, Mathieu Guerin wrote: > What are the consequences ? Because this file will be remove if the server > reboot. > Those temporary statistics are stored in global directory when server shuts down, so the risk here would be to lose a portion of this data in the case of a crash, either at PG or at OS level. > If we change the parameter stats_temp_directory is it necessary to reboot > the server ? > No, sending SIGHUP to the server is enough. > When I lauch a SHOW ALL; command, the parameter stats_temp_director is > not here. > You should get it... stats_temp_directory| pg_stat_tmp | Writes temporary statistics files to the specified directory. -- Michael
Re: [HACKERS] WARNING : pgstat wait timeout - stats_temp_directory - postgres 9.1
Mathieu Guerin escribió: > Hello, > > I am facing a problem with pgstat as my subject says. I known, some topics > are open about that, but I would like to go deeper. > > Some person told that the better way to don't have this message anymore is > to configure pgstat.stat to be loaded in the RAM with a tmpfs mount point. > > What are the consequences ? Because this file will be remove if the server > reboot. There are two separate files, one is the temp file which is used while the server is running and is written very frequently. You put that one on volatile storage (stats_temp_directory) and immediately see a performance benefit. The other one is the permanent file, which is written only once when the system is shutting down. This is not put in stats_temp_directory, so it's safe. In case of a crash (the server didn't have the chance to write the permanent file), stats would be reset anyway at restart, so there's no conceptual problem with the permanent file not being written. > If we change the parameter stats_temp_directory is it necessary to reboot > the server ? No, a reload (pg_ctl reload) is sufficient. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WARNING : pgstat wait timeout - Postgres 9.1
Hello, I am facing a problem with pgstat as my subject says. I known, some topics are open about that, but I would like to go deeper. Some person told that the better way to don't have this message anymore is to configure pgstat.stat to be loaded in the RAM with a tmpfs mount point. What are the consequences ? Because this file will be remove if the server reboot. If we change the parameter stats_temp_directory is it necessary to reboot the server ? When I lauch a SHOW ALL; command, the parameter stats_temp_director is not here. For information, my pgstat.stat file is up to 1,3MB. Thank you for your help. Math
[HACKERS] WARNING : pgstat wait timeout - stats_temp_directory - postgres 9.1
Hello, I am facing a problem with pgstat as my subject says. I known, some topics are open about that, but I would like to go deeper. Some person told that the better way to don't have this message anymore is to configure pgstat.stat to be loaded in the RAM with a tmpfs mount point. What are the consequences ? Because this file will be remove if the server reboot. If we change the parameter stats_temp_directory is it necessary to reboot the server ? When I lauch a SHOW ALL; command, the parameter stats_temp_director is not here. For information, my pgstat.stat file is up to 1,3MB. Thank you for your help. Math
[HACKERS] WARNING: pgstat wait timeout
While doing some pgbench testing on a new server with 9.1.1, I noticed quite a lot of $subject in the logs. I did some Googling and found this previous thread: http://archives.postgresql.org/pgsql-hackers/2010-01/msg02897.php It doesn't seem like the issue was resolved? I did: # pgbench -i -s 1000 bench1 # pgbench -c 8 -j 4 -T 1000 bench1 The server is: PostgreSQL 9.1.1 Ubuntu 10.04 2.6.32-35-generic #78-Ubuntu SMP x86_64 Quad Core Xeon, 22GB RAM, 2x SATA RAID1 The warning seems to come in average 3-4 times a minute: 2011-12-03 23:07:31 CET::@:[13728]: WARNING: pgstat wait timeout 2011-12-03 23:07:37 CET::@:[13728]: WARNING: pgstat wait timeout 2011-12-03 23:07:47 CET::@:[13556]: WARNING: pgstat wait timeout 2011-12-03 23:07:52 CET::@:[13729]: WARNING: pgstat wait timeout Some non-default postgresql.conf settings: shared_buffers = 6GB effective_cache_size = 12GB work_mem = 16MB synchronous_commit = off checkpoint_segments = 16 checkpoint_completion_target = 0.9 autovacuum_max_workers = 6 Is this a bug, something wrong with my setup, or simply harmless noise (although annoying in that case)? Regards, -- a. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WARNING: pgstat wait timeout
2010/1/29 Greg Smith : > I just found a few of these errors in a log file during some pgbench testing > tonight. Linux, recent CVS HEAD; given the range of systems and versions > this has been reported against now, this bug doesn't look like a platform or > version/build specific issue. > > Unfortunately the instance I had up wasn't setup very well for logging, but I > did notice that all of the ones I saw had nearby messages about autovacuum > issues, which seems to match Tom's earlier suggestion at > http://archives.postgresql.org/pgsql-bugs/2009-07/msg00083.php that the > source of the warning (but not necessarily the underlying problem) for these > is the autovacuum launcher complaining; here's two different sets: > > ERROR: canceling autovacuum task > CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" > WARNING: pgstat wait timeout > WARNING: pgstat wait timeout > ERROR: canceling autovacuum task > CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" > > WARNING: pgstat wait timeout > ERROR: canceling autovacuum task > CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" > ERROR: canceling autovacuum task > CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" > > Because of what I'm (not) seeing in pg_stat_bgwriter, I'm suspicious that its > underlying work or messages may be involved here. I'm not seeing the sort of > totals I expect in that view after these large bouts of activity. Now, my > use tonight has included the new pg_stat_reset_shared('bgwriter') to clear > out those stats between runs, so perhaps that's involved too. Guessing not > only because of the reports going back to 8.4 that also have this error > message. > > Will keep an eye out for this one now that I know I might run into it, have > already cranked up the logging and will look for something reproducible to > gather more info. I've seen this happen semi-frequently during initdb on win32 on an Amazon EC2 image. I attributed it to the combination of windows and overloaded virtualization, but it may just be that it shows up more often there. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WARNING: pgstat wait timeout
I just found a few of these errors in a log file during some pgbench testing tonight. Linux, recent CVS HEAD; given the range of systems and versions this has been reported against now, this bug doesn't look like a platform or version/build specific issue. Unfortunately the instance I had up wasn't setup very well for logging, but I did notice that all of the ones I saw had nearby messages about autovacuum issues, which seems to match Tom's earlier suggestion at http://archives.postgresql.org/pgsql-bugs/2009-07/msg00083.php that the source of the warning (but not necessarily the underlying problem) for these is the autovacuum launcher complaining; here's two different sets: ERROR: canceling autovacuum task CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" WARNING: pgstat wait timeout WARNING: pgstat wait timeout ERROR: canceling autovacuum task CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" WARNING: pgstat wait timeout ERROR: canceling autovacuum task CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" ERROR: canceling autovacuum task CONTEXT: automatic analyze of table "pgbench.public.pgbench_accounts" Because of what I'm (not) seeing in pg_stat_bgwriter, I'm suspicious that its underlying work or messages may be involved here. I'm not seeing the sort of totals I expect in that view after these large bouts of activity. Now, my use tonight has included the new pg_stat_reset_shared('bgwriter') to clear out those stats between runs, so perhaps that's involved too. Guessing not only because of the reports going back to 8.4 that also have this error message. Will keep an eye out for this one now that I know I might run into it, have already cranked up the logging and will look for something reproducible to gather more info. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WARNING: pgstat wait timeout
Il 21/01/2010 03:33, Jaime Casanova ha scritto: On Wed, Jan 20, 2010 at 9:32 PM, Jaime Casanova wrote: On Wed, Jan 20, 2010 at 6:20 PM, Sergey E. Koposov wrote: Hello hackers, I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. i see the same yesterday when initdb a freshly compiled 8.5dev + lock_timeout patch i thought maybe it was related to that patch and was thinking in recompile without the patch but hadn't time, obviously i was wrong ah! i forgot to say that it was on win32 + mingw, to confirme that patch works fin in that os I've seen it a few days ago with 8.5alpha3 on NetBSD when I left the backend running for a few days. Backend was completely inactive but the massage was repeated 3-4 times. Googling didn't help and I didnt' know how to replicate it. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WARNING: pgstat wait timeout
On Wed, Jan 20, 2010 at 9:32 PM, Jaime Casanova wrote: > On Wed, Jan 20, 2010 at 6:20 PM, Sergey E. Koposov wrote: >> Hello hackers, >> >> I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. > > i see the same yesterday when initdb a freshly compiled 8.5dev + > lock_timeout patch > i thought maybe it was related to that patch and was thinking in > recompile without the patch but hadn't time, obviously i was wrong > ah! i forgot to say that it was on win32 + mingw, to confirme that patch works fin in that os -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WARNING: pgstat wait timeout
On Wed, Jan 20, 2010 at 6:20 PM, Sergey E. Koposov wrote: > Hello hackers, > > I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. i see the same yesterday when initdb a freshly compiled 8.5dev + lock_timeout patch i thought maybe it was related to that patch and was thinking in recompile without the patch but hadn't time, obviously i was wrong -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WARNING: pgstat wait timeout
Hello hackers, I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. I saw some reports about that message in the -bugs mailing list http://archives.postgresql.org/pgsql-bugs/2009-12/msg00175.php http://archives.postgresql.org/pgsql-bugs/2009-07/msg00081.php where the backtrace from the statisctic collector was requested. Although I don't have any other bad sympthoms in the system, I still obtained a backtrace from the statistics collector process. Since I'm not 100% sure that the message is really a bug, feel free to ignore. But if needed I have PG still running, so I can check something else if needed. Here is the (rather innocent IMHO) backtrace of the statistic collector process: (gdb) bt #0 0x7f31ddfc4b1f in poll () from /lib/libc.so.6 #1 0x005bf7da in PgstatCollectorMain (argc=, # argv=) at pgstat.c:2718 #2 0x005c0131 in pgstat_start () at pgstat.c:631 #3 0x005c474d in reaper (postgres_signal_arg=out>) #at postmaster.c:2322 #4 #5 0x7f31ddfc6c83 in select () from /lib/libc.so.6 #6 0x005c20fc in ServerLoop () at postmaster.c:1347 #7 0x005c34a7 in PostmasterMain (argc=3, argv=0x144fd20) at #postmaster.c:1040 #8 0x0056cdc8 in main (argc=3, argv=0x144fd20) at main.c:188 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /opt/pgsql/bin/postgres, process 24677 - Bt full: (gdb) bt full #0 0x7f31ddfc4b1f in poll () from /lib/libc.so.6 No symbol table info available. #1 0x005bf7da in PgstatCollectorMain (argc=, argv=) at pgstat.c:2718 len = 64 msg = {msg_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, msg_dummy = {m_hdr = { m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}}, msg_inquiry = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, inquiry_time = 0}, msg_tabstat = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, m_databaseid = 0, m_nentries = 0, m_xact_commit = 0, m_xact_rollback = 0, m_entry = {{t_id = 0, t_counts = { t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 138, t_tuples_inserted = 138, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 2672, t_counts = {t_numscans = 1, t_tuples_returned = 1, t_tuples_fetched = 1, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 1259, t_counts = {t_numscans = 4, t_tuples_returned = 553, t_tuples_fetched = 0, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 24, t_blocks_hit = 24}}, {t_id = 2615, t_counts = {t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 0, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 1, t_blocks_hit = 1}}, {t_id = 2685, t_counts = {t_numscans = 1, t_tuples_returned = 1, t_tuples_fetched = 1, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 1172815, t_counts = {t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 0, t_tuples_inserted = 50, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 50, t_new_dead_tuples = 0, t_blocks_fetched = 23077, t_blocks_hit = 15381}}, {t_id = 1172684, t_counts = {t_numscans = 0, t_tuples_returned = 0, msg = {msg_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, msg_dummy = {m_hdr = { m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}}, msg_inquiry = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, inquiry_time = 0}, msg_tabstat = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, m_databaseid = 0, m_nentries = 0, m_xact_commit = 0, m_xact_rollback = 0, m_entry = {{t_id = 0, t_counts = { t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 138, t_tuples_inserted = 138, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 2672, t_counts = {t_numscans = 1, t_tuples_returned = 1, t_tuples_fetched = 1, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 1259, t_count