>>
>>> 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.
>>>
>>
>> A f
Craig James a écrit :
> 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 ma
Mathieu Nebra wrote:
Alexander Staubo a écrit :
On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra 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 RE
On Tue, Jun 23, 2009 at 9:04 PM, Mathieu Nebra 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 would have to test i
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
the
On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra 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 has not read.
I f
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 r
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 rout
On 6/23/09 7:54 AM, "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 "flag
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.
Th
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.
I
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.
I
On Tue, Jun 23, 2009 at 11:50 AM, Mathieu Nebra 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 httpd logs. Not su
Robert Haas a écrit :
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
not better just to store last time user visited the topic ? or forum in
general, and compare that ?
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 co
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 "f
Mathieu Nebra 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.)
>>
>> Secondly, a
>> > 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 yo
Alexander Staubo a écrit :
> On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra 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 thi
> 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 mor
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 http://java.sun.com/javase/6/docs/api/java/
>
> 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 f
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 - 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,
On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra 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 remember he has read
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 field
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 - LastReadAnswerI
28 matches
Mail list logo