Jakub Wartak wrote:
Dnia niedziela, 20 listopada 2005 23:59, Paolo Lucente napisał:
The hardware is ok and the number of tuples in both the tables seems fine.
I would suggest to upgrade to either 0.9.3, 0.9.4p1 or the development
snapshot 0.9.5 in order to further troubleshoot the issue (the process
title can give additional informations while firing a "ps auxw").
Okay, I self-compiled 0.9.4p1, the results are identical, after several
minutes:
netflow=> select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename |
current_query | query_start
-------+---------+---------+----------+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------
17144 | netflow | 30462 | 100 | pmacct | UPDATE acct_in SET
packets=packets+6, bytes=bytes+288, stamp_updated=CURRENT_TIMESTAMP(0) WHERE
ABSTIME(1132588800)::Timestamp::Timestamp without time zone = stamp_inserted
AND ip_dst='192.168.86.98' | 2005-11-21 18:18:27.93401+01
17144 | netflow | 4273 | 100 | pmacct | <IDLE> | 2005-11-21
18:18:23.891838+01
( i tunned pgsql to provide some more statistics about queries that are
currently running ), notice again the "+288" bytes, for me it is very
strange, because as far as i understand ( based on sql_refresh_time option ),
the database shouldn be updated only every 30mins, not all the time... And
that continues updating of database is causing postgres/mysql to generate
very high load-average. And another thing: 192.168.86.98 doesn't exist.
I would enable SQL debugging in the pmacctd configuration file, and
check the actual INSERT/UPDATE queries - maybe the filters or something
aren't quite correct. Been there...
Also, copy one of those INSERT/UPDATE queries and run it in psql as
EXPLAIN [ANALYZE] UPDATE acct SET ...
None of the updates/inserts should be doing sequential reads of the
table - for best performance, everything should be indexed.
The fact that no nfacctd process consumes significative shares of the CPU
and the strace seems to confirm that it should not be matter of any endless
loop. Are you firing regularly VACUUMs of the tables ?
I think that Debian scripts are doing this already, from /etc/cron.d/postgres:
My experience has been that a weekly VACUUM is not enough. I do a
"VACUUM FULL; VACUUM ANALYZE" every 4 hours. My table sizes:
acct_hourly: 8,728,453 rows
acct_daily: 3,299,025 rows
acct_monthly: 164705 rows
You could also look at vacuuming individual tables, or at least reindex
the indexes more often. With a lot of updates, the indexes can have a
lot of dead space which slows down updates as well.
PostgreSQL 8.x has given me the best performance so far...
Wim
