Обсуждение: control the number of clog files and xlog files
Hi, Would you like to be so kind as to answer the following questions: - Is there any way to control the number of clog files and xlog files? I encounter an issue that there are too many clog files under the pg_clog/ directory which occupy more space than I can endure.. - What determines the number of clog files? what determines the number of xlog files? - I understand pg_xlog is used to record WAL. but what is pg_clog is used to? Is it used to record some meta-information on the xlog? - What effect does Deleting the clog and xlogfiles bring about? Will it cause Postgresql abnormal stopping? #I am not sure this is the right list to ask such a question.But since I post it in the ADMIN list, and no one give me an answer, I wanna try this list. Best regards Gera
Duan Ligong wrote: > Would you like to be so kind as to answer the following questions: > > - Is there any way to control the number of clog files and xlog files? > I encounter an issue that there are too many clog files under the > pg_clog/ directory which occupy more space than I can endure.. pg_clog files are controlled by tuple freezing, which is done by vacuum, and it depends on the autovacuum_min_freeze_age parameter and vacuum_freeze_min_age. Please read http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html and http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html#GUC-VACUUM-FREEZE-MIN-AGE > - What determines the number of clog files? what determines the > number of xlog files? The number of xlog files will depend on checkpoints. You need to restrict checkpoint_segments to control this. Note that this can have a serious performance impact. > - I understand pg_xlog is used to record WAL. but what is pg_clog > is used to? Is it used to record some meta-information on the xlog? clog is the "commit log", i.e. it records transactions that have been committed and those that have been aborted. You cannot delete files unless you want to corrupt your database. > - What effect does Deleting the clog and xlogfiles bring about? > Will it cause Postgresql abnormal stopping? Your data will be corrupt. It may continue to work for a while, and suddenly stop working at a future time. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro, Thanks for your answer. It would be very helpful. > > Would you like to be so kind as to answer the following questions: > > > > - Is there any way to control the number of clog files and xlog files? > > I encounter an issue that there are too many clog files under the > > pg_clog/ directory which occupy more space than I can endure.. > > pg_clog files are controlled by tuple freezing, which is done by vacuum, > and it depends on the autovacuum_min_freeze_age parameter and > vacuum_freeze_min_age. Please read So can we reduce the number of clog by increasing the autovacuum_min_freeze_age parameter and vacuum_freeze_min_age ? > http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html > and > http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html#GUC-VACUUM-FREEZE-MIN-AGE > > - What determines the number of clog files? what determines the > > number of xlog files? > > The number of xlog files will depend on checkpoints. You need to > restrict checkpoint_segments to control this. Note that this can have a > serious performance impact. > > - I understand pg_xlog is used to record WAL. but what is pg_clog > > is used to? Is it used to record some meta-information on the xlog? > > clog is the "commit log", i.e. it records transactions that have been > committed and those that have been aborted. You cannot delete files > unless you want to corrupt your database. Could you explain how the clog files work roughly? (What is inside of the clog files? when and how the new clog files are created? when and in what case the old files are deleted or rotated? how does postgresql regard a file is old enough to be deleted? Does Vacuum will definitely cause deleting of old files and creating of new clog files?) > > - What effect does Deleting the clog and xlogfiles bring about? > > Will it cause Postgresql abnormal stopping? > Your data will be corrupt. It may continue to work for a while, and > suddenly stop working at a future time. I encoutered a scenario that there are many files and some of them are as old as one month ago. Does all these files including the old files are still useful for postgresql? and when will they deleted or rotated? Or should they be deleted and maintained by external programs? Best regards Duan > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance --------------------------------------------------- Duan Ligong : 8-0086-22-354
Duan Ligong wrote: > Alvaro, Thanks for your answer. > It would be very helpful. > > > > Would you like to be so kind as to answer the following questions: > > > > > > - Is there any way to control the number of clog files and xlog files? > > > I encounter an issue that there are too many clog files under the > > > pg_clog/ directory which occupy more space than I can endure.. > > > > pg_clog files are controlled by tuple freezing, which is done by vacuum, > > and it depends on the autovacuum_min_freeze_age parameter and > > vacuum_freeze_min_age. Please read > > So can we reduce the number of clog by increasing the > autovacuum_min_freeze_age parameter and vacuum_freeze_min_age > ? Yes, but decreasing the value. Sorry, you ask more questions that I have time to answer right now. > I encoutered a scenario that there are many files and some of them > are as old as one month ago. Does all these files including the > old files are still useful for postgresql? and when will they deleted > or rotated? Or should they be deleted and maintained by external > programs? Yes, those files are still useful. They will be deleted eventually. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support