Hi Paolo,
Thanks again for your help.
Looks like I found some jump here. It did not write 2010-05-17 10:50:01
and 2010-05-17 10:55:01, It jumped to 2010-05-17 11:00:02 in process queue,
but I didnt receive any error message.
pmacct=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query
| query_start | backend_start |
client_addr | client_port
-------+---------+---------+----------+----------+------------------------------+-------------------------------+-------------------------------+-------------+-------------
16384 | pmacct | 19281 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 11:00:02.537012-03 |
127.0.0.1 | 48735
16384 | pmacct | 19209 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:45:01.102775-03 |
127.0.0.1 | 45592
16384 | pmacct | 19120 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:10:01.061321-03 |
127.0.0.1 | 36701
16384 | pmacct | 19132 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:15:01.240846-03 |
127.0.0.1 | 43986
16384 | pmacct | 19142 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:20:02.274897-03 |
127.0.0.1 | 55187
16384 | pmacct | 19154 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:25:01.117171-03 |
127.0.0.1 | 58203
16384 | pmacct | 19164 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:30:01.461328-03 |
127.0.0.1 | 48586
16384 | pmacct | 19174 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:35:01.558699-03 |
127.0.0.1 | 57097
16384 | pmacct | 19285 | 10 | postgres | <IDLE>
| 2010-05-17 11:00:27.241229-03 | 2010-05-17 11:00:08.317005-03 |
| -1
16384 | pmacct | 19197 | 16385 | pmacct | <command string not
enabled> | | 2010-05-17 10:40:01.667021-03 |
127.0.0.1 | 55568
I'm testing this in a XEN HVM with 12G RAM and It is not using too much CPU.
But in dom0, qemu-dm daemon (I/O) is using 70% CPU. This could be the
problem.
I will test it in a physical machine too.
Thanks.
2010/5/14 Paolo Lucente <[email protected]>
> 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.
>
> If there is some loss due to SQL writers queuing, you should verify
> some missing time-bins in your dataset. stamp_inserted is the field
> to check: can you spot any "jumps"?
>
> > Why the process are being accumulated just when my database is about 15G?
> Is
> > it possible that I'm losing data, even with no errors?
>
> Can suggest to verify the basic things, ie. is there enough disk
> space, can you verify shortage of memory, is CPU running 100%, is
> any maintenance scripts locking the SQL table up? But i'm pretty
> sure you have already done all of this. I can essentially see two
> possible reasons:
> * this is related to the data-set itself (too large, too complex,
> etc.); I know you already not make use of UPDATE queries and
> leverage the COPY statement instead. Can suggest to a) optimize
> indexing and b) split data in different tables on, say, an hourly
> basis posed that you are currently doing that more coarse grained,
> ie. daily/weekly/monthly. The b) should be easy enough to test.
> * this is strictly related to PostgreSQL. For example, you restart
> the database (this should drop the writers queued; or indeed you
> can kill them manually); rename the table currently in use to
> something else, create an empty table for pmacct to write to and
> the behaviour persists. In such a case perhaps it's best to turn
> the issue to a PostgreSQL forum.
>
> Cheers,
> Paolo
>
>
--
Sergio Roberto Charpinel Jr.
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
