Обсуждение: 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 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 > > ---------------------------(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 > 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 >