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

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