Re: Performance Problem

On Wed, 12 Mar 2003, Stefan Sturm wrote:

> we have a performance problem with PostgreSQL 7.2.4. But first I try to
> explain our situation:
>
> We have an online game. This game is a fantasy game, where you can buy
> and sell players for your team.
> All players for the teams are stored in one table. So in this table we
> have just one primary key and to foreign keys.
> When we have around 500 Users at one time on the maschine, the game is
> slowing down(Time for one spage up to 1minute and more).  And I think
> the problem is this table. There are a lot of reading and writing
> connections to this table.
>
> So now my question: What can I do to increase the performance?

We'll need more information, like what kind of queries you're doing, to
give detailed answers, but as general questions:

Is that 500 simultaneous queries to the database?  What sort of real db
traffic are you seeing?  Are you analyzing frequently enough?  Are you
vacuuming enough?  What are the postgresql.conf settings (specifically
shared_buffers and sort_mem as a starting point)? What kind of query plans
are you getting for your queries (see explain analyze)?

It's possible that if you're doing lots of updates that you may be running
into concurrency problems with the foreign keys, but without knowing the
query mix (and examples) you're running, it's hard to say.