Search Postgresql Archives

Very long execution time of "select nextval('..');"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

we run postgresql-8.1 on a dedicated debian box 64bit, dual-core CPU, 8GB RAM, 
RAID-1.

We select our primary keys with select nextval before we actually insert a 
record. In my logs i print every statement  which takes longer than 250ms

there are lots of values fetched each day with nextval, but i have about 20-50 
statements each day which take up to 17 seconds(!):

LOG:  duration: 12636.746 ms  statement: EXECUTE <unnamed>  [PREPARE:  select 
nextval ('member_id_seq')]

[it is always the same statement so i just show some numbers:
LOG:  duration: 4000.991 ms  ...
LOG:  duration: 2994.532 ms  ...
LOG:  duration: 611.167 ms  ...
LOG:  duration: 17072.196 ms  
LOG:  duration: 16570.860 ms 
LOG:  duration: 14816.153 ms  
LOG:  duration: 265.855 ms 
LOG:  duration: 1238.361 ms  

...
there are about 50 commands per day like this taking longer than 250ms. 

i have no idea why something like nextval() can take longer than 1ms. 

and if i do it manually it shows up like this:
select nextval ('member_id_seq');
 nextval
---------
  569304
(1 row)

Time: 0.651 ms

Unfortunatly  i can not tell at which time this happens as the log doesn't 
show the time of day.

As it is a web application everything above 250ms is not acceptable. Something 
like 17seconds (!) is like not working at all.

i observe my server with munin and the load of the server is at the maximum 
about 0.5.

Can this be related to the autovacuum process, which we run every 50 minutes?

It seems that something is blocked. But i thought that nextval is never 
blocked by anyhing else.

now i looked at some stats
select blks_read, blks_hit from pg_statio_user_sequences;
( i dropped names and relids as they are not important.)
 
blks_read | blks_hit
-----------+----------
         2 |        0
        22 |      125
        14 |      142
         2 |        0
        14 |        0
        43 |      498
        27 |       24
        34 |        0
        25 |       12
        55 |       55
         8 |       17
         2 |        0
        14 |        0
        34 |        0
         2 |        0
        33 |      539
        58 |       25
        59 |       53
        34 |        0
         2 |        0
         2 |        0
        39 |      135
         2 |        0
        38 |        4
        34 |        0
         2 |        0
        14 |        0
        15 |      131
        23 |     1223
        22 |      147
         1 |     2015
        10 |        2
        13 |       67
        34 |        0
        34 |        0
        32 |     3610
        46 |      590

I dont have any clue what is happening but something runs rather suboptimal.

Any help is very appreciated.

kind regards,
Janning

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux