Hello Jakub, thank you for your words about the project and for the data about your acutal issue. The increased number of processes is basically a queue of writers that wait to obtain the permission to "use" the DB. This is because the use of the DB is protect by LOCKs. A new writer joins the queue each 'sql_refresh_time' seconds.
When everything works correctly, a writer is finished with the DB before the next one starts. What i've not been able to understand is whether the *same* configuration causes the *same* problem under MySQL. 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"). 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 ? From your configuration i see you have enabled the 'sql_optimize_clauses'; did you setup an INDEX on the tables in order to speed up the queries ? Does the EXPLAIN confirm that the queries are using such index ? You can try wrapping a query from your strace report. If anyone has hints or any better question to focalize the problem from the PostgreSQL side, please submit them here ! In the end, i see that you are using an extremely big 'sql_refresh_time'. Try reducing it, say to 60-120 secs; and commenti out the 'sql_startup_delay' directive. Let me know how things get evolved ! Cheers, Paolo
