Re: [PERFORM] Server Freezing

[Waldomiro]

I´m using PostgreSQL 8.1, and my settings are: checkpoint_segments=50 checkpoint_timeout=300 checkpoint_warning=30 commit_delay=0 commit_siblings=5 archive_command= cp -i %p/BACKUP/LOGS/%f autovacuum=off bgwriter_all_maxpages=5 bgwriter_all_percent=0.333 bgwriter_delay=200 bgwriter_lru_maxpages=5 bgwriter_lru_percent=1 fsync=on full_page_writes=on stats_block_level=on stats_command_string=on stats_reset_on_server_start=off stats_row_level=on stats_start_collector=on Waldomiro Fernando Hevia escreveu: -----Mensaje original----- De: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] En nombre de Waldomiro Enviado el: Lunes, 30 de Noviembre de 2009 22:03 Para: pgsql-performance@postgresql.org Asunto: [PERFORM] Server Freezing Hi everybody, ... That table receives about 3000 inserts and 60000 updates each day, but at night I do a TRUNCATE TABLE1 (Every Night), so the table is very small. There is an index by field1 too. Some days It works very good all day, but somedays I have 7 seconds freeze, I mean, my serves delays 7 seconds on this statement: SELECT field1 FROM TABLE1 WHERE field2 = '10' Hi. You should probably consider creating a partial index on field2 = '10'. I looked up to the statistics for that table, but the statistics says that postgres is reading memory, not disk, becouse the table is very small and I do a select every second, so the postgres keeps the table in shared buffers. You say you dont vacuum this table, but considering 60000 updates on 3000 records, assuming you are updating each record 20 times, your table could eat up the space of 60M records. ¿Have you considered this? Though, I am not sure how this impacts when the whole table is held in shared buffers. Why this 7 seconds delay? How could I figure out what is happening? Turn log_checkpoints = on to see in the logs if these occur during the freeze. Also log_lock_waits = on will help diagnose the situation. What version of postgres are you running and how are your checkpoints configured? Regards, Fernando. -- Waldomiro Caraiani Neto ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| GRUPO SHX Desenvolvimento + 55 (16) 3331.3268 waldom...@shx.com.br www.shx.com.br