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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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...1/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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster