Обсуждение: Continous increase in insertion rate with time!!!
Hi Group, I am finding an increase in insertion rate with time with steady insertion and updation rate of 70 records per sec with my app using Postgres 7.4.5 on Solaris 5.8. I am using Postgres7.4.5 on solaris5.8 (1-Gig Ram) machine. During the initial stages say Ist 1hr or so, the insertion rate is about 5 sec per insert but it slowly starts building up with time and after 24 hour goes to 12-15 sec and after 48hr goes to 25 sec. Update is fast and is in less than a sec. I have attached the postgres conf file and the statistics collected during the run. Is this a known feature (or limitation). How can I avoid this by minimising the disk read? Shld I consider updating postgresql.conf with more appropriate values. (i.e disabling fysnc and check pointing based on chkpoint timeout as I perform continous insert -> update and then batch delete) **************************************** postgresql.conf **************************************** max_connections = 100 shared_buffers = 32000 #(Org 1000) sort_mem = 8192 #(Org 1024) fsync = true wal_sync_method = fsync wal_buffers = 8 checkpoint_segments = 10 #(Org 3) effective_cache_size = 1000 **************************************** cdr_backup=# select * from pg_stat_user_tables; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del ---------+--------------------+-------------------------+----------+---- ----------+----------+---------------+-----------+-----------+---------- - 9471204 | public | cdr | 41069 | 39957057 | 1 | 0 | 19056991 | 0 | 19054378 9471214 | public | cdr_archive | 2327 | 23331784 | 492714 | 19233822 | 19054379 | 0 | 19031842 cdr_backup=# select * from pg_stat_user_indexes; relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch ---------+------------+------------+-----------------+------------------ --------------+----------+--------------+--------------- 9471214 | 9471222 | public | cdr_archive | cdr_archive_accesstime_key | 492785 | 19233822 | 19233822 9471204 | 9471212 | public | cdr | cdr_transid_key | 1 | 0 | 0 cdr_backup=# select * from pg_statio_user_indexes; relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit ---------+------------+------------+-----------------+------------------ --------------+---------------+-------------- 9471214 | 9471222 | public | cdr_archive | cdr_archive_accesstime_key | 1472 | 73638177 9471204 | 9471212 | public | cdr | cdr_transid_key | 934 | 58408992 cdr_backup=# select * from pg_statio_user_indexes; relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit ---------+------------+------------+-----------------+------------------ --------------+---------------+-------------- 9471214 | 9471222 | public | cdr_archive | cdr_archive_accesstime_key | 1472 | 73638267 9471204 | 9471212 | public | cdr | cdr_transid_key | 934 | 58408992 (4 rows) cdr_backup=# select * from pg_statio_user_tables; relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit ---------+--------------------+-------------------------+--------------- -+---------------+---------------+--------------+-----------------+----- -----------+----------------+--------------- 9471204 | public | cdr | 19912 | 137570556 | 934 | 58408992 | 76 | 1318 | 2 | 1226 9471214 | public | cdr_archive | 222246 | 112726711 | 1472 | 73638408 | 122 | 2316 | 2 | 4511 Thanks, Rajan
Hi Rajan, Have do any of your tables contain indexes? If so drop them during the inserts, then recreate them after. As inserts require the indexes to be updated, as the table gets larger the re-indexing takes longer too perform. Cheers Noel >Hi Group, > >I am finding an increase in insertion rate with time with steady >insertion and updation rate of 70 records per sec with my app using >Postgres 7.4.5 on Solaris 5.8. >I am using Postgres7.4.5 on solaris5.8 (1-Gig Ram) machine. >During the initial stages say Ist 1hr or so, the insertion rate is about >5 sec per insert but it slowly starts building up with time and after 24 >hour goes to 12-15 sec and after 48hr goes to 25 sec. Update is fast and >is in less than a sec. >I have attached the postgres conf file and the statistics collected >during the run. >Is this a known feature (or limitation). How can I avoid this by >minimising the disk read? >Shld I consider updating postgresql.conf with more appropriate values. >(i.e disabling fysnc and check pointing based on chkpoint timeout as I >perform continous insert -> update and then batch delete) > > >**************************************** >postgresql.conf >**************************************** >max_connections = 100 >shared_buffers = 32000 #(Org 1000) >sort_mem = 8192 #(Org 1024) > >fsync = true >wal_sync_method = fsync >wal_buffers = 8 >checkpoint_segments = 10 #(Org 3) >effective_cache_size = 1000 >**************************************** > > > >cdr_backup=# select * from pg_stat_user_tables; > relid | schemaname | relname | seq_scan | >seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | >n_tup_del >---------+--------------------+-------------------------+----------+---- >----------+----------+---------------+-----------+-----------+---------- >- > 9471204 | public | cdr | 41069 | >39957057 | 1 | 0 | 19056991 | 0 | 19054378 > 9471214 | public | cdr_archive | 2327 | >23331784 | 492714 | 19233822 | 19054379 | 0 | 19031842 > >cdr_backup=# select * from pg_stat_user_indexes; > relid | indexrelid | schemaname | relname | >indexrelname | idx_scan | idx_tup_read | idx_tup_fetch >---------+------------+------------+-----------------+------------------ >--------------+----------+--------------+--------------- > 9471214 | 9471222 | public | cdr_archive | >cdr_archive_accesstime_key | 492785 | 19233822 | 19233822 > 9471204 | 9471212 | public | cdr | cdr_transid_key >| 1 | 0 | 0 > > >cdr_backup=# select * from pg_statio_user_indexes; > relid | indexrelid | schemaname | relname | >indexrelname | idx_blks_read | idx_blks_hit >---------+------------+------------+-----------------+------------------ >--------------+---------------+-------------- > 9471214 | 9471222 | public | cdr_archive | >cdr_archive_accesstime_key | 1472 | 73638177 > 9471204 | 9471212 | public | cdr | cdr_transid_key >| 934 | 58408992 > > >cdr_backup=# select * from pg_statio_user_indexes; > relid | indexrelid | schemaname | relname | >indexrelname | idx_blks_read | idx_blks_hit >---------+------------+------------+-----------------+------------------ >--------------+---------------+-------------- > 9471214 | 9471222 | public | cdr_archive | >cdr_archive_accesstime_key | 1472 | 73638267 > 9471204 | 9471212 | public | cdr | cdr_transid_key >| 934 | 58408992 >(4 rows) > >cdr_backup=# select * from pg_statio_user_tables; > relid | schemaname | relname | heap_blks_read >| heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | >toast_blks_hit | tidx_blks_read | tidx_blks_hit >---------+--------------------+-------------------------+--------------- >-+---------------+---------------+--------------+-----------------+----- >-----------+----------------+--------------- > 9471204 | public | cdr | 19912 >| 137570556 | 934 | 58408992 | 76 | >1318 | 2 | 1226 > 9471214 | public | cdr_archive | 222246 >| 112726711 | 1472 | 73638408 | 122 | >2316 | 2 | 4511 > > >Thanks, >Rajan > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >
I just have a single index created due to composite (3 column) primary key constraint which I cannot drop as I need to chk the duplicate records. Also as I said, there must not be more than 1000 records in the table at any given time as I am perform insert and update and a batch delete continously. -----Original Message----- From: Noel Faux [mailto:noel.faux@med.monash.edu.au] Sent: Monday, November 01, 2004 12:33 PM To: Rajan Bhide Cc: pgsql-novice@postgresql.org; tgl@sss.pgh.pa.us; pgman@candle.pha.pa.us Subject: Re: [NOVICE] Continous increase in insertion rate with time!!! Hi Rajan, Have do any of your tables contain indexes? If so drop them during the inserts, then recreate them after. As inserts require the indexes to be updated, as the table gets larger the re-indexing takes longer too perform. Cheers Noel >Hi Group, > >I am finding an increase in insertion rate with time with steady >insertion and updation rate of 70 records per sec with my app using >Postgres 7.4.5 on Solaris 5.8. I am using Postgres7.4.5 on solaris5.8 >(1-Gig Ram) machine. During the initial stages say Ist 1hr or so, the >insertion rate is about 5 sec per insert but it slowly starts building >up with time and after 24 hour goes to 12-15 sec and after 48hr goes to >25 sec. Update is fast and is in less than a sec. >I have attached the postgres conf file and the statistics collected >during the run. >Is this a known feature (or limitation). How can I avoid this by >minimising the disk read? >Shld I consider updating postgresql.conf with more appropriate values. >(i.e disabling fysnc and check pointing based on chkpoint timeout as I >perform continous insert -> update and then batch delete) > > >**************************************** >postgresql.conf >**************************************** >max_connections = 100 >shared_buffers = 32000 #(Org 1000) >sort_mem = 8192 #(Org 1024) > >fsync = true >wal_sync_method = fsync >wal_buffers = 8 >checkpoint_segments = 10 #(Org 3) >effective_cache_size = 1000 >**************************************** > > > >cdr_backup=# select * from pg_stat_user_tables; > relid | schemaname | relname | seq_scan | >seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | >n_tup_del >---------+--------------------+-------------------------+----------+--- >---------+--------------------+-------------------------+----------+- >----------+----------+---------------+-----------+-----------+--------- >----------+----------+---------------+-----------+-----------+- >- > 9471204 | public | cdr | 41069 | >39957057 | 1 | 0 | 19056991 | 0 | 19054378 > 9471214 | public | cdr_archive | 2327 | >23331784 | 492714 | 19233822 | 19054379 | 0 | 19031842 > >cdr_backup=# select * from pg_stat_user_indexes; > relid | indexrelid | schemaname | relname | >indexrelname | idx_scan | idx_tup_read | idx_tup_fetch >---------+------------+------------+-----------------+----------------- >---------+------------+------------+-----------------+- >--------------+----------+--------------+--------------- > 9471214 | 9471222 | public | cdr_archive | >cdr_archive_accesstime_key | 492785 | 19233822 | 19233822 > 9471204 | 9471212 | public | cdr | cdr_transid_key >| 1 | 0 | 0 > > >cdr_backup=# select * from pg_statio_user_indexes; > relid | indexrelid | schemaname | relname | >indexrelname | idx_blks_read | idx_blks_hit >---------+------------+------------+-----------------+----------------- >---------+------------+------------+-----------------+- >--------------+---------------+-------------- > 9471214 | 9471222 | public | cdr_archive | >cdr_archive_accesstime_key | 1472 | 73638177 > 9471204 | 9471212 | public | cdr | cdr_transid_key >| 934 | 58408992 > > >cdr_backup=# select * from pg_statio_user_indexes; > relid | indexrelid | schemaname | relname | >indexrelname | idx_blks_read | idx_blks_hit >---------+------------+------------+-----------------+----------------- >---------+------------+------------+-----------------+- >--------------+---------------+-------------- > 9471214 | 9471222 | public | cdr_archive | >cdr_archive_accesstime_key | 1472 | 73638267 > 9471204 | 9471212 | public | cdr | cdr_transid_key >| 934 | 58408992 >(4 rows) > >cdr_backup=# select * from pg_statio_user_tables; > relid | schemaname | relname | heap_blks_read >| heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | >toast_blks_hit | tidx_blks_read | tidx_blks_hit >---------+--------------------+-------------------------+-------------- >---------+--------------------+-------------------------+- >-+---------------+---------------+--------------+-----------------+---- >-+---------------+---------------+--------------+-----------------+- >-----------+----------------+--------------- > 9471204 | public | cdr | 19912 >| 137570556 | 934 | 58408992 | 76 | >1318 | 2 | 1226 > 9471214 | public | cdr_archive | 222246 >| 112726711 | 1472 | 73638408 | 122 | >2316 | 2 | 4511 > > >Thanks, >Rajan > >---------------------------(end of >broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > >