Hi,
Thanks for your supporting.
Just to finish this thread, I have made some changes, and the problem
stopped. I'll report them here:
- I'm using postgresql.conf default from CentOS, and increased
checkpoint_segments.
- I'm using sql_cache_entries: 263167
- rebuild my index and pk ( still analyzing this, because I'm still a little
restricted to some queries to have good performance)
- I'm running analyze in every insert, within the create table function:
CREATE OR REPLACE FUNCTION cria_tab_pmacct(text)
RETURNS void AS $$
DECLARE
myrec RECORD;
BEGIN
SELECT 1 INTO myrec FROM pg_catalog.pg_class WHERE relkind = 'r' AND
relname = $1 AND pg_catalog.pg_table_is_visible(oid) LIMIT 1;
IF NOT FOUND
THEN
EXECUTE 'CREATE TABLE ' || quote_ident($1) || ' (
.....
CONSTRAINT ' || $1 ||'_pk PRIMARY KEY
(stamp_inserted, ip_src, ip_dst, port_src, port_dst, ip_proto)
)';
EXECUTE 'CREATE INDEX ibytes_' || $1 || ' ON ' ||
quote_ident($1) || '(bytes)';
ELSE
EXECUTE 'ANALYZE ' || $1;
END IF;
END;
$$ LANGUAGE plpgsql;
Cheers.
2010/5/20 Karl O. Pinc <[email protected]>
> On 05/14/2010 12:15:27 PM, Paolo Lucente wrote:
> > Hi Sergio,
> >
> > On Fri, May 14, 2010 at 10:58:00AM -0300, Sergio Charpinel Jr. wrote:
> >
> > > I couldnt get any useful information from this command.
> > > I get no erros in postgresql, nfacctd and pmacctd log files.
> >
> > I would expect you to see a "Maximum number of SQL writer processes
> > reached" message in the logs. I will do some checks and let you know.
> >
> > > Data is still being written in DB, but I'm suspecting that some are
> > being
> > > lost. Because my database grew just 5G/day after that.
>
> It occurs to me that some of this may have to do with postgresql
> table vacuuming and the autovacuum process. I've had problems
> in the past when initially loading data into large tables
> before the first vaccuming/analyze was done. It would take
> some time before things stabilized. My technique was to
> "pause" data loading and do a vacuum analyze every now and
> then. This is probably not feasible.
>
> I can imagine there could be situations where data is loaded
> fast enough to trigger autovacuums that don't have enough
> time to finish so you get multiple processes running.
> If you are also deleting data this would explain the database
> size/growth mis-match, because the deleted data has not yet
> been recovered.
>
> It's a thought.
>
> Karl <[email protected]>
> Free Software: "You don't pay back, you pay forward."
> -- Robert A. Heinlein
>
>
--
Sergio Roberto Charpinel Jr.
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
