|
|
| Database: |
Any, MySQL
|
| Participants: |
Dongsheng Cai, Eloy Lafuente (stronk7) and Eric Merrill
|
| Security Level: |
None
|
| Resolved date: |
15/Jun/08
|
| Affected Branches: |
MOODLE_15_STABLE, MOODLE_18_STABLE, MOODLE_19_STABLE
|
| Fixed Branches: |
MOODLE_20_STABLE
|
|
At our school we have seen performance problems with chat (which of course we know), but we have developed a possible fix.
The general problem that we reduced, is that chat logs (history) is kept in the same table as messages from active conversations. So ever refresh, each client is doing a select on the chat table, which contains all chats. In our case that adds up to +400k rows, so even a simple select takes quite some overhead.
Our solution was to split the chat into 2 table. One is the normal 'chat_messages', and the other is 'chat_messages_current'. Clients query against 'chat_messages_current'. When somebody talks, the message is written to both tables. Every cron cycle, any message older than 8 hours is deleted from 'chat_messages_current'. Basically that means that the frequent select will be against a table that will rarely have more then a few hundred messages in it.
For us, this decreased loads by about a factor of 10 on our MySQL server.
-eric
|
|
Description
|
At our school we have seen performance problems with chat (which of course we know), but we have developed a possible fix.
The general problem that we reduced, is that chat logs (history) is kept in the same table as messages from active conversations. So ever refresh, each client is doing a select on the chat table, which contains all chats. In our case that adds up to +400k rows, so even a simple select takes quite some overhead.
Our solution was to split the chat into 2 table. One is the normal 'chat_messages', and the other is 'chat_messages_current'. Clients query against 'chat_messages_current'. When somebody talks, the message is written to both tables. Every cron cycle, any message older than 8 hours is deleted from 'chat_messages_current'. Basically that means that the frequent select will be against a table that will rarely have more then a few hundred messages in it.
For us, this decreased loads by about a factor of 10 on our MySQL server.
-eric |
Show » |
made changes - 12/May/08 09:05 AM
| Field |
Original Value |
New Value |
|
Assignee
|
Martin Dougiamas
[ dougiamas
]
|
Dongsheng Cai
[ dongsheng
]
|
made changes - 12/May/08 09:05 AM
|
Fix Version/s
|
|
2.0
[ 10122
]
|
|
Database
|
[MySQL, Any]
|
[Any, MySQL]
|
made changes - 15/Jun/08 08:31 PM
|
Status
|
Open
[ 1
]
|
Resolved
[ 5
]
|
|
Resolution
|
|
Duplicate
[ 3
]
|
|
Bug
Resolved
Major
Chat module performance, possible fix
Assigning this to Dongsheg to research for Moodle 2.0 (cannot make this for 1.9 because DB aren't recommended in one stable version).
Dongsheng, can you analyse this, at least including:
(not priory for now, fist we must fix other 1.9.x bugs)
Ciao