On Mon, Jun 22, 2009 at 12:06 AM, Scott Marlowescott.marl...@gmail.com wrote:
On Sun, Jun 21, 2009 at 12:28 PM, Peter Albanpeter.alb...@gmail.com wrote:
Hi,
Here is the query :
duration: 2533.734 ms statement:
SNIP
Limit (cost=4313.54..4313.55 rows=3 width=595) (actual
On Tue, Jun 23, 2009 at 10:52 AM, Laurent Labordekerdez...@gmail.com wrote:
On Mon, Jun 22, 2009 at 12:06 AM, Scott Marlowescott.marl...@gmail.com
wrote:
On Sun, Jun 21, 2009 at 12:28 PM, Peter Albanpeter.alb...@gmail.com wrote:
Hi,
Here is the query :
duration: 2533.734 ms statement:
Hi all,
I'm running a quite large website which has its own forums. They are
currently heavily used and I'm getting performance issues. Most of them
are due to repeated UPDATE queries on a flags table.
This flags table has more or less the following fields:
UserID - TopicID - LastReadAnswerID
On 06/23/2009 01:12 PM, Mathieu Nebra wrote:
I'm running a quite large website which has its own forums. They are
currently heavily used and I'm getting performance issues. Most of them
are due to repeated UPDATE queries on a flags table.
This flags table has more or less the following fields:
On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebramate...@siteduzero.com wrote:
This flags table has more or less the following fields:
UserID - TopicID - LastReadAnswerID
We are doing pretty much same thing.
My problem is that everytime a user READS a topic, it UPDATES this flags
table to
On 06/23/2009 02:37 PM, Alexander Staubo wrote:
(That said, I believe PostgreSQL diffs tuple updates, so in practice
PostgreSQL might not be writing anything if you run an update with
the same value. I will let someone more intimate with the internal
details of updates to comment on this.)
No,
Mathieu Nebra wrote:
Hi all,
I'm running a quite large website which has its own forums. They are
currently heavily used and I'm getting performance issues. Most of them
are due to repeated UPDATE queries on a flags table.
This flags table has more or less the following fields:
UserID -
In our application we defer the updates to a separate asynchronous
process using a simple queue mechanism, but in our case, we found that
the updates are fast enough (in the order of a few milliseconds) not
to warrant batching them into single transactions.
We do a very similar trick for
On Tue, 23 Jun 2009, Nikolas Everett wrote:
If you happen to be using Java, HashMap and TreeMap are perfect for this
because they are reentrant so you don't have to worry about
synchronizing your sweeper with your web page activities.
See the note in
On 06/23/2009 01:12 PM, Mathieu Nebra wrote:
I'm running a quite large website which has its own forums. They are
currently heavily used and I'm getting performance issues. Most of
them
are due to repeated UPDATE queries on a flags table.
This flags table has more or less the following
Alexander Staubo a écrit :
On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebramate...@siteduzero.com wrote:
This flags table has more or less the following fields:
UserID - TopicID - LastReadAnswerID
We are doing pretty much same thing.
My problem is that everytime a user READS a topic, it
Which pg version are you using?
I should have mentionned that before sorry: PostgreSQL 8.2
I think there is an awful lot of speculation on this thread about what
your problem is without anywhere near enough investigation. A couple
of seconds for an update is a really long time, unless your
Mathieu Nebra mateo21 'at' siteduzero.com writes:
(That said, I believe PostgreSQL diffs tuple updates, so in practice
PostgreSQL might not be writing anything if you run an update with
the same value. I will let someone more intimate with the internal
details of updates to comment on this.)
On 06/23/2009 04:54 PM, Mathieu Nebra wrote:
On 06/23/2009 01:12 PM, Mathieu Nebra wrote:
I'm running a quite large website which has its own forums.
They are currently heavily used and I'm getting performance
issues. Most of
them
are due to repeated UPDATE queries on a flags table.
This
Which pg version are you using?
I should have mentionned that before sorry: PostgreSQL 8.2
I definitely would consider upgrading to 8.3 - even without any config
changes it might bring quite some improvement.
But mainly it would allow you to use asynchronous commit - which could
possibly
not better just to store last time user visited the topic ? or forum in
general, and compare that ?
On Tue, Jun 23, 2009 at 11:50 AM, Mathieu Nebramate...@siteduzero.com wrote:
Approximately how many requests per second are you servicing? Also,
How can I extract this information from the database? I know how to use
pg_stat_user_tables. My table has:
I was thinking you might look at your
So your update doesn't take long to run during off-peak times, so
basically your options are:
1. Optimize your postgresql.conf settings or upgrade to the latest
version of PostgreSQL.
2. Redesign your forum code so it can scale better.
3. Upgrade your servers hardware as it may be overloaded.
So your update doesn't take long to run during off-peak times, so
basically your options are:
1. Optimize your postgresql.conf settings or upgrade to the latest
version of PostgreSQL.
2. Redesign your forum code so it can scale better.
3. Upgrade your servers hardware as it may be overloaded.
You're holding this behavior to far too strict of a transactional guarantee.
The client software can cache a set of recent views, and sent updates in
bulk every 1 or 2 seconds. Worst case, if your client crashes you lose a
second worth of user metadata updates on last accessed and view counts.
On 6/23/09 7:54 AM, Mathieu Nebra mate...@siteduzero.com wrote:
On 06/23/2009 01:12 PM, Mathieu Nebra wrote:
I'm running a quite large website which has its own forums. They are
currently heavily used and I'm getting performance issues. Most of
them
are due to repeated UPDATE queries on a
All the other comments are accurate, though it does seem like
something the database ought to be able to handle.
The other thing which hasn't been mentioned is that you have a lot of
indexes. Updates require maintaining all those indexes. Are all of
these indexes really necessary? Do you have
Hi there,
Please help me to make a decision on how to manage users.
For some reason it is easier in the project I'm working on to split data
by schemes and assign them to Postgres' users (I mean those created with
CREATE USER) rather than support 'owner' fields referring to a global
users
Greg Stark a écrit :
All the other comments are accurate, though it does seem like
something the database ought to be able to handle.
The other thing which hasn't been mentioned is that you have a lot of
indexes. Updates require maintaining all those indexes. Are all of
these indexes really
On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebramate...@siteduzero.com wrote:
The flags table keeps track of every topic a member has visited and
remembers the last answer which was posted at this moment. It allows the
user to come back a few days after and immediately jump to the last
answer he
Mathieu Nebra wrote:
Greg Stark a écrit :
All the other comments are accurate, though it does seem like
something the database ought to be able to handle.
The other thing which hasn't been mentioned is that you have a lot of
indexes. Updates require maintaining all those indexes. Are all of
On Tue, Jun 23, 2009 at 9:04 PM, Mathieu Nebramate...@siteduzero.com wrote:
We have indexes on them, so we can SELECT every topic WHERE the user has
written. Is it the good way of doing this?
I'm kind of skeptical that a simple index on userid,topic isn't
sufficient to handle this case. But you
27 matches
Mail list logo
