Welcome, Guest. Please Login or Register.
March 29, 2024, 08:34:34 AM
Home Help Search Log in Register
News: SMF is the next generation in forum software, almost completely re-written from the ground up, make sure you don't fall for cheap imitations that suffer from feature bloat!

YaBB SE Community  |  English User Help  |  FAQ  |  Large message boards and MySQL logs of slow response « previous next »
Pages: [1] 2 3 ... 10 Reply Ignore Print
Author Topic: Large message boards and MySQL logs of slow response  (Read 248218 times)
Ralph Button (rbutton)
Noobie
*
Posts: 12


Just a guy that is still in love with his wife!

rbutton1 WWW
Large message boards and MySQL logs of slow response
« on: April 10, 2002, 05:42:06 PM »
Reply with quote

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?
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #1 on: April 10, 2002, 07:54:33 PM »
Reply with quote

the querie structure will be changed and indexes added

medi
« Last Edit: April 10, 2002, 07:57:59 PM by mediman » Logged

mainComm Dev Team
Ralph Button (rbutton)
Noobie
*
Posts: 12


Just a guy that is still in love with his wife!

rbutton1 WWW
Re:Large message boards and MySQL logs of slow response
« Reply #2 on: April 11, 2002, 03:58:47 PM »
Reply with quote

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

Logged
beneluxrail
Noobie
*
Posts: 17


Re:Large message boards and MySQL logs of slow response
« Reply #3 on: April 11, 2002, 06:01:02 PM »
Reply with quote

Quote from: mediman on April 10, 2002, 07:54:33 PMthe 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.    :(
Logged
andrea
Global Moderator
YaBB God
*****
Posts: 4400


Peace on Earth

WWW
Re:Large message boards and MySQL logs of slow response
« Reply #4 on: April 11, 2002, 07:47:26 PM »
Reply with quote

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...
Logged

beneluxrail
Noobie
*
Posts: 17


Re:Large message boards and MySQL logs of slow response
« Reply #5 on: April 11, 2002, 07:51:52 PM »
Reply with quote

Maybe a stupid question, but how do you do that? I do have phpmysql avalibe.
Logged
Hypocrite
YaBB God
*****
Posts: 692


For To End Yet Again

dimmuh@gmx.net WWW
Re:Large message boards and MySQL logs of slow response
« Reply #6 on: April 11, 2002, 07:54:57 PM »
Reply with quote

I'm also interested about how this is done and what should I backup before doing it to avoid anything getting broken.
« Last Edit: April 11, 2002, 07:55:27 PM by Hypocrite » Logged
andrea
Global Moderator
YaBB God
*****
Posts: 4400


Peace on Earth

WWW
Re:Large message boards and MySQL logs of slow response
« Reply #7 on: April 11, 2002, 07:56:51 PM »
Reply with quote

Quote from: beneluxrail on April 11, 2002, 07:51:52 PMMaybe 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));
Logged

andrea
Global Moderator
YaBB God
*****
Posts: 4400


Peace on Earth

WWW
Re:Large message boards and MySQL logs of slow response
« Reply #8 on: April 11, 2002, 07:58:30 PM »
Reply with quote

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.
« Last Edit: April 11, 2002, 07:59:54 PM by andrea » Logged

beneluxrail
Noobie
*
Posts: 17


Re:Large message boards and MySQL logs of slow response
« Reply #9 on: April 11, 2002, 08:03:54 PM »
Reply with quote

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??)
Logged
andrea
Global Moderator
YaBB God
*****
Posts: 4400


Peace on Earth

WWW
Re:Large message boards and MySQL logs of slow response
« Reply #10 on: April 11, 2002, 08:07:27 PM »
Reply with quote

Quote from: Hypocrite on April 11, 2002, 07:54:57 PMI'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?
« Last Edit: April 11, 2002, 08:11:01 PM by andrea » Logged

AnalogDuck
Jr. Member
**
Posts: 54


I'm a Ducky! Quack quack...

ICQ - 131723443analogduck@hotmail.com WWW
Re:Large message boards and MySQL logs of slow response
« Reply #11 on: April 11, 2002, 08:07:54 PM »
Reply with quote

Good job!  Awesome tip!  Thank you Andrea!!   ;D
Logged

-- What does an "analog duck" sound like anyway?  That's the kind of noises I want to make... ;)
andrea
Global Moderator
YaBB God
*****
Posts: 4400


Peace on Earth

WWW
Re:Large message boards and MySQL logs of slow response
« Reply #12 on: April 11, 2002, 08:12:40 PM »
Reply with quote

rbutton is owing the thanks!
Logged

AnalogDuck
Jr. Member
**
Posts: 54


I'm a Ducky! Quack quack...

ICQ - 131723443analogduck@hotmail.com WWW
Re:Large message boards and MySQL logs of slow response
« Reply #13 on: April 11, 2002, 08:45:40 PM »
Reply with quote

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!  :D
Logged

-- What does an "analog duck" sound like anyway?  That's the kind of noises I want to make... ;)
Hypocrite
YaBB God
*****
Posts: 692


For To End Yet Again

dimmuh@gmx.net WWW
Re:Large message boards and MySQL logs of slow response
« Reply #14 on: April 11, 2002, 09:02:02 PM »
Reply with quote

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.
Logged
Pages: [1] 2 3 ... 10 Reply Ignore Print 
YaBB SE Community  |  English User Help  |  FAQ  |  Large message boards and MySQL logs of slow response « previous - next »
 


Powered by MySQL Powered by PHP YaBB SE Community | Powered by YaBB SE
© 2001-2003, YaBB SE Dev Team. All Rights Reserved.
SMF 2.1.4 © 2023, Simple Machines
Valid XHTML 1.0! Valid CSS

Page created in 0.028 seconds with 19 queries.