Thread: Checkpoint question
To improve the performance of PostgreSQL, resp. to avoid too frequently occuring checkpoints, it is recommended to increase the number of wal files (checkpoint_segments). So I increased the number of checkpoint segments to be able to write a lot of data into the database without interruptions due to checkpoints. My question now is concerning the cost of a checkpoint if I have a lot of checkpoint segments. At checkpoint time will there be checkpointed all the information contained in all log files, lets assume that I have 40 checkpoint segments for example, or will only some informations of the log files be checkpointed? If all log files are checkpointed, won't this take a lot of time, and if so wouldn't it be better to leave the number of checkoint segments to a smaller value? I'm not quite familiar with the checkpoint concept, so maybe I'm wrong with my thoughts. Can anyone give advice/explanations? Thanks Andreas ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
On 21/07/2003 07:30 u15074 wrote: > To improve the performance of PostgreSQL, resp. to avoid too frequently > occuring > checkpoints, it is recommended to increase the number of wal files > (checkpoint_segments). > So I increased the number of checkpoint segments to be able to write a > lot of > data into the database without interruptions due to checkpoints. > My question now is concerning the cost of a checkpoint if I have a lot of > checkpoint segments. At checkpoint time will there be checkpointed all > the > information contained in all log files, lets assume that I have 40 > checkpoint > segments for example, or will only some informations of the log files be > checkpointed? > If all log files are checkpointed, won't this take a lot of time, and if > so > wouldn't it be better to leave the number of checkoint segments to a > smaller value? > I'm not quite familiar with the checkpoint concept, so maybe I'm wrong > with my > thoughts. Can anyone give advice/explanations? I'm sure there must be some point where increasing the number of wal files does not improve performance and your reasoning seem right to me. ISTR reading something in the archives about someone doing some performance tests with number of wal buffers and not seeing a big difference even when large numbers were used. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Zitat von Tom Lane <tgl@sss.pgh.pa.us>: > A checkpoint pushes out all unwritten data since the last checkpoint. > So yeah, it stands to reason that if you increase the time between > checkpoints, each checkpoint will take longer. Whether this is really > a problem is not clear --- the checkpoint is happening in background > after all. Why should you care how long it takes? > > regards, tom lane > > What I want to do, is to write a lot of data in the database over a longer period (assume 1 hour or longer) with constant data rates - I am trying to use Postgres to store measurement data. When I only had a few checkpoint_segments (3 I think), I always got an interruption when the database was checkpointing (what took about 3 seconds). So I increased the checkpoint_segments and the pauses went away. But now I think if I reach the segments limit again, I will get even bigger pauses (of course at some point in the time a checkpoint has to be performed). By the way, if you say, checkpointing is happening in the background, I don't know what causes the pauses. ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
u15074 <u15074@hs-harz.de> writes:
> By the way, if you say, checkpointing is happening in the background, I don't
> know what causes the pauses.
Probably insufficient disk bandwidth. If you have two drives available,
try putting the WAL files (pg_xlog directory) on a different drive from
the data files. Assuming you have adequate RAM, updates will be mainly
limited by writes to WAL, while checkpoint doesn't touch WAL and is all
about pushing data from RAM to the data files. So with a proper drive
split, checkpoint really shouldn't affect update rate at all. (It could
affect the time for SELECT queries, if they need to fetch data that
isn't in RAM, but that didn't seem to be your complaint.)
regards, tom lane
Zitat von Tom Lane <tgl@sss.pgh.pa.us>: > Probably insufficient disk bandwidth. If you have two drives available, > try putting the WAL files (pg_xlog directory) on a different drive from > the data files. Assuming you have adequate RAM, updates will be mainly > limited by writes to WAL, while checkpoint doesn't touch WAL and is all > about pushing data from RAM to the data files. So with a proper drive > split, checkpoint really shouldn't affect update rate at all. (It could > affect the time for SELECT queries, if they need to fetch data that > isn't in RAM, but that didn't seem to be your complaint.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > Can you specify more exactly what you mean with update rate? I moslty perform inserts on the database (is that what you mean?). Also I do not understand, why checkpoint does not touch WAL, but RAM. I thought that a checkpoint reads the information from the WAL-Files and pushes these information to the data files. Thanks Andreas ------------------------------------------------- This mail sent through IMP: http://horde.org/imp/
u15074 <u15074@hs-harz.de> writes:
> Can you specify more exactly what you mean with update rate? I moslty perform
> inserts on the database (is that what you mean?).
Sure, inserts/updates/deletes.
> Also I do not understand, why checkpoint does not touch WAL, but RAM. I thought
> that a checkpoint reads the information from the WAL-Files and pushes these
> information to the data files.
Not in Postgres. Updates are applied immediately to copies of the data
file pages in RAM; all that checkpoint has to do is force those buffers
out to disk.
regards, tom lane
>>>>> "u" == u15074 <u15074@hs-harz.de> writes: u> What I want to do, is to write a lot of data in the database over a u> longer period (assume 1 hour or longer) with constant data rates - u> I am trying to use Postgres to store measurement data. If you have real-time constraints, then I'd recommend writing your log data to a flat file on its own dedicated disk drive, and importing to postgres once the experiment is done. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/