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