Large message boards and MySQL logs of slow response

[Ralph Button (rbutton)]

Have others noticed that on large message boards the indexes do not seem to reflect the way data is selected?  Inside MySql the logs for slow queries show a large number of queries that are bogging the system down.  Some pages take over 45 seconds to open and the problem is just in the queries not the actual screen template.  It's sitting waiting for data to be returned from the database.

I'm currently using the explain plan against the queries to clean up the joins the sql does.  As I do response gets slowly better.  Just that before I redo all the indexes was wondering if anyone else has already done this?

[mediman]

the querie structure will be changed and indexes added

medi

[Ralph Button (rbutton)]

I have created the following indexes and find that I have a 7X improvment in response on the forum!!!!!
Also MySQL no longer has listed 8,000 slow queries PER DAY - I'm now down to 2 queries in the slow query log.

alter table topics add key (isSticky);
alter table members add key(memberGroup(30));
alter table members add key(posts);
alter table members add key(memberName(30));
alter table members add key(dateRegistered);
alter table members add key(lastLogin);
alter table messages add key(posterTime);
alter table instant_messages add key (ID_MEMBER_FROM);
alter table instant_messages add key (ID_MEMBER_TO);
alter table instant_messages add key (deletedBy);
alter table instant_messages add key(fromName(30));
alter table instant_messages add key(toName(30));
alter table categories add key (catOrder);
alter table boards add key (ID_CAT);
alter table log_boards add key (ID_BOARD);
alter table log_boards add key (memberName(30));
alter table log_topics add key (membername(30));
alter table log_topics add key (logTime);
alter table log_topics add key (ID_TOPIC);
alter table log_activity add key (month);
alter table log_activity add key (day);
alter table log_activity add key (year);
alter table log_activity add key (mostOn);
alter table log_errors add key (logTime);
alter table log_errors add key (memberName(30));
alter table log_floodcontrol add key (ip(20));
alter table log_floodcontrol add key (logTime);
alter table log_karma add key (ID_TARGET);
alter table log_karma add key (ID_EXECUTOR);
alter table log_karma add key(logTime);
alter table log_mark_read add key (memberName(30));
alter table log_mark_read add key (ID_BOARD);
alter table log_mark_read add key (logTime);
alter table log_online add key (logTime);
alter table settings add key(variable(30));


I also have done the following memory settings
set-variable = max_connections=500
set-variable = key_buffer=64M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=8M
set-variable = record_buffer=4M
set-variable = sort_buffer=8M
set-variable = table_cache=256
set-variable = thread_cache_size=256

[beneluxrail]

the querie structure will be changed and indexes added

medi

When will that be released? I do have the same problem. Its so fu*&(* slow now its almost imposible to work with since the upgrade to 1.3.0.    

[andrea]

I just inserted the above indices into my test board. My board has only
130 members
and less than 2000 posts.

The difference is extrem! The board is much faster now. The boardindex is loading in zero time, which was not before. I always felt there must be something wrong. Not only the boardindex, many other screens load much faster: IM inbox and outbox, board settings, thread overview in a board, return to thread overview after the entry of a new post...

[beneluxrail]

Maybe a stupid question, but how do you do that? I do have phpmysql avalibe.

[Hypocrite]

I'm also interested about how this is done and what should I backup before doing it to avoid anything getting broken.

[andrea]

Maybe a stupid question, but how do you do that? I do have phpmysql avalibe.

Enter phpMyAdmin and enter the query above into the "Run SQL query" field (with copy & paste).

You need to add the db prefix into the query, I am using "yabbse_" prefix. So the query which I did run is:


alter table yabbse_topics add key (isSticky);
alter table yabbse_members add key(memberGroup(30));
alter table yabbse_members add key(posts);
alter table yabbse_members add key(memberName(30));
alter table yabbse_members add key(dateRegistered);
alter table yabbse_members add key(lastLogin);
alter table yabbse_messages add key(posterTime);
alter table yabbse_instant_messages add key (ID_MEMBER_FROM);
alter table yabbse_instant_messages add key (ID_MEMBER_TO);
alter table yabbse_instant_messages add key (deletedBy);
alter table yabbse_instant_messages add key(fromName(30));
alter table yabbse_instant_messages add key(toName(30));
alter table yabbse_categories add key (catOrder);
alter table yabbse_boards add key (ID_CAT);
alter table yabbse_log_boards add key (ID_BOARD);
alter table yabbse_log_boards add key (memberName(30));
alter table yabbse_log_topics add key (membername(30));
alter table yabbse_log_topics add key (logTime);
alter table yabbse_log_topics add key (ID_TOPIC);
alter table yabbse_log_activity add key (month);
alter table yabbse_log_activity add key (day);
alter table yabbse_log_activity add key (year);
alter table yabbse_log_activity add key (mostOn);
alter table yabbse_log_errors add key (logTime);
alter table yabbse_log_errors add key (memberName(30));
alter table yabbse_log_floodcontrol add key (ip(20));
alter table yabbse_log_floodcontrol add key (logTime);
alter table yabbse_log_karma add key (ID_TARGET);
alter table yabbse_log_karma add key (ID_EXECUTOR);
alter table yabbse_log_karma add key(logTime);
alter table yabbse_log_mark_read add key (memberName(30));
alter table yabbse_log_mark_read add key (ID_BOARD);
alter table yabbse_log_mark_read add key (logTime);
alter table yabbse_log_online add key (logTime);
alter table yabbse_settings add key(variable(30));

[andrea]

Important note: if your board is busy, you should put into maintenance mode first, *before* you add the indices. And save dump data and structure of course, also before.

[beneluxrail]

Wow way to go!!! This did do the job!!! Perfect!! (Why was this not in the 1.3.0 final release, or am i saying something sily now??)

[andrea]

I'm also interested about how this is done and what should I backup before doing it to avoid anything getting broken.

If you do not feel sure about backup then don't run this query. A short tutorial about db save/restore will be posted soon in the documentation board. Here is the link to the documentation board.

Me personally did run the query in my test board only, not in my life board. The official YaBB SE board does not have this db structure at the moment. And there stays the question: what happens with future board upgrades if the db structure is different?

[AnalogDuck]

Good job!  Awesome tip!  Thank you Andrea!!  

[andrea]

rbutton is owing the thanks!

[AnalogDuck]

Oops!  Am I bad!

Yeah, I meant to give props to rbutton:  Great work!  You've saved everyone much misery!

All the same, you were quite helpful as well Andrea!  

[Hypocrite]

I think I'll wait for maybe Jeff or Joseph to comment on this one before going to work Because I don't have a test board where to play with this But it sounds tempting.