Re: [GENERAL] autovacumm not working ?
Mathew, For frequently updated tables I set vac_scale_factor to 0.01. I also changed cost_delay from 150 to 10 Now it seems to work much better. Autovacuum is executed much quicker and still doesn't influence regular operations very much (and that was my concern when it was set to 0). I guess that cost_delay = 150 was much too high and was probably the main reason of my problems. One of the frequently updated tables was much bigger then others : about 400.000 records and vacuuming that table was really taking a lot of time, so from time to time there was bigger delay between vacuuming of other tables. If problems will continue probably I will turn off autovacum for this bigger table and will vacuum it from cron. The rest of tables I will leave for autovacuum (updated much more frequently but much smaller in size: around 40.000 records) Does it seem reasonable ? Tomasz Rakowski - Original Message From: Matthew T. O'Connor [EMAIL PROTECTED] To: Tomasz Rakowski [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 9:50:42 AM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: I have problem with frequently updated table (around 30.000 records and 1000 updates/minute, few indexes). After a while all queries to that table become much slower then at the begining (the number of records in the table is quite stable all the time). I can see that autovacuum is executed (select * from pg_stat_all_tables) so it should update statistics and free unused space. Sounds like autovacuum is running, but not often enough to keep up with this highly active table. You may be able to get better results by setting table specific autovacuum thresholds for this table so that it get vacuumed more often. However if your table is *very* active then autovacuum may not be able to keep up even with the more aggressive settings, this is a known problem which is hopefully addressed in 8.3, some people solve this by turning off autovacuum for the highly active table and using a cron script to vacuum a table every minute or so. Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center. http://autos.yahoo.com/green_center/
Re: [GENERAL] autovacumm not working ?
Hi there, I run VACUUM VERBOSE and the output from it is below: - INFO: vacuuming ais.t_ais_position INFO: scanned index t_ais_position_pkey to remove 972 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.32 sec. INFO: scanned index ix_t_ais_position_update_time to remove 972 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 2.81 sec. INFO: scanned index idx_ais_position to remove 972 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 1.29 sec. INFO: t_ais_position: removed 972 row versions in 305 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.46 sec. INFO: index t_ais_position_pkey now contains 26582 row versions in 145 pages DETAIL: 972 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index ix_t_ais_position_update_time now contains 26582 row versions in 250 pages DETAIL: 972 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index idx_ais_position now contains 26664 row versions in 246 pages DETAIL: 972 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.93 sec. INFO: t_ais_position: found 972 removable, 26582 nonremovable row versions in 498 pages DETAIL: 22 dead row versions cannot be removed yet. There were 9796 unused item pointers. 498 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.01u sec elapsed 6.81 sec. Query returned successfully with no result in 6889 ms. -- Then I left system running for several hours. There was about 1 mln updates to the table (1000/min). The number of rows in the table haven't changed much: from 26582 to 26962 rows. Autovacuum was executed on avarage every 5 minutes (scale_factor for this table is 0.01, base_thresh is 100, naptime is default 1 min). Then I run VACUUM VERBOSE one more time: INFO: vacuuming ais.t_ais_position INFO: scanned index t_ais_position_pkey to remove 2387 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.32 sec. INFO: scanned index ix_t_ais_position_update_time to remove 2387 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 19.22 sec. INFO: scanned index idx_ais_position to remove 2387 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 2.17 sec. INFO: t_ais_position: removed 2387 row versions in 489 pages DETAIL: CPU 0.00s/0.00u sec elapsed 1.54 sec. INFO: index t_ais_position_pkey now contains 26962 row versions in 146 pages DETAIL: 2387 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index ix_t_ais_position_update_time now contains 26962 row versions in 2218 pages DETAIL: 2387 index row versions were removed. 19 index pages have been deleted, 11 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index idx_ais_position now contains 27306 row versions in 348 pages DETAIL: 2387 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 1.68 sec. INFO: t_ais_position: found 2387 removable, 26962 nonremovable row versions in 498 pages DETAIL: 19 dead row versions cannot be removed yet. There were 8001 unused item pointers. 498 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.01u sec elapsed 26.06 sec. Query returned successfully with no result in 26101 ms. - The strange thing is that number of pages allocated for t_ais_position table and t_ais_position_pkey index haven't changed (so autovacuum works ok on them) , but the number of pages allocated to ix_t_ais_position_update_time index increased from 250 to 2218 (x 9 times). ix_t_ais_position_update_time index is created as create index ix_t_ais_position_update_time on t_ais_position(update_time), so it is suppose to be updated very frequently (as main table). The other index t_ais_position_pkey is on primary key and values doesn't change at all Could somebody explain me that ? (something more then autovacuum doesn't keep with updates would be nice) Is it still possible to use autovacuum for such tables or I really should switch to VACUUM run from cron ? Do you think that if I run VACUUM from cron every 5 minutes I would see exactly the same behaviour ? Tomasz Rakowski - Original Message From: Alvaro Herrera [EMAIL PROTECTED] To: Tomasz Rakowski [EMAIL PROTECTED] Cc: Matthew T. O'Connor [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 3:54:14 PM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: Alvaro, I changed autovacuum parametrs for this specific table in pg_autovacuum insert into pg_autovacuum(vacrelid,enabled,vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor
Re: [GENERAL] autovacumm not working ?
How restart of database server influances autovacuum process ? I think that somewhere on this mailing list I read that autovacuum in such case looses some important information and after database server restart will not behave as expected until VACUUM ANALYZE is executed. Is it true ? Tomasz Rakowski - Original Message From: Alvaro Herrera [EMAIL PROTECTED] To: Tomasz Rakowski [EMAIL PROTECTED] Cc: Matthew T. O'Connor [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 2:50:40 PM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: Matthew, Thank you for reply. I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01). Then waited for autovacuum to be activated for given table (watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long). Did you reload (pg_ctl reload) after changing the postgresql.conf settings? Also note that you can alter values for a specific table by putting them in the pg_autovacuum table. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC I am amazed at [the pgsql-sql] mailing list for the wonderful support, and lack of hesitasion in answering a lost soul's question, I just wished the rest of the mailing list could be like this. (Fotis) (http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php) Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/
[GENERAL] autovacumm not working ?
Hi everybody, I have problem with frequently updated table (around 30.000 records and 1000 updates/minute, few indexes). After a while all queries to that table become much slower then at the begining (the number of records in the table is quite stable all the time). I can see that autovacuum is executed (select * from pg_stat_all_tables) so it should update statistics and free unused space. But when after a while when I run VACUUM VERBOSE ANALYZE I saw strange results: --- INFO: vacuuming ais.t_ais_position INFO: scanned index t_ais_position_pkey to remove 552875 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 2.81 sec. INFO: scanned index ix_t_ais_position_update_time to remove 552875 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 144.73 sec. INFO: scanned index idx_ais_position to remove 552875 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 281.09 sec. INFO: t_ais_position: removed 552875 row versions in 8611 pages DETAIL: CPU 0.00s/0.00u sec elapsed 211.54 sec. INFO: index t_ais_position_pkey now contains 30445 row versions in 367 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index ix_t_ais_position_update_time now contains 30445 row versions in 18524 pages DETAIL: 0 index row versions were removed. 4789 index pages have been deleted, 4789 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index idx_ais_position now contains 30445 row versions in 35981 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 281.12 sec. INFO: t_ais_position: found 552875 removable, 30445 nonremovable row versions in 24525 pages DETAIL: 0 dead row versions cannot be removed yet. There were 1229773 unused item pointers. 24478 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 1112.89 sec. INFO: analyzing ais.t_ais_position INFO: t_ais_position: scanned 3000 of 24525 pages, containing 3721 live rows and 0 dead rows; 3000 rows in sample, 30419 estimated total rows Total query runtime: 1136688 ms. -- There are 30445 live rows in the table/indexes, but also 552875! rows to be removed (in table and indexes). I would expect that autovacuum would free unused rows much quicker, so the number of rows to be removed never would be so huge!. It is not surprising now that queries are very slow: 30445 rows are located in 24525 pages (after I run VACUUM FULL it occupied just 400 pages!) and and some indexes are located in 18524 pages (after I run REINDEX it occupy just 120 pages). Ofcourse after I run VACUUM and REINDEX manually all queries are much quicker. Is there any bug in autovacuum or I did something wrong ? Should I run VACUUM/REINDEX periodically anyway ? Tomasz # - # PostgreSQL configuration file # - port = 5432# (change requires restart) max_connections = 50# (change requires restart) shared_buffers = 32MB# min 128kB or max_connections*16kB work_mem = 1MB# min 64kB maintenance_work_mem = 16MB# min 1MB max_fsm_pages = 204800# min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 150# 0-1000 milliseconds checkpoint_segments = 30# in logfile segments, min 1, 16MB each effective_cache_size = 128MB log_destination = 'stderr'# Valid values are combinations of redirect_stderr = on# Enable capturing of stderr into log log_line_prefix = '%t '# Special values: stats_start_collector = on# needed for block or row stats stats_row_level = on autovacuum = on# enable autovacuum subprocess? datestyle = 'iso, mdy' lc_messages = 'C'# locale for system error message lc_monetary = 'C'# locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C'# locale for time formatting Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting
Re: [GENERAL] autovacumm not working ?
Matthew, Thank you for reply. I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01). Then waited for autovacuum to be activated for given table (watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long). When autovacuum was done I started VACUUM VERBOSE manually (to be more precise 6 sec later). I expected that VACUUM VERBOSE will show me just few records to be removed (maybe around hundred), but in fact it reported 3200 record to be removed. It seems that autovacuum was started (anyway I expected it to be activated little bit earlier) but haven't do anything. Any suggestions why ? Do you know what is a difference between acivated autovacuum and VACUUM for given table ? Tomasz - Original Message From: Matthew T. O'Connor [EMAIL PROTECTED] To: Tomasz Rakowski [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 9:50:42 AM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: I have problem with frequently updated table (around 30.000 records and 1000 updates/minute, few indexes). After a while all queries to that table become much slower then at the begining (the number of records in the table is quite stable all the time). I can see that autovacuum is executed (select * from pg_stat_all_tables) so it should update statistics and free unused space. Sounds like autovacuum is running, but not often enough to keep up with this highly active table. You may be able to get better results by setting table specific autovacuum thresholds for this table so that it get vacuumed more often. However if your table is *very* active then autovacuum may not be able to keep up even with the more aggressive settings, this is a known problem which is hopefully addressed in 8.3, some people solve this by turning off autovacuum for the highly active table and using a cron script to vacuum a table every minute or so. Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
Re: [GENERAL] autovacumm not working ?
Alvaro, I changed autovacuum parametrs for this specific table in pg_autovacuum insert into pg_autovacuum(vacrelid,enabled,vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) values ( (select oid from pg_class where relname='t_ais_position'), True, 100, 0.01 , 100, 0.02, -1, -1, -1, -1 ) Should I somehow let autovacuum deamon know about new table configuration or above insert is enough ? Tomasz - Original Message From: Alvaro Herrera [EMAIL PROTECTED] To: Tomasz Rakowski [EMAIL PROTECTED] Cc: Matthew T. O'Connor [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 2:50:40 PM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: Matthew, Thank you for reply. I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01). Then waited for autovacuum to be activated for given table (watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long). Did you reload (pg_ctl reload) after changing the postgresql.conf settings? Also note that you can alter values for a specific table by putting them in the pg_autovacuum table. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC I am amazed at [the pgsql-sql] mailing list for the wonderful support, and lack of hesitasion in answering a lost soul's question, I just wished the rest of the mailing list could be like this. (Fotis) (http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php) Expecting? Get great news right away with email Auto-Check. Try the Yahoo! Mail Beta. http://advision.webevents.yahoo.com/mailbeta/newmail_tools.html
Re: [GENERAL] autovacumm not working ?
Alvaro, I see autovacuum process starting in avarage every 5 minutes (table contains 30.000 records, and update rate is about 1000records /min). But what is strange is that the number of pages allocated to the table are constant (at least for this hour: 500) and number of pages allocated to indexes are constantly growing (109 - 145, 92 - 250!!!, 194-256) But as I stated in first post after a while it gets worse and worse (thousands of allocated pages for the same number of records..) Tomasz - Original Message From: Alvaro Herrera [EMAIL PROTECTED] To: Tomasz Rakowski [EMAIL PROTECTED] Cc: Matthew T. O'Connor [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 3:54:14 PM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: Alvaro, I changed autovacuum parametrs for this specific table in pg_autovacuum insert into pg_autovacuum(vacrelid,enabled,vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age) values ( (select oid from pg_class where relname='t_ais_position'), True, 100, 0.01 , 100, 0.02, -1, -1, -1, -1 ) Should I somehow let autovacuum deamon know about new table configuration or above insert is enough ? The insert should be enough. You do see the autovacuum process starting on that database, right? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
[GENERAL] database locks
Hi , I'm facing frustrating problem with locking in postgres server. I have application which do update one table few thousands time each minute. From time to time call to database is locked and is waiting for something (In pgAdmin3 in 'Server Status' window in 'Lock' tab I can see UPDATE statement siting there for hours... and usually it is the the only lock! ) At the same time I can connect to database from other applications (including pgAdmin3) and do any query on that table. This behaviour is identical with one reported in : http://archives.postgresql.org/pgsql-hackers/2006-10/msg00289.php I'm using Postgres 8.2.3 on Windows 2000 SMP (two processors, 4GB RAM, RAID 5) and I can still observe that issue! When I restart machine with /numproc=1 it works OK. If I switch off database connection pooling in my application (so I use just one connection to database ) the issue also doesn't occur. It still happens when I set stats_row_level=off and autovacuum=off and run VACUUM periodically from batch. Any suggestions ? My postgresql.conf file: # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the postmaster. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # pg_ctl reload. Some settings, such as listen_addresses, require # a postmaster shutdown and restart to take effect. #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir'# use data in another directory #hba_file = 'ConfigDir/pg_hba.conf'# host-based authentication file #ident_file = 'ConfigDir/pg_ident.conf'# IDENT configuration file # If external_pid_file is not explicitly set, no extra pid file is written. #external_pid_file = '(none)'# write an extra pid file #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #listen_addresses = 'localhost'# what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 max_connections = 50 # note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777# octal #bonjour_name = ''# defaults to the computer name # - Security Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = off #password_encryption = on #db_user_namespace = off # Kerberos #krb_server_keyfile = '' #krb_srvname = 'postgres' #krb_server_hostname = ''# empty string matches any keytab entry #krb_caseins_users = off # - TCP Keepalives - # see 'man 7 tcp' for details #tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0# TCP_KEEPCNT; # 0 selects the system default #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 3# min 16 or max_connections*2, 8KB each #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5# can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 8192# min 64, size in KB maintenance_work_mem = 131072
