Re: Continous increase in insertion rate with time!!! - Mailing list pgsql-novice

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
>
>