Re: Sending several commands simultaneously to PostgreSQL 8.4

On Monday, January 21, 2013, Alexander Farber wrote:

> Hello,
>
> I run a card game written in Perl on
> a CentOS 6.3 / 64 bit + PostgreSQL 8.4.13
> where quite a lot player statistics are written
> to the d/b (score, game results, moves, etc.)
>
> Here a player profile: http://preferans.de/DE11198
>
> The machine has a quad intel + 32 GB RAM.
>
> In poostgresql.conf I set:
>
> max_connections = 100
> shared_buffers = 4096MB
> work_mem = 32MB
> log_min_duration_statement = 10000
>
> I also use pgbouncer (for PHP scripts),
> but my Perl game daemon talks
> directly to /tmp/.s.PGSQL.5432
>

Why not have Perl go through pgbouncer as well?

>
> My game daemon runs in a non-forking loop
> and poll()s TCP sockets to the player machines..
>
> Players complain about my server
> freezing for few seconds sometimes
> and I can see it myself in the game logs -
> when data is sometimes written to d/b
> (and postmaster processes take 90% CPU).
>

Any idea what causes that? Your code only seems to do anything with the
database at the time that someone logs out. Does everyone log out at the
same time?

>
> So my question is:
>
> do I have to program a separate daemon -
> which would be polled via a Unix domain
> socket by my main game daemon and
> which would handle sending SQL commands
> (typically insert's and select's)?
>

One alternative possibility would be to use synchronous_commit=off. This
opens up the possibility that transactions would be lost in the case of a
crash. But you change your code to send off updates and not wait for a
response, as you seem to be proposing, then you are also introducing that
possibility, just implicitly.

Cheers,

Jeff