7.4.6 FC2 MUCH slower from 2.6.9-1.11 to 2.6.10-1.8

31 minutes in 2.6.10-1.8:

select kstime(), update_ranking_usuarios(), kstime();
kstime | update_ranking_usuarios | kstime
---------------------+-------------------------+---------------------
2005-01-13 20:27:56 | | 2005-01-13 20:58:46
(1 row)

5 minutes in 2.6.9-1.11:

select kstime(), update_ranking_usuarios(), kstime();
kstime | update_ranking_usuarios | kstime
---------------------+-------------------------+---------------------
2005-01-13 23:24:30 | | 2005-01-13 23:29:26
(1 row)

The function:

CREATE OR REPLACE FUNCTION update_ranking_usuarios()
RETURNS void AS
'declare
linha record;
rank integer;
begin
rank := 0;
for linha in
select usuario
from usuarios_producao
where not anonymous --and n_time != 446
order by pontos_0 desc
loop
rank := rank + 1;
update usuarios_producao
set rank_0 = rank
where usuario = linha.usuario
;
end loop;
-- ----------------------------------------------------------
rank := 0;
for linha in
select usuario
from usuarios_producao
where not anonymous --and n_time != 446
order by pontos_0 + (( pontos_0 - pontos_7) / 7) desc
loop
rank := rank + 1;
update usuarios_producao
set rank_24 = rank
where usuario = linha.usuario
;
end loop;
-- ----------------------------------------------------------
rank := 0;
for linha in
select usuario
from usuarios_producao
where not anonymous --and n_time != 446
order by pontos_0 + pontos_0 - pontos_7 desc
loop
rank := rank + 1;
update usuarios_producao
set rank_7 = rank
where usuario = linha.usuario
;
end loop;
-- ----------------------------------------------------------
rank := 0;
for linha in
select usuario
from usuarios_producao
where not anonymous --and n_time != 446
order by pontos_0 + (( pontos_0 - pontos_7) * 30 / 7) desc
loop
rank := rank + 1;
update usuarios_producao
set rank_30 = rank
where usuario = linha.usuario
;
end loop;
return;
end;'
LANGUAGE 'plpgsql' STABLE;

Part of postgresql.conf:

shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each
sort_mem = 49152 # min 64, size in KB
vacuum_mem = 32768 # min 1024, size in KB
vacuum_mem = 32768
max_fsm_pages = 300000 # min max_fsm_relations*16, 6 bytes each
fsync = false
wal_buffers = 256
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each

The log file shows only a long list of:

LOG: recycled transaction log file "0000010A000000D0"
LOG: recycled transaction log file "0000010A000000CF"
LOG: recycled transaction log file "0000010A000000C9"
...

Regards, Clodoaldo Pinto

_______________________________________________________
Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet rápida e grátis