Continous increase in insertion rate with time!!! - Mailing list pgsql-novice
| From | Rajan Bhide |
|---|---|
| Subject | Continous increase in insertion rate with time!!! |
| Date | |
| Msg-id | FF851C7EEB75954F9BCFB5CA117AB1EC013FE2D4@delta.nulinkinc.com Whole thread Raw |
| Responses |
Re: Continous increase in insertion rate with time!!!
|
| List | pgsql-novice |
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
pgsql-novice by date: