From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Sending several commands simultaneously to PostgreSQL 8.4 |
Date: | 2013-01-21 12:57:49 |
Message-ID: | CAADeyWjoM583u8mrtjveURpLY-iimc4Mhr8og63mHUCuXj_uqQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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).
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)?
Or does such a generic daemon exist already?
Or can multiple commands be sent to
the PostgreSQL simultaneously?
Here is how my Perl daemon talks to to
the d/b usually (this happens inside of the
poll() loop and thus stops everything for ms):
sub logout($) {
my $user = shift;
my $id = $user->{ID};
eval {
my $dbh = DBI->connect_cached(DSN, DBUSER, DBPASS, {
AutoCommit => 1,
PrintWarn => 1,
PrintError => 1,
RaiseError => 1,
FetchHashKeyName => 'NAME_lc',
pg_enable_utf8 => 1
});
if ($user->{SCORE}) {
my $sth_money = $dbh->prepare_cached(
q{select pref_update_money(?, ?)});
$sth_money->execute($id, $user->{SCORE});
$user->{SCORE} = 0;
}
my $sth_logout = $dbh->prepare_cached(
q{update pref_users set logout=now() where id=?});
$sth_logout->execute($id);
};
warn $@ if $@;
}
Thanks for any ideas
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Ernst | 2013-01-21 14:19:53 | Re: pg_Restore |
Previous Message | Edson Richter | 2013-01-21 12:38:15 | What is impact of "varchar_opts"? |