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
Just tried 2.6.10-1.9_FC2 with the same bad results.
Am I the only one experiencing this? What would be special? The hardware? It
has nothing special, a popular Asus motherboard, 1GB memory, a modern Maxtor HD
and nothing else.
Clodoaldo
--- Clodoaldo Pinto <clodoaldo_pinto@yahoo.com.br> escreveu: 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 eachThe 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---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
_______________________________________________________
Yahoo! Acesso Gr�tis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet r�pida e gr�tis
I made the same experience now with PG 8.01, FC3 and 512MB memory. The previous
was PG 7.4.6, FC2 and 1GB.
The difference in performance between kernels 2.6.9-1.724 and 2.6.10-1.766 is
still huge.
I notice that in 2.6.10 the function accesses the disk much more.
Does anyone know what changes from 2.6.9 to 2.6.10 could cause this drop in
performance?
Regards, Clodoaldo Pinto
_______________________________________________________
Yahoo! Acesso Gr�tis - Instale o discador do Yahoo! agora. http://br.acesso.yahoo.com/ - Internet r�pida e gr�tis
On Sun, 20 Feb 2005 12:35:02 -0300 (ART), Clodoaldo Pinto
<clodoaldo_pinto@yahoo.com.br> wrote:
I made the same experience now with PG 8.01, FC3 and 512MB memory. The previous
was PG 7.4.6, FC2 and 1GB.The difference in performance between kernels 2.6.9-1.724 and 2.6.10-1.766 is
still huge.I notice that in 2.6.10 the function accesses the disk much more.
Does anyone know what changes from 2.6.9 to 2.6.10 could cause this drop in
performance?Regards, Clodoaldo Pinto
This message did not connect to the original thread. So this is the reference:
http://archives.postgresql.org/pgsql-general/2005-01/msg00569.php
Show quoted text
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 eachThe 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