Hi,

my provider informed me, that there is high traffic at the server generated by some scripts:

| 2299516 | web24 | localhost | usr_web24_1 | Query | 19 | Sending

data | SELECT m.id,m.subject,m.catid,m.thread,m.name,m.time,t.message

FROM jos_fb_messages_text as t JOIN j |

| 2299517 | web24 | localhost | usr_web24_1 | Query | 7 | Sending

data | SELECT m.id,m.subject,m.catid,m.thread,m.name,m.time,t.message

FROM jos_fb_messages_text as t JOIN j |

| 2299522 | web24 | localhost | usr_web24_1 | Query | 5 | Sending

data | SELECT m.id,m.subject,m.catid,m.thread,m.name,m.time,t.message

FROM jos_fb_messages_text as t JOIN j |

| 2299523 | web24 | localhost | usr_web24_1 | Query | 3 | Sending

data | SELECT m.id,m.subject,m.catid,m.thread,m.name,m.time,t.message

FROM jos_fb_messages_text as t JOIN j |

| 2299524 | web24 | localhost | usr_web24_1 | Query | 2 | Sending

data | SELECT m.id,m.subject,m.catid,m.thread,m.name,m.time,t.message

FROM jos_fb_messages_text as t JOIN j

I used the Version: Installed version: 1.0.5RC1 | 2008-09-25 | 755 [ Redwood ]
Are there same problems on other servers, too?

Reading messages isn't the issue, but showing threads in a category is. The reason is that FB makes a SQL query which reads through all messages from all threads in current category. So it is makes huge table scans for fb_messages and then groups and sorts the results. If the DB configuration does not have huge space for keeping these queries/tables/tmp_tables in memory, this query is done every time someone loads a page.

There might be some other very slow queries too, but I'm still a new user of FB and haven't looked through all the queries. BTW: Search is also very slow, but rarely used.

Unfortunately it looks like that this cannot be fixed in 1.0.5 because it's in feature freeze (or could it?). FB could be made faster by introducing a new table fb_topics, which contains threadid, last_message, last_post_time and perhaps some other information.

I have a board with over 200 000 messages and because of those poor queries MySQL server is sometimes taking 100-200% CPU load. And these figures are after I've optimized my.cfg and the whole DB is in memory (using 500MB of RAM).

If some of the devs see my post, contact me. I will be implementing this sooner or later with or without help.