Continous increase in insertion rate with time!!!

From: "Rajan Bhide" <rbhide(at)starentnetworks(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Cc: <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: Continous increase in insertion rate with time!!!
Date: 2004-11-01 06:28:01
Message-ID: FF851C7EEB75954F9BCFB5CA117AB1EC013FE2D4@delta.nulinkinc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Noel Faux 2004-11-01 07:02:59 Re: Continous increase in insertion rate with time!!!
Previous Message John Fabiani 2004-10-31 17:18:47 Re: moving from MySQL to Postgres